Implementing Custom Field Selection in a REST API Using PHP and MySQL on Ubuntu 20.04
Offering users the ability to select specific fields when requesting data from your API is a common and practical feature. If your REST API delivers product data—perhaps with fields like product_id, product_name, retail_price, discount, category_name, reviews, ratings, and related_products—most clients will only need a subset of this data for their application UI, such as on mobile or desktop platforms.
This is where selective field responses become useful. For example, if you use an endpoint like http://localhost/products.php that returns JSON, users could refine the response by appending a fields parameter, such as http://localhost/products.php?fields=product_name,category_name,retail_price. Your backend logic would interpret this request, verify the requested fields against a predefined allow list, and return only those, thereby minimizing bandwidth use and server load.
This tutorial demonstrates how to set up that functionality in a REST API using PHP and MySQL on Ubuntu 20.04.
Requirements
Before getting started, ensure the following are available:
- An Ubuntu 20.04 server
- A user account with sudo privileges
- A LAMP stack installed (with either MySQL or MariaDB)
Creating the Database
Start by connecting to your server and accessing MySQL with the command below:
$ sudo mysql -u root -p
After entering the MySQL root password, create a new database named variety_store:
mysql> CREATE DATABASE variety_store;
Next, add a dedicated user for the database. Replace EXAMPLE_PASSWORD with a secure password of your choice:
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 you’re using MariaDB, modify the grant command as follows:
MariaDB> GRANT ALL PRIVILEGES on variety_store.* TO ‘variety_store_user’@’localhost’ identified by ‘EXAMPLE_PASSWORD’;
Switch to the variety_store database to begin defining your schema:
mysql> USE variety_store;
Defining Tables and Inserting Data
Creating the Product Categories Table
Define a 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 a few sample categories to the table:
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’);
Verify that the data was inserted correctly:
mysql> SELECT
category_id,
category_name
FROM products_categories;
The output should resemble the following:
+-------------+---------------+ | category_id | category_name | +-------------+---------------+ | 1 | ELECTRONICS | | 2 | CLOTHING | | 3 | SHOES | +-------------+---------------+ 3 rows in set (0.00 sec)
Creating the Products Table
Now define the main products table, which includes information like pricing, category links, and discounts:
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 a few product entries, matching them with their corresponding category IDs:
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’);
Confirm the entries by running a join query to see products alongside their 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;
The expected 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 | +------------+------------------+-------------+---------------+--------------+----------+ 5 rows in set (0.00 sec)
To exit the MySQL prompt, run:
mysql> QUIT;
With your database schema and sample data now prepared, you’re ready to proceed with creating the PHP script that connects to this database and returns the data in JSON format.
Building the PHP Resource File
Next, create the products.php file under your web server’s root directory using nano:
$ sudo nano /var/www/html/products.php
Start the file with a <?php tag, then set the response type to JSON. Inside a try block, define the database name and access credentials you configured earlier.
setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
Set up a list of default fields to return if the client request doesn’t specify any particular ones. Use explode and array_map('trim') to split the fields into a clean array.
$default_fields = ‘product_id,
product_name,
category_id,
category_name,
retail_price,
discount,
sale_price
‘;
$default_fields = array_map(‘trim’, explode(‘,’, $default_fields));
Check for the fields parameter in the URL. If present, parse it into the $user_fields array and remove any unauthorized fields using array_intersect. Otherwise, fall back to $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;
}
Before executing the query, convert ambiguous or virtual field names. For example, category_id must be namespaced as products.category_id, and sale_price must be defined as a computed field.
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();
}
<h4>Now build the SQL query using the sanitized column list. Join the products and products_categories tables, execute the query, and return the result in JSON format.</h4>
$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 the file and close the editor. At this point, your /var/www/html/products.php file should look exactly like the example provided above.
Testing the API Endpoint
Begin by sending a request to the http://localhost/products.php endpoint without specifying any field filters:
$ curl http://localhost/products.php
You should see a response containing all default fields as defined in your PHP script. Note that the sale_price value is dynamically computed 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
}
]
}
Now test the endpoint by requesting a specific set of fields using URL parameters:
$ curl http://localhost/products.php?fields=product_name,category_name,retail_price
The response should only include 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
}
]
}
To fetch only one field, such as product_name, try the following:
$ curl http://localhost/products.php?fields=product_name
The resulting data should contain 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"
}
]
}
If a user mixes valid and invalid field names—such as product_id,zzzz,product_name,yyyyyyy—only valid fields will be returned:
$ curl http://localhost/products.php?fields=product_id,zzzz,product_name,yyyyyyy
{
"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"
}
]
}
However, if the request contains only invalid field names, such as:
$ curl http://localhost/products.php?fields=xxx,yyy,zzz
The API will return an error message:
{
"message": "No valid fields are supplied"
}
Conclusion
In this tutorial, you’ve successfully built a PHP-based REST API that supports dynamic field selection using MySQL on Ubuntu 20.04. You can now tailor this foundation to accommodate more advanced features depending on your project’s requirements.
