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
- View the inserted data in the table.
Execution Video: