How to Insert JSON Data into MySQL using PHP

PHP


In this tutorial I will show you How to Insert JSON Data into MySQL using PHP. To convert JSON to MySQL using PHP includes some steps like how to read JSON file, convert json to array and insert that json array into MySQL database. Here are the steps to Insert JSON data into MySQL using PHP.

Step 1: Read the JSON file in PHP

We have to read the JSON file and store its contents in a PHP variable. As PHP supports the function file_get_contents() which will read the entire file and returns it as a string.


//read the json file contents
$studentjsondata = file_get_contents('students.json');

Here “students.json” is the JSON file name we want to read.

Step 2: Convert JSON String into PHP Array

Now we have to convert the JSON string to PHP associative array. Again we use the PHP json decode function which decodes JSON string into PHP array.


//convert json object to php associative array
$data = json_decode($studentjsondata, true);

The first parameter $studentjsondata contains the JSON file contents. The second parameter true will convert the string into PHP associative array.

Step 3: Extract the Array Values in Variables

Now we need to parse the PHP array and store them into PHP variables.

//get the student details
$id = $data['studentId'];
$name = $data['personal']['name'];
$age = $data['personal']['age'];
$streetaddress = $data['personal']['address']['streetaddress'];
$city = $data['personal']['address']['city'];
$state = $data['personal']['address']['state'];
$postalcode = $data['personal']['address']['postalcode'];

Step 4: Insert JSON to MySQL Database with PHP Code

Using the above steps, we have extracted all the values from the JSON file. Now we will insert the extracted JSON object values into the MySQL table using below query.


//insert into mysql table
$sql = "INSERT INTO tbl_students(studentId, name, age, streetaddress, city, state, postalcode)
    VALUES('$id', '$name', '$age', '$streetaddress', '$city', '$state', '$postalcode')";
if(!mysqli_query($con, $sql))
{
    die('Error : ' . mysql_error());
}

Now we have successfully imported JSON data into MySQL database.

students.json

{
    "studentId": "ST001",
    "personal": {
        "name": "John Smith",
        "age": "29",
        "address": {
            "streetaddress": "5 14th Street",
            "city": "New York",
            "state": "NY",
            "postalcode": "12548"
        }
    }
}

Related Tutorials