Sort PHP Web Resource API with Custom URL Parameters
Introduction
When you're designing web applications such as Application Programming Interfaces (APIs), sorting data is vital. Sorting makes it easier for end-users to read and understand the data. For instance, finding a customer from an alphabetical list of names is faster. In this guide, you'll use PHP and MySQL functions to set up the functionality of sorting data on multiple fields with URL parameters on a web resource.
Prerequisites
Make sure you have the following:
- An Ubuntu 20.04 server.
- A sudo user.
- A LAMP Stack. This guide works with either MySQL or the MariaDB server.
Set up a Sample Database
Connect to your server and run the command below to log in to the MySQL command-line interface.
$ sudo mysql -u root -pKey in the root password of your MySQL database server and press Enter to continue. When you get the mysql> prompt, run the command below to create a company_db database.
mysql> CREATE DATABASE company_db;Set up a sample_user. You'll require this user's credentials to connect to the company_db database with PHP later in this guide.
mysql> CREATE USER 'sample_user'@'localhost' IDENTIFIED WITH mysql_native_password BY 'EXAMPLE_PASSWORD';
mysql> GRANT ALL PRIVILEGES ON company_db.* TO 'sample_user'@'localhost';
mysql> FLUSH PRIVILEGES;Change the syntax for creating the user to the below statement if you're using the MariaDB server.
MariaDB> GRANT ALL PRIVILEGES on company_db.* TO 'sample_user'@'localhost' identified by 'EXAMPLE_PASSWORD';Switch to the company_db database by running the USE statement.
mysql> USE company_db;Set up a clients registration table. This table will store a unique id (client_id), first_name, last_name and a phone number for each client. In a real-world scenario, customer names may appear in the clients table without any logical order, and this is why you require a form of sorting mechanism when designing your PHP API to sort the records.
For now, create the clients table.
mysql> CREATE TABLE clients (
client_id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
first_name VARCHAR(50),
last_name VARCHAR(50),
phone VARCHAR(15)
) ENGINE = InnoDB;Enter some records to the clients table.
mysql> INSERT INTO clients (first_name, last_name, phone) VALUES ('MARY', 'ROE', '22222');
mysql> INSERT INTO clients (first_name, last_name, phone) VALUES ('ZACHARY', 'DUKE', '77777');
mysql> INSERT INTO clients (first_name, last_name, phone) VALUES ('BOB', 'RIGHT', '88888');
mysql> INSERT INTO clients (first_name, last_name, phone) VALUES ('ANTHONY', 'SMITH', '44444');To ensure the records are in place, run the SELECT statement against the clients table.
mysql> SELECT
client_id,
first_name,
last_name,
phone
FROM clients;You should see a list of customers as shown below. As you might have noticed, these names are not arranged in any alphabetical order, and it is just a random list of the client's records.
+-----------+------------+-----------+-------+
| client_id | first_name | last_name | phone |
+-----------+------------+-----------+-------+
| 1 | MARY | ROE | 22222 |
| 2 | ZACHARY | DUKE | 77777 |
| 3 | BOB | RIGHT | 88888 |
| 4 | ANTHONY | SMITH | 44444 |
+-----------+------------+-----------+-------+
4 rows in set (0.00 sec)Exit from the MySQL command-line interface.
mysql> QUIT;Once you're through with initializing the database and the clients' register, you'll now move on to creating an API endpoint that will allow end-users to query the data using custom sort options.
Create a PHP Resource File
Next, you'll create a clients.php web resource file at the root directory of your website /var/www/html/. In this file, you'll connect to the database that you've created in the previous step to query the customers table.
Also, when end-users request the web resource, you'll provide them with the option of sorting the data in the customers table by appending a sort URL parameter. For instance, to sort the clients by first_name, they should form the URL below.
https://localhost/clients.php?sort=first_nameOpen the /var/www/html/clients.php file using nano.
$ sudo rm /var/www/html/clients.php
$ sudo nano /var/www/html/clients.phpOpen a <php tag and add a header function to allow HTTP clients and web browsers to parse the data in JSON format.
<?php
header("Content-Type:application/json");Then, open a try { block and include the database variables below.
try {
$db_name = 'company_db';
$db_user = 'sample_user';
$db_password = 'EXAMPLE_PASSWORD';
$db_host = 'localhost';Create a PHP PDO object and connect it to the database you created earlier in this guide by adding the lines below.
$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);Initialize a list of the column names that you want to be included as sort fields. In this guide, you can just the client_id, first_name and last_name columns. Skip the phone number column for now. Remember, in a production environment, you might not want end-users to sort data on any column.
You're also using the list variable to ignore any field supplied by the end-user that does not exist in the clients table. This is also a good method of minimizing the risk of SQL injection attacks. There is no special reason why you're excluding the phone field in this guide. This demonstrates how some fields can be deliberately skipped from the sort fields' allow list as required in some business workflows.
$sortable_fields = array_map('trim', explode(',', 'client_id, first_name, last_name'));Retrieve the sort URL parameter supplied by any HTTP client requesting your clients.php resource file by adding the information below. The first line below stores the sort fields in a $custom_sort_fields array. Also, initialize an empty $sort variable.
$custom_sort_fields = array_map('trim', explode(',', $_GET['sort']));
$sort = '';Next, add the PHP ...foreach (...) {...} statement below to loop through the $custom_sort_fields array and retrieve independent values supplied in the GET sort variable. The logical statement ...if (!empty($value)) {... }... ensures that you're not including empty values in the sort logic.
Also, by using the statement ...if ($value[0] == '-') {...}... you're catching any sort fields that need to be sorted in descending order(...desc ,...) by checking if the first character is a minus sign -. Otherwise, if there is no preceding negative sign, the column will be arranged in ascending order ...asc ,...
The logic behind the code block below is to create a concatenated string of an SQL sort statement. For instance, when an HTTP client queries the URL http://localhost/clients.php?sort=first_name,-lastname, the script below will produce the concatenated string order by first_name asc, last_name desc.
foreach ($custom_sort_fields as $value) {
if (!empty($value)) {
if ($value[0] == '-'){
if (in_array(ltrim($value, '-'), $sortable_fields)) {
$sort .= ltrim($value, '-'). " desc ,";
}
} else {
if (in_array($value, $sortable_fields) ) {
$sort .= $value . " asc ,";
}
}
}
}
if ($sort != '') {
$sort = ' order by ' . $sort;
$sort = rtrim($sort, ',');
}Next, construct an SQL statement and add the $sort variable with the sort statement concatenated at the end ...from clients ' . $sort;.... Then, execute the SQL command using PDO and create an array of the clients' data.
$sql = 'select
client_id,
first_name,
last_name,
phone
from clients
' . $sort
;
$stmt = $pdo->prepare($sql);
$stmt->execute();
$clients = [];
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
$clients[] = $row;
}
$response = [];
$response['data'] = $clients;Finally, echo out the JSON encoded data and close the PHP ...try {...} catch {...} ... block.
echo json_encode($response, JSON_PRETTY_PRINT) . "\n";
} catch (PDOException $e) {
echo 'Database error. ' . $e->getMessage();
}When you are through with editing the file, it will be similar to the content shown below.
<?php
header("Content-Type:application/json");
try {
$db_name = 'company_db';
$db_user = 'sample_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);
$sortable_fields = array_map('trim', explode(',', 'client_id, first_name, last_name'));
$custom_sort_fields = array_map('trim', explode(',', $_GET['sort']));
$sort = '';
foreach ($custom_sort_fields as $value) {
if (!empty($value)) {
if ($value[0] == '-'){
if (in_array(ltrim($value, '-'), $sortable_fields)) {
$sort .= ltrim($value, '-'). " desc ,";
}
} else {
if (in_array($value, $sortable_fields) ) {
$sort .= $value . " asc ,";
}
}
}
}
if ($sort != '') {
$sort = ' order by ' . $sort;
$sort = rtrim($sort, ',');
}
$sql = 'select
client_id,
first_name,
last_name,
phone
from clients
' . $sort
;
$stmt = $pdo->prepare($sql);
$stmt->execute();
$clients = [];
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
$clients[] = $row;
}
$response = [];
$response['data'] = $clients;
echo json_encode($response, JSON_PRETTY_PRINT) . "\n";
} catch (PDOException $e) {
echo 'Database error. ' . $e->getMessage();
}Save and close the file by pressing Ctrl + X, then Y and Enter.
Test the PHP Application
Use curl to test the PHP application. In the first test, don't supply any sort parameters to the URL.
$ curl localhost/clients.phpYou'll see a list of customers as displayed below just as you entered it in the clients table.
{
"data": [
{
"client_id": 1,
"first_name": "MARY",
"last_name": "ROE",
"phone": "22222"
},
{
"client_id": 2,
"first_name": "ZACHARY",
"last_name": "DUKE",
"phone": "77777"
},
{
"client_id": 3,
"first_name": "BOB",
"last_name": "RIGHT",
"phone": "88888"
},
{
"client_id": 4,
"first_name": "ANTHONY",
"last_name": "SMITH",
"phone": "44444"
}
]
}Now, append a sort URL parameter with a value of first_name to order the customers list by first_name in ascending order.
$ curl localhost/clients.php?sort=first_nameAs you've noted below, the customers' list is now ordered alphabetically by the first_name field.
{
"data": [
{
"client_id": 4,
"first_name": "ANTHONY",
"last_name": "SMITH",
"phone": "44444"
},
{
"client_id": 3,
"first_name": "BOB",
"last_name": "RIGHT",
"phone": "88888"
},
{
"client_id": 1,
"first_name": "MARY",
"last_name": "ROE",
"phone": "22222"
},
{
"client_id": 2,
"first_name": "ZACHARY",
"last_name": "DUKE",
"phone": "77777"
}
]
}Next, order the clients by first_name in descending order by running the command below. Please note that you should precede it with a minus - sign to order any field in descending order.
$ curl localhost/clients.php?sort=-first_nameEnsure you get a list of clients' names ordered by first_name in descending order.
{
"data": [
{
"client_id": 2,
"first_name": "ZACHARY",
"last_name": "DUKE",
"phone": "77777"
},
{
"client_id": 1,
"first_name": "MARY",
"last_name": "ROE",
"phone": "22222"
},
{
"client_id": 3,
"first_name": "BOB",
"last_name": "RIGHT",
"phone": "88888"
},
{
"client_id": 4,
"first_name": "ANTHONY",
"last_name": "SMITH",
"phone": "44444"
}
]
}The PHP code you're implementing is not limited to ordering records by a single column. You can include a comma-separated list of values in the sort field. You can notice a significant difference in such an arrangement if two customers share the first_name.
Enter some new records in the clients table to test that functionality. Log in back to the MySQL command-line console.
$ sudo mysql -u root -pEnter your root password for the MySQL server and hit Enter to continue.
Switch to the company_db database
mysql> USE company_db;Insert some new records into the clients table. These entries will help you to have a good idea of how ordering records on multiple fields works.
Insert the records.
mysql> INSERT INTO clients (first_name, last_name, phone) VALUES ('ZACHARY', 'KATE', '494949');
mysql> INSERT INTO clients (first_name, last_name, phone) VALUES ('ANTHONY', 'DAVE', '16161');Exit from the MySQL interface.
mysql> QUIT;Now query the localhost/clients.php? resource again. This time around, append the string sort=first_name,last_name to order the records by both the first_name and last name fields.
$ curl localhost/clients.php?sort=first_name,last_nameThe output below confirms that the records are being arranged by the first_name and then by last_name.
{
"data": [
{
"client_id": 6,
"first_name": "ANTHONY",
"last_name": "DAVE",
"phone": "16161"
},
{
"client_id": 4,
"first_name": "ANTHONY",
"last_name": "SMITH",
"phone": "44444"
},
{
"client_id": 3,
"first_name": "BOB",
"last_name": "RIGHT",
"phone": "88888"
},
{
"client_id": 1,
"first_name": "MARY",
"last_name": "ROE",
"phone": "22222"
},
{
"client_id": 2,
"first_name": "ZACHARY",
"last_name": "DUKE",
"phone": "77777"
},
{
"client_id": 5,
"first_name": "ZACHARY",
"last_name": "KATE",
"phone": "494949"
}
]
}Run another request, but this time around, order the records by first name in ascending order and finally by last_name in descending order.
$ curl localhost/clients.php?sort=first_name,-last_nameYou should see the JSON response below.
{
"data": [
{
"client_id": 4,
"first_name": "ANTHONY",
"last_name": "SMITH",
"phone": "44444"
},
{
"client_id": 6,
"first_name": "ANTHONY",
"last_name": "DAVE",
"phone": "16161"
},
{
"client_id": 3,
"first_name": "BOB",
"last_name": "RIGHT",
"phone": "88888"
},
{
"client_id": 1,
"first_name": "MARY",
"last_name": "ROE",
"phone": "22222"
},
{
"client_id": 5,
"first_name": "ZACHARY",
"last_name": "KATE",
"phone": "494949"
},
{
"client_id": 2,
"first_name": "ZACHARY",
"last_name": "DUKE",
"phone": "77777"
}
]
}Finally, try to run the query with a non-listed sort field such as phone.
$ curl localhost/clients.php?sort=phoneAs you can see below, the field has been ignored and the PHP API has just returned the records in the order the records were inserted in the database.
{
"data": [
{
"client_id": 1,
"first_name": "MARY",
"last_name": "ROE",
"phone": "22222"
},
{
"client_id": 2,
"first_name": "ZACHARY",
"last_name": "DUKE",
"phone": "77777"
},
{
"client_id": 3,
"first_name": "BOB",
"last_name": "RIGHT",
"phone": "88888"
},
{
"client_id": 4,
"first_name": "ANTHONY",
"last_name": "SMITH",
"phone": "44444"
},
{
"client_id": 5,
"first_name": "ZACHARY",
"last_name": "KATE",
"phone": "494949"
},
{
"client_id": 6,
"first_name": "ANTHONY",
"last_name": "DAVE",
"phone": "16161"
}
]
}For the sake of clarity and to understand how an allow list's sort fields work, edit the /var/www/html/clients.php file and try to include the phone field in the allow list.
Open the file /var/www/html/clients.php using nano.
$ sudo nano /var/www/html/clients.phpNext, locate the below line.
$sortable_fields = array_map('trim', explode(',', 'client_id, first_name, last_name'));Include the phone at the end of the sortable fields by changing the above line to.
$sortable_fields = array_map('trim', explode(',', 'client_id, first_name, last_name, phone'));Save and close the /var/www/html/clients.php.
Now, try to order the records with the phone column and verify if everything is working as expected.
$ curl localhost/clients.php?sort=phoneYour entries should now be ordered with the phone number field, as shown below.
{
"data": [
{
"client_id": 6,
"first_name": "ANTHONY",
"last_name": "DAVE",
"phone": "16161"
},
{
"client_id": 1,
"first_name": "MARY",
"last_name": "ROE",
"phone": "22222"
},
{
"client_id": 4,
"first_name": "ANTHONY",
"last_name": "SMITH",
"phone": "44444"
},
{
"client_id": 5,
"first_name": "ZACHARY",
"last_name": "KATE",
"phone": "494949"
},
{
"client_id": 2,
"first_name": "ZACHARY",
"last_name": "DUKE",
"phone": "77777"
},
{
"client_id": 3,
"first_name": "BOB",
"last_name": "RIGHT",
"phone": "88888"
}
]
}Conclusion
In this guide, you've used PHP and MySQL functions to sort JSON API data. You may extend the coding in this guide to suit your use-case.