How to convert MYSQL data to JSON using PHP

Posted on 06th April 2016

Many web applications expect external data in JSON format, hence converting MySQL data to JSON is something that web developers encounter on a regular basis. JSON (JavaScript Object Notation) is fast becoming the most popular data format in server/browser communication. It is light weight, human readable and easy to generate and parse.

Data from MySQL database can be easily converted into JSON format using PHP. The below example uses Sakila sample database that comes with standard MySQL installation. It fetches the first 3 rows of actor table into an associative array using mysqli_fetch_assoc(). Then the array is encoded into JSON using json_encode.


// 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");

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

//Initialize array variable
  $dbdata = array();

//Fetch into associative array
  while ( $row = $result->fetch_assoc())  {

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


	"last_update":"2006-02-15 04:34:33"
	"last_update":"2006-02-15 04:34:33"
	"last_update":"2006-02-15 04:34:33"

As you can see, the result is a valid JSON. You may use this in combination with Jquery/AJAX to pass database data to a web application that expects data in JSON.

Post a comment


Jack | November 1, 2019 5:36 AM |

How do i pass them into javascript. Is that achievable?

Mo | November 4, 2019 11:46 AM |

you have to use AJAX

Riley | November 4, 2019 11:49 AM |

ofcourse it is achievable and it is very easy too.. echo '<script>'; echo 'var name = ' . json_encode($dbdata) . ';'; echo '</script>';

Muhammad Jaury | August 7, 2019 9:59 AM |

How are the code if I want the result only show firstname and lastname of all actor?

Patrick | September 3, 2019 7:31 AM |

you can use a foreach loop as below: foreach($dbdata as $key => $value) { if($key == "first_name" || $key == "last_name") { echo $value; } }

Sathish | July 20, 2019 10:18 AM |

This article helped my project. thank you

kamran khan | May 26, 2019 8:00 AM |

Great Job Well Done Keep It Up

Anjali | April 2, 2019 7:07 AM |

Thanks alot this ready helped me with my project.