Tutorial to Convert Data from MySQL To JSON using PHP

By | June 29, 2016

We may need to Convert our data from MySQL to JSON and it can be done very easily with the help of PHP. Mostly people prefer JSON over XML as JSON is easy to parse as compared to XML. It is choosed over XML because it is plain text and in human readable format, light weight and in the form of key value pair. So lets check how to convert MySQL to JSON using PHP.

mysql to json

In order to convert data from MySQL to JSON we need to use simple json_encode() method, which will convert the data to JSON format. Here’s a complete example on converting data to JSON.

How to Convert Data from MySQL To JSON using PHP

Steps to convert mysql to json string using PHP

For the tutorial purpose I will create a database TEST. And a table Details which will comprise of ID and Name. I will then add records


CREATE DATABASE TEST;


CREATE TABLE DETAILS

(

ID INT(3),

NAME Varchar(50)

);

Then I will simple add records in the table


INSERT INTO DETAILS (ID,NAME) VALUES (1,'SANGRAM');
INSERT INTO DETAILS (ID,NAME) VALUES (2,'STEVE');
INSERT INTO DETAILS (ID,NAME) VALUES (3,'SARAH');
INSERT INTO DETAILS (ID,NAME) VALUES (4,'LIAM');

Now that we have the records ready I will go ahead and fetch the data with PHP. So I will be creating two seperate files for Database connectivity and other for fetching details it’s not a good practice to write different components in the same file. Make sure you save the below two files in same directory.

Step 1: Create Database.php and establish connection with Database

Database.php

<?php
mysql_connect("localhost","root","");
mysql_select_db("test");
?>

Step 2: Fetch the result from MySQL database using PHP

FetchData.php

<?php
include_once("db.php");
$sql = "SELECT * FROM DETAILS"; 
$result = mysql_query($sql) or die(mysql_error());
 
while($row = mysql_fetch_assoc($result))
{
 
 $array[] = $row;
}
 //print_r($array);

$json_data = json_encode($array);
print_r($json_data);
?>

Step 3: Convert the PHP array result to JSON string using json_encode()

On line 13 we have data in array to convert it in JSON we will use function json_encode() and pass array as argument. Once this is done our data will look like this.

mysql to json

I hope this tutorial was helpful if you have any doubts please drop a comment. I will answer the doubts.

Check How to Convert JSON to array using PHP