Use Custom API Fields with PHP and MySQL on Ubuntu 20.04
Introduction
Allowing end-users to define custom fields when requesting data from your application programming interface (API) endpoints is inevitable. When you're exposing data through a REST API (for instance, a products database), it might contain many fields like product_id, product_name, retail_price, discount, category_name, reviews, ratings and related_products. However, your data consumers may not require a response from all those fields when displaying data in their custom user interfaces, such as a mobile or desktop application.
This is where custom fields come into play. For instance, if you've got an endpoint like http://localhost/products.php that outputs data in JSON format, end-users can append a URL parameter with the names of the fields that they want returned, for example http://localhost/products.php?fields=product_name,category_name,retail_price. In such a scenario, your application logic will parse the custom fields, check them against an allow list, and return the data requested by the HTTP client. This saves bandwidth costs and reduces the load sent to your database server.
In this guide, you'll implement the custom fields functionality in a REST API with PHP and MySQL on Ubuntu 20.04.
Prerequisites
Before you begin, ensure that you have got the following:
- An Ubuntu 20.04 server.
- A sudo user.
- A LAMP Stack. You can either use MySQL or the MariaDB server to complete this tutorial.
Set up the Database
First, connect to your server and run the command below to log in to the MySQL database.
$ sudo mysql -u root -pEnter the root password of your MySQL database server and hit Enter to proceed. Then, create a variety_store database.
mysql> CREATE DATABASE variety_store;Create a variety_store_user for your database. Replace the EXAMPLE_PASSWORD with a strong value. You'll require the user's credentials later when connecting to the database in your PHP script.
mysql> CREATE USER 'variety_store_user'@'localhost' IDENTIFIED WITH mysql_native_password BY 'EXAMPLE_PASSWORD';
mysql> GRANT ALL PRIVILEGES ON variety_store.* TO 'variety_store_user'@'localhost';
mysql> FLUSH PRIVILEGES;Change the statements above to the below command in case you're using the MariaDB server.
MariaDB> GRANT ALL PRIVILEGES on variety_store.* TO 'variety_store_user'@'localhost' identified by 'EXAMPLE_PASSWORD';Switch to the variety_store database.
mysql> USE variety_store;Create a products_categories table.
mysql> CREATE TABLE products_categories (
category_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
category_name VARCHAR(50)
) ENGINE = InnoDB;Populate the products_categories table with three categories.
mysql> INSERT INTO products_categories (category_name) VALUES ('ELECTRONICS');
mysql> INSERT INTO products_categories (category_name) VALUES ('CLOTHING');
mysql> INSERT INTO products_categories (category_name) VALUES ('SHOES');Query the products_categories to make sure the table was populated.
mysql> SELECT
category_id,
category_name
from products_categories;Ensure you get the category list displayed below.
+-------------+---------------+
| category_id | category_name |
+-------------+---------------+
| 1 | ELECTRONICS |
| 2 | CLOTHING |
| 3 | SHOES |
+-------------+---------------+
3 rows in set (0.00 sec)Next, create a products table. This table stores the different items that are available for sale in your store. It has multiple columns including the product_id, product_name, category_id, retail_price and discount. Later in this tutorial, you will implement a PHP script that allows HTTP clients to request the required columns by defining an HTTP GET parameter.
Create the products table.
mysql> CREATE TABLE products (
product_id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
product_name VARCHAR(50),
category_id INT,
retail_price DOUBLE,
discount DOUBLE
) ENGINE = InnoDB;Populate the products table with some records. As you might have noted, the category_id column in this table refers to the same column in the products_categories table.
mysql> INSERT INTO products (product_name, category_id, retail_price, discount) VALUES ('5G PHONE', '1', '420.25', '15.00');
mysql> INSERT INTO products (product_name, category_id, retail_price, discount) VALUES ('LED TORCH', '1', '5.35', '0.00');
mysql> INSERT INTO products (product_name, category_id, retail_price, discount) VALUES ('WOOLEN SOCKS', '2', '9.99', '0.00');
mysql> INSERT INTO products (product_name, category_id, retail_price, discount) VALUES ('LONG SLEEVE POLO', '2', '18.25', '4.25');
mysql> INSERT INTO products (product_name, category_id, retail_price, discount) VALUES ('SUEDE SHOE', '3', '33.40', '3.40');Run a SELECT and a JOIN statement against the products and products_categories tables to ensure the data was inserted in the correct categories.
mysql> SELECT
product_id,
product_name,
products.category_id,
category_name,
retail_price,
discount
FROM products
LEFT JOIN products_categories
ON products.category_id = products_categories.category_id;You should get the following output with the category names spelled out for each product.
+------------+------------------+-------------+---------------+--------------+----------+
| product_id | product_name | category_id | category_name | retail_price | discount |
+------------+------------------+-------------+---------------+--------------+----------+
| 1 | 5G PHONE | 1 | ELECTRONICS | 420.25 | 15 |
| 2 | LED TORCH | 1 | ELECTRONICS | 5.35 | 0 |
| 3 | WOOLEN SOCKS | 2 | CLOTHING | 9.99 | 0 |
| 4 | LONG SLEEVE POLO | 2 | CLOTHING | 18.25 | 4.25 |
| 5 | SUEDE SHOE | 3 | SHOES | 33.4 | 3.4 |
+------------+------------------+-------------+---------------+--------------+----------+
5 rows in set (0.00 sec)Log out from the MySQL command-line interface.
mysql> QUIT;With the database, tables, and sample records in place, you'll now move ahead to create a PHP script that connects to the database to display records in JSON format.
Create a PHP Resource File
Create a new /var/www/html/products.php file in the root directory of your web server using nano.
$ sudo nano /var/www/html/products.phpThen, open a new <?php tag and define a JSON header. Next, open a PHP try { tag and enter the database name and the user's credentials that you've defined earlier.
<?php
header("Content-Type:application/json");
try {
$db_name = 'variety_store';
$db_user = 'variety_store_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);Next, define the default column names that should be returned by your API endpoint in case an HTTP client doesn't provide any custom field. Then, create a $default_fields array from these field names using the PHP explode statement. To trim the whitespaces, use the array_map('trim' statement.
$default_fields = 'product_id,
product_name,
category_id,
category_name,
retail_price,
discount,
sale_price
';
$default_fields = array_map('trim', explode(',', $default_fields));Next use the PHP if (...) {...} else {...} statement to check if the HTTP client has provided comma-separated fields names in the fields URL parameter. Convert the comma-separated values to an array named $columns. Again, use the array_map('trim' statement to remove any whitespaces from the list. In case the HTTP client has not defined any custom fields, assign the default fields defined above to the $columns array.
Use the statement array_intersect($default_fields, $user_fields); to remove any user-defined column names that are not defined in the allowed list to prevent SQL injection.
if (isset($_GET['fields'])) {
$user_fields = array_map('trim', explode(',', $_GET['fields']));
$columns = array_intersect($default_fields, $user_fields);
} else {
$columns = $default_fields;
}Next, you need to map derived columns to the actual database ALIAS names. For instance, if an HTTP client requests a category_id to be included in the column list, this will throw an error because the SQL syntax you're using in this PHP file is linked to two tables, the column name will be ambiguous.
To overcome this challenge, loop through the $columns array and replace category_id with the products.category_id field name. Similarly, the sale_price column defined as one of the default fields is not a real field name from the database. It is a derived column that takes the result of the retail_price minus the discount column.
After field mapping, include the $columns = implode(',', $columns); statement to convert back the $columns array to a clean comma-separated field names and make sure the result is not an empty string.
for ($i = 0 ; $i < count($columns) ; $i++) {
if ($columns[$i] == 'category_id') {
$columns[$i] = 'products.category_id';
}
if ($columns[$i] == 'sale_price') {
$columns[$i] = '(retail_price - discount) as sale_price';
}
}
$columns = implode(',', $columns);
if (trim($columns) == '') {
$error = [];
$error['message'] = "No valid fields are supplied";
echo json_encode($error, JSON_PRETTY_PRINT) . "\n";
exit();
}Finally, construct an SQL statement with the clean column names retrieved from the $columns variable. Execute a SELECT statement against the products and products_categories tables and echo out the data in JSON format. Otherwise, throw any error that the PDO class might encounter.
$sql = 'select '
. $columns
. ' from products
left join products_categories
on products.category_id = products_categories.category_id
';
$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) . "\n";
} catch (PDOException $e) {
$error = [];
$error['message'] = $e->getMessage();
echo json_encode($error, JSON_PRETTY_PRINT) . "\n";
}Save and close the file. When you're done, your /var/www/html/products.php file should be similar to the content below.
<?php
header("Content-Type:application/json");
try {
$db_name = 'variety_store';
$db_user = 'variety_store_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);
$default_fields = 'product_id,
product_name,
category_id,
category_name,
retail_price,
discount,
sale_price
';
$default_fields = array_map('trim', explode(',', $default_fields));
if (isset($_GET['fields'])) {
$user_fields = array_map('trim', explode(',', $_GET['fields']));
$columns = array_intersect($default_fields, $user_fields);
} else {
$columns = $default_fields;
}
for ($i = 0 ; $i < count($columns) ; $i++) {
if ($columns[$i] == 'category_id') {
$columns[$i] = 'products.category_id';
}
if ($columns[$i] == 'sale_price') {
$columns[$i] = '(retail_price - discount) as sale_price';
}
}
$columns = implode(',', $columns);
if (trim($columns) == '') {
$error = [];
$error['message'] = "No valid fields are supplied";
echo json_encode($error, JSON_PRETTY_PRINT) . "\n";
exit();
}
$sql = 'select '
. $columns
. ' from products
left join products_categories
on products.category_id = products_categories.category_id
';
$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) . "\n";
} catch (PDOException $e) {
$error = [];
$error['message'] = $e->getMessage();
echo json_encode($error, JSON_PRETTY_PRINT) . "\n";
}Test the Application
First, request the http://localhost/products.php endpoint without entering any custom field values in the URL.
$ curl http://localhost/products.phpYou should get the following output showing that all field names are returned as defined by the $default_fields variable in the /var/www/html/products.php file. Also, please note that the script calculates the sale_price by subtracting the value of the retail_price from the discount column.
{
"data": [
{
"product_id": 1,
"product_name": "5G PHONE",
"category_id": 1,
"category_name": "ELECTRONICS",
"retail_price": 420.25,
"discount": 15,
"sale_price": 405.25
},
{
"product_id": 2,
"product_name": "LED TORCH",
"category_id": 1,
"category_name": "ELECTRONICS",
"retail_price": 5.35,
"discount": 0,
"sale_price": 5.35
},
{
"product_id": 3,
"product_name": "WOOLEN SOCKS",
"category_id": 2,
"category_name": "CLOTHING",
"retail_price": 9.99,
"discount": 0,
"sale_price": 9.99
},
{
"product_id": 4,
"product_name": "LONG SLEEVE POLO",
"category_id": 2,
"category_name": "CLOTHING",
"retail_price": 18.25,
"discount": 4.25,
"sale_price": 14
},
{
"product_id": 5,
"product_name": "SUEDE SHOE",
"category_id": 3,
"category_name": "SHOES",
"retail_price": 33.4,
"discount": 3.4,
"sale_price": 30
}
]
}Try to enter some custom field names in the URL.
$ curl http://localhost/products.php?fields=product_name,category_name,retail_priceYou should now get an output showing only the product_name, category_name, and retail_price fields as defined in the URL above.
{
"data": [
{
"product_name": "5G PHONE",
"category_name": "ELECTRONICS",
"retail_price": 420.25
},
{
"product_name": "LED TORCH",
"category_name": "ELECTRONICS",
"retail_price": 5.35
},
{
"product_name": "WOOLEN SOCKS",
"category_name": "CLOTHING",
"retail_price": 9.99
},
{
"product_name": "LONG SLEEVE POLO",
"category_name": "CLOTHING",
"retail_price": 18.25
},
{
"product_name": "SUEDE SHOE",
"category_name": "SHOES",
"retail_price": 33.4
}
]
}To retrieve just the product_name column, enter the URL below.
$ curl http://localhost/products.php?fields=product_nameYou should see a list with the product_name field only, as shown below.
{
"data": [
{
"product_name": "5G PHONE"
},
{
"product_name": "LED TORCH"
},
{
"product_name": "WOOLEN SOCKS"
},
{
"product_name": "LONG SLEEVE POLO"
},
{
"product_name": "SUEDE SHOE"
}
]
}Please note if you hit the API endpoints with a mixture of allowed and disallowed field names such as http://localhost/products.php?fields=product_id,zzzz,product_name,yyyyyyy, the invalid ones will be ignored, and only the valid fields will be returned.
$ curl http://localhost/products.php?fields=product_id,zzzz,product_name,yyyyyyyOutput showing the ignored field names.
{
"data": [
{
"product_id": 1,
"product_name": "5G PHONE"
},
{
"product_id": 2,
"product_name": "LED TORCH"
},
{
"product_id": 3,
"product_name": "WOOLEN SOCKS"
},
{
"product_id": 4,
"product_name": "LONG SLEEVE POLO"
},
{
"product_id": 5,
"product_name": "SUEDE SHOE"
}
]
}If you make a request purely with only invalid field names, an error will be thrown.
$ curl http://localhost/products.php?fields=xxx,yyy,zzzError output.
{
"message": "No valid fields are supplied"
}As you can confirm from the outputs above, your API endpoint is working as expected.
Conclusion
In this guide, you've implemented a custom fields functionality in a REST API endpoint with PHP and MySQL on Ubuntu 20.04. You may extend the code snippets in this guide to suit your API use-case.