How to Insert Multiple JSON Data into MySQL Database in PHP

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.

Also Read:

• How to Export MySQL Database to JSON File using PHP
• How to Check if JSON String is Valid or Not using PHP

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. I hope you like this post. If you like it, please share it in your social circle.