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.
<!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: