How to Fetch Data from Database using JSON in PHP

This article will guide you through the process of using JSON in PHP to fetch data from a database, giving you a thorough grasp of all the required procedures and best practices.

When interacting with APIs (Application Programming Interfaces), JSON has emerged as the preferred format for data transmission. Unlike previous formats like XML, JSON features an easy-to-read syntax that is accessible to both humans and machines.

Plus, JSON is the format of choice for many developers because the majority of modern programming languages offer several libraries for encoding and decoding JSON. 

Table Content

1. Make a Database and Table

2. Link the Database

3. Fetch Data from Database

4. Test the Application

5. Conclusion

Make a Database and Table

We must first create the database and table that will be the source of our data before we can start working on our app. 

You can use PHPMyAdmin or MySQL Workbench to create a database with any name. I used the named database json_data_php_db in my instance. 

Then, to create the product table in your database, use the following SQL statement.

CREATE TABLE tbl_product (
    id int,
    name varchar(150)
);

Certain records must exist in the table once it is created since we will retrieve them and convert them to JSON format.

A few dumping data have already been added to my product table.

Use the SQL insert query statement I supplied you below to add some dumping data to the table. 

INSERT INTO tbl_product VALUES(1,'iPhone');
INSERT INTO tbl_product VALUES(2,'iPad');
INSERT INTO tbl_product VALUES(3,'iMac');
INSERT INTO tbl_product VALUES(4,'iWatch');
INSERT INTO tbl_product VALUES(5,'MacBook Pro');
INSERT INTO tbl_product VALUES(6,'MacBook Air');
INSERT INTO tbl_product VALUES(7,'HomePod mini');

Link the Database

This is the dbconfig.php file, which has a PDO-extended MySQL database connection.

I connected the MySQL database using the try...catch block. There won't be any issues if the database connects successfully; but, the catch section will raise an exception if something goes wrong.  

<?php

$db_host="localhost"; 
$db_user="root";	
$db_password="";	
$db_name="json_data_php_db";	

try
{
	$db=new PDO("mysql:host={$db_host};dbname={$db_name}",$db_user,$db_password);
	$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
}
catch(PDOEXCEPTION $e)
{
	$e->getMessage();
}

?>

Fetch Data from Database

This is the index.php file that has the entire code in it. In this file, I gathered data from the product table and formatted it in JSON.   

<?php	
		
	require_once "dbconfig.php";  // include database connection file
				
	$select_stmt = $db->prepare("SELECT * FROM tbl_product");	// write sql select query to select data	
	$select_stmt->execute();  // execute the query 
			
	$productData = array();  // make an empty array
				
	while($row=$select_stmt->fetch(PDO::FETCH_ASSOC)) // fetch data
	{
		$productData[] = $row; // assign data into array
	}	
				
	echo json_encode($productData); // transform the data to JSON format and display it

?>

Explanation:

Row no 3 - I included the dbconfig.php file, which has the database connection code, using the require_once keyword.

require_once "dbconfig.php";  // include database connection file

Row no 5 - I used the SQL select query to select the product table record in the prepare() function using the $db object of the database connection file; this statement is stored in the $select_stmt variable. 

$select_stmt = $db->prepare("SELECT * FROM tbl_product");	// write sql select query to select data

Row no 6 - The query is run by the execute() method.  

$select_stmt->execute();  // execute the query

Row no 8 - I made a blank array and put the $productData variable in it.

$productData = array();  // make an empty array

Row no 10 to 13 - Opened while loop, The fetch method of PDOStatement gives a row from the result set. 

PDO instructs to return the array value indexed by the product table in all columns by using the PDO:: FETCH_ASSOC argument. 

The $row is an array that holds data in array format.

Next, all the data is pasted to the $productData array variable. The $productData variable we had already created in the previous step.

while($row=$select_stmt->fetch(PDO::FETCH_ASSOC)) // fetch data
{
	$productData[] = $row; // assign data into array
}

Row no 15 - The $productData array variable is then pasted into the "json_enocde()" function, which converts all of the data into JSON and the "echo" command displays them.

echo json_encode($productData); // transform the data to JSON format and display it

Test the Application

Alright, let's test this application now. To view the output in JSON format, run the PHP code in a browser.

Output:

[{"id":1,"name":"iPhone"},{"id":2,"name":"iPad"},{"id":3,"name":"iMac"},{"id":4,"name":"iWatch"},{"id":5,"name":"MacBook Pro"},{"id":6,"name":"MacBook Air"},{"id":7,"name":"HomePod mini"}]

Conclusion:

In conclusion, web developers have plenty of options when it comes to extracting data from a database via JSON in PHP. Because of its effectiveness, readability, and compatibility, JSON is a useful tool for building responsive and dynamic online applications. As you begin putting these strategies into practice, keep in mind that in the dynamic realm of web development, ongoing learning and adaptability are essential.