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.

Watch a video course Learn object oriented 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 mysqli_fetch_assoc(). Afterwards, the array is encoded to JSON with json_encode.

Below, you can see the full example:

<?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:

[
    {
    "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 json_encode function and its usage, you can check out this page.