How to Create JSON Nested Object Using PHP PDO

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.

The examples inside this lesson use the MySQL language. However PDO is an abstraction layer that allows to connect to different databases.

Data structure

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.

CREATE TABLE user (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT ,
    firstName VARCHAR(100) NOT NULL ,
    lastName VARCHAR(100) NOT NULL ,
    email VARCHAR(100) NOT NULL ,

    PRIMARY KEY ('id')
) ENGINE = InnoDB;

INSERT INTO user (id, firstName, lastName, email)
VALUES (NULL, 'John', 'Dow', 'johndoe@nmail.com'),
       (NULL, 'Jane', 'Dy', 'mary@email.com');

Database connection

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 = "test";
 
 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 user"; 
 $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.

$data = array();
if($statement->execute()){
 while ($row = $statement->fetchAll(PDO::FETCH_ASSOC)) {
  $data['data'] = $row;
  // $data = $row;
 }
}

if(!empty($data)){
    header("Access-Control-Allow-Origin: *");//this allows coors
    header('Content-Type: application/json');
    print json_encode($data);
} else {
 echo 'error';
}

This code makes the query to our database by retrieving users. Then it cycles on the results to create an array that contains them all. Finally, thanks to the json_encode() function, it converts the results into a JSON.

So, the result will be the following:

{
  "data": [
    {
        id: "1",
        firstName: "John",
        lastName: "Doe",
        email: "johndoe@nmail.com"
    },
    {
        id: "2",
        firstName: "Jane",
        lastName: "Dy",
        email: "janedy@nmail.com"
    }
  ]
}

Src:
https://stackoverflow.com/questions/2770273/pdostatement-to-json