How to Fetch Data from Database in JSON format in PHP - onlyxcodes

Sunday 12 May 2024

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.


how to fetch data from database in json format in php

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

?>

No comments:

Post a Comment

Post Bottom Ad