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):

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 pagecurrent_page = request.GET.page || 1// number of items to display per pageitems_per_page = 3// where should the query start fromstart_index = (current_page - 1) * items_per_page

// total itemstotal_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 pagesongs = 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!