Posts tagged ‘pagination’

20/11/2010

PHP pagination of search results

Displaying data from a database in a webpage is something every web developer needs to do very frequently. I remember my early attempts at displaying a certain number of records per page like a Google search to be immensely frustrating and difficult to get working.

So this is an instructional example I’ve put together,  which is a mishmash of various example scripts I’ve found over the years, and butchered, adapted and refined to fill my requirements. Feel free to use it, or parts of it to help you:

<?php
//Get the page number (if the search has already been done) and the users input from the search form (q)
 $pagenum = @$_GET['pagenum'];
 
$var = @$_GET['q'];

//Slight protection from sql injection

$trimmed = trim($var);

 

if ($trimmed=="")
{
echo "You didn't enter anything to search.";
exit();
}
// Connect to your Database
mysql_connect("localhost", "dbname", "dbpass") or die(mysql_error());
mysql_select_db("dbname") or die(mysql_error());
//This checks to see if there is a page number. If not, it will set it to page 1
if (!(isset($pagenum)))
{
$pagenum = 1;
}
//Here we count the number of results
//Edit $data to be your query
$data = mysql_query("SELECT * FROM dbtable WHERE value1 LIKE '%$trimmed%' || value2 LIKE '%$trimmed%' ORDER BY value1") or die(mysql_error());
$rows = mysql_num_rows($data);
//If no rows are returned, tell the user
if ($rows==0)
{
echo "Sorry, your search: &quot;" . $trimmed . "&quot; returned no results";
exit();}
//This is the number of results displayed per page
$page_rows = 8;
//This tells us the page number of our last page
$last = ceil($rows/$page_rows);
//This makes sure the page number isn't below one, or more than our maximum pages
if ($pagenum < 1)
{
$pagenum = 1;
}
elseif ($pagenum > $last)
{
$pagenum = $last;
}
//This sets the range to display in our query
$max = 'limit ' .($pagenum - 1) * $page_rows .',' .$page_rows;
//This is your query again, the same one... the only difference is we add $max into it
$data_p = mysql_query("SELECT * FROM dbtable WHERE value1 LIKE '%$trimmed%' || value2 LIKE '%$trimmed%' ORDER BY value1 $max") or die(mysql_error());
echo "<td align='center'>";
// This shows the user what page they are on, and the total number of pages
echo "$pagenum of $last<br />";
// First we check if we are on page one. If we are then we don't need a link to the previous page or the first page so we put in our "faded" buttons, (no links). If we aren't then we generate links to the first page, and to the previous page.
if ($pagenum == 1)
{
echo "<img src='../resources/fadefirst.jpg' width='50' alt='First' /><img src='../resources/fadeprevious.jpg' width='50' alt='Previous' />";
}
else
{
echo " <a href='{$_SERVER['PHP_SELF']}?pagenum=1&dvalue=$dvalue&q=$trimmed'><img src='../resources/first.jpg' width='50' alt='First' title='First' /></a> ";
echo " ";
$previous = $pagenum-1;
echo " <a href='{$_SERVER['PHP_SELF']}?pagenum=$previous&dvalue=$dvalue&q=$trimmed'><img src='../resources/previous.jpg' width='50' alt='Previous' title='Previous' /></a> ";
}
//just a spacer
echo "&nbsp;&nbsp;&nbsp;";
//This does the same as above, only checking if we are on the last page, and then generating the Next and Last links
if ($pagenum == $last)
{
echo "<img src='../resources/fadenext.jpg' width='50' alt='Next' /><img src='../resources/fadelast.jpg' width='50' alt='Last' />";
}
else {
$next = $pagenum+1;
echo " <a href='{$_SERVER['PHP_SELF']}?pagenum=$next&dvalue=$dvalue&q=$trimmed'><img src='../resources/next.jpg' width='50' alt='Next' title='Next' /></a> ";
echo " ";
echo " <a href='{$_SERVER['PHP_SELF']}?pagenum=$last&dvalue=$dvalue&q=$trimmed'><img src='../resources/last.jpg' width='50' alt='Last' title='Last' /></a> ";
}
//Set up the headers of the table to display your query results
echo "</td></tr></table></div>
<div><table border='1' width='100%'>
<tr align='center'>
<th width='20%'>Image</th>
<th width='20%'>Item1</th>
<th width='20%'>Item2</th>
<th width='20%'>Item3</th>
<th width='20%'>Item4</th>
</tr>";
//This is where you display your query results
while($info = mysql_fetch_array( $data_p ))
{
$image=$info['image'];
$v1=$info['value1'];
$v2=$info['value2'];
$v3=$info['value3'];
$v4=$info['value4'];
echo "<tr align='center'>";
echo "<td width='20%'> <img src ='$image'> </td>";
echo "<td width='20%'> $v1 </td>";
echo "<td width='20%'> $v2 </td>";
echo "<td width='20%'> $v3 </td>";
echo "<td width='20%'> $v4 </td>";
echo "</tr>";
}
echo "</table>";
// Same Paigation display as earlier - so the user has next/previous/first/last page navigation above and below the table of search results on each page
echo "<div align='center'>";
if ($pagenum == 1)
{
echo "<img src='../resources/fadefirst.jpg' width='50' alt='First' /><img src='../resources/fadeprevious.jpg' width='50' alt='Previous' />";
}
else
{
echo " <a href='{$_SERVER['PHP_SELF']}?pagenum=1&q=$trimmed'><img src='../resources/first.jpg' width='50' alt='First' title='First' /></a> ";
echo " ";
$previous = $pagenum-1;
echo " <a href='{$_SERVER['PHP_SELF']}?pagenum=$previous&q=$trimmed'><img src='../resources/previous.jpg' width='50' alt='Previous' title='Previous' /></a> ";
}
echo "&nbsp;&nbsp;&nbsp;";
if ($pagenum == $last)
{
echo "<img src='../resources/fadenext.jpg' width='50' alt='Next' /><img src='../resources/fadelast.jpg' width='50' alt='Last' />";
}
else {
$next = $pagenum+1;
echo " <a href='{$_SERVER['PHP_SELF']}?pagenum=$next&q=$trimmed'><img src='../resources/next.jpg' width='50' alt='Next' title='Next' /></a> ";
echo " ";
echo " <a href='{$_SERVER['PHP_SELF']}?pagenum=$last&q=$trimmed'><img src='../resources/last.jpg' width='50' alt='Last' title='Last' /></a> ";
}
echo "<br />$pagenum of $last</div>";
?>

 

Tags: ,