How to Export MySQL Data into JSON Format in PHP

JSON :-

JSON is a lightweight data interchange format, It is very easy to read as well as write. Additionally, It is very for machines to parse and generate. We can easily manage and exchange data across various platforms.

Majority of social networking websites including Facebook, Twitter use JSON as a data exchange format.

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

For example :-

[
   {"id":"1","name":"Ethan","roll_no":"131","degree":"BSCS"},
   {"id":"2","name":"Janet","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":"Ethan","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.

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, 'Ethan', '131', 'BSCS'),
(2, 'Janet', '135', 'BSCS'),
(3, 'Anna', '117', 'BSCS'),
(4, 'Pauline', '112', 'BSCS'),
(5, 'Ryan', '1244', 'BS Telecommunication'),
(6, 'Jane', '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 :-

Source :- http://www.c-sharpcorner.com/