How to Use Custom Fields in a REST API with PHP and MySQL on Ubuntu 20.04
Enabling API consumers to select specific fields when querying your REST API is often essential. If your API—for instance, a product database—exposes a broad dataset containing fields like product_id, product_name, retail_price, discount, category_name, reviews, ratings, and related_products, clients might not need all of them to render a view in a web or mobile application.
This is where field customization proves useful. Suppose you have an endpoint such as http://localhost/products.php returning JSON data. Clients can append a parameter like ?fields=product_name,category_name,retail_price to retrieve only the needed fields. Your backend should process the parameter, validate fields against an allowed list, and return filtered results. This helps reduce bandwidth usage and database workload.
In this step-by-step tutorial, you’ll build the functionality using PHP and MySQL on Ubuntu 20.04.
Prerequisites
Before starting, ensure the following:
- An Ubuntu 20.04 server
- Access to a sudo-enabled user
- A LAMP stack with either MySQL or MariaDB installed
Creating the Database
Connect to your server and log in to MySQL using:
$ sudo mysql -u root -p
After entering your root password, create the database:
mysql> CREATE DATABASE variety_store;
Set up a user for the database (replace EXAMPLE_PASSWORD with a secure password):
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;
If using MariaDB, use the following instead:
MariaDB> GRANT ALL PRIVILEGES on variety_store.* TO 'variety_store_user'@'localhost' identified by 'EXAMPLE_PASSWORD';
Switch to your newly created database:
mysql> USE variety_store;
Creating the Categories Table
Create the table to hold product categories:
mysql> CREATE TABLE products_categories (
category_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
category_name VARCHAR(50)
) ENGINE = InnoDB;
Add some category data:
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');
Confirm the records were inserted:
mysql> SELECT
category_id,
category_name
from products_categories;
Creating the Products Table
Now, create the products table with fields like name, price, and discount:
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;
Insert sample product entries. The category_id links each product to its respective category:
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');
Validating the Data with a Join
Run a join query to confirm that the data was inserted correctly and displays with associated category names:
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;
Sample output should look like this:
+------------+------------------+-------------+---------------+--------------+----------+
| 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 |
+------------+------------------+-------------+---------------+--------------+----------+
Exiting the MySQL Interface
After verifying the setup, exit the MySQL shell:
mysql> QUIT;
Now that your database, tables, and initial records are ready, you’re set to build a PHP script that connects to the database and returns JSON responses based on requested fields.
Creating the PHP Resource File
Next, create the file /var/www/html/products.php on your web server using the nano editor:
$ sudo nano /var/www/html/products.php
Inside this file, begin with opening the PHP tag and setting a header that specifies the output type as JSON. Use a try block to establish the database connection by referencing the credentials you previously defined:
<?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);
Then, define a fallback list of fields to return if the client does not provide specific field names. Transform this list into an array using explode and ensure all values are stripped of whitespace using array_map:
$default_fields = 'product_id,
product_name,
category_id,
category_name,
retail_price,
discount,
sale_price
';
$default_fields = array_map('trim', explode(',', $default_fields));
Use conditional logic to determine if the client sent specific fields using the fields URL parameter. If so, break them into an array and sanitize them. If not, fall back to the default list. Then, use array_intersect to prevent unauthorized fields from being used in the query:
if (isset($_GET['fields'])) {
$user_fields = array_map('trim', explode(',', $_GET['fields']));
$columns = array_intersect($default_fields, $user_fields);
} else {
$columns = $default_fields;
}
Next, handle derived or ambiguous fields. If a user includes category_id, prefix it with the table name to avoid SQL conflicts. Also convert sale_price to its computed SQL expression. Then, convert the field list back into a comma-separated string for use in the query:
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();
}
Now build the SQL query using the selected columns, run it with PDO, and return the results in JSON format. If an error occurs, catch it and return the message:
$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 products.php file. The complete script should now look like the one above and be ready to deliver filtered JSON results to your API consumers.
Testing the REST API Functionality
Start by accessing the endpoint at http://localhost/products.php without any field parameters.
$ curl http://localhost/products.php
This command should return all fields as specified in the $default_fields array within your products.php script. Note that the sale_price field is dynamically calculated by subtracting discount from retail_price.
{
"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
}
]
}
Requesting Specific Fields via URL Parameters
Use custom query parameters to filter fields in the response:
$ curl http://localhost/products.php?fields=product_name,category_name,retail_price
You should see an output with only the selected fields:
{
"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
}
]
}
Retrieving a Single Column
To fetch only product names, execute the following command:
$ curl http://localhost/products.php?fields=product_name
The result will display only the product_name field:
{
"data": [
{
"product_name": "5G PHONE"
},
{
"product_name": "LED TORCH"
},
{
"product_name": "WOOLEN SOCKS"
},
{
"product_name": "LONG SLEEVE POLO"
},
{
"product_name": "SUEDE SHOE"
}
]
}
Using Mixed Valid and Invalid Fields
Even if your query includes unsupported fields, the API will ignore them and return only valid ones:
$ curl http://localhost/products.php?fields=product_id,zzzz,product_name,yyyyyyy
The response will include only valid fields:
{
"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"
}
]
}
Supplying Only Invalid Fields
If the request includes entirely invalid fields, the script will return an error:
$ curl http://localhost/products.php?fields=xxx,yyy,zzz
The output will be:
{
"message": "No valid fields are supplied"
}
This demonstrates that your custom fields REST API is working properly and securely.
Conclusion
In this walkthrough, you’ve built a REST API on Ubuntu 20.04 using PHP and MySQL that supports dynamic field selection by clients. You can now adapt the provided script to better suit your project needs or extend it with additional logic.