Troubleshoot PHP MySQL Query

Question

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?

Are you trying to simulate an ID that increments automatically? In that case you can just use AUTO_INCREMENT on the column and it will do this for you. — robbrit
– robbrit, Commented Apr 30, 2011 at 18:56

Charles · Accepted Answer · 2011-04-30 19:02:36Z

4

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.

answered Apr 30, 2011 at 19:02

Charles

51.5k13 gold badges107 silver badges146 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Mark Pope · Accepted Answer · 2011-04-30 18:59:44Z

2

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

answered Apr 30, 2011 at 18:59

Mark Pope

11.3k11 gold badges51 silver badges59 bronze badges

2 Comments

Zac Brown Over a year ago

I attempted to add the auto increment to the column, but Ikeep gtting this error: #1063 - Incorrect column specifier for column 'Key'

Mark Pope Over a year ago

What type of column is Key? It should be one of (TINYINT, SMALLINT, INTEGER, or BIGINT) for auto increment to work

Thomas · Accepted Answer · 2011-04-30 19:00:34Z

0

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')";

answered Apr 30, 2011 at 19:00

Thomas

2,1371 gold badge32 silver badges47 bronze badges

Collectives™ on Stack Overflow

Troubleshoot PHP MySQL Query

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related