Gavin Benda
Pagination in PHP and mySQL
Another little mini-tutorial to a commonly sought-after feature in a lot of applications. When you are dealing with large sets of data, i.e. thousands of rows, it is nessicary to split your results up over a number of pages, say 100 results per page. We will be using the LIMIT feature of mySQL to do our dirty work, and it will make our job rather easy!
SELECT * FROM products LIMIT 50
This query will simply return the first 50 results and stop, we can however add another argument to this and have mySQL only return the "second" set of 50 results (50-100) like so:
SELECT * FROM products LIMIT 50,50
This tells our database to "start at result 50, and retrieve 50 more beyond that". Ok, so with our database basics out of the way, onto the PHP. We'll start by setting up a couple of variables such as how many results to display per page, and which page we should be starting at from the GET variable, i.e. results.php?page=5
$perpage = 100; // maximum results per page
$startpage = (isset($_GET['page'])) ? intval($_GET['page']) : 0;
To explain the 2nd line of code above, I'm using an inline if statement to check if a specific page has been requested, and if it is, we'll ensure it's an integer to protect our query from any SQL injection. If nothing is set, it will simply default to zero (page 1 of results).
The next part is quite easy, we simply add our query to this code and use our variables above to define which data should be selected. To get our "start page" we simply multiply the page number by how many we're displaying per page, for example "page 5" is 5*100 = start at result number 500, and retrieve 50 more results.
$perpage = 100; // maximum results per page
$startpage = (isset($_GET['page'])) ? intval($_GET['page']) : 0;
$results = mysql_query('SELECT * FROM products LIMIT '. ($startpage*$perpage) . ','. $perpage);
To calculate exacly how many pages you have in total, you'll have to run a simple query such as:
SELECT count(*) as total FROM products
simply divide the total by your perpage variable, and there you have it!
Comments on this article:
#2 Michael says:
$startpage = (isset($_GET['page'])) ? intval($_GET['page']) : 0;
Could be expressed as:
$startpage = isset($_GET['page']) ? (int) $_GET['page'] : 0;
Although being valid the second pair of parentheses isn’t required, as you do not need to change operator precedence. Also type casting is not only faster than the intval function call; it is also imo more readable AND also easier/ quicker to type.
#3 Gavin says:
@Michael
Although technically correct, I'd say the difference between typecasting the value as an int, and calling the intval function is pretty much a non-issue. Over 100,000 calls, you only save something like 12 milliseconds in total. ;)
#4 Michael says:
Whilst I acknowledge the cpu time would be minimal on todays hardware, any easy advantage should be taken.
When a function is called alot of things happen, PHP has to deal with globals, symbol tables, most of this is on the stack and causes major performance issues during recursive functions.
It is approximately 3x faster to use type casting over intval. In general I'd say it's good practise to use type casting as it is easier to type and has other benefits.
And on a Xeon 5120 Dual Core processor, 100,000 iterations came out 34 milliseconds difference at 1,000,000 was 123 milliseconds (in favor of typecasting of course).
However, on a more average computer, (P4 2.6Ghz-HT), the difference was 200 milliseconds (100,000 iterations) and 800 milliseconds (1,000,000 iterations) (In favor of type casting).
The difference is there, not enough to go and change every intval() in a program, but definately something everyone should be aware of when they are programming.
#5 Michael says:
"To calculate exactly how many pages you have in total, you'll have to run a simple query such as:
SELECT count(*) as total FROM products"
MySQL and other SQL servers allow SQL_CALC_FOUND_ROWS which will count and store the amount of rows if the LIMIT clause wasn't there. You can then use FOUND_ROWS() straight after to find the amount of rows in the previous query.
For example:
SELECT SQL_CALC _FOUND_ROWS * FROM `table` LIMIT 0,100;
SELECT FOUND_ROWS();
This saves the SQL server from having to execute the query twice. This can speed things up a lot if you are paginating search results or other complex result set.
Add Comment:
old windmill
google showing forum thread details
mountain sunset
png transparencies
New canon prime lens
photw: accomodation hill
mdadm RAID using an existing drive
new business and changes
photw: koala
photw: fire sticks
netforge provides high quality and friendly website design services to business. We're Australian based and reliable... (find out more).
Netforge
Richard O'neill
Jeroen Mulder
Adam Polselli
DailyWTF
Matt Cutts
#1 Jeroen Mulder says:
Pagination is indeed ridiculously easy. An added 'trick' I always do is subtract the page variable by one (if the value is 1 or more), so the value 1 will correspond to the first resultset, instead of the second one.