Tutorials  /  Databases

Custom Fields in REST API with PHP & MySQL on Ubuntu

Ccentron Redaktion · June 2025 ·5 min read ·Databases, Tutorial

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
DB

Matching infrastructure at centron

Databases need care in production: centron can take over updates, backups and monitoring – from a single instance to a full cluster. Explore managed clusters →

Creating the Database

Connect to your server and log in to MySQL using:

Console
$ sudo mysql -u root -p

After entering your root password, create the database:

Code
mysql> CREATE DATABASE variety_store;

Set up a user for the database (replace EXAMPLE_PASSWORD with a secure password):

Code
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:

Code
MariaDB> GRANT ALL PRIVILEGES on variety_store.* TO 'variety_store_user'@'localhost' identified by 'EXAMPLE_PASSWORD';

Switch to your newly created database:

Code
mysql> USE variety_store;

Creating the Categories Table

Create the table to hold product categories:

Code
mysql> CREATE TABLE products_categories (
       category_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
       category_name VARCHAR(50)
       ) ENGINE = InnoDB;

Add some category data:

Code
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:

Code
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:

Code
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:

Code
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:

Code
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:

Code
+------------+------------------+-------------+---------------+--------------+----------+
| 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:

Code
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:

Console
$ 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
<?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:

Code
$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:

Code
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:

Code
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:

Code
    $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.

Console
$ 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.

JSON
{
"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:

Console
$ curl http://localhost/products.php?fields=product_name,category_name,retail_price

You should see an output with only the selected fields:

JSON
{
    "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:

Console
$ curl http://localhost/products.php?fields=product_name

The result will display only the product_name field:

JSON
{
    "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:

Console
$ curl http://localhost/products.php?fields=product_id,zzzz,product_name,yyyyyyy

The response will include only valid fields:

JSON
{
    "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:

Console
$ curl http://localhost/products.php?fields=xxx,yyy,zzz

The output will be:

JSON
{
    "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.

Jetzt 200 € Guthaben sichern

Testen Sie Ihr Setup auf ccloud³

Registrieren Sie sich in der ccloud³ und erhalten Sie 200 € Startguthaben für Ihr Projekt – z. B. für eine PostgreSQL-VM mit automatischen Backups.

centron Redaktion Technische Redaktion

Das Redaktionsteam von centron schreibt Anleitungen aus dem Betriebsalltag: getestet auf unserer eigenen Plattform, betrieben im Rechenzentrum in Hallstadt bei Bamberg.

Kategorie Databases
Teilen
Noch offene Fragen?

Our team will help you with your specific setup - in German or English, by people who run the platform themselves.

Frage stellen Hilfe durchsuchen
War dieses Tutorial hilfreich?

Your answer is stored anonymously and helps us improve our tutorials.

Kommentare

No comments yet - be the first to ask a question about this tutorial.

Sign in to comment

Comments are open to centron customers. Sign in to your account to ask a question about this tutorial.

Anmelden Konto erstellen
Weiterlesen

Das könnte Sie auch interessieren

Linux Basics

Linux Export Command: Syntax, Examples and Usage

Learn how the Linux export command works, including syntax, options, environment variables, PATH updates, persistent settings and more.
Databases

PostgreSQL-Datenbanken sichern und wiederherstellen mit pg_dump

pg_dump, pg_dumpall und pg_restore – Backups für Migration, Tests und Disaster Recovery.
Python

Wie Sie Unit-Tests in Flask durchführen

Unit-Tests in Flask mit pytest: Testumgebung einrichten, Fixtures anlegen, Routen und Fehlerfälle prüfen und Coverage messen.
← Alle Tutorials ansehen

Jetzt kostenlos anfangen

Melden Sie sich an und erhalten Sie in den ersten 60 Tagen ein Guthaben von 200 € bei centron.

Dieses Werbeangebot gilt nur für neue Konten. Angebot ausschließlich für Gewerbetreibende.

Jetzt loslegen Sales kontaktieren