How to Return MySQL Data in JSON Format With PHP On Ubuntu 20.04 Server

Introduction

JavaScript Object Notation(JSON) has become the standard data interchange format when working with Application Programming Interfaces(APIs). JSON has a simple syntax that is easily readable by both humans and machines compared to legacy formats like Extensible Markup Language(XML).

In addition, most modern programming languages provide tonnes of libraries for encoding and parsing JSON making it the preferred format for many developers. In this guide, you'll set up a simple MySQL database and then use some PHP functions to retrieve and convert data in JSON format. You'll test this guide on an Ubuntu 20.04 server.

Prerequisites

Before you begin, make sure you've the following:

An Ubuntu 20.04 server configured with a non-root sudo user and a firewall. You may refer to our How to Set Up Your First Ubuntu 16.04 Server on Alibaba Cloud guide to create a non-root sudo user and configure a firewall.
A LAMP stack server. Please review the guide on How to Install LAMP Stack to set up and configure Linux, Apache, MySQL, and PHP.

Step 1 - Creating a Sample Database and a Table

In this step, you'll set up a sample database and a table. You'll also populate the table with some records that you'll use throughout this guide to test the JSON format. Begin by connecting to your server and issue the command below to log in to your MySQL as root.

$ sudo mysql -u root -p

Enter your root password for your MySQL server and press ENTER to proceed. Then, issue the following command to create a company_db database:

mysql> CREATE DATABASE company_db;

Confirm the output below to make sure you've created the database:

Query OK, 1 row affected (0.01 sec)

To access the new company_db database from PHP, you require a non-root database user. Run the following command to set up a company_db_user. Replace EXAMPLE_PASSWORD with a strong value:

mysql> CREATE USER 'company_db_user'@'localhost' IDENTIFIED WITH mysql_native_password BY 'EXAMPLE_PASSWORD';
       GRANT ALL PRIVILEGES ON company_db.* TO 'company_db_user'@'localhost';           
       FLUSH PRIVILEGES;

You should receive the following output confirming that you've successfully created the user:

...
Query OK, 0 rows affected (0.00 sec)

Next, issue the USE keyword to switch to the new company_db database:

mysql> USE company_db;

You should receive the following output to confirm you've switched to the new database without errors:

Database changed

Next, set up a sample employees table by running the following command:

mysql> CREATE TABLE employees (
           employee_id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
           first_name VARCHAR(50),
           last_name VARCHAR(50)
       ) ENGINE = InnoDB;

Confirm the following output to ensure you've created the table without any errors:

Query OK, 0 rows affected (0.03 sec)

Next, use the INSERT statement to populate the employees table with some test records:

mysql> INSERT INTO employees (first_name, last_name) VALUES ('JOHN', 'DOE');
       INSERT INTO employees (first_name, last_name) VALUES ('MARY', 'ROE');
       INSERT INTO employees (first_name, last_name) VALUES ('PETER', 'SMITH');
       INSERT INTO employees (first_name, last_name) VALUES ('JANE', 'DERICK');

You should get the following output to confirm the INSERT commands:

...
Query OK, 1 row affected (0.01 sec)

Make sure the data is in place by running a SELECT statement against the employees table:

mysql> SELECT
       employee_id,
       first_name,
       last_name
       FROM employees;

You should get the following output listing all records in the employees table:

+-------------+------------+-----------+
| employee_id | first_name | last_name |
+-------------+------------+-----------+
|           1 | JOHN       | DOE       |
|           2 | MARY       | ROE       |
|           3 | PETER      | SMITH     |
|           4 | JANE       | DERICK    |
+-------------+------------+-----------+
4 rows in set (0.00 sec)

Exit from MySQL command-line interface:

mysql> QUIT;

Output:

Bye

You now have the database, tables, and some sample records. In the next step, you'll code a PHP script that fetches records from the MySQL database and converts the output to JSON format.

Step 2 - Creating a PHP Script

In this step, you'll use the PHP Data Object(PDO) library to connect to the database that you've created in Step 1 and retrieve records from the employees table. Using the nano text editor, open a new employees.php file in the root directory of your web server:

$ sudo nano /var/www/html/employees.php

Then, begin by opening a new <?php tag. Then, enter the statement header("Content-Type:application/json") to allow clients to treat the data as JSON. Next, declare the database variables that you've set up in Step 1. Use the syntax PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION to capture any errors that you may encounter when connecting to the database. Also, use PDO::ATTR_EMULATE_PREPARES,false to let MySQL handle the emulation of prepared statements:

<?php 

    header("Content-Type:application/json");

    try {
            $database_name     = 'company_db';
            $database_user     = 'company_db_user';
            $database_password = 'EXAMPLE_PASSWORD';
            $database_host     = 'localhost';

            $pdo = new PDO('mysql:host=' . $database_host . '; dbname=' . $database_name, $database_user, $database_password);
            $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);  
            $pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES,false);

Next, initialize an SQL statement with the correct syntax for fetching data from the employees table. Then, pass the $sql command to the $pdo object. Loop through the response using the PHP while (...) statement to create an associative array and place the result in a $data[] variable. Finally, use the PHP json_encode function to encode the array to JSON:

            $sql = 'select
                    employee_id,
                    first_name,
                    last_name                                 
                    from employees
                    ';           

            $stmt = $pdo->prepare($sql);
            $stmt->execute();
           
            $data = [];

            while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {          
                 $data[] = $row;  
            } 

           $response         = [];
           $response['data'] =  $data;

           echo json_encode($response, JSON_PRETTY_PRINT);

        } catch (PDOException $e) {
            echo 'Database error. ' . $e->getMessage();
        }

When you've finished editing, your /var/www/html/employees.php file, it should be similar to the following text:

<?php 

    header("Content-Type:application/json");

    try {
            $database_name     = 'company_db';
            $database_user     = 'company_db_user';
            $database_password = 'EXAMPLE_PASSWORD';
            $database_host     = 'localhost';

            $pdo = new PDO('mysql:host=' . $database_host . '; dbname=' . $database_name, $database_user, $database_password);
            $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);  
            $pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES,false);          

            $sql = 'select
                    employee_id,
                    first_name,
                    last_name                                 
                    from employees
                    ';           

            $stmt = $pdo->prepare($sql);
            $stmt->execute();
           
            $data = [];

            while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {          
                 $data[] = $row;  
            } 

           $response         = [];
           $response['data'] =  $data;

           echo json_encode($response, JSON_PRETTY_PRINT);

        } catch (PDOException $e) {
            echo 'Database error. ' . $e->getMessage();
        }

Save and close the file by pressing CTRL + X, Y, and ENTER. You now have the correct PHP code for retrieving data from the MySQL database and encoding it to JSON. In the next step, you'll run the code and see what happens.

Step 3 - Testing the PHP Script

In this step, you'll execute your PHP file to test the functionality of the code you've written. Open a new browser window and enter the URL below. Replace 192.0.2.1 with the correct public IP address or domain name of your server:

http://192.0.2.1/employees.php

You should now receive the following JSON response on your browser:

{
   "data":[
      {
         "employee_id":1,
         "first_name":"JOHN",
         "last_name":"DOE"
      },
      {
         "employee_id":2,
         "first_name":"MARY",
         "last_name":"ROE"
      },
      {
         "employee_id":3,
         "first_name":"PETER",
         "last_name":"SMITH"
      },
      {
         "employee_id":4,
         "first_name":"JANE",
         "last_name":"DERICK"
      }
   ]
}

The output above confirms that your PHP code is working and indeed, you've successfully converted your database records to the standard JSON format.

Conclusion

In this tutorial, you've set up a sample database, created a table, and populated it with some records. Then, you've used the PDO library to fetch data from your database and finally used some PHP functions to convert the data to the JSON format. Use this guide when working on your next API application.

Community

How to Return MySQL Data in JSON Format With PHP On Ubuntu 20.04 Server

Introduction

Prerequisites

Step 1 - Creating a Sample Database and a Table

Step 2 - Creating a PHP Script

Step 3 - Testing the PHP Script

Conclusion

Read previous post:

francisndungu

You may also like

Comments

francisndungu

Related Products

PolarDB for MySQL

PolarDB for PostgreSQL

OpenAPI Explorer

API Gateway