:: oddé mySQL syntax error ::

[geeOff]

this is driving me nuts...why doesn't this line work ?

$sql = "select * from answers WHERE unit = $unit
AND section = $section AND question = $question AND
group = '2' AND correct = 'y' ORDER By rand() LIMIT 1"; 

i have tried it as "group = $question", "group = 2", "group = ''", "group = 'y'", etc... taking out the group part of the query, it always runs fine...anytime i add it i get

You have an error in your SQL
syntax near 'group = '2' AND correct = 'y' ORDER By
rand() LIMIT 1' at line 1

this group column is the same crap as unit, section, question...all int(11)s. i.e., this works fine:

$sql = "select * from answers WHERE
unit = $unit AND section = $section AND question =
$question AND correct = 'y' ORDER By rand() LIMIT 1"; 

any ideas ? thanks,

Geoff

[Technosailor]

i'm no expert but can you really use the rand() funtion in an order by statement?

Sketch

[geeOff]

actually, i haven't had any problems with the "ORDER By rand() LIMIT 1". i love that line, works great.

the problem was the column name group. this is a reserved word (that i have yet to use), correct ? i figured mebbe this was the case & changed group to "groups"...works like charm.

thx -- G

[Technosailor]

uuhhhh....yeah, that was my second guess.

Sketch

[geeOff]

[Defender1]

btw, you have to put single quotes around the variables.
section = '$section'

[geeOff]

for whatever reason, PHP 4.0.4pl1 / MySQL 3.23.36 let's me use no quote with variables. stuff like this:

"select * from answers WHERE unit = $unit 
AND section = $section AND question = $question 
AND correct = 'y' ORDER By rand() LIMIT 1"; 

is fine with "naked" $vars...but that makes me a little nervous, so i just changed them...thanks.

Geoff

[freddydoesphp]

To answer your question, variables that hold nothing more than numbers don't need quotes. If there are any other characters in there you must use single quotes.

i'm no expert but can you really use the rand() funtion in an order by statement?

New to 3.23

[DR_LaRRY_PEpPeR]

yes, in fact you SHOULDN'T put quotes around numbers in your queries. MySQL lets you get away with it, but MattR says that other RDBMSes won't.

[geeOff]

AH -- that makes sense now (most of my $vars ARE all numbers)...thanks guys, this is "good stuff" for a newbie.

Geoff