Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 5 of 5
  1. #1
    New Coder
    Join Date
    May 2011
    Location
    NYC
    Posts
    28
    Thanks
    5
    Thanked 0 Times in 0 Posts

    What the..! WHERE clause

    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.
    Last edited by mrdemin; 06-14-2011 at 02:56 AM.

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,869
    Thanks
    79
    Thanked 4,421 Times in 4,386 Posts
    Show the *actual* query.

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

    Example:
    Code:
    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.

    Code:
    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.
    Code:
    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.

  • Users who have thanked Old Pedant for this post:

    mrdemin (06-14-2011)

  • #3
    New Coder
    Join Date
    May 2011
    Location
    NYC
    Posts
    28
    Thanks
    5
    Thanked 0 Times in 0 Posts
    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)

    Thanks!

  • #4
    Rockstar Coder
    Join Date
    Jun 2002
    Location
    USA
    Posts
    9,074
    Thanks
    1
    Thanked 328 Times in 324 Posts
    Quote Originally Posted by mrdemin View Post
    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.
    OracleGuy

  • #5
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,869
    Thanks
    79
    Thanked 4,421 Times in 4,386 Posts
    Take a peek here:

    http://dev.mysql.com/doc/mysqld-vers...rvedwords.html

    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.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •