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.

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` (
    `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
    `date_posted` DATE NOT NULL ,
    `name` VARCHAR( 250 ) NOT NULL ,
    `email` VARCHAR( 1160 ) NOT NULL ,
    `phone` VARCHAR( 24 ) NOT NULL ,
    `status` INT NOT NULL
    ) ENGINE = MYISAM ;

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
    $a=mysql_connect('localhost','root');
    //Select the database
    $b=mysql_select_db('mysql_to_json',$a);
     
    //Table name
    $table='user_details';
    //
    $fet=mysql_query('select id,date_posted,name,email,phone from'.$table);
    $json = array();
    while($r=mysql_fetch_array($fet)){
      $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.