How to Export MySQL Data Into JSON Format in PHP

Hi guys. Today I will explain how to create or export data from a MySQL database into JavaScript Object Notation (JSON) format using PHP.





Note:Now you can read it on my blog.I have updated this article on my personal blog here.


JSON

JSON is a lightweight data-interchange format. It is easy for humans to read and write. It is easy for machines to parse and generate. it's lightweight, readable and easily manageable for exchanging data across various platforms.

Now a days most of the social networking application APIs like Facebook and Twitter use JSON as data exchange format.

A JSON Array starts with "[" and ends with "]". Between them, a number of values can reside. If there are more than one value then they are separated by ",".

For example:

[
   {"id":"1","name":"Ehtesham","roll_no":"131","degree":"BSCS"},
   {"id":"2","name":"Raza","roll_no":"135","degree":"BSCS"}
]

JSON Object

An object starts with "{" and ends with "}". Between them, a number of string name/value pairs can reside. The name and value is separated by a ":" and if there is more than one name/value pairs then they are separated by ",".

For example:

{"id":"1","name":"Ehtesham","roll_no":"131","degree":"BSCS"}

PDO

The PHP Data Objects (PDO) extension defines a lightweight, consistent interface for accessing databases in PHP. PDO provides a data-access abstraction layer, which means that, regardless of which database you're using, you use the same functions to issue queries and fetch data. You just need to change the database drivers. Let's start with it. Reference by PHP.

Student table:



With SQL query 

CREATE TABLE IF NOT EXISTS `student` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `roll_no` varchar(255) NOT NULL,
  `degree` text NOT NULL,
  PRIMARY KEY (`id`)
)

Records in MySQL



With Query:

INSERT INTO `student` (`id`, `name`, `roll_no`, `degree`) VALUES
(1, 'Ehtesham', '131', 'BSCS'),
(2, 'Raza', '135', 'BSCS'),
(3, 'Zaryab', '117', 'BSCS'),
(4, 'Zaid', '112', 'BSCS'),
(5, 'Farrukh', '1244', 'BS Telecommunication'),
(6, 'Salman', '084', 'BSCS');

index.php:

<?php
//PDO is a extension which  defines a lightweight, consistent interface for accessing databases in PHP.
$db=new PDO('mysql:dbname=jason;host=localhost;','root','');
//here prepare the query for analyzing, prepared statements use less resources and thus run faster
$row=$db->prepare('select * from student');

$row->execute();//execute the query
$json_data=array();//create the array
foreach($row as $rec)//foreach loop
{
$json_array['id']=$rec['id'];
    $json_array['name']=$rec['name'];
    $json_array['roll_no']=$rec['roll_no'];
    $json_array['degree']=$rec['degree'];
//here pushing the values in to an array
    array_push($json_data,$json_array);

}

//built in PHP function to encode the data in to JSON format
echo json_encode($json_data);


?>

Output in JSON format:


I have attached the source code files for you to download.