How to Insert Multiple JSON Data into MySQL Database in PHP

On 10/10/2015

Hi! I have discussed about inserting json into mysql using php a while back. And I got several queries from readers about inserting multiple json objects into DB using the exact method. Inserting multiple objects involves multiple database transactions. There are less elegant ways to do this but using mysqli library’s Prepared Statement is the effective and secure way for this type of job. Prepared Statements has several advantages like utilizing fewer resources, preventing sql injection etc. I’ll show you here with an example, how to use mysqli prepared statement to insert multiple json data into mysql database in php.

Create MySQL Database & Table

First we need to create mysql database required for our example. Run this below sql command to create it.

CREATE DATABASE `employee`;
USE `employee`;
CREATE TABLE IF NOT EXISTS `emp` (
  `id` int(8) NOT NULL AUTO_INCREMENT,
  `name` varchar(30) NOT NULL,
  `gender` varchar(10) NOT NULL,
  `designation` varchar(30) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1;

Sample JSON File with Multiple Objects

We also need a json file to use for our example. Below is the file ‘empdata.json’ with multiple json objects containing some employee details.

File Name: empdata.json

[
    {
        "name": "Michael Bruce",
        "gender": "Male",
        "designation": "System Architect"
    },
    {
        "name": "Jennifer Winters",
        "gender": "Female",
        "designation": "Senior Programmer"
    },
    {
        "name": "Donna Fox",
        "gender": "Female",
        "designation": "Office Manager"
    },
    {
        "name": "Howard Hatfield",
        "gender": "Male",
        "designation": "Customer Support"
    }
]

We have to read through the above json file and insert the objects (i.e., employee details) one by one into mysql database.

Inserting Multiple JSON Data into MySQL Database in PHP

This is the php code snippet for inserting multiple json objects into mysql database.

<?php
    // open mysql connection
    $host = "localhost";
    $username = "mysql_username";
    $password = "mysql_password";
    $dbname = "employee";
    $con = mysqli_connect($host, $username, $password, $dbname) or die('Error in Connecting: ' . mysqli_error($con));

    // use prepare statement for insert query
    $st = mysqli_prepare($con, 'INSERT INTO emp(name, gender, designation) VALUES (?, ?, ?)');

    // bind variables to insert query params
    mysqli_stmt_bind_param($st, 'sss', $name, $gender, $designation);

    // read json file
    $filename = 'empdata.json';
    $json = file_get_contents($filename);   

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

    // loop through the array
    foreach ($data as $row) {
        // get the employee details
        $name = $row['name'];
        $gender = $row['gender'];
        $designation = $row['designation'];
        
        // execute insert query
        mysqli_stmt_execute($st);
    }
    
    //close connection
    mysqli_close($con);
?>

As you can see, first we connect to the mysql database using the statement mysqli_connect().

Next we use prepare statement mysqli_prepare() to prepare the insert query. Since we have to do multiple insertions into the database, preparing the query is quite effective and consumes less database resources.

The ? (question mark) in the INSERT query indicates that there are three parameters to be inserted into the database.

Next using the statement mysqli_stmt_bind_param(), we bind the insert query parameters to the upcoming variables that hold the json data. The function’s second param, ‘sss’ resembles the data type of the parameters. ‘s’ stands for string values. (Check here for rest of the type options)

Next we read the json file and convert the data into php associative array. The method file_get_contents($filename); returns the contents of the file into a variable. And this in turn is converted into array using json_decode() function.

Since there are multiple json data to be inserted into the db, we have to loop through the array using foreach statement. And store the employee details into variables and insert them into database using the mysqli_stmt_execute($st); statement. This will execute the insert query we have prepared earlier.

Once the database insertion is complete, we have to release the database handle we created at the beginning. The statement mysqli_close($con); will close the opened mysql database connection and empty the handle.

That was all about inserting multiple json data into mysql database using php. You can run the above given php snippet and it will insert the json file contents into the database.

Also Read:

15 comments:

  1. mysqli_stmt_bind_param($st, 'sss', $name, $gender, $designation);

    what is 'sss' stand for?

    ReplyDelete
    Replies
    1. 's' stands for string data type of the given parameter. Since we have 3 params and all of them ($name, $gender, $designation) are of string type, we have used 'sss'.

      Cheers.

      Delete
  2. mysqli_prepare() expects parameter 1 to be mysqli, null given

    ReplyDelete
    Replies
    1. Hi! What do you mean by null given? '$con' (mysqli handler) is the first param provided in the mysqli_prepare() statement above.

      Delete
  3. Valli Pandy, thanx for good job)

    Could you help me with one question? How can i get and decode information from several .json files? They have the simmilar structure. What kind of transformation needs code?

    ReplyDelete
    Replies
    1. Hi! If you have all your json files in a folder, then you can read through the files one by one like this.

      $jdir = scandir($dirpath);
      foreach($jdir as $filename)
      {
          // read json file
          $json = file_get_contents($filename);
          ...
          
          // execute insert query
          mysqli_stmt_execute($st);
      }
      ...

      Hope this helps!
      Cheers.

      Delete
  4. Hi Valli Pandy!
    Thx for the nice tutorial! I already managed to insert single json object to my db. Could you tell me how to handle multiple json objects when the json tree is more complex? The data I am working with has the following structure:

    $animal = $data['_embedded']['Options']['0']['_embedded']['animal']['_links'][Code];

    the next object would be:

    $animal = $data['_embedded']['Options']['1']['_embedded']['animal']['_links'][Code];

    etc.

    How can I loop through such data? Thank you very much for your support!

    ReplyDelete
    Replies
    1. You have to loop through the array. Something like this,

      foreach ($data as $row)
          $animal = $row['_embedded']['animal']['_links'][Code];

      Delete
  5. hi
    can you write a tutorial on updating the above provided mysql database with dynamic json using php.
    i.e add new value but if value exist in database update it.
    thanks.
    great tutorial
    Am a newbie.

    ReplyDelete
  6. hi
    can you write a tutorial on updating the above provided mysql database with dynamic json using php.
    i.e add new value but if value exist in database update it.
    thanks.
    great tutorial
    Am a newbie.

    ReplyDelete
    Replies
    1. Yep! I can do it coming days! And glad you liked it:)

      Delete
  7. This comment has been removed by the author.

    ReplyDelete
  8. This comment has been removed by the author.

    ReplyDelete
  9. Hi, great tutorial! I am getting odd behavior though. Only the first letter of each value in my json object is being inserted into my database - the rest of the value is truncated. Any idea what might cause this? Thanks!

    ReplyDelete
  10. Hey, im trying to learn how to insert json data into my database, ive copied this code to test run it but nothing is being inserted. is there a specific way to run this php code for it to be inserted?

    ReplyDelete

Contact Form

Name

Email *

Message *