View Full Version : What the..! WHERE clause

06-14-2011, 03:47 AM
So I've been working on my site for some time and just ran into a problem I don't understand.

Previously when using WHERE in my select statement I've had no problem but now I'm stuck.

The statement that tells me to check my syntax is

SELECT * FROM tablename WHERE column='something';
This worked before, now it will only work with the ending as such:
`column`= something;

Note this is only applies to one of the tables in my database!

Actually it applies to TWO tables, both which I had just created. I thought maybe the syntax is different between myIsam and InnoDB tables, but changing the engine didn't help any.

Old Pedant
06-14-2011, 04:08 AM
Show the *actual* query.

Probably your tablename or column is/are keyword(s).


SELECT * FROM anytable WHERE key = 'whatever'

will give that error, because key is a reserved keyword.

If indeed that's the problem, you can either change the column (or table) name or you can put backticks ( the ` character...usually shares a key with the ~ character) around the problem name(s). Which seems to be what you did, so I'm not clear why you think there is another problem.

SELECT * FROM anytable WHERE `key` = 'whatever'

But if that's not the problem, you'll have to show your real code. It doesn't work to show us sample code that wouldn't have the problem.

Oh...one more thing: If the field in question is *NOT* a text field (e.g., a CHAR() or VARCHAR() field) or datetime field, then technically you should *not* put apostrophes around the value you are testing.

SELECT * FROM anytable WHERE `key` = 17
or even
SELECT * FROM anytable WHERE `key` = 17.3718

You normally only use apostrophes around text and date/time values.

MySQL (almost alone among databases) usually *will* allow you to put apostrophes around numeric values, but it's not a good practice to get into. For some reason, many PHP programmers put them there, unlike programmers in other server side languages.

06-14-2011, 04:16 AM
You know, I was just trying to figure it out myself, and I think it may be that I'm using reserved keywords!
My column names that didn't work were "to" and "from".

So you actually hit my question right on the head ;)

All this reading and learning, yet this is the first time I heard mysql has reserved keywords! (of course it may be that I chose not to remember haha)


06-14-2011, 04:33 AM
All this reading and learning, yet this is the first time I heard mysql has reserved keywords! (of course it may be that I chose not to remember haha)

MySQL isn't alone in this, pretty much any dialect of SQL is going to have reserved keywords. I personally just avoid naming columns or tables with reserved keywords because it annoys me to have to type the back ticks.

Old Pedant
06-14-2011, 05:17 AM
Take a peek here:


Yep, as you can see, both "FROM" and "TO" are in the lists.

Some function names can be used as table/column names without the backticks, but it's a good idea to avoid them if you are aware of them.