Use PHP json_decode function to parse json object and insert into MySQL Database

On 12/09/2014

Hi, in this post we'll see how to parse json object, extract the data and push it into MySQL Database with PHP. As a web developer, we often come across situations to deal with API's and manipulate the output data. And those API's output will usually be in json format. PHP has a function named json_decode to parse json object and return them in an array format, which we can easily extract and process further.

This is our sample json object which we want to parse,

$json_obj = '{
          "empid": "M01AH",
          "personal": {
               "name":"Ricky Jones",
               "gender": "Male"
          },
          "profile": {
               "designation": "VP",
               "department": "Marketing"
          }
     }';

As we can see, it contains employee details and follows a nested array pattern. Now we want to extract all the elements and push it into a MySQL Database table, say "tbl_employee". So we'll first use json_decode function to convert this to a stdclass object and store each of the elements into variables. Below is the PHP code to do this.

<?php
//convert to stdclass object
$emp_data = json_decode($json_obj);

//store the element values into variables
$id = $emp_data->empid;
$name = $emp_data->personal->name;
$gender = $emp_data->personal->gender;
$designation = $emp_data->profile->designation;
$department = $emp_data->profile->department;
?>

Next we'll insert the extracted values into the MySQL Database table with this code,

<?php
//insert values into mysql database table
$sql="INSERT INTO tbl_employee (empid, empname, gender, designation, department) 
VALUES ('$id', '$name', '$gender', '$designation', '$department')";

if(!mysql_query($sql,$con)) //$con is mysql connection object
{
 die('Error : ' . mysql_error());
}
?>

That's it. We've successfully parsed a json object and inserted those values into MySQL table.

Here is the full PHP code for doing this,

<?php
$json_obj = '{
          "empid": "M01AH",
          "personal": {
               "name":"Ricky Jones",
               "gender": "Male"
          },
          "profile": {
               "designation": "VP",
               "department": "Marketing"
          }
     }';

//convert to stdclass object
$emp_data = json_decode($json_obj);

//store the element values into variables
$id = $emp_data->empid;
$name = $emp_data->personal->name;
$gender = $emp_data->personal->gender;
$designation = $emp_data->profile->designation;
$department = $emp_data->profile->department;

//insert values into mysql database
$sql="INSERT INTO tbl_employee (empid, empname, gender, designation, department) 
VALUES ('$id', '$name', '$gender', '$designation', '$department')";

if(!mysql_query($sql,$con)) //$con is mysql connection object
{
     die('Error : ' . mysql_error());
}
?>

Convert json into Associative Array

We can also make json_decode function to return an associative array by using,

json_decode($json_obj, true)

Recommended Read: How to Insert JSON File into MySQL using PHP

Recommended Read: How to use PHP PREG MATCH function for Form Validation

The second parameter is optional and it should be set as "true" to return an associative array. If it is omitted, then the function will return stdclass object, which is what we have done in our example.

Hope this tutorial had given you a basic idea of parsing json in PHP. Have any feedback? Please comment below. And don't forget to subscribe to our RSS feed to get notified about our tutorials.

5 comments:

  1. hi,your blog is very helpful to us...click here to See codeigniter insert into database

    ReplyDelete
  2. I cannot get this to work

    Here we go " . $data['id'];

    //get the stops details
    $id = $data['id'];
    $name = $data['name'];
    $description = $data['description'];
    $lat = $data['lat'];
    $lon = $data['lon'];
    $buddy = $data['buddy'];

    //insert into mysql table
    $sql = "INSERT INTO new_stops(id, name, description, lat, lon, buddy)
    VALUES('$id', '$name', '$description', '$lat', '$lon', '$buddy')";
    if(!mysql_query($sql,$con))
    {
    die('Error : ' . mysql_error());
    }
    ?>
    I know it is loading the JSON string but the database table only gets one row added with all values being 0 or empty.

    ReplyDelete
    Replies
    1. I could see you are trying to access the data from an array. In that case just make sure you use json_decode() function to convert into array like this,

      $data = json_decode($jsondata, true);

      adding 'true' as second param will convert the json object to associative array.

      Please check this article http://www.kodingmadesimple.com/2014/12/how-to-insert-json-data-into-mysql-php.html

      Delete
  3. how to insert multiple json records into the database?

    ReplyDelete
    Replies
    1. Hi, if json contains multiple records, you have to loop through the array like this,

      $emp_data = json_decode($json_obj, true);

      foreach($emp_data as $row)
      {
      //get the employee details
      $id = $row['empid'];

      ...

      //insert into db
      mysql_query($sql,$con);
      }

      Hope this helps.
      Cheers.

      Delete

Contact Form

Name

Email *

Message *