How to Convert Data from MySQL to JSON using PHP

Posted by Valli Pandy On 1/12/2015

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 datahere. 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.

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 aboutusing 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);
?>

Recommended Read: Use PHP json_decode Function to Insert JSON to MySQL Database

Recommended Read: How to use PHP PREG MATCH function to validate Form Input

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

Last Modified: Oct-18-2015