JSON stands for JavaScript Object Notation which is used to transmit the structured information over the community (server and internet functions). It’s doable to load/retailer JSON in your XAMPP server by changing it right into a PHP array or object. On this information, we are going to focus on the right way to import JSON from a file and JSON string into the MySQL XAMPP server.
Loading Format
We have to present the JSON string /JSON file as within the following format in order that the JSON information is inserted into the database with none errors/exceptions:
Right here, “Identify” is the column identify within the desk and “Worth” refers back to the row worth with respect to the column.
Json_decode()
As we already mentioned, it’s not doable to load JSON instantly into the MySQL database. It must be transformed right into a PHP object like array. So, this perform is used to decode the given JSON string right into a PHP object.
Syntax:
json_decode(json_string, assoc=true/false)
The primary parameter refers back to the JSON string. If you wish to convert the JSON string into an associative array, you possibly can set the second parameter to True. In any other case, it returns an object. It’s the default one.
File_get_contents()
We are able to use this perform provided that the JSON information exists within the file. The file_get_contents() perform is used to learn a file into string.
Syntax:
file_get_contents(file/path, begin, max_length)
The file/path specifies the filename or path the place the file is positioned.
Begin is an elective parameter that specifies the place to start out studying.
The max_length can be an elective parameter that specifies the utmost size of JSON information to learn. If it’s not specified, it reads till the Finish-of-File (EOF).
Mysqli_multi_query()
We have to carry out a number of INSERT question which insert the JSON information into the MySQL database. For this, we use the mysqli_multi_query() perform. This perform is used to carry out a number of queries in opposition to the required MySQL database.
Syntax:
mysqli_multi_query(creating_connection_xampp, query_statement)
The primary parameter takes the XAMPP connection and a number of queries. Right here, it’s insert which is separated with a semicolon that’s specified because the second parameter.
Steps: Importing JSON to MySQL
Within the PHP script, create the connection utilizing the mysqli() perform by specifying the next parameters.
Parameter 1: Server identify, Parameter 2: Person identify, Parameter 3: password and Parameter 4: Database Identify.
$creating_connection_xampp=new mysqli(“localhost”, “root”, “”, “Database_name”);
Decode the JSON string to a PHP array object.
JSON File:
json_decode(file_get_contents(“file_name.json”), true)
JSON String:
json_decode(json_string, true)
Use the “foreach” loop to iterate the decoded_json_array and insert one after the other row into the desk.
foreach($decoded_json_array as $row) {
// Insert question
$insert_statement.= “INSERT INTO table_name VALUES (‘”.$row[“Column1”].“‘, ‘”.$row[“Column2”].“‘,…); “;
}
Carry out the earlier a number of INSERT question utilizing the mysqli_multi_query() perform.
mysqli_multi_query($creating_connection_xampp, $insert_statement);
Database Snapshot
Let’s create a database named “linuxhint”. Create a desk named “job_details” with 5 columns which might be proven within the following with information sorts:
Situation 1: PHP Script – Import JSON into MySQL from the File
Let’s see the PHP script that imports the JSON information (from the file) into MySQL by implementing the beforehand mentioned steps.
Contemplate the next JSON information that exists within the “contracts.json” file:
[
{
“Contract”: “Salesforce Developer”,
“Experience”:10,
“Type”: “Technical”,
“Location”: “USA” ,
“Role”: “Full-Time”
},
{
“Contract”: “JavaDeveloper”,
“Experience”:20,
“Type”: “Technical”,
“Location”: “UK” ,
“Role”: “Part-Time”
},
{
“Contract”: “Analyst”,
“Experience”:2,
“Type”: “Business”,
“Location”: “India” ,
“Role”: “Full-Time”
},
{
“Contract”: “Sales-person”,
“Experience”:0,
“Type”: “Sales”,
“Location”: “UK” ,
“Role”: “Part-Time”
},
{
“Contract”: “Marketing”,
“Experience”:5,
“Type”: “Marketing”,
“Location”: “India” ,
“Role”: “Part-Time”
}
]
Create the PHP script with following code and put it aside underneath the “xampp/htdocs” folder as json_import.php. Place the earlier JSON file additionally underneath this path.
// Parameter 1: Server identify
// Parameter 2: Person identify
// Parameter 3: password
// Parameter 4: Database Identify
// database identify is Linuxhint
$creating_connection_xampp = new mysqli(‘localhost’, ‘root’, ”, ‘LinuxHint’);
// Convert contracts.json to PHP Array
$decoded_json_array = json_decode(file_get_contents(“contracts.json”), true);
$insert_statement = ”;
// Use foreach loop to iterate the decoded_json_array and insert one after the other row into the desk
foreach($decoded_json_array as $i) {
// Insert question
$insert_statement.= “INSERT INTO job_details VALUES (‘”.$i[“Contract”].“‘, ‘”.$i[“Experience”].“‘,'”.$i[“Type”].“‘, ‘”.$i[“Location”].“‘,'”.$i[“Role”].“‘); “;
}
$completed = mysqli_multi_query($creating_connection_xampp, $insert_statement);
if($completed){
echo “<middle><h1>Profiles – Inserted</h1> </middle>”;
}
?>
Output:
Open the browser and sort the “http://localhost/json_import.php” URL that executes the earlier script.
We are able to see that the information are inserted into the “job_details” desk. Let’s open the desk and confirm that the information are inserted or not.
Situation 2: PHP Script – Import the JSON String into MySQL
Let’s see the PHP script that imports the JSON information into MySQL.
<?php
// Parameter 1: Server identify
// Parameter 2: Person identify
// Parameter 3: password
// Parameter 4: Database Identify
// database identify is Linuxhint
$creating_connection_xampp = new mysqli(‘localhost’, ‘root’, ”, ‘LinuxHint’);
// Convert contracts.json to PHP Array
$decoded_json_array = json_decode(‘[
{
“Contract”: “Android Developer”,
“Experience”:11,
“Type”: “Technical”,
“Location”: “USA” ,
“Role”: “Part-Time”
},
{
“Contract”: “CEO”,
“Experience”:45,
“Type”: “Alll”,
“Location”: “India” ,
“Role”: “Full-Time”
}
]’, true);
$insert_statement = ”;
// Use foreach loop to iterate the decoded_json_array and insert one after the other row into the desk
foreach($decoded_json_array as $i) {
// Insert question
$insert_statement.= “INSERT INTO job_details VALUES (‘”.$i[“Contract”].“‘, ‘”.$i[“Experience”].“‘,'”.$i[“Type”].“‘, ‘”.$i[“Location”].“‘,'”.$i[“Role”].“‘); “;
}
$completed = mysqli_multi_query($creating_connection_xampp, $insert_statement);
if($completed){
echo “<middle><h1>Profiles – Inserted</h1> </middle>”;
}
?>
Output:
Open the browser and sort the “http://localhost/json_import.php” URL that executes the earlier script.
We are able to see that these two information are inserted into the “job_details” desk. Let’s open the desk and confirm that the information are inserted or not.
Conclusion
Now, we’re in a position to import the JSON information or from a file into MySQL XAMPP with the PHP script. This script utilized primarily three features that we mentioned with syntax and parameters. You will need to create the database and desk construction to import JSON into the MySQL desk. As a part of this information, we used just one “job_details” MySQL desk and imported a JSON file and JSON string into this desk.
