How to convert result of MySQL query in JSON format

Generally, the majority of PHP applications work with MySQL as the back-end database, we can extract the data via the MySQLi class. When you think about JSON with PHP and MySQL, it can be quite intimidating. That’s why we wrote this article, to finally demystify the subject.

The example below uses “users” database. It retrieves the rows of the “employee” table in an associative array using mysqli_fetch_assoc(). Then the associative array is encoded in JSON using json_encode.
 

How to convert result of MySQL query in JSON format

<?php

  //database credentials
  $host = 'localhost';
  $user = 'root';
  $pass = ' ';
  $db = 'users';

  //Create a database connection
  $conn = new mysqli($host, $user, $pass, $db);

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

  //Retrieve rows from the employee table
  $res = $conn->query("SELECT * FROM employee");

  //Initialize an array
  $data = array();

  //Retrieve the lines
  while ( $row = $res->fetch_assoc())  {
     $data[] = $row;
  }

  //Convert data in JSON format
  echo json_encode($data);
 
?>

Output:

[
	{
	  "id":"1",
	  "name":"Alex",
	  "age":"25",
	  "address":"San Francisco"
	},
	{
	  "id":"2",
	  "name":"Bob",
	  "age":"30",
	  "address":"New York"
	}
]

MCQPractice competitive and technical Multiple Choice Questions and Answers (MCQs) with simple and logical explanations to prepare for tests and interviews.Read More