How to Retrieve data from a Database in JSON format using PHP?

Chances are you have come across the need to access your database to get the data in JSON format, and I am here to tell you how.

I know this task can be intimidating for some who only know the basics of JavaScript (or even PHP). JSON is actually quite simple once you understand it. To retrieve the data from a database, we need to follow the steps below:

Get the database connection

First, create a database connection using mysqli().

<?php 
	$servername = "localhost"; 
	$username = "root"; 
	$password = ""; 
	$dbname = "codehasbug"; 
	
	//  Create connection   
	$db = new mysqli ($servername, $username, $password, $dbname); 

	// Check connection 
	if ($db->connect_error) { 
		die("Connection failed: " . $db->connect_error); 
	}

Create a SQL query to retrieve data from the database

Here is our user table data:

In the above image, you can see there is a total of 4 columns present in the user table. In order to retrieve data from the user table, use the below code:

//get all users
$result = $db->query("SELECT * FROM user");

//declare an empty array
$userArray = array();

//check if query return something
if( $result ){


	while( $row = $result->fetch_object() ) {
    	
		//store each record into $userArray
    	$userArray[] = $row;
    }

    //echo json value
    echo json_encode($userArray);
    
}else{
	echo "no records found";
}

This will produce the following output:

[
  {
    "id": "1",
    "first_name": "Glenn",
    "last_name": "Quillinane",
    "email": "gquillinane0@t.co"
  },
  {
    "id": "2",
    "first_name": "Ashly",
    "last_name": "Burgis",
    "email": "aburgis1@usgs.gov"
  },
  {
    "id": "3",
    "first_name": "Erich",
    "last_name": "Barlace",
    "email": "ebarlace2@va.gov"
  },
  {
    "id": "4",
    "first_name": "Gerhardine",
    "last_name": "Saltmarshe",
    "email": "gsaltmarshe3@jugem.jp"
  },
  {
    "id": "5",
    "first_name": "Nikolai",
    "last_name": "Pawelski",
    "email": "npawelski4@hibu.com"
  }
]

Let’s check the code step by step:

• Make a query to user table i.e. $db->query("SELECT * FROM user") and store the result in $result variable.
• Declare an empty array $userArray = array(); to store the each row.
• In while() loop, fetch the data from user table by using $result->fetch_object().
• Store each record in $userArray i.e. $userArray[] = $row;
• Now using php json_encode(), convert the array into JSON formatted string i.e. json_encode($userArray)

Conclusion:

The above code will produce the JSON formatted string. Assuming your goal was to use this data in JQuery, I would recommend reading this tutorial on how to show JSON data in HTML using jQuery and ajax and simply return the rendered HTML. Cheers!

If you found this article useful, please leave a comment below and/or share the article on Twitter or your social network of choice. If you want to stay up to date with my latest posts, feel free to follow me on Twitter.