How to insert JSON data into MySQL database using PHP

  •  
  •  
  •  
  •  
  •  
  •  

In this tutorial, I will show you how to insert JSON data into MySQL database using PHP. In JSON file we can store huge amount of data in an array format. So that, sometimes you need to store these JSON data into the database. Please follow the below simple PHP script which will guide you about how to insert JSON data into MySQL database using PHP.

Also, you can read – How to get data from MySQL database in JSON format using Ajax and PHP.

How to insert JSON data into MySQL database using PHP

Database Configuration (db.php)

<?php
define('HOSTNAME','localhost');
define('DB_USERNAME','DATABASE-USERNAME');
define('DB_PASSWORD','DATABASE-PASSWORD');
define('DB_NAME', 'DATABASE-NAME');

$con = mysqli_connect(HOSTNAME, DB_USERNAME, DB_PASSWORD, DB_NAME) or die ("error");
// Check connection
if(mysqli_connect_errno($con))	echo "Failed to connect MySQL: " .mysqli_connect_error();
?>

Change database name, username, and password according to your credentials.

Database Table

CREATE TABLE `students` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `f_name` varchar(255) NOT NULL,
  `l_name` varchar(255) NOT NULL,
  `class` varchar(10) NOT NULL,
  `section` varchar(3) NOT NULL,
  `roll` varchar(3) NOT NULL,
  PRIMARY KEY(`id`)
)

JSON Data (student_data.json)

[
  {
    "f_name" : "Mitrajit",
    "l_name" : "Samanta",
    "class"  : "XII",
    "section": "A",
    "roll"   : "1"
  },

  {
    "f_name" : "Anil",
    "l_name" : "Roy",
    "class"  : "XI",
    "section": "A",
    "roll"   : "3"
  },

  {
    "f_name" : "Niharika",
    "l_name" : "Gupta",
    "class"  : "XI",
    "section": "B",
    "roll"   : "5"
  },

  {
    "f_name" : "Tanima",
    "l_name" : "Chakraborty",
    "class"  : "XII",
    "section": "A",
    "roll"   : "9"
  }
]

Some sample JSON data on which we will perform our test.

PHP – Insert JSON Data into MySQL Database (index.php)

<?php
$query = '';
$table_data = '<table border="1" class="table">';
//Read the JSON file and stored in a variable
$data = file_get_contents('student_data.json'); 
//Convert JSON string into PHP array format
$data_array = json_decode($data, true); 

foreach($data_array as $row) {
    //Build multiple insert query 
    $query .= "INSERT INTO `students` set 
          `f_name`  = '".$row['f_name']."',
          `l_name`  = '".$row['l_name']."', 
          `class`   = '".$row["class"]."',
          `section` = '".$row["section"]."', 
          `roll`    = '".$row["roll"]."';";

          	
    $table_data .= '
                 <tr>
                   <td>'.$row["f_name"].'</td>
                   <td>'.$row["l_name"].'</td>
                   <td>'.$row["class"].'</td>
                   <td>'.$row["section"].'</td>
                   <td>'.$row["roll"].'</td>
                 </tr>';
}

$table_data .= '</table>';

//Execute Mutliple query
if(mysqli_multi_query($con, $query)) {
   $msg = $table_data;
} else {
   $msg = "Something wrong! Please try again.";
}

mysqli_close($con);



<div class="container-fluid">    
    <div class="row">    
       <div class="col-md-12"><h6>Inserted JSON data</h6></div>    
    </div> 
    <div class="row">    
       <div class="col-md-12"><?php echo $msg; ?></div>    
    </div>    
</div>
?>

Insert JSON data into MySQL database is very simple.  The file_get_contents() function reads the contents from the student_data.json file and stores it in a variable as a string. json_decode() function converts these JSON encoded data into PHP array variable. The mysqli_multi_query() function executes multiple queries at once. Once mysqli_multi_query() executed and data are inserted into MySQL database, the data will display in a tabular format.

Complete Source Code – How to insert JSON data into MySQL Database

You can download the complete source code from the below Download link. Please like and share this tutorial with others.

About Mitrajit

6 comments on “How to insert JSON data into MySQL database using PHP

  1. Hello. Can you tell me why maximym json is 4 items

    [
    {
    1
    },
    {
    2
    },
    {
    3
    },
    {
    4
    }
    ]

    If I try 5 or 6 or more it’s didnt add 🙁

    Thank you!

  2. how to add this to database with above method

    {“kind”:”track”,”id”:503593338,”created_at”:”2018/09/22 16:20:45 +0000″,”user_id”:216359930,”duration”:227730,”commentable”:true,”state”:”finished”,”original_content_size”:40159364,”last_modified”:”2018/09/22 16:20:45 +0000″,”sharing”:”public”,”tag_list”:””,”permalink”:”you-got-the-love”,”streamable”:true,”embeddable_by”:”all”,”downloadable”:false,”purchase_url”:null,”label_id”:null,”purchase_title”:null,”genre”:””,”title”:”You Got The Love”,”description”:””,”label_name”:null,”release”:null,”track_type”:null,”key_signature”:null,”isrc”:null,”video_url”:null,”bpm”:null,”release_year”:null,”release_month”:null,”release_day”:null,”original_format”:”wav”,”license”:”all-rights-reserved”,”uri”:”https://api.soundcloud.com/tracks/503593338″,”user”:{“id”:216359930,”kind”:”user”,”permalink”:”jimi-turner”,”username”:”Jimi Turner”,”last_modified”:”2016/04/02 12:16:24 +0000″,”uri”:”https://api.soundcloud.com/users/216359930″,”permalink_url”:”http://soundcloud.com/jimi-turner”,”avatar_url”:”https://i1.sndcdn.com/avatars-000214946472-kk60wo-large.jpg”},”attachments_uri”:”https://api.soundcloud.com/tracks/503593338/attachments”,”permalink_url”:”http://soundcloud.com/jimi-turner/you-got-the-love”,”artwork_url”:null,”waveform_url”:”https://w1.sndcdn.com/YVNVaXezFP8H_m.png”,”stream_url”:”https://api.soundcloud.com/tracks/503593338/stream”,”playback_count”:0,”download_count”:0,”favoritings_count”:0,”comment_count”:0,”likes_count”:0,”reposts_count”:0,”policy”:”ALLOW”,”monetization_model”:”NOT_APPLICABLE”}

Leave a Reply

Your email address will not be published. Required fields are marked *

Prove you are a human *