Wednesday 20 April 2016

Get Multiple JSON Data & Insert into Mysql Database in PHP



In this PHP tutorial I show you how to get json data from file and insert into mysql table. If suppose friends you have lots of data into json format data, so I have make one simple php script that get data from json file and then after it will insert into database table. Following are the steps in PHP Script for Import JSON file data into Mysql Database table.

1. First we have make database connection in PHP Script by using mysqli_connect() function.
2. Second we have read JSON data file in PHP Code by using file_get_contents() function.
3. Third We have convert JSON string to PHP Array by using json_decode() function in our PHP Script.
4. Fourth We have extract PHP Array data by using foreach loop.
5. Fifth in foreach loop we have make multiple Insert query and append into one variable.
6. Then after we have run all Insert query by using mysqli_multi_query() function.

So, this way we have simple way to get data from JSON file and imported into Mysql Database. We have convert JSON data to Msyql by using PHP by follow above steps and you will understand the concept like how to read json file and how to convert json file data to array and how to insert multiple json array data into mysql database. This all things we have discuss in this post.




Source Code


index.php


<html>  
      <head>  
           <title>Webslesson Tutorial</title> 
           <script src="https://ajax.googleapis.com/ajax/libs/jquery/2.2.0/jquery.min.js"></script>
     <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/css/bootstrap.min.css" />
     <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/js/bootstrap.min.js"></script>
     <style>
   
   .box
   {
    width:750px;
    padding:20px;
    background-color:#fff;
    border:1px solid #ccc;
    border-radius:5px;
    margin-top:100px;
   }
  </style>
      </head>  
      <body>  
        <div class="container box">
          <h3 align="center">Import JSON File Data into Mysql Database in PHP</h3><br />
          <?php
          $connect = mysqli_connect("localhost", "root", "", "test"); //Connect PHP to MySQL Database
          $query = '';
          $table_data = '';
          $filename = "employee_data.json";
          $data = file_get_contents($filename); //Read the JSON file in PHP
          $array = json_decode($data, true); //Convert JSON String into PHP Array
          foreach($array as $row) //Extract the Array Values by using Foreach Loop
          {
           $query .= "INSERT INTO tbl_employee(name, gender, designation) VALUES ('".$row["name"]."', '".$row["gender"]."', '".$row["designation"]."'); ";  // Make Multiple Insert Query 
           $table_data .= '
            <tr>
       <td>'.$row["name"].'</td>
       <td>'.$row["gender"].'</td>
       <td>'.$row["designation"].'</td>
      </tr>
           '; //Data for display on Web page
          }
          if(mysqli_multi_query($connect, $query)) //Run Mutliple Insert Query
    {
     echo '<h3>Imported JSON Data</h3><br />';
     echo '
      <table class="table table-bordered">
        <tr>
         <th width="45%">Name</th>
         <th width="10%">Gender</th>
         <th width="45%">Designation</th>
        </tr>
     ';
     echo $table_data;  
     echo '</table>';
          }




          ?>
     <br />
         </div>  
      </body>  
 </html>  
 

employee_data.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"  
   },  
   {  
     "name": "Kevin Sanders",  
     "gender": "Male",  
     "designation": "Food service worker"  
   },
   {  
     "name": "Fay K. Whitney",  
     "gender": "Female",  
     "designation": "Bookbinder"  
   },
   {  
     "name": "Heather Hernandez",  
     "gender": "Female",  
     "designation": "Corporate recruiter"  
   },
   {  
     "name": "Rhonda Okelley",  
     "gender": "Female",  
     "designation": "Image designer"  
   },
   {  
     "name": "Sandy Jacobsen",  
     "gender": "Male",  
     "designation": "Publicity agent"  
   },
   {  
     "name": "Marisa Williams",  
     "gender": "Female",  
     "designation": "Molder"  
   },
   {  
     "name": "Neil Simmons",  
     "gender": "Male",  
     "designation": "Engine and other machine assembler"  
   },
   {  
     "name": "Raymond Lara",  
     "gender": "Male",  
     "designation": "Clerical assistant"  
   },
   {  
     "name": "William Carpenter",  
     "gender": "Male",  
     "designation": "Broker"  
   },
   {  
     "name": "Ted Baer",  
     "gender": "Male",  
     "designation": "Conservation worker"  
   }
 ]

tbl_employee


--  
 -- Table structure for table `tbl_employee`  
 --  
 CREATE TABLE IF NOT EXISTS `tbl_employee` (  
  `id` int(11) NOT NULL AUTO_INCREMENT,  
  `name` varchar(50) NOT NULL,  
  `gender` varchar(10) NOT NULL,  
  `designation` varchar(30) NOT NULL,  
  PRIMARY KEY (`id`)  
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;  
 --  
 -- Dumping data for table `tbl_employee`  
 --

7 comments:

  1. it's doesnot work, it just insert last record

    ReplyDelete
  2. Thank you. And how to make a search on such a table?

    ReplyDelete
  3. You can easily insert the records using prepared statements... Thanks! very nice Lesson...

    ReplyDelete
  4. Works for me, how do I set it so I only add 1 time or a button that adds it to the database?

    ReplyDelete