Export MySQL Data Into JSON Format Using PHP

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

JSON

JSON is a lightweight format for data exchange. It is easy for humans to read and write. It is easy for machines to parse and generate. It is lightweight, easy to read and easy to use for exchanging data across multiple platforms.

Today most APIs social networking applications like Facebook and Twitter using JSON data interchange format.

A JSON array begins with "[" and ends with "]". Among them, a series of values can reside. If more than one value below which are separated by "."

For instance:

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

JSON Object

An object begins with "{" dy ends with "}". Among them, a series of name / value pairs can reside chain. 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) defines a lightweight and consistent access to databases on the PHP interface. PDO provides access to data abstraction layer, which means that regardless of which database you are using, the same functions are used to query and retrieve data. Simply change the database drivers. Let's start with him. PHP reference.

Student table:

With SQL query 

1. CREATE TABLE IF NOT EXISTS `student` (  
2.   `id` int(10) NOT NULL AUTO_INCREMENT,  
3.   `name` varchar(255) NOT NULL,  
4.   `roll_no` varchar(255) NOT NULL,  
5.   `degree` text NOT NULL,  
6.   PRIMARY KEY (`id`)  
7. )  

Records in MySQL

With Query:

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

index.php:

1. <?php  
2. //PDO is a extension which  defines a lightweight, consistent interface for accessing databases in PHP.  
3. $db=new PDO('mysql:dbname=jason;host=localhost;','root','');  
4. //here prepare the query for analyzing, prepared statements use less resources and thus run faster  
5. $row=$db->prepare('select * from student');  
6.   
7. $row->execute();//execute the query  
8. $json_data=array();//create the array  
9. foreach($row as $rec)//foreach loop  
10. {  
11. $json_array['id']=$rec['id'];  
12.     $json_array['name']=$rec['name'];  
13.     $json_array['roll_no']=$rec['roll_no'];  
14.     $json_array['degree']=$rec['degree'];  
15. //here pushing the values in to an array  
16.     array_push($json_data,$json_array);  
17.   
18. }  
19.   
20. //built in PHP function to encode the data in to JSON format  
21. echo json_encode($json_data);  
22.   
23.   
24. ?>  

Output in JSON format: