Convert Data from MySQL to JSON using PHP

MySQL, PHP


Converting data from MySQL to JSON format in PHP, is one of the most prominent task in web development. JSON is the most preferred data format over xml as data exchange between web and mobile applications. JSON format has it’s own advantages like light weight, ability to store complex data structures in plain text and human readability. In previous post we have already discussed about converting JSON data into MySQL using PHP. Now in this post we will see how to convert data from mysql to json using php.

Create MySQL Database Table

Here is the MySQL Database Table tbl_students we are going to use as an example. Run below sql commands to create table in the Database.

CREATE TABLE IF NOT EXISTS `tbl_students` (
  `studentId` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(250) NOT NULL,
  `age` varchar(20) NOT NULL,
  `streetaddess` varchar(250) NOT NULL,
  `city` varchar(200) NOT NULL,
  `state` varchar(100) NOT NULL,
  `postalcode` varchar(50) NOT NULL,
  PRIMARY KEY (`studentId`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;


INSERT INTO `tbl_students` (`studentId`, `name`, `age`, `streetaddess`, `city`, `state`, `postalcode`) VALUES
(1, 'Steve', '23', '4 Street', 'New York', 'NY', '12548'),
(2, 'John', '32', '14 Street', 'New York', 'NY', '54633');

Convert MySQL to JSON String using PHP

Here are the steps of converting mysql to json string using php.

Step 1: MySQL Database Connection in PHP

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


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

Step 2: Fetch Data from the Database

After the connection, fetch the required table data from database. Using the php function mysqli_query(), we are going to fetch all the rows from the table ‘tbl_students’.


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

Step 3: Convert Result Set to PHP Array

Now loop through the MySQL result set we got from step-2 and convert it into a php array.

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

Step 4: Convert PHP Array to JSON String

Use the PHP function json_encode() to convert the php array into json string.


echo json_encode($stuarray);

That’s it! We have successfully converted mysql to json using php.

Convert MySQL to JSON File using PHP

If you want to write the data from MySQL to json file, use below piece of code at the end instead of ‘echo’ statement.

    
//write to json file
$fp = fopen('studentsdata.json', 'w');
fwrite($fp, json_encode($stuarray));
fclose($fp);

Hope this tutorial well help you to convert the data from MySQL to JSON using PHP.

Related Tutorials