How to Convert MySQL Data Into JSON Format in PHP using PDO

Submitted by alpha_luna on Friday, July 29, 2016 - 15:11.

In this tutorial, we are going to learn how to Convert MySQL Data Into JSON Format in PHP using PDO. This is a simple, short source codes, and useful tutorial that we have today. JSON is exchanging format to web development or in the mobile applications. It is easy to convert the data into plain text format if we are going to use it, so let's begin to convert into JSON format. This is our MySQL data which used to an example in this tutorial, so kindly copy and paste this following data into your PHPMyAdmin and it will create a table after you run it.

--
-- Table structure for table `tbl_users`
--
 
CREATE TABLE `tbl_users` (
  `id` INT(11) NOT NULL,
  `first_name` VARCHAR(25) NOT NULL,
  `last_name` VARCHAR(25) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
 
--
-- Dumping data for table `tbl_users`
--
 
INSERT INTO `tbl_users` (`id`, `first_name`, `last_name`) VALUES
(1, 'Captain', 'America'),
(2, 'Super', 'Woman'),
(3, 'Bat', 'Man'),
(4, 'Wolv', 'Verine'),
(5, 'Spider', 'Man'),
(6, 'Wonder', 'Woman');

After that, create a database connection and we are going to use the PDO extension, and this is the code.

<?php
	$database_hostname = "localhost";
	$database_user = "root";
	$database_password = "";
	$database_name = "json_tutorial";
 
	try{
		$database_connection = new PDO("mysql:host=$database_hostname;dbname=$database_name",$database_user,$database_password);
		$database_connection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
 
	}catch(PDOException $z){
 
		die($z->getMessage());
	}
?>

Display Records using SELECT Statement from MySQL

We are going to fetch our records from the user's table to get the list of users or to display the data.

	require_once 'dbconfig.php';
 
	$query = "SELECT * FROM tbl_users";	
	$statement = $database_connection->prepare($query);
	$statement->execute();

Convert MySQL Data to an Array

After constructing in the codes above using SELECT Statement, we have to convert the MySQL Data to an Array. Here's the source code below.

	$userData_List = array();
 
	while($row=$statement->fetch(PDO::FETCH_ASSOC)){
		$userData_List['List of Users'][] = $row;	
	}
	echo json_encode($userData_List);

Convert Array to JSON

In this case, we are going to convert Array to JSON using this function and it's called "json_encode();". Kindly add an array name to the open and closed parenthesis, and it shows like this.

	echo json_encode($userData_List);

Complete Source Code for Converting MySQL Data Into JSON Format

Here's the full source code in this tutorial to convert the MySQL Data to JSON Format in PHP using PDO Extension.

<?php
	require_once 'dbconfig.php';
 
	$query = "SELECT * FROM tbl_users";	
	$statement = $database_connection->prepare($query);
	$statement->execute();
 
	$userData_List = array();
 
	while($row=$statement->fetch(PDO::FETCH_ASSOC)){
		$userData_List['List of Users'][] = $row;	
	}
	echo json_encode($userData_List);
?>

Hope that this simple tutorial that I created may help you to your future projects. Also, for the beginners who want to learn basic of coding in PHP/MySQL and JSON. Enjoy coding. If you are interested in programming, we have an example of programs that may help you even just in small ways. Share us your thoughts and comments below. Thank you so much for dropping by and reading this tutorial post. For more updates, don’t hesitate and feel free to visit this website more often and please share this with your friends or email me at [email protected]. Practice Coding. Thank you very much.

Add new comment

2857 views