How to Convert MySQL Data to JSON with PHP

Almost all web applications expect external data in JSON format. Consequently, converting data to JSON has become a common practice for developers.

Besides, JSON is quite comfortable in usage, as it is human readable and lightweight.

You can straightforwardly convert Data from the MySQL database to JSON with PHP.

An Example of Conversion from MySQL to JSON

In this section, we will consider an example by using the Sakila sample database, which works with standard MySQL installation. It is capable of fetching the first three rows of actor table into an associative array with the help of <kbd class="highlighted">mysqli_fetch_assoc()</kbd>. Afterwards, the array is encoded to JSON with <kbd class="highlighted">json_encode</kbd>.

Below, you can see the full example:

php convert mysql to json

<?php

// Initialize variable for database credentials
$dbhost = 'hostname';
$dbuser = 'username';
$dbpass = 'password';
$dbname = 'sakila';

//Create database connection
$dblink = new mysqli($dbhost, $dbuser, $dbpass, $dbname);

//Check connection was successful
if ($dblink->connect_errno) {
  printf("Failed to connect to database");
  exit();
}

//Fetch 3 rows from actor table
$result = $dblink->query("SELECT * FROM actor LIMIT 3");

//Initialize array variable
$dbdata = [];

//Fetch into associative array
while ($row = $result->fetch_assoc()) {
  $dbdata[] = $row;
}

//Print array in JSON format
echo json_encode($dbdata);

?>

The output of the example is the following:

php convert mysql to json output

[
    {
    "actor_id":"1",
    "first_name":"LISA",
    "last_name":"ADAMS",
    "last_update":"2008-03-10 04:34:33"
    },
    {    
    "actor_id":"2",
    "first_name":"JOHN",
    "last_name":"SPILBERG",
    "last_update":"2008-03-10 04:34:33"
    },
    {
    "actor_id":"3",
    "first_name":"ED",
    "last_name":"CHASE",
    "last_update":"2006-02-15 04:34:33"
    }
]

So, the output of the code is a valid JSON. It can be used along with jQuery / AJAX, and Fetch for passing data to web applications.

Describing JSON

JSON (JavaScript Object Notation) is considered a lightweight format applied to store and transport data.

It is commonly applied once data is forwarded from a server to a web page. JSON is quite simple to understand and use.

Syntactically, it is equivalent to the code used for generating JavaScript objects.

Description of the json_encode Function

This function is used for returning a string that contains a JSON representation of the provided value.

Its encoding is impacted by the provided options. Additionally, the encoding of float values relies on the value of serialize_precision.

For more information about the <kbd class="highlighted">json_encode</kbd> function and its usage, you can check out this page.