What's wrong with this MySQL statement?

[DirtyB]

I need help from you guys. I am trying to write some PHP and MySQL code for one of the arbi development, and I am getting error with the following statement:

SELECT * FROM mytable WHERE category='major' AND key='minor'

I think its such a simple SQL statement but I couldn't figure it out what's wrong with the above.

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 'key='minor'' at line 1

Need help. :bowdown:

 

[casidnet]

Do either major or minor have a single quote in them? Like "o'hare" make sure you escape all those with backslashes first.

 

[cziffra]

'Key' is a reserved word in MySQL. You need to use another name for the column.

 

[casidnet]

ah yeah forgot about that, backticking it will work too but cziffra is right a different column name would be better.

 

[DirtyB]

You guys nailed it. I have changed the field 'key' to 'mykey' and its working fine.

Thanks to cziffra for pointing it out. Its working fine.

On a side note, how come it allowed me to create the table in the first place???? Anyways, thanks a lot guys. That's very quick.

 

[casidnet]

did you do something like
create table `mytable` (
`key` char()....,
`other` chr()....);

To create it? You can use reserved words as long as they are quoted with backticks.

 

[threehundred]

no sense in using reserved words when there are so many other words you can use. Since you forgot about this, make sure not to label a column with the reserved word DESC for description, as it is reserved for DESCENDING. Just a tip.

 

[sleepylee]

I've had this problem before when using phpMyAdmin - it automatically places your table definitions in backticks so that it's all legitimate, then you get shat on when you actually try to use it.

Any way to stop phpMyAdmin not auto-backtick column names on creation?

 

[Bluesaga]

Nope, backticking is good programing practice, and recommended when creating queries for it aswell!

 

[sleepylee]

I read somewhere that php parsing of the backticks is really fucking slow.

[EDIT] Just looked up what I (thought I) saw, and it turns out I'd misread it. Oops. Let's go with the backticks then.