Convert MySQL database to JSON using PHP code

  1. Previous
  2. Next
  3. Recent

    1. Signs 'He' May be Cheating on You
    2. 7 Vows of Hindu Marriage - Know What 'Saat Vachans' Mean
    3. RuPay vs Visa vs Mastercard vs American Express- A Comparison
    4. What is RuPay? Everything You Need to Know About India's Domestic Payment System!
    5. Top 10 Online Portals to Find a High Paying Job in India
    6. Top Indians who Revolutionized the IT Sector in India
    7. 8 Initiatives by Narendra Modi that Could Change the Future of the Nation
    8. 8 Work-from-home Jobs for Housewives, Students, and Part timers
    9. Complete Company Registration Process in India - Explained
    10. Change Name in India in 5 Steps - Filing Affidavit, Newspaper Ad & Gazette Notification

In the last few tutorials we talked about exporting MySQL database records to XML file and vise-verse, today we’ll discuss about converting database records to JSON format and save to a file on local system.

JSON is very much like XML. Both provides easy way storing and transferring data. They can be read, and changes can be made directly to them without using any third party service. While both are same in most respect, there lies some difference too. JSON is more simpler, concise and faster than XML. That’s why, developers are more preferring JSON to XML nowadays.

In today’s tutorial, we’ll fetch a few questions with unique-id and level and save them to a dynamically created file called ‘myjson.json’. This file is created in runtime with a .json extension and database records are written to it in JSON format.

Step-1: Make Database Connection

Before we start, create a connection to database. In the example, our database is called as ‘test’ with ‘quiz_techno table’ that contains 3 fields: id, level and question.

Here is the code,


<?php
$con=mysql_connect("localhost","root","") or die("Database connection failed");
if($con)
{
mysql_selectdb("test",$con);
}
?>

[Note: If your database name is different, replace with your own.]

Step 2: Fetch records and convert MySQL database to JSON

Next is the big code that retrieves data from the database and convert to JSON format and save to ‘myjson.php’ file.


<?php

$data=array();
$qa=array();
$query=mysql_query("SELECT qid, level, question FROM quiz_techno ORDER BY qid");
while($geteach=mysql_fetch_array($query))
{
$id=$geteach[0];
$level=$geteach[1];
$question=$geteach[2];
$qa[]=array('id'=> $id, 'level'=> $level, 'question'=> $question);
}
$data['qa']=$qa;

$fp = fopen('myjson.json', 'w');
fwrite($fp, json_encode($data));
fclose($fp);

?>

  1. Data is fetched from the database using SQL query.
  2. Next, the fetched data are placed in arrays.
  3. Finally a file called ‘myjson.json’ is created and all file contents are written to it. json_encode() method converts the built array to JSON format before writing to the file.

Here is the generated output JSON file,


{"qa":[
 {"id":"33",
 "level":"6",
 "question":"F3 key in a keyboard is the shortcut for"},

{"id":"838",
 "level":"3",
 "question":"What is the full form of SEO?"},

 {"id":"1508",
 "level":"2",
 "question":"What is the full form of DNS?"},

 {"id":"2364",
 "level":"1",
 "question":"Which is the biggest social networking site?"},

 {"id":"2612",
 "level":"5",
 "question":"In which year Facebook was launched?"},

 {"id":"2965",
 "level":"9",
 "question":"The biggest factor in deciding website loading time is"},

 {"id":"3053",
 "level":"9",
 "question":"Name the operating system developed by Sun Microsystems?"},

....

Once the json file of the database entries is created, we can carry it as well as make changes with ease. Also, we can read the file contents using json_decode() method of php or using eval() Javascript function.

In the next tutorial, we’ll learn about parsing JSON file using JavaScript and PHP.

blog comments powered by Disqus