The AppGini Blog
A few tips and tricks to make your coding life a tiny bit better.

How to paginate a database query in PHP the no-frameworks way

OK, I admit! Every time I have to write similar code I get stuck rethinking it — every time … If I wasted 10 minutes thinking of it every time, and I did so for 100 times, then I’ve already wasted more than 2 working days in the last 12 years doing it! I guess it’s time to stop that loss!

So, the issue I’m talking about is when having a web page (or a web service, or a command line utility, or any other interface) that lists a set of records from a database, and then the user wants to retrieve the next set of records, this is called pagination. If I’m not explaining it clearly, feel free to look it up in Wikipedia .

So, how should pagination be handled in the server side? Here is the most recent way I did it … If you have a better suggestion, feel free to post your comment below. I’ll be using this from now, and update it as necessary:

/* first, receive user input -- specifically, the requested page number */
$page = 1; /* default is page 1 */
if(isset($_REQUEST['page'])) $page = intval($_REQUEST['page']);
if($page < 1) $page = 1;

/* how many records per page? Here I define it in code, but could as well be
specified by user or stored in some config file or whatever ... */
define('results_per_page', 50);

/* now, the offset formula -- the trickiest part! */
$skip = results_per_page * ($page - 1);

/* finally, apply this in the query */
$query = "select foo, bar from baz limit {$skip}, " . results_per_page;

The tricky part that wastes most of the time in the above code is line 12 .. that is, figuring out the formula for calculating the offset, or the number of records to skip from the query .. it’s a simple one, I know, but if you’re like me and get confused by the zero-one worlds … it could drag your mind for some time! So, I hope this would save you 10 minutes in your next coding adventure.