How to Convert Data from MySQL to JSON using PHP

In PHP, Converting Data from MySQL to JSON Format is one of the prominent tasks in Web Development. JSON has gained popularity over the years and is preferred over xml as data exchange format between web applications.

Using json format has its own advantages like being light weight, ability to store complex data structures in plain text and very human readable. Earlier we have discussed about converting json to mysql data here. Now let us see how to convert mysql result set to json in php.

Create MySQL Database

Here is the MySQL Database I'm going to use as an example. Run these sql commands to create the Database.

Read Also:

• How to Export MySQL Database to CSV File using PHP
• Build Simple Search Engine using AJAX, PHP & MySQL
• Login and Registration Script using PHP, MySQL & jQuery

CREATE TABLE IF NOT EXISTS `tbl_employee` (
  `employee_id` int(4) NOT NULL AUTO_INCREMENT,
  `employee_name` varchar(60) NOT NULL,
  `designation` varchar(30) NOT NULL,
  `hired_date` date NOT NULL,
  `salary` int(10) NOT NULL,
  PRIMARY KEY (`employee_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

INSERT INTO `tbl_employee` (`employee_id`, `employee_name`, `designation`, `hired_date`, `salary`) VALUES
(1, 'Steve', 'VP', '2013-08-01', 60000),
(2, 'Robert', 'Executive', '2014-10-09', 20000),
(3, 'Luci', 'Manager', '2013-08-20', 40000),
(4, 'Joe', 'Executive', '2013-06-01', 25000),
(5, 'Julia', 'Trainee', '2014-10-01', 10000);

Convert MySQL to JSON String in PHP

Here are the steps in converting mysql to json string with php.

Step 1: Open MySQL Database Connection in PHP

First establish connection to mysql database using mysqli_connect() function.

<?php
    //open connection to mysql db
    $connection = mysqli_connect("hostname","username","password","db_employee") or die("Error " . mysqli_error($connection));
?>

Step 2: Fetch Data from MySQL Database

After opening the connection, fetch the required table data from mysql db. Using the php function mysqli_query(), I'm going to fetch all the rows from the table 'tbl_employee'.

<?php
    //fetch table rows from mysql db
    $sql = "select * from tbl_employee";
    $result = mysqli_query($connection, $sql) or die("Error in Selecting " . mysqli_error($connection));
?>

Step 3: Convert MySQL Result Set to PHP Array

Next loop through the mysql result set we got from step-2 and convert it to php array.

<?php
    //create an array
    $emparray = array();
    while($row =mysqli_fetch_assoc($result))
    {
        $emparray[] = $row;
    }
?>

Step 4: Convert PHP Array to JSON String

Next use the PHP function json_encode() to convert the php array to json string. Learn about using php json_decode() function here.

<?php 
    echo json_encode($emparray);
?>

That's it! We have successfully converted mysql to json using php. Here is the complete PHP code for it.

<?php
    //open connection to mysql db
    $connection = mysqli_connect("hostname","username","password","db_employee") or die("Error " . mysqli_error($connection));

    //fetch table rows from mysql db
    $sql = "select * from tbl_employee";
    $result = mysqli_query($connection, $sql) or die("Error in Selecting " . mysqli_error($connection));

    //create an array
    $emparray = array();
    while($row =mysqli_fetch_assoc($result))
    {
        $emparray[] = $row;
    }
    echo json_encode($emparray);

    //close the db connection
    mysqli_close($connection);
?>

Run the code and you get an output something like this.

Convert MySQL to JSON File in PHP

If you want to write the data from mysql to json file, use this piece of code at the end instead of 'echo' statement.

<?php
    //write to json file
    $fp = fopen('empdata.json', 'w');
    fwrite($fp, json_encode($emparray));
    fclose($fp);
?>

Read Also:

• How to Convert CSV to JSON using PHP
• How to Read JSON from Remote URL in PHP
• How to Store JSON Data in File using PHP

Find this PHP MySQL to JSON conversion tutorial useful? Like us on Facebook and never miss any of our tutorials.