Pagination - Previous and Next on Search Results

[jamesmiddz]

Hi.

I'm really stuck on this script which a friend and I have been trying to get working for some time. Can anyone see any errors in it?

The erro message we keep on getting is;

SELECT count(1)as matches from countries where f2 LIKE 'Y' and
Can't complete query for matches because 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 '' at line 1

<?php 
include("connector.php"); 

//set paging limits 
$display_len = 10;    //10 per page 
$nextblock  = 0;            //start at the first set of 10 records 
$matches    = 0;            //total records in query 

if (isset($_POST['matches'])){ $matches = $_POST['matches']; } 

//debug statements (remove when working) 
echo "matches:$matches<br>"; 
echo "nextblock: $nextblock<br>"; 

//----------------------------------------------------------------------------- 
// Check to see what paging operation, if any, was requested 
//-----------------------------------------------------------------------------      

   switch (trim(@$_POST["btnSub"])){ 
      Case "Next": 
//----------------------------------------------------------------------------- 
//                  NEXT BLOCK 
//----------------------------------------------------------------------------- 
        $nextblock = @$_POST["nextblock"] + $display_len; 
        break; 

  Case "Prev": 
//----------------------------------------------------------------------------- 
//                  PREVIOUS  BLOCK 
//----------------------------------------------------------------------------- 
        $nextblock = @$_POST["nextblock"] - $display_len; 
        If ($nextblock < 0 ){ 
           $nextblock = 0; 
        } 
            break; 
//----------------------------------------------------------------------------- 
//                  LAST  BLOCK 
//----------------------------------------------------------------------------- 
      Case "Last": 
        $nextblock = Int($matches / $display_len) * $display_len; 

    If ($matches % $display_len == 0){ 
       $nextblock = $nextblock - $display_len; 
    } 
    break; 
//----------------------------------------------------------------------------- 
//                 FIRST BLOCK 
//----------------------------------------------------------------------------- 
      default: 
        $nextblock = 0; 

   } //end switch 


$f1=@$_GET['f1']; 
$f2=@$_GET['f2']; 
$f3=@$_GET['f3']; 
$f4=@$_GET['f4']; 

$matchStmt  = "SELECT count(1)as matches from countries where "; 
$searchStmt = "SELECT * from countries where " ; 

$sql_where = ""; 
if ($f1) 
$sql_where .= "f1 LIKE 'Y' and " ; 
if ($f2) 
$sql_where .= "f2 LIKE 'Y' and " ; 
if ($f3) 
$sql_where .= "f3 LIKE 'Y' and " ; 
if ($f4) 
$sql_where .= "f4 LIKE 'Y' and " ; 



$stmt = $searchStmt . substr($sql_where, 0, strlen($searchStmt)-4) . " limit $nextblock, $display_len "  ; 

//get the total matches for the query 
$sql = $matchStmt . substr($sql_where, 0, strlen($searchStmt)-4) ; 


echo $sql."<br>";           //kill this when it works 
  $matches_result = mysql_query($sql) or die("Can't complete query for matches because ".mysql_error()); 
if ($matches_result) { 
  $row = mysql_fetch_array($matches_result); 
  $matches = $row['matches']; 
} 


echo $stmt."<br>";   //kill this when it works 
//get the regular query 
$result=mysql_query($stmt) or die("Can't complete query for records because ".mysql_error()); 
$num=mysql_numrows($result); 
mysql_close(); 

$i=0; 

while ($i < $num) { 
   $id=mysql_result($result,$i,"id"); 
   $holsite=mysql_result($result,$i,"holsite"); 
   $f1=mysql_result($result,$i,"f1"); 
   $f2=mysql_result($result,$i,"f2"); 
   $f3=mysql_result($result,$i,"f3"); 
   $f4=mysql_result($result,$i,"f4"); 

   echo "<table border=\"0\" cellspacing=\"0\" cellpadding=\"2\"> 
         <form name=\"myform\" action=\"".$_SERVER['PHP_SELF']."\" method=\"post\">"; 


echo "$id<br> 
         $holsite<br>  
         $f1<br> 
         $f2<br> 
         $f3<br> 
         $f4<br> 
              <br> 
              <br> "; 
   ++$i; 
} 


pager($matches, $nextblock, $display_len); 

echo "</form></table>"; 

function pager($matches, $nextblock, $display_len) 
{ 
   echo "<tr><td colspan=3 align=center>To page through list, use:<BR>"; 

  If ($NextBlock <= 0){ $prev_status = "disabled";} 
  If ($NextBlock + $Display_len >= $matches){$next_status = "disabled";} 

  echo "<input type=submit value=\" First \" name=\"btnSub\" $prev_status > 
        <input type=submit value=\" Prev \"  name=\"btnSub\" $prev_status > 
        <input type=submit value=\" Next \"  name=\"btnSub\" $next_status > 
        <input type=submit value=\" Last \"  name=\"btnSub\" $next_status > 
        </td> 
        </tr> 
        <tr><td colspan=3 align=center>$matches Found "; 

  If ($matches > 0 ){ 
     echo "(Showing " . ($NextBlock+1) . " to " . ($matches) . "):"; 
  } 
  echo "</td></tr><input type=\"hidden\" name=\"matches\" value=\"$matches\">"; 

} 

?> 

If you spot a problem, please let me know.

Regards, James Middleton

[drawmack]

modify this section

<?php
if ($f1)  

$sql_where .= "f1 LIKE 'Y' and " ;  

if ($f2)  

$sql_where .= "f2 LIKE 'Y' and " ;  

if ($f3)  

$sql_where .= "f3 LIKE 'Y' and " ;  

if ($f4)  

$sql_where .= "f4 LIKE 'Y' and " ; 
?>

to be

<?php
$clauses = array();
if ($f1)  

$clauses[] = "f1 LIKE 'Y'" ;  

if ($f2)  

$clauses[] = "f2 LIKE 'Y'" ;  

if ($f3)  

$clauses[] = "f3 LIKE 'Y'" ;  

if ($f4)  

$clauses[] = "f4 LIKE 'Y'" ; 
$sql_where = implode(' AND ',$clauses);
?>

[dnast]

Another way to fix it is to change

$sql = $matchStmt . substr($sql_where, 0, strlen($searchStmt)-4) ;

to

$sql = $matchStmt . substr($sql_where, 0, -4);

[jamesmiddz]

Thanks for your time on this....I'm afraid I still can't get it working...any ideas?

[dnast]

Not sure. What's the value of $sql now and what's the error message?

[jamesmiddz]

http://www.shmootcase.co.uk/search/test2.php - you'll find the error message here if you choose and submit from the checkboxes.

Maybe I'm going about this in the wrong way...

I found this code which serves me well, but I can't seem to attach any pagination code to it without loads of error...maybe you have a suggestion;

<?php
include("connector.php");
$f1=$GET['f1'];
$f2=$GET['f2'];
$f3=$GET['f3'];
$f4=$GET['f4'];

$searchStmt = "SELECT * from countries where " ;

if ($f1)
$searchStmt .= "f1 LIKE'Y' and " ;
if ($f2)
$searchStmt .= "f2 LIKE'Y' and " ;
if ($f3)
$searchStmt .= "f3 LIKE'Y' and " ;
if ($f4)
$searchStmt .= "f4 LIKE'Y' and " ;

$stmt= substr($searchStmt, 0, strlen($searchStmt)-4) ;

$result=mysql_query($stmt);

$num=mysql_numrows($result);

mysql_close();
?>

		<? $i=0;

while ($i < $num) {
$id=mysql_result($result,$i,"id");
$holsite=mysql_result($result,$i,"holsite");
$f1=mysql_result($result,$i,"f1");
$f2=mysql_result($result,$i,"f2");
$f3=mysql_result($result,$i,"f3");
$f4=mysql_result($result,$i,"f4");

?>

	<table border="0" cellspacing="0" cellpadding="2">

<? echo "$id"; ?><br>
<? echo "$holsite"; ?><br>
<? echo "$f1"; ?><br>
<? echo "$f2"; ?><br>
<? echo "$f3"; ?><br>
<? echo "$f4"; ?><br>
<br>
<br>
<? ++$i;
}
echo "</table>";

?>

the form I used was quite simple;

<html>
<form name="form" action="results.php" method="get">
<b>Facilities</b><br>
<input type="checkbox" name="f1" value="Y"></td>
<font color="black">Showers</font></td><br>
<input type="checkbox" name="f2" value="Y"></td>
<font color="black">Games room</font></td><br>
<td width="20"><input type="checkbox" name="f3" value="Y"></td>
<font color="black">Toilet block</font></td><br>
<input type="checkbox" name="f4" value="Y"></td>
<font color="black">Golf course</font></td><br>
<input class="formblock" type="submit" name="Submit" value="Search" /></p>
</form>
</html>

I am using PHP4 on a mysql database;

CREATE TABLE countries (
id int(6) NOT NULL auto_increment,
holsite varchar(25) NOT NULL default '',
contactname varchar(25) NOT NULL default '',
address varchar(30) NOT NULL default '',
town varchar(30) NOT NULL default '',
postcode varchar(9) NOT NULL default '',
county varchar(20) NOT NULL default '',
country varchar(20) NOT NULL default '',
email varchar(50) NOT NULL default '',
email2 varchar(50) NOT NULL default '',
web varchar(50) NOT NULL default '',
tel varchar(20) NOT NULL default '',
accom varchar(25) NOT NULL default '',
theme varchar(30) NOT NULL default '',
theme2 varchar(30) NOT NULL default '',
theme3 varchar(30) NOT NULL default '',
special varchar(150) NOT NULL default '',
descript varchar(255) NOT NULL default '',
startdate1 varchar(6) NOT NULL default '',
enddate1 varchar(6) NOT NULL default '',
startdate2 varchar(6) NOT NULL default '',
enddate2 varchar(6) NOT NULL default '',
picture varchar(50) NOT NULL default '',
thumb varchar(50) NOT NULL default '',
status enum('N','Y') NOT NULL default 'N',
status2 enum('N','Y') NOT NULL default 'N',
regdate varchar(20) NOT NULL default '',
password varchar(10) NOT NULL default '',
username varchar(10) NOT NULL default '',
code varchar(6) NOT NULL default '',
joindate date NOT NULL default '0000-00-00',
login datetime NOT NULL default '0000-00-00 00:00:00',
f1 enum('N','Y') NOT NULL default 'N',
f2 enum('N','Y') NOT NULL default 'N',
f3 enum('N','Y') NOT NULL default 'N',
f4 enum('N','Y') NOT NULL default 'N',
f5 enum('N','Y') NOT NULL default 'N',
f6 enum('N','Y') NOT NULL default 'N',
f7 enum('N','Y') NOT NULL default 'N',
f8 enum('N','Y') NOT NULL default 'N',
f9 enum('N','Y') NOT NULL default 'N',
f10 enum('N','Y') NOT NULL default 'N',
f11 enum('N','Y') NOT NULL default 'N',
f12 enum('N','Y') NOT NULL default 'N',
f13 enum('N','Y') NOT NULL default 'N',
f14 enum('N','Y') NOT NULL default 'N',
f15 enum('N','Y') NOT NULL default 'N',
f16 enum('N','Y') NOT NULL default 'N',
f17 enum('N','Y') NOT NULL default 'N',
f18 enum('N','Y') NOT NULL default 'N',
f19 enum('N','Y') NOT NULL default 'N',
f20 enum('N','Y') NOT NULL default 'N',
f21 enum('N','Y') NOT NULL default 'N',
f22 enum('N','Y') NOT NULL default 'N',
f23 enum('N','Y') NOT NULL default 'N',
f24 enum('N','Y') NOT NULL default 'N',
f25 enum('N','Y') NOT NULL default 'N',
f26 enum('N','Y') NOT NULL default 'N',
PRIMARY KEY (id),
UNIQUE KEY username (username)
) TYPE=MyISAM AUTO_INCREMENT=144 ;

This is driving me mad...

😕 :queasy: :bemused:

[dnast]

Your code looks okay to me. I still see an extra "and" when I go to that link. Is the code on that page different than what you last posted?

Although it looks alright, I have a suggestion for your loop. Instead of

 <? $i=0;
while ($i < $num) {
$id=mysql_result($result,$i,"id");
$holsite=mysql_result($result,$i,"holsite");
$f1=mysql_result($result,$i,"f1");
$f2=mysql_result($result,$i,"f2");
$f3=mysql_result($result,$i,"f3");
$f4=mysql_result($result,$i,"f4");

?>

<table border="0" cellspacing="0" cellpadding="2">
<? echo "$id"; ?><br>
<? echo "$holsite"; ?><br>
<? echo "$f1"; ?><br>
<? echo "$f2"; ?><br>
<? echo "$f3"; ?><br>
<? echo "$f4"; ?><br>
<br>
<br>
<? ++$i;
} 
echo "</table>";


?>

An easier way would be

<table border="0" cellspacing="0" cellpadding="2">
<?
while ($row = mysql_fetch_row($result)) {
?>
<tr>
	<td><?=$row['id'];?></td>
	<td><?=$row['holsite'];?></td>
	<td><?=$row['f1'];?></td>
	<td><?=$row['f2'];?></td>
	<td><?=$row['f3'];?></td>
	<td><?=$row['f4'];?></td>
</tr>
<?
} //end of while loop
?>
</table>

For pagination, you could try this function I made for something I've been working on. You'll need an extra query to find the total matches (for $total).

//This function displays pagination for results and provides links to the next and
//previous pages (if possible) and links to the following and previous two pages
//wherever possible (for a total of 5 pages)
//
//Usage: Void show_pages(String url,String additional_parameters,int limit,int offset, int total_results)
function show_pages($url,$params,$limit,$offset,$total) {
    if (($offset - $limit) >= 0) {
      echo '<a href="'.$url.'?'.htmlentities(stripslashes($params)).'&amp;offset=0&amp;limit='.$limit.'" class="small">|&lt; </a> ';                     //1st page
      echo '<a href="'.$url.'?'.htmlentities(stripslashes($params)).'&amp;offset='.($offset - $limit).'&amp;limit='.$limit.'" class="small">&lt;</a> ';  //Previous page
    }
    $pages = ceil($total / $limit);
    $current_page = round($offset / $limit) + 1;
    $start = ($current_page < 3) ? 1 : ((($pages - $current_page) < 2) ? ($pages - 4) : ($current_page - 2));
    if ($start < 1) { $start = 1; }
    for ($i = $start; (($i <= $pages) && ($i - $start < 5)); $i++) {
      if ($i == $current_page) { echo '<span style="color: black; font-weight: bold; font-size: larger">'.$i.'</span> '; }
      else { echo '<a href="'.$url.'?'.htmlentities(stripslashes($params)).'&amp;offset='.(($i - 1) * $limit).'&amp;limit='.$limit.'">'.$i.'</a> '; }
    }
    if (($offset + $limit) < $total) {
      echo '<a href="'.$url.'?'.htmlentities(stripslashes($params)).'&amp;offset='.($offset + $limit).'&amp;limit='.$limit.'" class="small">&gt;</a> ';
      echo '<a href="'.$url.'?'.htmlentities(stripslashes($params)).'&amp;offset='.(($pages - 1) * $limit).'&amp;limit='.$limit.'" class="small">&gt;|</a> ';
    }
    echo "($pages page".(($pages != 1) ? "s" : "").")";
}

Edit: I forgot to add that you'll have to use $GET['limit'] and $GET['offset'] to use in your queries. And you should have default values in case they they aren't set. You'll most likely have to use $_GET['params'] as well to pass your other search criteria from page to page.

[jamesmiddz]

Sorry, I'm a bit of a newbie...how would I go about incorportating those $_get commands?

Here's what I have done so far:

<?php

$f1=$GET['f1'];
$f2=$GET['f2'];
$f3=$GET['f3'];
$f4=$GET['f4'];

include("connector.php");

$sSQL = "SELECT * from countries where ";
if ( isset($f1))
$sSQL .= "f1='Y' and";
if ( isset($f2))
$sSQL .= "f2='Y' and";
if ( isset($f3))
$sSQL .= "f3='Y' and";
if ( isset($f4))
$sSQL .= "f4='Y' and";

$total= substr($sSQL, 0, strlen($sSQL)-4) ;

//This function displays pagination for results and provides links to the next and 

//previous pages (if possible) and links to the following and previous two pages
//wherever possible (for a total of 5 pages)
//
//Usage: Void show_pages(String url,String additional_parameters,int limit,int offset, int total_results)
function show_pages($url,$params,$limit,$offset,$total) {
if (($offset - $limit) >= 0) {
echo '<a href="'.$url.'?'.htmlentities(stripslashes($params)).'&offset=0&limit='.$limit.'" class="small">|&lt; </a> '; //1st page
echo '<a href="'.$url.'?'.htmlentities(stripslashes($params)).'&offset='.($offset - $limit).'&limit='.$limit.'" class="small">&lt;</a> '; //Previous page
}
$pages = ceil($total / $limit);
$current_page = round($offset / $limit) + 1;
$start = ($current_page < 3) ? 1 : ((($pages - $current_page) < 2) ? ($pages - 4) : ($current_page - 2));
if ($start < 1) { $start = 1; }
for ($i = $start; (($i <= $pages) && ($i - $start < 5)); $i++) {
if ($i == $current_page) { echo '<span style="color: black; font-weight: bold; font-size: larger">'.$i.'</span> '; }
else { echo '<a href="'.$url.'?'.htmlentities(stripslashes($params)).'&offset='.(($i - 1) $limit).'&limit='.$limit.'">'.$i.'</a> '; }
}
if (($offset + $limit) < $total) {
echo '<a href="'.$url.'?'.htmlentities(stripslashes($params)).'&offset='.($offset + $limit).'&limit='.$limit.'" class="small">&gt;</a> ';
echo '<a href="'.$url.'?'.htmlentities(stripslashes($params)).'&offset='.(($pages - 1) $limit).'&limit='.$limit.'" class="small">&gt;|</a> ';
}
echo "($pages page".(($pages != 1) ? "s" : "").")";

?>
<table border="0" cellspacing="0" cellpadding="2">
<?
while ($row = mysql_fetch_row($result)) {
?>
<tr>
<td><?=$row['id'];?></td>
<td><?=$row['holsite'];?></td>
<td><?=$row['f1'];?></td>
<td><?=$row['f2'];?></td>
<td><?=$row['f3'];?></td>
<td><?=$row['f4'];?></td>
</tr>
<?
} //end of while loop
?>
</table>

[dnast]

Sorry, I guess I should've explained it better. I revised your code, but keep in mind I have no way of testing this where I'm at and I didn't use a PHP syntax highlighter :bemused:

But take note how the function is placed away from the other code and called later. I added comments to most of the changes I made.

<?php

//This function displays pagination for results and provides links to the next and 
//previous pages (if possible) and links to the following and previous two pages 
//wherever possible (for a total of 5 pages) 
// 
//Usage: Void show_pages(String url,String additional_parameters,int limit,int offset, int total_results) 
function show_pages($url,$params,$limit,$offset,$total) { 
    if (($offset - $limit) >= 0) { 
      echo '<a href="'.$url.'?'.htmlentities(stripslashes($params)).'&amp;offset=0&amp;limit='.$limit.'">|&lt; </a> ';                     //1st page 
      echo '<a href="'.$url.'?'.htmlentities(stripslashes($params)).'&amp;offset='.($offset - $limit).'&limit='.$limit.'">&lt;</a> ';      //Previous page 
    } 
    $pages = ceil($total / $limit); 
    $current_page = round($offset / $limit) + 1; 
    $start = ($current_page < 3) ? 1 : ((($pages - $current_page) < 2) ? ($pages - 4) : ($current_page - 2)); 
    if ($start < 1) { $start = 1; } 
    for ($i = $start; (($i <= $pages) && ($i - $start < 5)); $i++) { 
      if ($i == $current_page) { echo '<span style="color: black; font-weight: bold; font-size: larger">'.$i.'</span> '; } 
      else { echo '<a href="'.$url.'?'.htmlentities(stripslashes($params)).'&amp;offset='.(($i - 1) * $limit).'&limit='.$limit.'">'.$i.'</a> '; } 
    } 
    if (($offset + $limit) < $total) { 
      echo '<a href="'.$url.'?'.htmlentities(stripslashes($params)).'&amp;offset='.($offset + $limit).'&limit='.$limit.'">&gt;</a> ';       //Next page
      echo '<a href="'.$url.'?'.htmlentities(stripslashes($params)).'&amp;offset='.(($pages - 1) * $limit).'&limit='.$limit.'">&gt;|</a> '; //Last page
    } 
    echo "($pages page".(($pages != 1) ? "s" : "").")"; 
}



$f1=$_GET['f1'];
$f2=$_GET['f2'];
$f3=$_GET['f3'];
$f4=$_GET['f4'];

include("connector.php");


$sSQL  = "SELECT * from countries where ";
if ( isset($f1))
$sSQL .= "f1='Y' and";
if ( isset($f2))
$sSQL .= "f2='Y' and";
if ( isset($f3))
$sSQL .= "f3='Y' and";
if ( isset($f4))
$sSQL .= "f4='Y' and";

//Note: Changed $total to $sSQL right here. $total will be in the next block of code
$sSQL = substr($sSQL, 0, strlen($sSQL)-4) ;


//Finding the total results right here.
$result = mysql_query($sSQL)
    or die ("Error Message ".mysql_error());
$total = mysql_num_rows($result);


//Getting offset and limit. Setting them to defaults if they
//can't be found in $_GET. You can do this in two lines, but I
//didn't want to confuse you with that syntax.
if (isset($_GET['offset'])) {
	$offset = $_GET['offset'];
} else {
	$offset = 0;
}
if (isset($_GET['limit'])) {
	$offset = $_GET['limit'];
} else {
	$offset = 10;
}

//This time we're getting the results for the current page
$result = mysql_query($sSQL." LIMIT $offset,$limit")

?>
<table border="0" cellspacing="0" cellpadding="2"> 
<?php 
while ($row = mysql_fetch_row($result)) { 
?> 
<tr> 
    <td><?=$row['id'];?></td> 
    <td><?=$row['holsite'];?></td> 
    <td><?=$row['f1'];?></td> 
    <td><?=$row['f2'];?></td> 
    <td><?=$row['f3'];?></td> 
    <td><?=$row['f4'];?></td> 
</tr> 
<?php
} //end of while loop 
?> 
</table>

<?php
//Now let's say you want to show the page numbers after the table.
//First you'd have to put your form parameters in a string
$params = "f1=$f1&amp;f2=$f2&amp;f3=$f3&amp;f4=$f4";

//Calling the function to show pages.
//Note that you don't have to use those same variable names.
show_pages($_SERVER['PHP_SELF'],$params,$limit,$offset,$total);

?>

I have some other suggestions, but it's best to take things one at a time. Here's hoping I didn't screw anything up since I'm so reliant on syntax highlighting in my editors :p

Oh yeah, if you're going to copy and paste this, click on "quote" after this message and copy what's in between the

 tags. The message board changes some things every once in a while.

[jamesmiddz]

Hey, thanks for your time on this...I'm still getting an error, a different one this time;

Parse error: parse error, unexpected T_STRING in /customers/shmootcase.co.uk/shmootcase.co.uk/httpd.www/search/test10.php on line 101

Relating to this...

fset,$total);

any ideas?

Kind Regards, James

[dnast]

Hehe that's why I said click on Quote under that post and copy from the textbox in the forum's form. 😉 Those last two lines were supposed to be on the same line. I don't know why they wouldn't show up that way. There's probably a couple of other things that could be wrong with the forum's output. I know it turned all my &amp;amp;s into &'s, but that's understandable.

As far as I can tell, everything should work when copied out of a forum quote textbox, though.