CODEWITHSUNDEEP
phpmysql
Zac Brown
Zac Brown

Reputation: 6063

Troubleshoot PHP MySQL Query

I am trying to run this query:

$q1 = "INSERT INTO `Validation` (`Key`, `Status`, `Notes`, `Serial`) VALUES (max(Key)+1, 'Valid', '".mysql_real_escape_string($user)."', '0')";

through my PHP script, but each time it displays this 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 'Key)+1, 'Valid', 'USER', '0')' at line 1

Any ideas?

Upvotes: 1

Views: 616

Answers (3)

Charles
Charles

Reputation: 51411

KEY is a MySQL reserved word, so if you've picked it as a column name, you must quote it (with backticks) every time:

$q1 = "INSERT INTO `Validation` (`Key`, `Status`, `Notes`, `Serial`) 
    VALUES (
        max(`Key`) + 1, 
        'Valid', 
        '".mysql_real_escape_string($user)."'
        , '0'
    )";

Note that you don't have to quote non-reserved words with backticks.

I'm not actually sure that this will work -- doesn't MySQL prohibit reading from the same table that you're updating in a single query still? Regardless, as mentioned in the comments, it looks like you're just replicating by hand what AUTO_INCREMENT does for you automatically. Consider using it instead.

Upvotes: 4

Thomas
Thomas

Reputation: 2137

Try using a subquery.

$q1 = "INSERT INTO `Validation` (`Key`, `Status`, `Notes`, `Serial`) VALUES ((SELECT max(Key) FROM Validation)+1, 'Valid', '".mysql_real_escape_string($user)."', '0')";

Upvotes: 0

Mark Pope
Mark Pope

Reputation: 11264

I'm not sure you can use Key there. One way round this would be to do a SELECT statement before the INSERT to find out the max(Key) value. Another (preferable) way would be to make the database auto increment the KEY column

Upvotes: 2

Related Questions