Search Results Pagination
Wednesday June 7, 2006
Recently in our forum TriPan wrote:
I am extremely new to PHP and MYSQL. I managed to connect to MYSQL and get some data on the page but I want some help regarding the formatting. I want the records to be displayed on the same page in multiples of 15 or 20 so that the user doesn't have to scroll down. There are about 75 records.
Pagination is a very handy feature if you have a large database. It makes the pages smaller and easier to read, and can be used with both custom queries or to display the entire database contents. Fortunatly it's not that hard to do, so read our tutorial on Adding Pagination to MySQL Query Results.
I am extremely new to PHP and MYSQL. I managed to connect to MYSQL and get some data on the page but I want some help regarding the formatting. I want the records to be displayed on the same page in multiples of 15 or 20 so that the user doesn't have to scroll down. There are about 75 records.
Pagination is a very handy feature if you have a large database. It makes the pages smaller and easier to read, and can be used with both custom queries or to display the entire database contents. Fortunatly it's not that hard to do, so read our tutorial on Adding Pagination to MySQL Query Results.
Comments
hello,
I have no php or mysql xp but I’m happy to anounce you’ve made a typo in this page
http://php.about.com/od/finishedphp1/ss/php_login_code_2.htm
Login PHP Script - Login PHP - Free Login PHP Script
// now we insert it into the database $insert = “INSERT INTO users (username, password)
That commented the $insert out of the script.
good luck
Fixed
When checking to see how many records you have in your database you use:
$data = mysql_query(”SELECT * FROM topsites”) or die(mysql_error());
This is a very expensive way to do this query as returning ALL (*) items to just determine the number of rows is silly.
Best to just query by selecting only the IDs of the records:
$data = mysql_query(”SELECT id FROM topsites”) or die(mysql_error());
That is faster.
Yes, only pulling one field would be quicker. Like it says in the tutorial, this line needs to be edited to apply to your site
Not sure… but the very first part:
if (!(isset($pagenum)))
{
$pagenum = 1;
}
I changed to this when I tried to use your script:
if(isset($_GET[’pagenum’]))
{
$pagenum = $_GET[’pagenum’];
}
else
{
$pagenum = 1;
}
I don’t think you ever tried to actually get the pagenum from the url. Toodles.
for counting rows is much better to use count() function:
$rows = mysql_result(mysql_query(”SELECT count(*) FROM topsites”),0);
IT is too good
Hi,
I was wondering if you could help me with this code. I am having trouble. I can echo out the number of results as well as the number of pages needed but everytime i click next, the same results are returned on the next page. The URL does change, the next set of results are not. Thanks in advance. Below Is my code.
$last)
{
$pagenum = $last;
}
//This sets the range to display in our query
$max = ‘limit ‘ .($pagenum - 1) * $page_rows .’,’ .$page_rows;
//Retrieves data from MySQL
$data_p = mysql_query(”SELECT * FROM events $max”) or die(mysql_error());
//Puts it into an array
while($info = mysql_fetch_array( $data_p ))
{
//Outputs the image and other data
Echo ” “;
Echo “Name: “.$info[’name’] . ” “;
Echo “Date: “.$info[’date’] . ” “;
Echo “Description: “.$info[’description’] . ” “;
}
// This shows the user what page they are on, and the total number of pages
echo ” –Page $pagenum of $last– “;
// 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 do nothing. If we aren’t then we generate links to the first page, and to the previous page.
if ($pagenum == 1)
{
}
else
{
echo ” “;
echo ” “;
$previous = $pagenum-1;
echo ” “;
}
//just a spacer
echo ” —- “;
//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)
{
}
else {
$next = $pagenum+1;
echo ” Next -> “;
echo ” “;
echo ” Last ->> “;
}
?>
Sorry. Just looks like most of what i pasted didnt go through. Here it is:
$last)
{
$pagenum = $last;
}
//This sets the range to display in our query
$max = ‘limit ‘ .($pagenum - 1) * $page_rows .’,’ .$page_rows;
//Retrieves data from MySQL
$data_p = mysql_query(”SELECT * FROM events $max”) or die(mysql_error());
//Puts it into an array
while($info = mysql_fetch_array( $data_p ))
{
//Outputs the image and other data
Echo ” “;
Echo “Name: “.$info[’name’] . ” “;
Echo “Date: “.$info[’date’] . ” “;
Echo “Description: “.$info[’description’] . ” “;
}
// This shows the user what page they are on, and the total number of pages
echo ” –Page $pagenum of $last– “;
// 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 do nothing. If we aren’t then we generate links to the first page, and to the previous page.
if ($pagenum == 1)
{
}
else
{
echo ” “;
echo ” “;
$previous = $pagenum-1;
echo ” “;
}
//just a spacer
echo ” —- “;
//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)
{
}
else {
$next = $pagenum+1;
echo ” Next -> “;
echo ” “;
echo ” Last ->> “;
}
?>
angela you actually forgot to set variabe $pagenum from querry string
if(!isset($_REQUEST[’pagenum’]))
{
$pagenum=1;
}
else
{
$pagenum=$_REQUEST[’pagenum’];
}
After that It worked well
Thanks
this isn’t about search results paging at all. the title is completely misleading … a select * query is totally easy to paginate. search results are about dynamic queries on one or more columns. the pagination needs to include that query and be capable of writing the next prev link each time to access the found set.
disappointed
Hi,
I am facing a big problem. Here is my code:
$last)
{
$pagenum = $last;
}
$max = ‘limit ‘ .($pagenum - 1) * $page_rows .’,’ .$page_rows;
$data_p = mysql_query(”SELECT * FROM itemsinfotable WHERE ItemName = ‘$option1′ AND Camera = ‘$checkbox’ $max”) or die(mysql_error());
while($row = mysql_fetch_array($data_p)){
echo “$row[ItemModel] compare”;
}
echo ” –Page $pagenum of $last– “;
if ($pagenum == 1)
{
}
else
{
echo ” “;
echo ” “;
$previous = $pagenum-1;
echo ” “;
}
echo ” —- “;
if ($pagenum == $last)
{
}
else {
$next = $pagenum+1;
echo ” Next -> “;
echo ” “;
echo ” Last ->> “;
}
?>
In the above code, while passing the FORM value by POST method to the mysql query, the following lines from the above code fails to execute:
Line 1:
$data = mysql_query(”SELECT * FROM itemsinfotable WHERE ItemName = ‘$option1′ AND Camera = ‘$checkbox’”) or die(mysql_error());
Line 2:
$data_p = mysql_query(”SELECT * FROM itemsinfotable WHERE ItemName = ‘$option1′ AND Camera = ‘$checkbox’ $max”) or die(mysql_error());
I am getting the error on browser as:
” You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘-5,5′ at line 1 ”
Please help… Thanks in advance…
The easiest (and most elegant) way to incorporate pagination of search results is to use a class. The best one is the my_pagina class at finalwebsites.com. It is astonishingly simple to use. Just change the mysql parameters in the db.php and example.php and you are ready to go. No tinkering required at all.