| Search result pagination
i want to paginate rows returned in a result of a search function |
Najim
msg:4104371 | 12:29 pm on Mar 25, 2010 (gmt 0) |
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> |";
$first.="<a href=\"$self?page=".($NumberOfPage=1)."\">first</a> |";
}
else
{
$prev = ' ';
$first = ' ';
}
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> |";
$last.=" <a href=\"$self?page=".($NumberOfPages)."\" >last</a>";
} else {
$next = ' ';
$last = ' ';
}
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
msg:4104392 | 1:02 pm on Mar 25, 2010 (gmt 0) |
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
msg:4104411 | 1:33 pm on Mar 25, 2010 (gmt 0) |
@ 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
msg:4104434 | 2:02 pm on Mar 25, 2010 (gmt 0) |
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
msg:4104449 | 2:30 pm on Mar 25, 2010 (gmt 0) |
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> |";
$first.="<a href=\"$self?page=".($NumberOfPage=1)."\">first</a> |";
}
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
msg:4104664 | 7:36 pm on Mar 25, 2010 (gmt 0) |
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
msg:4105247 | 3:46 pm on Mar 26, 2010 (gmt 0) |
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> |";
$first.="<a href=\"$self?page=".($NumberOfPages=1)."\">first</a> |";
}
else
{
$prev = ' ';
$first = ' ';
}
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> |";
$last.=" <a href=\"$self?page=".($NumberOfPages)."\" >last</a>";
} else {
$next = ' ';
$last = ' ';//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> 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">
viewing page '.$page.' of '.$NumberOfPages.' '.$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\"> </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]
|
|
|
