How to Insert JSON Data into MySQL using PHP

What is JSON File Format?

JSON file contains information stored in JSON format and has the extension of "*.json". JSON stands for JavaScript Object Notation and is a light weight data exchange format. Being less cluttered and more readable than XML, it has become an easy alternative format to store and exchange data. All modern browsers supports JSON format.

Example of a JSON File

Do you want to know how a JSON file looks like? Well here is the sample.

As you can see by yourself, the JSON format is very human readable and the above file contains some employee details. I'm going to use this file as an example for this tutorial and show you how to insert this JSON object into MySQL database in PHP step by step.

Step 1: Connect PHP to MySQL Database

As the first and foremost step we have to connect PHP to the MySQL database in order to insert JSON data into MySQL DB. For that we use mysql_connect() function to connect PHP with MySQL.

<?php
    $con = mysql_connect("username","password","") or die('Could not connect: ' . mysql_error());
    mysql_select_db("employee", $con);
?>

Here "employee" is the MySQL Database name we want to store the JSON object. Learn more about using mysqli library for php and mysql database connection here.

Step 2: Read the JSON file in PHP

Next we have to read the JSON file and store its contents to a PHP variable. But how to read json file in php? Well! PHP supports the function file_get_contents() which will read an entire file and returns it as a string. Let’s use it to read our JSON file.

<?php
    //read the json file contents
    $jsondata = file_get_contents('empdetails.json');
?>

Here "empdetails.json" is the JSON file name we want to read.

Step 3: Convert JSON String into PHP Array

The next step for us is to convert json to array. Which is likely we have to convert the JSON string we got from the above step to PHP associative array. Again we use the PHP json decode function which decodes JSON string into PHP array.

<?php
    //convert json object to php associative array
    $data = json_decode($jsondata, true);
?>

The first parameter $jsondata contains the JSON file contents.

The second parameter true will convert the string into php associative array.

Step 4: Extract the Array Values

Next we have to parse the above JSON array element one by one and store them into PHP variables.

<?php
    //get the employee details
    $id = $data['empid'];
    $name = $data['personal']['name'];
    $gender = $data['personal']['gender'];
    $age = $data['personal']['age'];
    $streetaddress = $data['personal']['address']['streetaddress'];
    $city = $data['personal']['address']['city'];
    $state = $data['personal']['address']['state'];
    $postalcode = $data['personal']['address']['postalcode'];
    $designation = $data['profile']['designation'];
    $department = $data['profile']['department'];
?>

Step 5: Insert JSON to MySQL Database with PHP Code

Using the above steps, we have extracted all the values from the JSON file. Finally let's insert the extracted JSON object values into the MySQL table.

<?php
    //insert into mysql table
    $sql = "INSERT INTO tbl_emp(empid, empname, gender, age, streetaddress, city, state, postalcode, designation, department)
    VALUES('$id', '$name', '$gender', '$age', '$streetaddress', '$city', '$state', '$postalcode', '$designation', '$department')";
    if(!mysql_query($sql,$con))
    {
        die('Error : ' . mysql_error());
    }
?>

We are done!!! Now we have successfully imported JSON data into MySQL database.

Here is the complete php code snippet I have used to insert JSON to MySQL using PHP.

<?php
    //connect to mysql db
    $con = mysql_connect("username","password","") or die('Could not connect: ' . mysql_error());
    //connect to the employee database
    mysql_select_db("employee", $con);

    //read the json file contents
    $jsondata = file_get_contents('empdetails.json');
    
    //convert json object to php associative array
    $data = json_decode($jsondata, true);
    
    //get the employee details
    $id = $data['empid'];
    $name = $data['personal']['name'];
    $gender = $data['personal']['gender'];
    $age = $data['personal']['age'];
    $streetaddress = $data['personal']['address']['streetaddress'];
    $city = $data['personal']['address']['city'];
    $state = $data['personal']['address']['state'];
    $postalcode = $data['personal']['address']['postalcode'];
    $designation = $data['profile']['designation'];
    $department = $data['profile']['department'];
    
    //insert into mysql table
    $sql = "INSERT INTO tbl_emp(empid, empname, gender, age, streetaddress, city, state, postalcode, designation, department)
    VALUES('$id', '$name', '$gender', '$age', '$streetaddress', '$city', '$state', '$postalcode', '$designation', '$department')";
    if(!mysql_query($sql,$con))
    {
        die('Error : ' . mysql_error());
    }
?>