Free Online Courses for Software Developers - MrBool
× Please, log in to give us a feedback. Click here to login
×

You must be logged to download. Click here to login

×

MrBool is totally free and you can help us to help the Developers Community around the world

Yes, I'd like to help the MrBool and the Developers Community before download

No, I'd like to download without make the donation

×

MrBool is totally free and you can help us to help the Developers Community around the world

Yes, I'd like to help the MrBool and the Developers Community before download

No, I'd like to download without make the donation

How to Insert/Retrieve JSON Data to/from Database using PHP

This article explains how to insert data containing in JSON file to a Database and then to retrieve back the data from database and store in a JSON file.

In this article first of all I will put light on the introduction of JSON and then explain how to insert JSON data present in JSON file to a database and then the vice versa of the same i.e. retreiving back the data from database and to store it in a JSON file using PHP. Let’s start with the introduction to JSON:

JSON

JSON basically stands for Javascript Object Notation. JSON is quite simple, easy and light-weight format for exchanging and storing data from and to database. It is an alternative for XML (Extensible Markup Language). The data in JSON format is stored in JSON file with an extension of “.json”. the data present in JSON file is supported by all the present modern internet exploring browsers.

Syntax of JSON

The syntax of JSON is given below in the following example:

Listing 1. studJson.json

{
    "stdID": "0106",
    "stdData":{
        "stdName":"Mirwaise",
        "stdAge":"23",
        "stdGender":"Male",
        "stdNo":"12345",
        "stdAddress":{
            "stdStreet":"786 Street",
            "stdCity":"Riyadh",
            "stdCountry":"Saudi Arabia",
            "stdPostal":"98765"
        }
    },
    "stdEdu":{
        "stdDept":"Computer Science",
        "stdSemester":"8",
        "stdMajor":"Web Programming"
    }
} 

The above JOSN code will be used as source JSON file for the whole upcoming article contents. The next step is to show how to insert data present in JSON file to a database using PHP. I am using MySQL Database.

Inserting data presnt in JSON file to a Database through PHP

The method of inserting JSON data in a Database using PHP needs few steps to keep in mind through which it becomes easy for the learners to insert JSON format data into database.

Step 1: Creating Database and Connecting Database (MySQL) with PHP

First of all create a database (“jsondb” in my case) alongwith a table (“stdtable”). The number of columns should not be less than the required fields in the JSON file. In my case, I have created a table with the required fields as shown below:

Figure 1. stdtable Table along with columns.

Once you have created a database having table with the required fields, the next step is to connect that database with the PHP file. For this, a PHP file is to be made, which is given as:

Listing 2.jsonCon.php

<?php
    //connect and select the database
     $connect = mysql_connect("localhost","root","") or die('Database Not Connected. Please Fix the Issue! ' . mysql_error());
    mysql_select_db("jsondb", $connect);
    
     // get the contents of the JSON file 
     $jsonCont = file_get_contents('studJson.json');
    
     //decode JSON data to PHP array
     $content = json_decode($jsonCont, true);
    
     //Fetch the details of Student
     $std_id = $content['stdID'];
     $std_name = $content['stdData']['stdName'];
     $std_age = $content['stdData']['stdAge'];
     $std_gender = $content['stdData']['stdGender'];
     $std_no = $content['stdData']['stdNo'];
     $std_street = $content['stdData']['stdAddress']['stdStreet'];
     $std_city = $content['stdData']['stdAddress']['stdCity'];
     $std_country = $content['stdData']['stdAddress']['stdCountry'];
     $std_postal = $content['stdData']['stdAddress']['stdPostal'];
     $std_dept = $content['stdEdu']['stdDept'];
     $std_sem = $content['stdEdu']['stdSemester'];
     $std_major = $content['stdEdu']['stdMajor'];
    
    //Insert the fetched Data into Database
    $query = "INSERT INTO stdtable(std_id, std_name, std_age, std_gender, std_num, std_street, std_city, std_country, std_postal, std_dept, std_semstr, std_major)
    VALUES('$std_id', '$std_name', '$std_age', '$std_gender', '$std_no', '$std_street', '$std_city', '$std_country', '$std_postal', '$std_dept', '$std_sem', '$std_major')";
    
     if(!mysql_query($query,$connect))
    {
        die('Error : Query Not Executed. Please Fix the Issue!  ' . mysql_error());
    }
     else{
            echo "Data Inserted Successully!!!";
     }
?> 

In the above code, first of all a database connection is established using PHP function “mysql_connect()”. Then after connection, a database is selected using PHP built-in function “mysql_select_db()”. Once connection is made and a database is selected, then you can go further for the next operation, otherwise an error will be displayed for fixing the issues.

Step 2: Getting the Contents of JSON file

Now to get the contents of the JSON file, a PHP function “file_get_contents()” is used which contains the JSON file which you are trying to use and containing the data to be stored in database. This function reads the contents of the given file to a string. In the above code, the “studJson.json” depicts the JSON file whose data is to be read.

Step 3: Decode JSON data to PHP Associative Array

Once the JSON file data is read and available in the form of string, then it becomes easy to decode JSON data into array. For this, the PHP built-in function “json_decode()” function is used which converts the data present in string format to PHP associative array. In the above code, the “$jsonCont” variable is having the string contents of the provided JSON file which is to be decoded.

Step 4: Fetching the decoded values

The next step is to fetch the decoded values and store it in variables of PHP. This can be done by fetching one-by-one and storing it in different variables.

Step 5: Inserting the fetched JSON Data to into database

Finally, the fetched JSON data is inserted into the database using MySQL query. It is important to note that the names of table columns in the database should be written correctly as present in the database otherwise the data will not be inserted in the database.

Once you are done with the above defined steps, then there should not be any problem in inserting JSON data in database. Run the above code and you will get the output as follow:

Figure 2. Output of Inserting data to a Database.

Refresh your database and you will see that data is inserted successfully. The output for the database will be as follow:

Figure 3. Output of Inserted data in a Database.

You can see in the above snapshot that the data is inserted properly in the relevant columns. If you want to add more data in the columns of the table, change the data in the JSON file or add another JSON file with different name and run the code again. Let’s do it for another JSON file. I simply add another JSON file (studJson2.json) in the file_get_contents('studJson.json') function. The data present in “studJson2.json” file is given as:

Listing 3. studJson2.json

{
    "stdID": "0110",
    "stdData":{
        "stdName":"Nisar",
        "stdAge":"25",
        "stdGender":"Male",
        "stdNo":"78600",
        "stdAddress":{
            "stdStreet":"123 Street",
            "stdCity":"Swat",
            "stdCountry":"Canada",
            "stdPostal":"55511"
        }
    },
    "stdEdu":{
        "stdDept":"Computer Engineering",
        "stdSemester":"7",
        "stdMajor":"Software Engineering"
    }
} 

Run again and now you will get an output as follow:

Figure 4. Output of Inserted second data in a Database.

The next step is the reverse of the above i.e. retrieving data from database and storing in JSON file through PHP.

Retrieving data from Database and storing in JSON file through PHP

In this part, we are going to extract the data that is being stored in database and put in the provided JSON file. This process is among the most widely used processes by web developers now a day. For this to start, you should have a database created with proper table and columns as discussed above. This process also needs few steps to be kept in mind for making it convenient for the successful occurrence of such process. Let's start with defining each step one by one:

Step 1: Connect database with PHP

As defined above, the first and foremost task is to connect your database with the PHP. I am using the same database and same database connection as discussed above. After proper connection, the next steps will take place otherwise an error message will be displayed. The code for connection is as given brlow:

Listing 4.jsonRev.php

<?php
    //Step No. 1: Connect database with PHP
     $connect = mysql_connect("localhost","root","") or die('Database Not Connected. Please Fix the Issue! ' . mysql_error());
    mysql_select_db("jsondb", $connect);
?>

Step 2: Extracting data from database

Once connection is established, then there is a need of extracting the data present in the table of database. For this, the PHP built-in function “mysql_query()” is used which will extract/fetch all the records present in the rows of the table. In my case, the table will be “stdtable”.

Listing 5. jsonRev.php

<?php
    //Step No. 2: Extracting data from database
    $query = "SELECT * FROM stdtable";
     $res = mysql_query($query,$connect) or die("Query Not Executed " . mysql_error($connect));
?>

Step 3: Putting the fetched data in Arrays

Now after fetching the data, the data is to be stored in PHP arrays. For this, create a variable and assign an empty array to that variable. Such empty array will be used to get the records obtained as a result of mysql query. The PHP “mysql_fetch_assoc()” array will be used for this purpose.

Listing 6. jsonRev.php

<?php
    //Step No. 3: Putting the fetched data in Arrays
    $data_array = array();
    while($rows =mysql_fetch_assoc($res))
    {
        $data_array[] = $rows;
    }
?>

Step 4: Encoding Array into JSON

Once you have the fetched data available in array, then it is to be encoded or converted into JSON. For this, the PHP built-in function “json_encode()” is used. This function takes the array as an argument. This function will convert the array data into strings of JSON.

Step 5: Writing data to JSON file

Finally, after having data in JSON string, there is a need to write the such data to JSON file. For this, few PHP built-in functions are used for performing different operations. The “fopen()” function is used to open the file which you want to store your JSON data in it. Then the “fwrite()” function is takes two arguments; one for the opened file returned by “fopen()” function and the second argument for encoding the data present in the database.

Listing 7. jsonRev.php

<?php
    //Step No. 4 and 5: Encoding Array into JSON + Writing data to JSON file
     $fp = fopen('studRecords.json', 'w');
    //fwrite($fp, json_encode($data_array));
     
     if(!fwrite($fp, json_encode($data_array)))
    {
        die('Error : File Not Opened. ' . mysql_error());
    }
     else{
            echo "Data Retrieved Successully!!!";
     }
     fclose($fp);
?>

Here is the complete code for the above disussion:

Listing 8. jsonRev.php

<?php
    //Step No. 1: Connect database with PHP
$connect = mysql_connect("localhost","root","") or die('Database Not Connected. Please Fix the Issue! ' . mysql_error());
    mysql_select_db("jsondb", $connect);
     
     //Step No. 2: Extracting data from database
    $query = "SELECT * FROM stdtable";
     $res = mysql_query($query,$connect) or die("Query Not Executed " . mysql_error($connect));
    
     //Step No. 3: Putting the fetched data in Arrays
    $data_array = array();
    while($rows =mysql_fetch_assoc($res))
    {
        $data_array[] = $rows;
    }
     //Step No. 4 and 5: Encoding Array into JSON + Writing data to JSON file
     $fp = fopen('studRecords.json', 'w');
    //fwrite($fp, json_encode($data_array));
     
     if(!fwrite($fp, json_encode($data_array)))
    {
        die('Error : File Not Opened. ' . mysql_error());
    }
     else{
            echo "Data Retrieved Successully!!!";
     }
     fclose($fp);
 
    mysql_close($connect);
?>
 

The output for the above code will be as follow:

Figure 5. Output of Retrieving data from Database.

After executing the above code, you will see that the file “studRecords.json” will be automatically created if you had not created yet and will contain the data retrieved from the database in JSON format. So we are done with that and by following both the processes, you can easily insert and retrieve JSON data to and from the database.

Conclusion

JSON file format plays a vital role in present web development paradigm. Now a day JSON took the place of XML with its different easy and fast operating characteristics. In this article, first of all a brief overview of JSON format was given. Then the different steps of inserting JSON data to a database was explained with an example. Finally, the reverse of insertion process i.e. retrieving JSON data from database and storing in a JSON file, was explained with explaining each and every step with an example.

Links

http://www.json.org/



I am Computer Science Graduate having hands-on experience in Android, PHP, MySQL, CSS, Javascript, HTML etc.

What did you think of this post?
Services
[Close]
To have full access to this post (or download the associated files) you must have MrBool Credits.

  See the prices for this post in Mr.Bool Credits System below:

Individually – in this case the price for this post is US$ 0,00 (Buy it now)
in this case you will buy only this video by paying the full price with no discount.

Package of 10 credits - in this case the price for this post is US$ 0,00
This subscription is ideal if you want to download few videos. In this plan you will receive a discount of 50% in each video. Subscribe for this package!

Package of 50 credits – in this case the price for this post is US$ 0,00
This subscription is ideal if you want to download several videos. In this plan you will receive a discount of 83% in each video. Subscribe for this package!


> More info about MrBool Credits
[Close]
You must be logged to download.

Click here to login