The secret to paginating MySQL results lies in the
LIMIT clause of its
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
> 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 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')
songs will be used for rendering the items for the current page,
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!
Join Geezgo for free. Use Geezgo\’s end-to-end encrypted Chat with your Closenets (friends, relatives, colleague etc) in personalized ways.>>