Page 1 of 1

Pagination - Previous and Next on Search Results

Posted: Mon Jul 19, 2004 6:04 am
by 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

Code: Select all

<?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

Posted: Mon Jul 19, 2004 6:08 am
by kettle_drum
A space between "count(1)" and "as".

Posted: Mon Jul 19, 2004 6:29 am
by JAM
Addition:
Why count(1). Is the field actually named 1? If it works, great, just thinking that this is a rather odd way of naming fields. =)

Posted: Mon Jul 19, 2004 7:19 am
by qads
hmm..did know you could name them like that lol...or well, you learn something every day :P.

you may wanna use this function: viewtopic.php?t=18466

btw, nice avatar jam :D.