[SOLVED] You have an error in your SQL syntax

[almightyegg]

Long time no speak. I have a problem, I opened my page and it's told me I have incorrect syntax:

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

It's not being very helpful at telling me where abouts it is at - but I've managed to pin point it to within this code:

while($r3 = mysql_fetch_array($result3)){

   $id = $r3[id];
   $title = $r3[title];
   $username = $r3[username];
   $balala = mysql_query("SELECT * FROM topics WHERE `pid` = '$id',`fid` = '$f'") or die(mysql_error());
   $replies = mysql_num_rows($balala);
   $views = $r3[views];


$view2 = mysql_query("SELECT * FROM users WHERE username = '$username' LIMIT 1");
$userid = mysql_fetch_array($view2);

if($userid[admin] == "Yes"){
$woot = "<font color=\"#FF0000\"><b>$userid[admintitle] $username</b></font>";
}elseif($userid[mod] == "Yes"){
$woot = "<font color=\"#0000FF\"><b>Moderator $username</b></font>";
}else{
$woot = "<font color=\"#$userid[usernamecolor]\">$username</font>";
}
$title2 = preview($r3['post']);
?>
  <tr>
    <td style="width:250px;background-color:#101010;">
  PINNED: <a href="viewthread.php?f=<? echo "$f"; ?>&t=<? echo "$id"; ?>" title="<? echo "$title2"; ?>"><? echo "$title"; ?></a><br><a href="http://www.lordoftheabyss.com/player/view.php?id=<? echo "$userid[id]>$woot</a>\""; ?>
    </td>
    <td style="width:75px;background-color:#101010;"><? echo $replies; ?></td>
    <td style="width:75px;background-color:#101010;"><? echo $views; ?></td>

  </tr>
<tr>

<?
}

 

Sorry for the scruffy coding, and odd variable names Any help would be greatly received

[sasa]

change

SELECT * FROM topics WHERE `pid` = '$id',`fid` = '$f'

to

SELECT * FROM topics WHERE `pid` = '$id' and`fid` = '$f'

[almightyegg]

No difference thanks for the quick response

[almightyegg]

any more ideas?

[Mad Mick]

Where does $result3 come from? Sure it's not from that query?

 

Do $id and $f definitely have a value?

[premiso]

My bet is, you have an unescaped ' in your data. Give this a try:

[code=php:0]$sql = "SELECT * FROM topics WHERE `pid` = '$id',`fid` = '$f'";
$balala = mysql_query($sql) or die("SQL Was: " . $sql . "<br />Error returned: " . mysql_error());

 

And see what that returns.[/code]

[almightyegg]

My bet is, you have an unescaped ' in your data. Give this a try:

[code=php:0]$sql = "SELECT * FROM topics WHERE `pid` = '$id',`fid` = '$f'";
$balala = mysql_query($sql) or die("SQL Was: " . $sql . "<br />Error returned: " . mysql_error());

 

And see what that returns.

Made no difference - the error still shows the same:

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

 

 

 

Where does $result3 come from? Sure it's not from that query?

 

Do $id and $f definitely have a value?

The error comes after the $result3 query because other things have been successfully echoed after this point. But this is it anyway:

$result3 = mysql_query("SELECT * FROM topics WHERE fid = '$f' AND pinned = 'Yes' AND rid = '0' AND deleted = 'No' order by updatetimestamp desc");

 

I have checked $f has a value

$id should get a value during the loop, comes directly from a database

[premiso]

It should have returned the sql statement with an error message, given that it only returned the "same" error, either means you did not make that change, save the change or upload the file with the new change.

 

You should see something like:

SQL Was: SELECT * FROM topics WHERE `pid` = '(iddata)' AND `fid` = '(fdata)'

Error returned: sql error here.

 

Given that, I do not think this is where the error is thrown either way the sql statement should be like sasa said if you have not made that change yet, do so.  As for the $result3, you never posted that and you do not have an "or die(mysql_error())" so that would not throw the error, however. Try this:

 

$sql = "SELECT * FROM topics WHERE fid = '$f' AND pinned = 'Yes' AND rid = '0' AND deleted = 'No' order by updatetimestamp desc";
$result3 = mysql_query($sql) or die("SQL Was(result3): {$sql}<br />Error returned: " . mysql_error());

 

And see if that returns anything. Just as a reminder the first sql posted should be:

$sql = "SELECT * FROM topics WHERE `pid` = '$id' AND`fid` = '$f'";
$balala = mysql_query($sql) or die("SQL Was(balala): " . $sql . "<br />Error returned: " . mysql_error());

 

[almightyegg]

I made all of the changes you requested and I get:

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

 

This must mean the error is elsewhere?

[premiso]

Yep, keep using the same logic I posted above on each of your sql statements until you get the right one.

[almightyegg]

I found a line where it shows anerror:

while($r3 = mysql_fetch_array($result3)){

 

when replacing it with:

 

while($r3 = mysql_fetch_array($result3) or die("SQL Was(r3): " . $sql . "<br />Error returned: " . mysql_error())){

 

I get:

SQL Was(r3): SELECT * FROM topics WHERE fid = 'ghel' AND pinned = 'Yes' AND rid = '0' AND deleted = 'No' order by updatetimestamp desc

Error returned:

[premiso]

Post your full code, not just what you think is the problem. It will help as I doubt that sql statement is the culprit.

[almightyegg]

<?

$result = mysql_query("SELECT * FROM forums where board = '$f'");


$r = mysql_fetch_array($result);
$sql = "SELECT * FROM topics WHERE fid = '$f' AND pinned = 'Yes' AND rid = '0' AND deleted = 'No' order by updatetimestamp desc";
$result3 = mysql_query($sql) or die("SQL Was(result3): {$sql}<br />Error returned: " . mysql_error());
$forum_title = $r['title'];
?><center>
<table border="0" width="70%"><tr><td>
Welcome to the Game Help Forum, and probably the game entirely! If you are a having problems
locating or understanding things please look at the <a href="/helpfiles">Help Files</a> before posting here
otherwise the Forum will be spammed to the brim!</td></tr></table><br><br>
</centeR>
<table cellpadding="5" cellspacing="0" border="0" style="width:600px;border:1px solid #000;">
  <tr style="background-color:#222;">
    <td style="width:47%;background-color:#222;">Thread</td>
    <td style="width:12%;background-color:#222;">Replies</td>
    <td style="width:12%;background-color:#222;">Views</td>
    <td style="width:29%;background-color:#222;" rowspan=100 valign=top>
<?
if($mem[mod] == Yes){
echo "Mod Actions"; }else{} ?></td>
</tr>
<?
include "bbcodes.php";

while($r3 = mysql_fetch_array($result3) or die("SQL Was(r3): " . $sql . "<br />Error returned: " . mysql_error())){

   $id = $r3[id];
   $title = $r3[title];
   $username = $r3[username];
   $sql = "SELECT * FROM topics WHERE `pid` = '$id' AND`fid` = '$f'";
   $balala = mysql_query($sql) or die("SQL Was(balala): " . $sql . "<br />Error returned: " . mysql_error());
   $replies = mysql_num_rows($balala);
   $views = $r3[views];


$view2 = mysql_query("SELECT * FROM users WHERE username = '$username' LIMIT 1");
$userid = mysql_fetch_array($view2);

if($userid[admin] == "Yes"){
$woot = "<font color=\"#FF0000\"><b>$userid[admintitle] $username</b></font>";
}elseif($userid[mod] == "Yes"){
$woot = "<font color=\"#0000FF\"><b>Moderator $username</b></font>";
}else{
$woot = "<font color=\"#$userid[usernamecolor]\">$username</font>";
}
$title2 = preview($r3['post']);
?>
  <tr>
    <td style="width:250px;background-color:#101010;">
  PINNED: <a href="viewthread.php?f=<? echo "$f"; ?>&t=<? echo "$id"; ?>" title="<? echo "$title2"; ?>"><? echo "$title"; ?></a><br><a href="http://www.lordoftheabyss.com/player/view.php?id=<? echo "$userid[id]>$woot</a>\""; ?>
    </td>
    <td style="width:75px;background-color:#101010;"><? echo $replies; ?></td>
    <td style="width:75px;background-color:#101010;"><? echo $views; ?></td>

  </tr>
<tr>

<?
}

[premiso]

while($r3 = mysql_fetch_array($result3) or die("SQL Was(r3): " . $sql . "<br />Error returned: " . mysql_error())){

 

That is not valid to do, the only reason that dies is because after it has looped through this will be false, which it should to exit the loop.

 

Given that is the only way you get the error to popup the error is elsewhere and not in this code, check your included files for SQL statements and add on better error reporting to them..

[almightyegg]

when removing the included file there was no change...

 

After doing some more error checking the error is in another line further down the page :-S

$sql = "SELECT * FROM topics WHERE fid = '$f' AND pinned = 'No' AND rid = '0' AND deleted = 'No' order by updatetimestamp desc LIMIT $startval,$maxposts";
$result2 = mysql_query($sql) or die("SQL Was(result2): " . $sql . "<br />Error returned: " . mysql_error());

 

This shows:

SQL Was(result2): SELECT * FROM topics WHERE fid = 'ghel' AND pinned = 'No' AND rid = '0' AND deleted = 'No' order by updatetimestamp desc LIMIT 0,

Error returned: 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

[premiso]

Now that is helpful. Your variable ($maxposts) does not contain a value. Make sure it is being assigned a value.

 

SQL Was(result2): SELECT * FROM topics WHERE fid = 'ghel' AND pinned = 'No' AND rid = '0' AND deleted = 'No' order by updatetimestamp desc LIMIT 0,  (notice just the empty ,).

[almightyegg]

thank you! Works a treat

[Philip]

Well, $maxposts is empty. Thus it's giving LIMIT 0,

 

edit: beat me to it