query errors....

[joecooper]

Im trying to update a row using php in my MYSQL db, but it keeps failing. the code is this:

 

            $query = "UPDATE lotto SET state='1', winner='$user' ". "WHERE row = '$row' AND WHERE column = '$col' LIMIT 1"; //update the grid to clicked.
            mysql_query($query) or die(mysql_error());

 

ive tried taking away the second 'WHERE'. but no joy.

 

the error is:

 

"...right syntax to use near 'AND WHERE column = 29 LIMIT 1' at line 1"

[rhodesa]

remove the extra WHERE

            $query = "UPDATE lotto SET state='1', winner='$user' ". "WHERE row = '$row' AND column = '$col' LIMIT 1"; //update the grid to clicked.
            mysql_query($query) or die(mysql_error());

[joecooper]

yes as i said i tried that. just done it again and the error:

 

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 'column = '23' LIMIT 1' at line 1

[JonnoTheDev]

$query = "UPDATE lotto SET state='1', winner='".mysql_real_escape_string($user)."' WHERE row='".mysql_real_escape_string($row)."' AND column = '".mysql_real_escape_string($col)."' LIMIT 1"; 

 

Check your variables contain the correct data.

 

 

[joecooper]

yes they do. ive just now tried to do this within PMA :

 

UPDATE lotto SET state='1', winner='user' WHERE row='42' AND column='29'

 

and still same error? thats not even using varibles but solid text.

[PFMaBiSmAd]

http://dev.mysql.com/doc/refman/5.0/en/reserved-words.html

[.josh]

I believe your issue is that "column" is a mysql reserved word.  If that's the name of your column, put backticks around it like so:

 

`column`

 

better yet, change your column name to something other than a reserved word, as that's bad programming practice.

 

edit: PFM beat me to it

[JonnoTheDev]

Yep you get this because column is reserved syntax.

Bad choice naming your fields row & column

[JonnoTheDev]

Who can type the quickest eh?

[joecooper]

ah, would also explain why this isnt working

 

SELECT * FROM lotto WHERE 'row'=$row AND 'column'=$col

[JonnoTheDev]

That it would