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

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.