How to Insert JSON data into MySQL database using PHP? (original) (raw)
Last Updated : 31 May, 2024
To insert JSON data into MySQL database using PHP, use the **json_decode function in PHP to convert JSON object into an array that can be inserted into the database.
Here, we are going to see **how to insert JSON data into MySQL database **using PHP through the **XAMPP server in a step-by-step way.
**JSON Structure:
[{
"data1": "value1",
"data2": "value2",
. . .
"datan": "value n"
}]
**Example: The following is the content for the **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"
}
]
Insert JSON data into MySQL database using PHP
Use the **json_decode() Method to decode or convert a **JSON object to a PHP object.
**Syntax:
The json_decode function syntax is:
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 studentunder 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"
}
]
The following is the PHP code to store JSON data and display it on the web page.
PHP `
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/css/bootstrap.min.css" />
<script src=<style>
.box {
width: 750px;
padding: 20px;
background-color: #fff;
border: 1px solid #ccc;
border-radius: 5px;
margin-top: 100px;
}
</style>Geeks for Geeks Import JSON data into database
<?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
<span class="katex"><span class="katex-mathml"><math xmlns="http://www.w3.org/1998/Math/MathML"><semantics><mrow><mi>d</mi><mi>a</mi><mi>t</mi><mi>a</mi><mo>=</mo><mi>f</mi><mi>i</mi><mi>l</mi><msub><mi>e</mi><mi>g</mi></msub><mi>e</mi><msub><mi>t</mi><mi>c</mi></msub><mi>o</mi><mi>n</mi><mi>t</mi><mi>e</mi><mi>n</mi><mi>t</mi><mi>s</mi><mo stretchy="false">(</mo></mrow><annotation encoding="application/x-tex">data = file_get_contents(</annotation></semantics></math></span><span class="katex-html" aria-hidden="true"><span class="base"><span class="strut" style="height:0.6944em;"></span><span class="mord mathnormal">d</span><span class="mord mathnormal">a</span><span class="mord mathnormal">t</span><span class="mord mathnormal">a</span><span class="mspace" style="margin-right:0.2778em;"></span><span class="mrel">=</span><span class="mspace" style="margin-right:0.2778em;"></span></span><span class="base"><span class="strut" style="height:1.0361em;vertical-align:-0.2861em;"></span><span class="mord mathnormal" style="margin-right:0.10764em;">f</span><span class="mord mathnormal">i</span><span class="mord mathnormal" style="margin-right:0.01968em;">l</span><span class="mord"><span class="mord mathnormal">e</span><span class="msupsub"><span class="vlist-t vlist-t2"><span class="vlist-r"><span class="vlist" style="height:0.1514em;"><span style="top:-2.55em;margin-left:0em;margin-right:0.05em;"><span class="pstrut" style="height:2.7em;"></span><span class="sizing reset-size6 size3 mtight"><span class="mord mathnormal mtight" style="margin-right:0.03588em;">g</span></span></span></span><span class="vlist-s"></span></span><span class="vlist-r"><span class="vlist" style="height:0.2861em;"><span></span></span></span></span></span></span><span class="mord mathnormal">e</span><span class="mord"><span class="mord mathnormal">t</span><span class="msupsub"><span class="vlist-t vlist-t2"><span class="vlist-r"><span class="vlist" style="height:0.1514em;"><span style="top:-2.55em;margin-left:0em;margin-right:0.05em;"><span class="pstrut" style="height:2.7em;"></span><span class="sizing reset-size6 size3 mtight"><span class="mord mathnormal mtight">c</span></span></span></span><span class="vlist-s"></span></span><span class="vlist-r"><span class="vlist" style="height:0.15em;"><span></span></span></span></span></span></span><span class="mord mathnormal">o</span><span class="mord mathnormal">n</span><span class="mord mathnormal">t</span><span class="mord mathnormal">e</span><span class="mord mathnormal">n</span><span class="mord mathnormal">t</span><span class="mord mathnormal">s</span><span class="mopen">(</span></span></span></span>filename);
// Convert the JSON String into PHP Array
<span class="katex"><span class="katex-mathml"><math xmlns="http://www.w3.org/1998/Math/MathML"><semantics><mrow><mi>a</mi><mi>r</mi><mi>r</mi><mi>a</mi><mi>y</mi><mo>=</mo><mi>j</mi><mi>s</mi><mi>o</mi><msub><mi>n</mi><mi>d</mi></msub><mi>e</mi><mi>c</mi><mi>o</mi><mi>d</mi><mi>e</mi><mo stretchy="false">(</mo></mrow><annotation encoding="application/x-tex">array = json_decode(</annotation></semantics></math></span><span class="katex-html" aria-hidden="true"><span class="base"><span class="strut" style="height:0.625em;vertical-align:-0.1944em;"></span><span class="mord mathnormal">a</span><span class="mord mathnormal" style="margin-right:0.02778em;">rr</span><span class="mord mathnormal">a</span><span class="mord mathnormal" style="margin-right:0.03588em;">y</span><span class="mspace" style="margin-right:0.2778em;"></span><span class="mrel">=</span><span class="mspace" style="margin-right:0.2778em;"></span></span><span class="base"><span class="strut" style="height:1em;vertical-align:-0.25em;"></span><span class="mord mathnormal" style="margin-right:0.05724em;">j</span><span class="mord mathnormal">so</span><span class="mord"><span class="mord mathnormal">n</span><span class="msupsub"><span class="vlist-t vlist-t2"><span class="vlist-r"><span class="vlist" style="height:0.3361em;"><span style="top:-2.55em;margin-left:0em;margin-right:0.05em;"><span class="pstrut" style="height:2.7em;"></span><span class="sizing reset-size6 size3 mtight"><span class="mord mathnormal mtight">d</span></span></span></span><span class="vlist-s"></span></span><span class="vlist-r"><span class="vlist" style="height:0.15em;"><span></span></span></span></span></span></span><span class="mord mathnormal">eco</span><span class="mord mathnormal">d</span><span class="mord mathnormal">e</span><span class="mopen">(</span></span></span></span>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>`
**Output:
Open browser and type _localhost/base.php
DB table
View the inserted data in the table.
**Video Tutorial
Look at this video tutorial, where we have explained how to insert JSON data into MySQL database using PHP.