You are here : Home - Web Development - PHP - Easy Paging using PHP, MySQL

---

Easy Paging using PHP, MySQL

---

This easy to use function will allow you to add paging to your mysql search results.

Scenario : You have a small database and when you output the results it all displays fine. The problem arises as your database grows and more and more results get returned making the page too long. The solution is paging. All it does is count the total of items that will be returned by the query by the number of items you want to display on each page.

So if your search returns 100 results and you want 10 items per page then we simply create 10 (paging) links that allows us to jump to any page.

Using the code below, you can add paging to your existing scripts in no time at all. You may need to tweak some of the variable names etc if they conflict with variables in your current script.

Step 1 - set default values

---

<? 
//paging defaults 
if(!$page){$page=1;} 
if(!$perpage){ 
//$perpage can be passed in a query string to change the records per page (?perpage=10) 
$perpage=5;//how many records per page default to 5 
} 
?>

---

Step 2 - the function

---

<? 
function page_records($page,$pagecount,$recordstart,$COLUMN_NAME,$SEARCH_TERM,$perpage){ 
?> 
<table> 
<tr> 
<? 
for ($i=1; $i <= $pagecount;$i++){ 
if($i != $page){ 
echo"<td>[<a href=\"$PHP_SELF?search=1&COLUMN_NAME=$COLUMN_NAME&SEARCH_TERM=$SEARCH_TERM&page=$i&perpage=$perpage\"> $i </a>]</td>"    ; 
} 
else 
{ 
echo "<td style=\"color:silver;\">[ $i ]</td>"; 
} 
}//end for 
?> 
</tr> 
</table> 
<? 
}//end function 
?>

---

Step 3 get the number of rows/records returned from search after you've made the database connection do your search

---

<? 
//Assume $COLUMN_NAME and $SEARCH_TERM are variables passed from a form or query string 
$query = "SELECT * FROM TABLE WHERE $COLUMN_NAME = '$SEARCH_TERM'"; 
 
$result = mysql_query($query); 
//get how many records are in your search 
$totalreturned = mysql_num_rows($result); 
//to get the pages divide how many records by howmany items per page 
$pagecount = ceil($totalreturned/$perpage);     
//get the record number we want to start from 
$recordstart = ($page-1)*$perpage; 
 
//send all the variables to page_records function 
//replace $SEARCH_TERM , $COLUMN_NAME with your variable name 
page_records($page,$pagecount,$recordstart,$COLUMN_NAME,$SEARCH_TERM,$perpage); 
 
//finally do the search with the record range we want 
$query = "SELECT * FROM TABLE WHERE $COLUMN_NAME LIKE '%$SEARCH_TERM%' LIMIT $recordstart,$perpage"; 
$result = mysql_query($query); 
//the rest of your code goes here 
?>

---

That's about all there is to it. You can easily change the number of items to be diplayed by sending a query string ?perpage=NUM or via a form field name="perpage"

---

Why not discuss this article in our forum?

---

15 Feb 2004