Concept of pagination by SQL Query in MySQL

Before digging deep the Concept of pagination using PHP and MySQL, I would like to introduce you with pagination term.

What is pagination?

Pagination is the procedure of separating content into discrete pages.

In simple words, pagination is an automated process to display content in the form of pages which is broken into separate pages to make them more user-friendly. Pagination allows fetching limited data in chunks which makes data fetching fast and user-friendly.

The Pagination Process.

Let’s discuss the pagination process to understand the concept of pagination. In the process of pagination using PHP and MySQL, we divide the data into chunks and display each section on a separate page in a sequence. To do so, we use the SQL LIMIT. Effective use of SQL LIMIT clause page provides very fine pagination. So let’s start understanding with the help of code and examples.

I am taking a table named `fruits` which has two columns named `id` and `name`. The table is extremely simple and contains data of 100 fruit names and their IDs.

If you are not familiar with SQL queries please click here to read.

How to create pagination: the pagination process

To create pagination, we need to use the select statement. First I will do it by simple SQL queries then will integrate the complete code in PHP with MySQL.

To select a list of complete data is quite handy if the data is not huge. But for heavy data, it is a good practice to use pagination and break the data in parts.

Code example to fetch all the data from table fruits:

SELECT * FROM `fruits`;

Now if going towards pagination, there are a few things which are necessary.

Setting page limit and offset for pagination in the concept of MySQL

First is page limit. A page limit is a number defining how many records are to be fetched in a single query and will be displayed on a single page.

Second is order. Records must be fetched in an order. Like ascending order of ID or Fruits name, Descending order of Id or Fruits name. By default, the order will be ascending order of ID.

Query to display records in pagination.

Let’s take a Limit of 10 records. That means total records will be distributed in 10 pages (10 pages x 10records per page = 100 records).

The order I am considering is, ascending order of Fruits name that means records displayed will be alphabetically sorted regardless of ID.

*A better approach to fetch data is by using column names. You should always fetch only required columns, instead of all the columns. In my case, I have only two columns so I am not using column names.

SELECT * FROM `fruits` ORDER BY `name` ASC LIMIT 0, 10;

This code will display the first ten records in alphabetical order. Here I am using a limit of 10 with offset 0. Offset in limit defines where to start. The offset for the first row starts with 0 (zero) Like in array index starts with 0(zero).

The core of pagination is offset. If there is no offset used the query will display first ten records every time. However, by changing offset the records starting point changes and it signifies the minimum starting point.

The offset for the second page will be 10. The below query will fetch the second-page records.

SELECT * FROM `fruits` ORDER BY `name` ASC LIMIT 10, 10;

Calculate offset for pagination

The calculation for offset is very easy. You can use the below method to calculate the next offset.

NEXT OFFSET = (LAST OFFSET + LIMIT = 0+10 = 10).

In the same way the queries for all the pages from first t last will be:

/** Query for page #1 : frst page **/
SELECT * FROM `fruits` ORDER BY `name` ASC LIMIT 0, 10;
/** Query for page #2 **/
SELECT * FROM `fruits` ORDER BY `name` ASC LIMIT 10, 10;

/** Query for page #3 **/
SELECT * FROM `fruits` ORDER BY `name` ASC LIMIT 20, 10;

/** Query for page #4 **/
SELECT * FROM `fruits` ORDER BY `name` ASC LIMIT 30, 10;

/** Query for page #5 **/
SELECT * FROM `fruits` ORDER BY `name` ASC LIMIT 40, 10;

/** Query for page #6 **/
SELECT * FROM `fruits` ORDER BY `name` ASC LIMIT 50, 10;

/** Query for page #7 **/
SELECT * FROM `fruits` ORDER BY `name` ASC LIMIT 60, 10;

/** Query for page #8 **/
SELECT * FROM `fruits` ORDER BY `name` ASC LIMIT 70, 10;

/** Query for page #9 **/
SELECT * FROM `fruits` ORDER BY `name` ASC LIMIT 80, 10;

/** Query for page #10: last page **/
SELECT * FROM `fruits` ORDER BY `name` ASC LIMIT 90, 10;

The result for the queries will be like below images

sample Data for practice

Below is the export query for the table and data I am using in the operations to create the pagination process. You just need to copy the code below and execute the SQL.

/*
Table structure for table `fruits`
*/

CREATE TABLE `fruits` (
  `id` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY ,
  `name` varchar(100) NOT NULL
);

/*
* Dumping data for table `fruits`
*/

INSERT INTO `fruits` (`id`, `name`) VALUES
(1, 'Apple'),
(2, 'Akee'),
(3, 'Apricot'),
(4, 'Avocado'),
(5, 'Banana'),
(6, 'Bilberry'),
(7, 'Blackberry'),
(8, 'Blackcurrant'),
(9, 'Black sapote'),
(10, 'Blueberry'),
(11, 'Boysenberry'),
(12, 'Buddha\'s hande (fingered citron)'),
(13, 'Crab apples'),
(14, 'Currant'),
(15, 'Cherry'),
(16, 'Cherimoyae(Custard Apple)'),
(17, 'Chico fruit'),
(18, 'Cloudberry'),
(19, 'Coconut'),
(20, 'Cranberry'),
(21, 'Cucumber'),
(22, 'Damson'),
(23, 'Date'),
(24, 'Dragonfruit'),
(25, 'Durian'),
(26, 'Elderberry'),
(27, 'Feijoa'),
(28, 'Fig'),
(29, 'Goji berry'),
(30, 'Gooseberry'),
(31, 'Grape'),
(32, 'Avocado'),
(33, 'Bell pepper'),
(34, 'Chili pepper'),
(35, 'Corn kernel'),
(36, 'Guava'),
(37, 'Honeyberry'),
(38, 'Huckleberry'),
(39, 'Jabuticaba'),
(40, 'Jackfruit'),
(41, 'Jambul'),
(42, 'Japanese plum'),
(43, 'Jostaberry'),
(44, 'Jujube'),
(45, 'Juniper berry'),
(46, 'Kiwanoe'),
(47, 'Kiwifruit'),
(48, 'Kumquat'),
(49, 'Lemon'),
(50, 'Lime'),
(51, 'Loquat'),
(52, 'Longan'),
(53, 'Lychee'),
(54, 'Mango'),
(55, 'Mangosteen'),
(56, 'Marionberry'),
(57, 'Melon'),
(58, 'Miracle fruit'),
(59, 'Mulberry'),
(60, 'Nectarine'),
(61, 'Nance'),
(62, 'Orange'),
(63, 'Cucumber'),
(64, 'Eggplant'),
(65, 'Olive'),
(66, 'Pea'),
(67, 'Papaya'),
(68, 'Passionfruit'),
(69, 'Peach'),
(70, 'Pear'),
(71, 'Persimmon'),
(72, 'Plantain'),
(73, 'Plum'),
(74, 'Pineapple'),
(75, 'Pineberry'),
(76, 'Plumcote'),
(77, 'Pomegranate'),
(78, 'Pomelo'),
(79, 'Purple mangosteen'),
(80, 'Quince'),
(81, 'Raspberry'),
(82, 'Rambutane'),
(83, 'Redcurrant'),
(84, 'Salaleberry'),
(85, 'Salak'),
(86, 'Satsuma'),
(87, 'Soursop'),
(88, 'Star apple'),
(89, 'Star fruit'),
(90, 'Strawberry'),
(91, 'Surinam cherry'),
(92, 'Tamarillo'),
(93, 'Tamarind'),
(94, 'Ugli fruit'),
(95, 'White currant'),
(96, 'White sapote'),
(97, 'Yuzu'),
(98, 'Squash'),
(99, 'Tomato'),
(100, 'Zucchini');

I hope you liked this article, please share it with your friends.