Export MYSQL record to JSON Data

In this post I will give you a simple idea to create JSON Data from MYSQL record. Export the MYSQL record to JSON array using PHP. You may notice that top social network websites like Facebook and Twitter providing the option to get user data through API. The result of this request will display the JSON or XML output. We are going to create a similar JSON array from MYSQL database using PHP and MYSQL.

MYSQL create JSON Data Using PHP

You can download the original PHP email with attachment script from below:

Live Demo Download

First create a MYSQL database

CREATE DATABASE  `mysql_to_json` ;

Next we need to create a table in database called user_details to store the data and later we will export this data as JSON array

CREATE TABLE  `mysql_to_json`.`user_details` (
`date_posted` DATE NOT NULL ,
`name` VARCHAR( 250 ) NOT NULL ,
`email` VARCHAR( 1160 ) NOT NULL ,
`phone` VARCHAR( 24 ) NOT NULL ,
`status` INT NOT NULL

Next inset some data to the above table.

INSERT INTO `mysql_to_json`.`user_details` (`id`, `date_posted`, `name`, `email`, `phone`, `status`) VALUES 
('', '2013-02-12', 'Prasad', 'admin@webinfopedia.com', '1234567890', '1'),
('', '2013-02-13', 'webinfopedia', 'info@webinfopedia.com', '1234567890', '1'),
('', '2013-02-15', 'Jhon', 'jhon@webinfopedia.com', '3678652337', '1'),
('', '2013-02-17', 'Sam', 'sam@webinfopedia.com', '8433123677', '1'),
('', '2013-02-17', 'Jim', 'jim@webinfopedia.com', '1907456298', '1'),
('', '2013-02-20', 'San', 'san@webinfopedia.com', '9944227745', '1'),
('', '2013-02-22', 'User', 'user@webinfopedia.com', '3678652337', '1'),
('', '2013-02-26', 'Josep', 'josep@webinfopedia.com', '223456987', '1');

Now our MYSQL database is ready with table and data inside that.

Next we need to fetch that data from database and convert it as JSON array. Find the simple PHP code below

//Connect to mysql
//Select the database

//Table name
$fet=mysql_query('select id,date_posted,name,email,phone from'.$table);
$json = array();
  $json[] = $r;
//Display the JSOn data
echo $json_data=json_encode($json);

Above code will fetch all the data from MYSQL database and stores in a PHP array as JSON Data. Later you can use that to any purpose. Hope this post will help you. Don’t forget to make the FREE email subscription for more related stuff from webinfopedia.com in future.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>