Adding extra AND to query causes error, works otherwise

[theblackgold]

My error is 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 'inner='y' AND start<=CURDATE()' at line 1"


The intent of this section of the code is to fill $lft and $rgt with somewhat randomized content from the "item" column that meets several requirements. This code worked perfectly until I added:

AND inner='y'

To two of the SELECT statements. When I delete both occurances of this AND, the code will go back to properly selecting content as desired with the exception of taking the "inner" column into account.

I've done a lot of searching for an answer but I have not found anything. I get the feeling it is something simple that eludes me...



$bri=rand(0, 1);
if ($bri==0) {

$query="SELECT * FROM $table WHERE type='lft' AND subject='chr0001' AND start<=CURDATE()";
$result=mysql_query($query) or die(mysql_error());

$num=mysql_numrows($result) - 1;
$rand=rand(0,$num);

$lft=mysql_result($result,$rand,"item");
$lftsub=mysql_result($result,$rand,"subject");
}

else {

$query="SELECT * FROM $table WHERE type='lft' AND subject!='chr0001' AND inner='y' AND start<=CURDATE()";
$result=mysql_query($query) or die(mysql_error());

$num=mysql_numrows($result) - 1;
$rand=rand(0,$num);

$lft=mysql_result($result,$rand,"item");
$lftsub=mysql_result($result,$rand,"subject");
}


if ($bri==1) {

$query="SELECT * FROM $table WHERE type='rgt' AND subject='chr0001' AND start<=CURDATE()";
$result=mysql_query($query) or die(mysql_error());

$num=mysql_numrows($result) - 1;
$rand=rand(0,$num);

$rgt=mysql_result($result,$rand,"item");
$rgtsub=mysql_result($result,$rand,"subject");
}

else {

$query="SELECT * FROM $table WHERE type='rgt' AND subject!='chr0001' AND inner='y' AND start<=CURDATE()";
$result=mysql_query($query) or die(mysql_error());

$num=mysql_numrows($result) - 1;
$rand=rand(0,$num);

$rgt=mysql_result($result,$rand,"item");
$rgtsub=mysql_result($result,$rand,"subject");
}

 

[theblackgold]

I found out that INNER is a reserved word and cannot be used as a column name. I renamed this to something else and now it works fine.

 

[lgarner]

You can also wrap it in backticks (`inner`), but avoiding reserved words completely is certainly preferable.