JSON stands for JavaScript Object Notation which is used to transmit the structured data over the network (server and web applications). It is possible to load/store JSON in your XAMPP server by converting it into a PHP array or object.  In this guide, we will discuss how to import JSON from a file and JSON string into the MySQL XAMPP server.

Loading Format

We need to provide the JSON string /JSON file as in the following format so that the JSON data is inserted into the database without any errors/exceptions:

Here, “Name” is the column name in the table and “Value” refers to the row value with respect to the column.

Json_decode()

As we already discussed, it is not possible to load JSON directly into the MySQL database. It has to be converted into a PHP object like array. So, this function is used to decode the given JSON string into a PHP object.

Syntax:

 
The first parameter refers to the JSON string. If you want to convert the JSON string into an associative array, you can set the second parameter to True. Otherwise, it returns an object. It is the default one.

File_get_contents()

We can use this function only if the JSON data exists in the file. The file_get_contents() function is used to read a file into string.

Syntax:

 
The file/path specifies the filename or path where the file is located.

Start is an optional parameter that specifies the position to start reading.

The max_length is also an optional parameter that specifies the maximum length of JSON data to read. If it is not specified, it reads until the End-of-File (EOF).

Mysqli_multi_query()

We need to perform multiple INSERT query which insert the JSON data into the MySQL database. For this, we use the mysqli_multi_query() function. This function is used to perform multiple queries against the specified MySQL database.

Syntax:

 
The first parameter takes the XAMPP connection and one or more queries. Here, it is insert which is separated with a semicolon that is specified as the second parameter.

Steps: Importing JSON to MySQL

In the PHP script, create the connection using the mysqli() function by specifying the following parameters.

Parameter 1: Server name, Parameter 2: User name, Parameter 3: password and Parameter 4: Database Name.

 
Decode the JSON string to a PHP array object.

JSON File:

 
JSON String:

 
Use the “foreach” loop to iterate the decoded_json_array and insert one by one row into the table.

 
Perform the previous multiple INSERT query using the mysqli_multi_query() function.

Database Snapshot

Let’s create a database named “linuxhint”. Create a table named “job_details” with five columns that are shown in the following with data types:

Scenario 1: PHP Script – Import JSON into MySQL from the File

Let’s see the PHP script that imports the JSON data (from the file) into MySQL by implementing the previously discussed steps.

Consider the following JSON data that exists in the “contracts.json” file:

 
Create the PHP script with following code and save it under the “xampp/htdocs” folder as json_import.php. Place the previous JSON file also under this path.

 
Output:

Open the browser and type the “http://localhost/json_import.php” URL that executes the previous script.

We can see that the records are inserted into the “job_details” table. Let’s open the table and verify that the records are inserted or not.

Scenario 2: PHP Script – Import the JSON String into MySQL

Let’s see the PHP script that imports the JSON data into MySQL.

 
Output:

Open the browser and type the “http://localhost/json_import.php” URL that executes the previous script.

We can see that these two records are inserted into the “job_details” table. Let’s open the table and verify that the records are inserted or not.

Conclusion

Now, we are able to import the JSON data or from a file into MySQL XAMPP with the PHP script. This script utilized mainly three functions that we discussed with syntax and parameters. It is important to create the database and table structure to import JSON into the MySQL table. As part of this guide, we used only one “job_details” MySQL table and imported a JSON file and JSON string into this table.