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
:queasy: :bemused:
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;s into &'s, but that's understandable.