MySQL Pagination

How to paginate MySQL results#

The secret to paginating MySQL results lies in the LIMIT clause of its SELECT command.

We'll learn how to paginate results using PHP and Node.js, in order to understand better we'll work with data from a sample table.

Create a new database named learnmysql and run this query on it (we are creating a table named songs and populating it some sample data to work on):

CREATE TABLE `songs` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(50) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=11 ;

INSERT INTO `songs` VALUES(1, 'Paradise City');
INSERT INTO `songs` VALUES(2, 'Get in the Ring');
INSERT INTO `songs` VALUES(3, 'Night Train');
INSERT INTO `songs` VALUES(4, 'Sweet Child O'' Mine');
INSERT INTO `songs` VALUES(5, 'Welcome to the Jungle');
INSERT INTO `songs` VALUES(6, 'Pretty Tied Up');
INSERT INTO `songs` VALUES(7, 'November Rain');
INSERT INTO `songs` VALUES(8, 'Patience');
INSERT INTO `songs` VALUES(9, 'Mr. Brownstone');
INSERT INTO `songs` VALUES(10, 'Rocket Queen');

When you make a simple query like this:

SELECT id, title from songs

MySQL will return you all the rows from the songs table. See what happens when we use the LIMIT clause:

> SELECT id, title from songs LIMIT 0, 3
1 Paradise City
2 Get in the Ring
3 Night Train

> SELECT id, title from songs LIMIT 2, 5
3 Night Train
4 Sweet Child O' Mine
5 Welcome to the Jungle
6 Pretty Tied Up
7 November Rain

> SELECT id, title from songs LIMIT 3, 3
4 Sweet Child O' Mine
5 Welcome to the Jungle
6 Pretty Tied Up

> SELECT id, title from songs LIMIT 6, 3
7 November Rain
8 Patience
9 Mr. Brownstone

Observing a pattern? It certainly looks like LIMIT is the secret to paginating MySQL results.

The first parameter after LIMIT is the id to start your selection from, the second parameter is to limit the number or rows returned.

Here is the commented pseudocode implementation of pagination for our MySQL table:

// which page is being requested. if none specified - assume first page
current_page = request.GET.page || 1
// number of items to display per page
items_per_page = 3
// where should the query start from
start_index = (current_page - 1) * items_per_page

// total items
total_items = mysql.query('SELECT count(id) FROM songs')
// total pages. round it to the upper limit.
total_pages = ceil(total_items / items_per_page)
// items for the current page
songs = mysql.query('SELECT id, title from songs LIMIT $start_index, $items_per_page')

While songs will be used for rendering the items for the current page, current_page and total_pages are required for creating the navigation bar.

I hope this helps you implement pagination of MySQL results in your favorite programming language. Cheers!