In this article, we are going to see how to store JSON data into MySQL database using PHP through the XAMPP server.
Requirements: XAMPP Server
Introduction: PHP is an open-source scripting language used to connect with databases and servers. MySQL is a database query language that is used to manage the data in the database to communicate with PHP. JSON stands for JavaScript object notation which stores data in narrow format.
Structure:
[{
"data1": "value1",
"data2": "value2",
. . .
"datan": "value n"
}]
Â
Example: The following is the content for student.json file.
[
{
"name": "sravan kumar",
"gender": "Male",
"subject": "Software engineering"
},
{
"name": "sudheer",
"gender": "Male",
"subject": "deep learning"
},
{
"name": "radha",
"gender": "Female",
"subject": "Software engineering"
},
{
"name": "vani",
"gender": "Female",
"subject": "Software engineering"
}
]
- MySQL INSERT statement: It is used to insert data into the database.
Syntax:
INSERT INTO table_name(col1, col2, …, col n)
VALUES(value1, value2, …, value n);
Used Methods:
json_decode() Method: This function is used to decode or convert a JSON object to a PHP object.
Syntax:
json_decode(string, assoc)
- The string is the JSON data and the assoc is the boolean value.
- If assoc is true, data is converted into an associative array.
- false is the default value.
Approach:
- Start XAMPP server
- Create a table named student under database test.
- Write PHP code to insert JSON data into student table.
Steps:
- Start XAMPP server.
- Create a table named student under test database.
Â
- Create JSON data and save it as college_subjects.json under xampp-htdocs folder.
[
{
"name": "sravan kumar",
"gender": "Male",
"subject": "Software engineering"
},
{
"name": "sudheer",
"gender": "Male",
"subject": "deep learning"
},
{
"name": "radha",
"gender": "Female",
"subject": "Software engineering"
},
{
"name": "vani",
"gender": "Female",
"subject": "Software engineering"
}
]
PHP code: The following is the PHP code to store JSON data and display it on the web page.
PHP
<!DOCTYPE html> <html>   <head>     <script src=     </script>       <link rel="stylesheet" href=       <script src=     </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">             Geeks for Geeks Import JSON             data into database         </h3><br />                   <?php                       // Server name => localhost             // Username => root             // Password => empty             // Database name => test             // Passing these 4 parameters             $connect = mysqli_connect("localhost", "root", "", "test");                           $query = '';             $table_data = '';                         // json file name             $filename = "college_subjects.json";                         // Read the JSON file in PHP             $data = file_get_contents($filename);                         // Convert the JSON String into PHP Array             $array = json_decode($data, true);                         // Extracting row by row             foreach($array as $row) {                   // Database query to insert data                 // into database Make Multiple                 // Insert Query                 $query .=                 "INSERT INTO student VALUES                 ('".$row["name"]."', '".$row["gender"]."',                 '".$row["subject"]."'); ";                                $table_data .= '                 <tr>                     <td>'.$row["name"].'</td>                     <td>'.$row["gender"].'</td>                     <td>'.$row["subject"].'</td>                 </tr>                 '; // Data for display on Web page             }               if(mysqli_multi_query($connect, $query)) {                 echo '<h3>Inserted JSON Data</h3><br />';                 echo '                 <table class="table table-bordered">                 <tr>                     <th width="45%">Name</th>                     <th width="10%">Gender</th>                     <th width="45%">Subject</th>                 </tr>                 ';                 echo $table_data;                  echo '</table>';             }           ?>         <br />     </div> </body>   </html> |
Output:
- Open browser and type localhost/base.php
DB table
- View the inserted data in the table.
Execution Video:
