Subscribe to RSS feedReally simple PHP pagination
By Martin / Tutorials, PHP / Saturday August 16th, 2008 / 9 comments
Recently, the post count became so high for this blog that I had to implement pagination for them. Pagination can be quite troublesome sometimes, but eventually I came up with a really simple solution.
For the page, we simply use the querystring. In this example, let us use $_GET['page'] to initiallize the $page variable. If the GET var isn't set we default to page 1.
$page = isset($_GET['page']) ? (int) $_GET['page'] : 1;
Now, we want to figure out how many pages are in our query. For this, we can use the COUNT() SQL function, then divide the number returned by the amount of results per page we want.
$pages = implode(mysql_fetch_assoc(mysql_query("SELECT COUNT(key) FROM table")));
$pages = ceil($pages / 6);
Now, because we may have other GET variables set we want to preserve the querystring. To do this, we just assemble all the current variables (except the page one, of course) into a string.
$querystring = "";
foreach ($_GET as $key => $value) {
if ($key != "page") $querystring .= "$key=$value&";
}
Then, we can loop through the pages, and echo out the links to them. Using a conditional or ternary operator we can also assign a different class to the current page.
echo "Pages: ";
for ($i = 1; $i <= $pages; $i++) {
echo "<a " . ($i == $page ? "class=\"selected\" " : "");
echo "href=\"?{$querystring}page=$i";
echo "\">$i</a> ";
}
Then, to display only the results that are relevant to the current page we can use the LIMIT SQL clause, or in this case LIMIT (($page - 1) * 6), 6 (because we have 6 results per page, and the first page needs to query from the 0th record).
$result = mysql_query("SELECT * FROM table LIMIT " . (($page - 1) * 6) . ", 6");
∎
I hope you enjoyed reading this article. If you wish, you may view some of the other recent or popular things I have written, or subscribe to receive RSS updates. You can also add a comment, or share this article on Twitter or Facebook, below.
9 comments
boen_robot I believe it would be far more efficient to just cast two queries at the same time. One with LIMIT, and one with SELECT FOUND_ROWS() like so:
"SELECT SQL_CALC_FOUND_ROWS * FROM table LIMIT $page, 6;
SELECT FOUND_ROWS();"
Then again, after reading the PHP documentation on MySQL, it seems mysql_query() doesn't support multiple queries. Have you considered upgrading to MySQLi? I know for sure it has a multy query aware function.
Martin Hi boen_robot,
I haven't looked much at mysqli, but you are right, there should be an efficiency gain if FOUND_ROWS was used instead of a seperate COUNT query.
Pityu Hy.
I found it really useful, just, it won't show the last entry, and I don't know why :(
silly $result = mysql_query("SELECT * FROM table LIMIT " . (($page - 1) * 6) . ", 6);
should be
$result = mysql_query("SELECT * FROM table LIMIT " . (($page - 1) * 6) . ", 6");
You have a missing parenthesis after the last 6
Martin Fixed, thanks!
Chris How can you use this solution with results filtering? My filter stops working when I go from page 1 to 2.
Thanks!
Martin It depends on how your filter works, but in general, using my method you just need to make sure to apply the filtering clauses when counting the number of pages as well.
Totage I'm getting the following errors when I try to add this code to my script:
Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in /***/public_html/forums/view_topic.php on line 51
Warning: implode() [function.implode]: Argument must be an array in /***/public_html/forums/view_topic.php on line 51
Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /***/public_html/forums/view_topic.php on line 60
Code snippet:
// get value of id that sent from address bar
$id=$_GET['id'];
$sql="SELECT * FROM $tbl_name WHERE id='$id'";
$result=mysql_query($sql);
$rows=mysql_fetch_array($result);
?>
<strong><? echo $rows['topic']; ?></strong><table width="400" border="0" align="center" cellpadding="0" cellspacing="1" bgcolor="#CCCCCC"><tr><td>
<table width="100%" border="0" cellpadding="3" cellspacing="1" bordercolor="1" bgcolor="#FFFFFF"><tr><td bgcolor="#F8F7F1"></strong><? echo $rows['datetime']; ?> <strong>
<? echo $rows['name']; ?> :</td></tr><tr><td bgcolor="#F8F7F1"><? echo $rows['detail']; ?></td></tr></table></td></tr></table><br>
<?php
$pr=15;
$page = isset($_GET['page']) ? (int) $_GET['page'] : 1;
$pages = implode(mysql_fetch_assoc(mysql_query("SELECT COUNT(question_id='$id) FROM $tbl_name2")));
$pages = ceil($pages / $pr);
$querystring = "";
foreach ($_GET as $key => $value) {
if ($key != "page") $querystring .= "$key=$value&";
}
$result = mysql_query("SELECT * FROM $tbl_name2 WHERE question_id='$id LIMIT " . (($page - 1) * $pr) . ", $pr");
while ($info=mysql_fetch_array($result)) {
?>
<table width="400" border="0" align="center" cellpadding="0" cellspacing="1" bgcolor="#CCCCCC"><tr><td>
<table width="100%" border="0" cellpadding="3" cellspacing="1" bordercolor="1" bgcolor="#FFFFFF"><tr><td bgcolor="#F8F7F1"></strong><? echo $info['a_datetime']; ?> <strong>
<? echo $info['a_name']; ?> :</td></tr><tr><td bgcolor="#F8F7F1"><? echo $info['a_answer']; ?></td></tr></table></td></tr></table>
<?
}
echo "Pages: ";
for ($i = 1; $i <= $pages; $i++) {
echo "<a " . ($i == $page ? "class=\"selected\" " : "");
echo "href=\"?{$querystring}page=$i";
echo "\">$i</a> ";
}
What am I doing wrong? TIA for any help.
javed how to count num_rows if we joins 4 tables. and want to display data in paging