'You have an error in your SQL syntax' - but I don't!

PHP
1

Hi there folks. I’ve got a script that keeps generating this: 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 ‘0,’ at line 1

I don’t think it’s actually an SQL issue though. If I echo the query that I’m generating it looks fine. If I run the query via phpMyAdmin it returns the expected results. I’m running a near-identical script elsewhere that works fine, so I’m wondering if there’s any PHP goof that I’ve made that could trigger an SQL syntax error? The query looks like this: $query = “select * from characters where id=$trimmed”; and I’ve used near-identical queries elsewhere with no trouble.

I’ve been searching various fora for hours now and the standard answer almost always comes down to an error in the query, but I’m sure mine’s fine. Any help would be GREATLY appreciated.

Here’s the full script:


<style type="text/css">
.biolink {
font-family: tahoma; 
color:#ccc;
font-size:12pt;}
</style>

<?php $doc_root = $_SERVER['DOCUMENT_ROOT'];
include "$doc_root/includefiles/css_linkwide.php";

// Get the search variable from URL
$var = @$_GET['id'] ;

// holds the database login details
include "whoadmin/dbconnect.php";

// database connection script
mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");

// Escape dangerous characters from user input.
$var = mysql_real_escape_string($var);

//trim whitespace from the stored, cleaned up search variable
$trimmed = trim($var); 

// check for an empty string and display nothing. Close the layout and exit.
if ($var == "")
  {
echo "empty string";
exit;
  }

// check for an empty string and display nothing. Close the layout and exit.
if ($trimmed == "")
  {
echo "empty string";
  exit;
  }
  

// check for a search parameter. If there isn't one, echo the error.
if (!isset($var))
  {
  echo "<p>We dont seem to have a search parameter!</p>";
  exit;
  }

//specify database 
mysql_select_db("ewebhost_whoswho") or die("Unable to select database"); //select which database we're using

$query = "select * from characters where id=$trimmed"; 
//echo $query; 

 $numresults=mysql_query($query);
 $numrows=mysql_num_rows($numresults);

// If we have no results, let ppl know
if ($numrows == 0)
  {
  echo "<div class=\\"searchedfor\\">Sorry, your search returned zero results. Please check your spelling or try again using different search terms.</div>";
  }

// next determine if s has been passed to script, if not use 0
  if (empty($s)) {
  $s=0;
  }

// get results 
$query .= "limit $s,$limit"; 

// get the result of the request and store it in variable $query or die and display specific mysql error
$result = mysql_query($query) or die(mysql_error());

// begin to show results set
$count = 1 + $s ;

// assign variables to dbase rows
while ($row= mysql_fetch_array($result)) {
$name=mysql_result($result,$i,"name");
$title=mysql_result($result,$i,"title");
$text=mysql_result ($result,$i,"text");
$association=mysql_result($result,$i,"association");

echo "<div>
<img src=\\"images/title.gif\\" alt=\\"Who's Who\\" /><br /><br /><br />
<img src=\\"images/$name.jpg\\" alt=\\"$name\\" style=\\"float:left; margin-right:5px;\\" />
<span style=\\"font-weight:bold; color:#3399cc; font-size:14pt; font-family:verdana, tahoma, sans-serif\\">$title</span><br /><br />
<strong>Associated with:</strong>$association<br /><br />
<strong>About $name:</strong> $text<br clear=\\"all\\" />
</div>

<div>
<a href=\\"main.php\\"><img src=\\"images/button_home.gif\\" alt=\\"home\\" border=\\"0\\" /></a>&nbsp;
<a href=\\"#\\" onClick=\\"window.open('map.php','js','toolbars=no,width=580,height=430'); return false;\\"><img src=\\"images/button_map.gif\\" alt=\\"map\\" border=\\"0\\" /></a>&nbsp;
</div>
<br clear=\\"all\\" />";

  $count++ ;
  }
?>
2

Where is $limit given a value? What does $query contain (var_dump it just before querying)?

3

$query = “select * from characters where id=‘$trimmed’”;
use like this.and see?

4

what do you get when you echo $query after adding the LIMIT clause?
Where is $s supposed to be coming from?

Also, don’t execute a full SELECT * query just to count the records. Use SELECT count(*) which is a lot more efficient than selecting an entire table just to count the rows in PHP.

5

Sadly that didn’t help, but thanks for the suggestion.

6

so it’s not a mysql error

i’ve flagged this thread to be moved to the php forum

7

Thanks folks - I sorted this out by reworking al the code and solved it.

8

Yes this should be the case. So try to echo the $query variable:


$query .= "limit $s,$limit"; 
echo $query;

And check if it has the correct syntax. I think you will have error in phpmyadmin as well.

9

it is working. check ur syntax.
is it set $trimmed variable?

10

not sure if this might be the cause but, you should look again at your double ==


if ($trimmed == "")

and the ‘if’ above that in your post. may be better as eq

eg


if ($trimmed eq '')

but I only do perl so that was a guess.

bazz

11

You’re missing a space before limit.

12

It is probably treating characters as a keyword in which case to be able to use it as a table name in your query you need to enclose it in backticks.

characters