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:
