How to Export MySQL Data Into JSON Format in PHP – Info PHP

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

My sql to JSON

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


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:

  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 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:



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:

Student table

With SQL query 

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

Records in MySQLRecords 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’);  


  2. $db=new PDO(‘mysql:dbname=jason;host=localhost;’,‘root’,);  
  4. $row=$db->prepare(‘select * from student’);  
  6. $row->execute();  
  7. $json_data=array();  
  8. foreach($row as $rec)  
  9. {  
  10. $json_array[‘id’]=$rec[‘id’];  
  11.     $json_array[‘name’]=$rec[‘name’];  
  12.     $json_array[‘roll_no’]=$rec[‘roll_no’];  
  13.     $json_array[‘degree’]=$rec[‘degree’];  
  15.     array_push($json_data,$json_array);  
  17. }  
  20. echo json_encode($json_data);  
  23. ?>  

Output in JSON format:Output in JSON format
I have attached the source code files for you to download.

Article Prepared by Ollala Corp

You might also like More from author

Leave A Reply

Your email address will not be published.