Search result pagination

i want to paginate rows returned in a result of a search function

[Najim]

Hi all,

Am trying to build a basic search but am getting a small code issue here, i want to paginate my searched resultset and this is what i have
1) an input field ID "search" within a form of post method
2) when user submits the form it calls search.php and within that file i set $searchword = $_POST['search']; and i call a function $site->newSearch($site); if isset($searchword)
3) the SQL inside the function works well and returns results as
SELECT * FROM table WHERE column LIKE %$searchword% ORDER BY LIMIT ($page-1)*$limit, $limit

The problem
-----------
the pagination returns a properly paginated result set but when i move to next page all records in the search table are returned including those that do not meet the criteria in the where clause.

pagination code
----
$limit = 12;
$page = 1;

if(isset($_GET['page']))
{
$page=$_GET['page'];
}
$self=$_SERVER['PHP_SELF'];
$Nav="";

if($page > 1)
{
$prev.=" <a href=\"$self?page=".($page-1)."\">previous</a>&nbsp; |";
$first.="<a href=\"$self?page=".($NumberOfPage=1)."\">first</a>&nbsp; |";
}
else
{
$prev = '&nbsp;';
$first = '&nbsp;';
}
for($i=1; $i <=$NumberOfPages; $i++)
{
if($i==$page)
{
$Nav.="<B>$i</b>";
}
else
{ $Nav.="<a href=\"$self?page=".$i .">$i</a>";
}
}

if($page<$NumberOfPages)
{
$next.=" <a href=\"$self?page=".($page+1)."\">next</a>&nbsp; |";
$last.="&nbsp;<a href=\"$self?page=".($NumberOfPages)."\" >last</a>";
} else {
$next = '&nbsp;';
$last = '&nbsp;';
}

The results gets properly paginated on search.php but when i navigate to next page i get all results in the table.
ANY HELP..

[Matthew1980]

Hi there Najim,

This code:-

($page-1)*$limit, $limit

I'm wondering why you are asking the same var twice, when the last one should be the limit? :-

SELECT * FROM table WHERE column LIKE %$searchword% ORDER BY desc LIMIT 10,20

Also you have specified order by but no clause to the effect?

*Typed on the fly so syntax may be incorrect*

This says, from the 10th record, return the next 20 results, If I have read this wrong, my apologies, but that is how I have always understood it to work. :)

Also what is the URL telling you (or the status bar) when you hover/click the link?

Cheers,
MRb

[Najim]

@ matthew1980
Am sorry i made a typo in the above SQL, its
"SELECT * FROM table WHERE column LIKE %$searchword% ORDER BY item_name ASC LIMIT ".($page-1)*$limit.", $limit";

the url on the status bar is www.domain.com/search.php?page=2 when you hover/click next link

and this code ($page-1)*$limit,$limit is supposed to $_GET[page] hoping the result would then become 12,12
saying from 12th record, return next 12 result

if the table initially has 10 page and above query returns 3 pages, when i try navigate to next i go to 2nd page of 10 not of 3 as the query had returned, hope am a bit clear there?

[Readie]

try doing something like this:

$sql = 'SELECT * FROM table WHERE column LIKE "%' . mysql_real_escape_string($searchword) . '%" ORDER BY item_name ASC LIMIT ' . (($page - 1) * $limit) . ', ' . $limit;

Note: I've added quotes around %$searchword% to prevent an E_WARNING being sent if it's not an integer

[Matthew1980]

hi there Najim,

This may have nothing to do with the problem, but I noticed this:-

if($page > 1)
{
$prev.=" <a href=\"$self?page=".($page-1)."\">previous</a>&nbsp; |";
$first.="<a href=\"$self?page=".($NumberOfPage=1)."\">first</a>&nbsp; |";
}

I think there is an "s" missing off the end there, as every other reference to this is $NumberOfPages

and don't forget to: mysql_real_escape_string(strip_tags($_GET['var_name'])); whenever you are using get's and post's in a query!


Cheers,
MRb

[rocknbil]

Pagination is indeed more complex than it seems when you fully digest all the potential problems. :-) The previous posts touch on the first problem - database queries start at 0

limit 0, 10

but you want that to display in text as 1-10.

It gets worse when you have many results, and want to do

1-10 11-20 21-24

where it doesn't break evenly on your per page value. Or when you add "next" and previous."

Then multiply that by say, 2 million records . . . the resulting links will absolutely choke any browser.

I have a home grown solution that addresses all these (probably on this site, amongst my other ramblings,) but this is one of those cases where you don't want to re-invent the wheel, it will drain your day. :-) Aside from the many pagination classes out there:

elixduppy's solution [webmasterworld.com] (with lotsa links)

814 examples on this site [google.com]

Or, locate a class, there are many out there.

[Najim]

Well, first i have to thank all of you for the tech support you are offering me right here...

i have really spent some time to check out the previous posts on WW regarding pagination but i felt i had to publish my search function hoping someone here will help me with a trip that will definitely change my life.

this is my function
-------------------

function newSearch($searchword)
{
$limit = 12; // items on a page
$page = 1;

if(isset($_GET['page']))
{
$page=$_GET['page'];
}
$query = mysql_query("SELECT ".TBL_PRODS.".*, ".TBL_CAT.".*, ".TBL_GRP.".group_id FROM
".TBL_GRP.", ".TBL_CAT.", ".TBL_PRODS."
WHERE ".TBL_CAT.".cat_id = ".TBL_PRODS.".cat_id AND ".TBL_GRP.".group_id = ".TBL_CAT.".group_id
AND ".TBL_PRODS.".prod_name like '%".mysql_real_escape_string($searchword)."%'
AND ".TBL_PRODS.".isActive = 'Y'
ORDER BY ".TBL_PRODS.".prod_name ASC") or die (mysql_error());

$NumberOfResults = mysql_num_rows($query);

$NumberOfPages = ceil($NumberOfResults/$limit);

$query = "SELECT ".TBL_PRODS.".*, ".TBL_CAT.".*, ".TBL_GRP.".group_id FROM
".TBL_GRP.", ".TBL_CAT.", ".TBL_PRODS."
WHERE ".TBL_CAT.".cat_id = ".TBL_PRODS.".cat_id AND ".TBL_GRP.".group_id = ".TBL_CAT.".group_id
AND ".TBL_PRODS.".prod_name like '%".mysql_real_escape_string($searchword)."%'
AND ".TBL_PRODS.".isActive = 'Y'
ORDER BY ".TBL_PRODS.".prod_name ASC LIMIT ".(($page - 1) * $limit).", $limit";

$result= mysql_query($query);


$self = $_SERVER['PHP_SELF'];
$Nav = "";


if($page > 1)
{
$prev.=" <a href=\"$self?page=".($page-1)."\">previous</a>&nbsp; |";
$first.="<a href=\"$self?page=".($NumberOfPages=1)."\">first</a>&nbsp; |";
}
else
{
$prev = '&nbsp;';
$first = '&nbsp;';
}
for($i=1; $i <=$NumberOfPages; $i++)
{
if($i==$page)
{
$Nav.="<B>$i</b>";
}
else
{ $Nav.="<a href=\"$self?page=".$i .">$i</a>";
}
}

if($page<$NumberOfPages)
{
$next.=" <a href=\"$self?page=".($page+1)."\">next</a>&nbsp; |";
$last.="&nbsp;<a href=\"$self?page=".($NumberOfPages)."\" >last</a>";
} else {
$next = '&nbsp;';
$last = '&nbsp;';//Up to here nothing is printed yet on the browser, pagination only.
}


print '<div id="top_tabla"><strong>::.</strong><span> Search </span></div>

<p>('.$NumberOfResults.') matches found </p>';

if($NumberOfResults == 0)
{ // search did not return any values
print('<p>&nbsp;Sorry, there is currently no item matching your criteria.</p>');
}

// printing navigation if the resultset above holds values
if ($NumberOfResults != 0)
{
print('<div class="nav_number">
&nbsp;viewing page&nbsp;'.$page.' of '.$NumberOfPages.'&nbsp;&nbsp;&nbsp;'.$first.$next.$prev.$last.'
</div>');

}


$numcols =3; // Number of per page
$numcolsprinted = 3; // how many columns we have so far, do not change!
// get the results to be displayed in this set
// get each row

while ($row = mysql_fetch_array($result))
{
$id =$row['prod_id'];
$prod = stripslashes(strtolower($row["prod_name"]));
$price = number_format($format,0,',',',');
$ad_id = $row['ADid'];


// Now query for the thumb nail images
$im = mysql_query("SELECT * FROM ".TBL_IMG." WHERE ADid = '$ad_id' and isThumb = 'Y'") or die(mysql_error());
$rs_img = mysql_fetch_array($im);
$imageurl = $rs_img['file_name'];

if($imageurl=='')
{
$imageurl="nophoto.jpg";
}

// we have to check to see if a row has been completed and if it has, start a new one
if ($numcolsprinted == $numcols)
{
print "<table width=\"95%\" id=\"ad_data\">
<tr><td colspan=\"4\" class=\"spacer\">&nbsp;</td></tr><tr>";
$numcolsprinted =0;
}

$limiter = 40; //trimming the product name
if(strlen($prod)>$limiter) $prod = substr($prod, 0, strrpos(substr($prod, 0, $limiter), ' ')) . '...';

$link = "products.php?group=".urlencode($row['group_id'])."&cat=".urlencode($row['cat_id'])."
&prod=".urlencode($id)."&adid=".urlencode($row['ADid'])."";

print "<td width=\"30%\">
<a href=\"$link\"> <img src=\"picture/s2-".$imageurl."\" class=\"picAd\"> </a>
<div>
<a href=\"$link\"> ".ucwords($prod)."</a><br /><br />";
if($row['pro_price'] == "")
{
print '<b>'.$row['pro_basis'].'</b><br /><br />';
}

print "</div>
</td>";

$numcolsprinted++; // increment row counter
}// end while loop, at this point we have all the results
// now we need to pad out the table with empty cells if there's a final row with
// fewer results than the number of columns we want to display

$colstobalance = $numcols - $numcolsprinted;

for ($i=1; $i<=$colstobalance; $i++) {
print "<td></td></tr>";
}
print '</table>';
}
-----------
this is how i call the function within search.php

$search = stripslashes($_POST['search']);
if(isset($search))
{
$site->newSearch($search);
}
-----------
@rocknbill - elixduppy's solution works great but when i come to nesting tables i get issues...

Live site: <snip> , try searching something like "f" 28 matches will be found but when i get to next bluuuuuuuu, everything in the table appears..

i feel am missing something small.. so please save me guardian PRO's

[edited by: eelixduppy at 4:08 pm (utc) on Mar 26, 2010]
[edit reason] no personal URLs, please [/edit]