How to Fetch Data from Database in JSON format in PHP

Hi, In this tutorial, I will demonstrate to you how to fetch data from a database in JSON format in PHP.

Database integration is a standard needed in today's web development environment. Getting data out of a database and transferring it in JSON (JavaScript Object Notation) format is one effective method. 

JSON is a simple, lightweight data transfer format that is simple to read, write, and analyze for machines as well as for humans.

Create a Database and Table

I have made a database called json_data_php_db in my PhpMyAdmin, and I have made a table named product inside of it.

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

In the product table, I have inserted seven dumping records.

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');

Establish the Database Connection

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

<?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 in JSON Format

This is index.php file, and it has PHP scripts in it. This file contains a PHP PDO script that I have used to obtain all records from the product table and use PHP's json_encode() function to convert them to JSON format. 

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

?>

Learn More:

What does array splice do in PHP

What is POST variable in PHP

What is Custom PHP Development

How do you append to text in PHP

How to make OTP Verification in PHP

How to store textarea value in database in PHP

How to get the class name in PHP

How to Run PHP Code in Browser

How to Check if a String is Null in PHP

How to Pass Dropdown Selected Value to Database in PHP

How to Create Drop Down List in PHP with MySQL

How to Process a Form using PHP