Author: Francis Ndungu
Last Updated: Thu, Jul 29, 2021Paging is a method of retrieving small, defined chunks from a larger set of data. For example, if you're running an online store with thousands of products, you can display 50 items at a time and give customers navigation buttons to move between the pages.
In PostgreSQL, you can retrieve pages with the LIMIT
and OFFSET
clauses. Use the LIMIT
clause to define the number of rows to retrieve and the OFFSET
keyword to specify the number of rows to skip from the beginning.
To complete this PostgreSQL pagination tutorial, ensure you have the following.
Apache webserver and PHP. For this tutorial, you can skip Step - 2. Install a Database Server since you'll use PostgreSQL as the database server.
SSH to your server and update the package information index.
$ sudo apt -y update
Install the pdo_pgsql
driver. PHP requires this extension to communicate to the PostgreSQL server.
$ sudo apt install -y php-pgsql
Restart Apache to load the new changes.
$ sudo systemctl restart apache2
Log in to your PostgreSQL server as the postgres
user.
$ sudo -u postgres psql
Enter the password and press ENTER to proceed.
Create a test_shop
database.
postgres=# CREATE DATABASE test_shop;
Switch to the new test_shop
database.
postgres=# \c test_shop;
Create a products
table.
test_shop=# CREATE TABLE products (
product_id SERIAL PRIMARY KEY,
product_name VARCHAR (50),
retail_price NUMERIC
);
Insert some data into the products
table.
test_shop=# INSERT INTO products(product_name, retail_price) VALUES ('RAIN JACKET', 55.60 );
INSERT INTO products(product_name, retail_price) VALUES ('LEATHER BELT', 9.63);
INSERT INTO products(product_name, retail_price) VALUES ('WOOLEN SWEATER', 55.32);
INSERT INTO products(product_name, retail_price) VALUES ('JUMP SUIT', 18.30 );
INSERT INTO products(product_name, retail_price) VALUES ('JEANS HANGER', 2.35);
INSERT INTO products(product_name, retail_price) VALUES ('KITCHEN TOWEL', 1.89);
INSERT INTO products(product_name, retail_price) VALUES ('PEN ORGANIZER', 3.65);
INSERT INTO products(product_name, retail_price) VALUES ('RECHARGEABLE TORCH', 22.60);
INSERT INTO products(product_name, retail_price) VALUES ('COTTON SWABS', 1.23);
INSERT INTO products(product_name, retail_price) VALUES ('TOOTH PICKS', 2.20);
INSERT INTO products(product_name, retail_price) VALUES ('USB CABLE', 3.50);
INSERT INTO products(product_name, retail_price) VALUES ('QUICK CHARGER', 35.60);
Make sure the records are in place by executing a SELECT
statement against the products
table.
test_shop=# SELECT
product_id,
product_name,
retail_price
FROM products;
Confirm the output below.
product_id | product_name | retail_price
------------+--------------------+--------------
1 | RAIN JACKET | 55.60
2 | LEATHER BELT | 9.63
3 | WOOLEN SWEATER | 55.32
4 | JUMP SUIT | 18.30
5 | JEANS HANGER | 2.35
6 | KITCHEN TOWEL | 1.89
7 | PEN ORGANIZER | 3.65
8 | RECHARGEABLE TORCH | 22.60
9 | COTTON SWABS | 1.23
10 | TOOTH PICKS | 2.20
11 | USB CABLE | 3.50
12 | QUICK CHARGER | 35.60
(12 rows)
Exit PostgreSQL.
test_shop=# \q
To display paged PostgreSQL data, you'll connect to your database from a PHP script. Then, you'll display the data on a standard HTML web page.
Your PHP file should reside in the root directory of your webserver. To create it, open a new /var/www/html/products.php
file using nano
.
$ sudo nano /var/www/html/products.php
Define a new HTML document and include a title
, a head
, and a body
tag. Then, define an HTML table
. You'll use it to list the products from your database.
<html>
<head>
<title>Paginated PostgreSQL Data</title>
</head>
<body>
<h1 align = 'center'> Products List </h1>
<table align='center' border='1px'>
<tr align='left'>
<th>Id</th>
<th>Product Name</th>
<th>Retail Price</th>
</tr>
Create the PHP content below to connect to the database that you created earlier. Replace EXAMPLE_PASSWORD
with the correct password for the postgres
user.
<?php
try {
$db_name = 'test_shop';
$db_user = 'postgres';
$db_password = 'EXAMPLE_PASSWORD';
$db_host = 'localhost';
$pdo = new PDO('pgsql: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);
Define an $sql_count
statement below. Use the PDO prepare
and execute
methods to get the total count of products and place the results in a $count
variable.
$sql_count = 'select count(*) as count
from products
';
$stmt = $pdo->prepare($sql_count);
$stmt->execute();
$row_count = $stmt->fetch();
$count = $row_count['count'];
In this script, you're retrieving the current page using the page
URL variable. The value changes once you click the navigation links. Use the code below to retrieve the $_GET['page']
variable only if set. Otherwise, use 1
as the default $page
value.
if (isset($_GET['page'])) {
$page = $_GET['page'];
} else {
$page = 1;
}
Assign the variable $per_page
a value; this controls the total products that you want to display on each page. For this tutorial, use 5
. When you visit this script on a web browser, you'll see 5
products on each page. To instruct the PHP script the total number of pages that it should expect, use the formula $total_pages = ceil($count / $per_page);
. This rounds off any decimals since it is not possible to have fractions in pages.
In this guide, your page count($total_pages
) should be 3
since your products
table has 12
records. If you divide 12
by 5
, you'll get 2.4
. After rounding this figure off with the PHP ceil
function, you get 3
pages.
Next, define an $offset
variable. This represents the number of rows to skip when executing the $sql
statement. Determine the value of this variable using the formula ($page - 1) * $per_page;
. To page the results from products
table, include the $per_page
and $offset
variables in the SQL string using the syntax ...limit ' . $per_page . ' offset ' . $offset
.
$per_page = 5;
$offset = ($page - 1) * $per_page;
$total_pages = ceil($count / $per_page);
$sql = 'select
product_id,
product_name,
retail_price
from products
limit ' . $per_page . ' offset ' . $offset ;
Prepare the SQL command above and execute it. When the sql
string is completed, it should execute the following commands depending on the current page
.
Page 1: /products.php?page=1
'select productid, productname, retail_price from products limit 5 offset 0;
Page 2: /products.php?page=2
'select productid, productname, retail_price from products limit 5 offset 5;
Page 3: /products.php?page=3
'select productid, productname, retail_price from products limit 5 offset 10;
Enter the code below to loop through the records and list them on the HTML table.
$stmt = $pdo->prepare($sql);
$stmt->execute();
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
echo '<tr>
<td>' . $row['product_id'] . '</td>
<td>' . $row['product_name'] . '</td>
<td align="right">' . $row['retail_price'] . '</td>
</tr>';
}
Echo out the current page number and the total pages in a new table row.
echo "<tr align='center'>"
. "<td colspan='3'>Page " . $page . " of " . $total_pages . "</td>"
. "</tr>";
Populate the $pagination_urls
variable. This is a list of clickable navigation links for moving between the pages. The First
page and Last
page links are mandatory and will be active on all pages.
$pagination_urls = '';
$pagination_urls .= "<a href='/products.php?page=1'>First </a>";
To display a Previous
page link, get the value of the current page from the $page
variable and decrement it once. Use the PHP if (...) {...} else {...}
to disable the link by removing the href
tag if you're already on the First
page.
if ($page != 1) {
$pagination_urls .= " <a href='/products.php?page=". ($page - 1) . "'>Previous</a>";
} else {
$pagination_urls .= " <a>Previous</a>";
}
To get a link for the Next
page, increment the $page
variable once. Also, disable the Next
page link if you're already on the Last
page.
if ($page != $total_pages) {
$pagination_urls .= " <a href='/products.php?page=". ($page + 1) . "'>Next</a>";
} else {
$pagination_urls .= " <a>Next</a>";
}
$pagination_urls .= " <a href='/products.php?page=" . $total_pages ."'>Last</a>";
Finally, echo out the pagination URLs inside a <td>
tag and close the php
, table
, body
, and html
tags.
echo "<tr align='center'>"
. "<td colspan='3'>" . $pagination_urls . "</td>"
. "</tr>";
} catch (PDOException $e) {
echo 'Database error.' . $e->getMessage();
}
?>
</table>
</body>
</html>
When completed, your PHP file should be similar to the following content.
<html>
<head>
<title>Paginated PostgreSQL Data</title>
</head>
<body>
<h1 align = 'center'> Products List </h1>
<table align='center' border='1px'>
<tr align='left'>
<th>Id</th>
<th>Product Name</th>
<th>Retail Price</th>
</tr>
<?php
try {
$db_name = 'test_shop';
$db_user = 'postgres';
$db_password = 'EXAMPLE_PASSWORD';
$db_host = 'localhost';
$pdo = new PDO('pgsql: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);
$sql_count = 'select count(*) as count
from products
';
$stmt = $pdo->prepare($sql_count);
$stmt->execute();
$row_count = $stmt->fetch();
$count = $row_count['count'];
if (isset($_GET['page'])) {
$page = $_GET['page'];
} else {
$page = 1;
}
$per_page = 10;
$offset = ($page - 1) * $per_page;
$total_pages = ceil($count / $per_page);
$sql = 'select
product_id,
product_name,
retail_price
from products
limit ' . $per_page . ' offset ' . $offset ;
$stmt = $pdo->prepare($sql);
$stmt->execute();
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
echo '<tr>
<td>' . $row['product_id'] . '</td>
<td>' . $row['product_name'] . '</td>
<td align="right">' . $row['retail_price'] . '</td>
</tr>';
}
echo "<tr align='center'>"
. "<td colspan='3'>Page " . $page . " of " . $total_pages . "</td>"
. "</tr>";
$pagination_urls = '';
$pagination_urls .= "<a href='/products.php?page=1'>First </a>";
if ($page != 1) {
$pagination_urls .= " <a href='/products.php?page=". ($page - 1) . "'>Previous</a>";
} else {
$pagination_urls .= " <a>Previous</a>";
}
if ($page != $total_pages) {
$pagination_urls .= " <a href='/products.php?page=". ($page + 1) . "'>Next</a>";
} else {
$pagination_urls .= " <a>Next</a>";
}
$pagination_urls .= " <a href='/products.php?page=" . $total_pages ."'>Last</a>";
echo "<tr align='center'>"
. "<td colspan='3'>" . $pagination_urls . "</td>"
. "</tr>";
} catch (PDOException $e) {
echo 'Database error.' . $e->getMessage();
}
?>
</table>
</body>
</html>
Save the file by pressing CTRL + X, then Y and ENTER. Your PostgreSQL pagination script is now ready for testing.
In a web browser, visit the URL below and replace 192.0.2.1
with your server's correct public IP address or domain name.
http://192.0.2.1/products.php
You should now see the First
page displaying 5
products as shown below. As you can see from the output below, the Previous page link is disabled since you're already on the First
page.
Page 1:
Click Next to navigate to the second page and note how the URL on your web browser changes to include the page
parameter.
Page 2:
Again, click Next or Last to navigate to the Last
page. The Next link is disabled on this page since you're already on the Last
page.
Page 3:
The above outputs confirm that your PHP script is now working as expected.
In this guide, you've created a sample database and a table. You've then populated the table with sample records and coded a PHP script to page data using the PostgreSQL LIMIT
and OFFSET
clauses. Use the syntax in this guide to page your database data when working with many records.