Author: Francis Ndungu
Last Updated: Fri, Nov 13, 2020JSON (JavaScript Object Notation) is a light-weight data interchange format used to create an API (Application Programming Interface) for your web application, desktop software, or mobile app. JSON format is easier to read and write than formats such as XML (Extensible Markup Language). This guide explains how to query a MySQL database and format the data in JSON format with PHP on a Vultr Ubuntu 20.04 server.
To follow along with this guide, ensure you have the following:
A Vultr Ubuntu 20.04 cloud server.
A sudo user.
A LAMP stack.
Log in to the MySQL server as root.
$ sudo mysql -u root -p
Enter the root password for your MySQL server when prompted and hit ENTER to continue.
Create a test_db database.
mysql> CREATE DATABASE test_db;
To access the database, create a test_user with full privileges to the test_db database that you've created above.
Replace EXAMPLE_PASSWORD with the preferred password. Make the password as strong as possible by using a combination of letters, numbers, and symbols.
mysql> CREATE USER 'test_user'@'localhost' IDENTIFIED WITH mysql_native_password BY 'EXAMPLE_PASSWORD';
mysql> GRANT ALL PRIVILEGES ON test_db.* TO 'test_user'@'localhost';
mysql> FLUSH PRIVILEGES;
Switch to the test_db database to make it the currently selected database.
mysql> USE test_db;
Next, create a products table. This table will hold your sample products data that you will query later with a PHP script and display the response in JSON format:
mysql> CREATE TABLE products (
product_id INT AUTO_INCREMENT PRIMARY KEY,
product_name VARCHAR(50),
retail_price DECIMAL(17, 2)
);
Run the commands below one by one to insert sample data into the products table.
Don't enter any value in the product_id column as this will be automatically generated because you've defined the field with an AUTO_INCREMENT keyword.
mysql> INSERT INTO products (product_name, retail_price) VALUES ('CLOUD COMPUTE SERVICE', 2.50);
mysql> INSERT INTO products (product_name, retail_price) VALUES ('BARE METAL', 120.00);
mysql> INSERT INTO products (product_name, retail_price) VALUES ('BLOCK STORAGE', 1.00);
mysql> INSERT INTO products (product_name, retail_price) VALUES ('DEDICATED CLOUD', 60.00);
Ensure that the products were inserted into the database by running the query below.
mysql> SELECT
product_id,
product_name,
retail_price
from products;
Make sure that you get the output shown below.
+------------+-----------------------+--------------+
| product_id | product_name | retail_price |
+------------+-----------------------+--------------+
| 1 | CLOUD COMPUTE SERVICE | 2.50 |
| 2 | BARE METAL | 120.00 |
| 3 | BLOCK STORAGE | 1.00 |
| 4 | DEDICATED CLOUD | 60.00 |
+------------+-----------------------+--------------+
4 rows in set (0.00 sec)
Log out from the MySQL database.
quit;
Next, open a new /var/www/html/products.php file for editing purposes.
$ sudo nano /var/www/html/products.php
Add the information shown below to the file. Match the test_db, test_user, and EXAMPLE_PASSWORD with the correct values that you've used to set up the database in step 1.
<?php
header("Content-Type:application/json");
try {
$db_name = 'test_db';
$db_user = 'test_user';
$db_password = 'EXAMPLE_PASSWORD';
$db_host = 'localhost';
$pdo = new PDO('mysql:host=' . $db_host . '; dbname=' . $db_name, $db_user, $db_password);
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES,false);
$sql = 'select
product_id,
product_name,
retail_price
from products
';
$stmt = $pdo->prepare($sql);
$stmt->execute();
$products = [];
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
$products[] = $row;
}
$response = [];
$response['data'] = $products;
echo json_encode($response, JSON_PRETTY_PRINT);
} catch (PDOException $e) {
echo 'Database error. ' . $e->getMessage();
}
Press CTRL + X, Y then ENTER to save and close the file.
The /var/www/html/products.php file explained:
...
$db_name = 'test_db';
$db_user = 'test_user';
$db_password = 'EXAMPLE_PASSWORD';
$db_host = 'localhost';
$pdo = new PDO('mysql:host=' . $db_host . '; dbname=' . $db_name, $db_user, $db_password);
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES,false);
...
You've used the code block above to connect to your test_db database using PDO (PHP Data Object).
...
$sql = 'select
product_id,
product_name,
retail_price
from products
';
...
The SQL command above retrieves the products' data from the database.
...
$stmt = $pdo->prepare($sql);
$stmt->execute();
...
You've ensured that the data is queried using a prepared statement by entering the two lines above.
...
$products = [];
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
$products[] = $row;
}
...
You've initialized an empty $products array. When fetching the data from the MySQL database using PDO, the while {...} code above inserts all the products to the $products array.
...
$response = [];
$response['data'] = $products;
echo json_encode($response, JSON_PRETTY_PRINT);
...
You've created an empty $response array, and then you've added the $products array to the $response['data'] element.
Finally, you've echoed the JSON encoded response. The JSON_PRETTY_PRINT flag renders the JSON data with proper formatting and alignment.
Enter the URL below and replace the public_ip_address with your server's public IP address to test the PHP script.
http://public_ip_address/products.php
If you've followed this guide, you should now get your products in JSON format as shown below.
{
"data": [
{
"product_id": 1,
"product_name": "CLOUD COMPUTE SERVICE",
"retail_price": "2.50"
},
{
"product_id": 2,
"product_name": "BARE METAL",
"retail_price": "120.00"
},
{
"product_id": 3,
"product_name": "BLOCK STORAGE",
"retail_price": "1.00"
},
{
"product_id": 4,
"product_name": "DEDICATED CLOUD",
"retail_price": "60.00"
}
]
}
Notice in the output above, the PHP script was able to format the data in JSON format. You may consume this data in a web application, mobile application, or desktop application. Please note, JSON data is easy to work with and is one of the most recommended modern data exchange formats for creating APIs.
In this guide, you've used PHP to retrieve and format data in JSON format from a MySQL server on a Ubuntu 20.04 cloud server at Vultr. Adjust the PHP script to suit your needs and add more features to your website. For example, you may change the SQL command to retrieve one product based on PHP GET variables from the URL parameters.