Pagination - Previous and Next on Search Results

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
jamesmiddz
Forum Newbie
Posts: 18
Joined: Wed Jun 23, 2004 11:48 am
Location: UK

Pagination - Previous and Next on Search Results

Post 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
kettle_drum
DevNet Resident
Posts: 1150
Joined: Sun Jul 20, 2003 9:25 pm
Location: West Yorkshire, England

Post by kettle_drum »

A space between "count(1)" and "as".
User avatar
JAM
DevNet Resident
Posts: 2101
Joined: Fri Aug 08, 2003 6:53 pm
Location: Sweden
Contact:

Post 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. =)
qads
DevNet Resident
Posts: 1199
Joined: Tue Apr 23, 2002 10:02 am
Location: Brisbane

Post 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.
Post Reply