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 11 of 11
  1. #1
    Senior Coder
    Join Date
    Jul 2011
    Posts
    1,226
    Thanks
    3
    Thanked 171 Times in 171 Posts

    SQL Syntax Error

    I have a query which is reporting a syntax error. I can't see why...this is my query:
    INSERT INTO tp_matches (game, date, event, hometeam, awayteam, map1, map2, map3, map1_score, map2_score, map3_score, body) VALUES ('Counter Strike Source', '1314813600', 'i43', 1, 1, 'de_dust2', , , '30-0', '-', '-', '<p>Description of test match</p>');

  • #2
    Senior Coder djm0219's Avatar
    Join Date
    Aug 2003
    Location
    Wake Forest, North Carolina
    Posts
    1,285
    Thanks
    4
    Thanked 201 Times in 198 Posts
    You didn't tell us what the error you are getting is but the word date is a reserved word so, at the very least, your column named date will need to have back ticks around it.

    Code:
    INSERT INTO tp_matches (game, `date`, event, hometeam, awayteam, map1, map2, map3, map1_score, map2_score, map3_score, body) VALUES ('Counter Strike Source', '1314813600', 'i43', 1, 1, 'de_dust2', , , '30-0', '-', '-', '<p>Description of test match</p>');
    Dave .... HostMonster for all of your hosting needs

  • #3
    Senior Coder
    Join Date
    Jul 2011
    Posts
    1,226
    Thanks
    3
    Thanked 171 Times in 171 Posts
    Quote Originally Posted by djm0219 View Post
    You didn't tell us what the error you are getting is but the word date is a reserved word so, at the very least, your column named date will need to have back ticks around it.

    Code:
    INSERT INTO tp_matches (game, `date`, event, hometeam, awayteam, map1, map2, map3, map1_score, map2_score, map3_score, body) VALUES ('Counter Strike Source', '1314813600', 'i43', 1, 1, 'de_dust2', , , '30-0', '-', '-', '<p>Description of test match</p>');
    The error doesn't seem to be concerned with date being a reserved word. Are you sure that's correct?

    It's a syntax error, as stated in my OP. Specifically:
    Code:
     ERROR 1064 (42000): 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 ', '30-0', '-', '-', '<p>Description of test match</p>')'

  • #4
    Senior Coder
    Join Date
    Jul 2011
    Posts
    1,226
    Thanks
    3
    Thanked 171 Times in 171 Posts
    Quote Originally Posted by djm0219 View Post
    You didn't tell us what the error you are getting is but the word date is a reserved word so, at the very least, your column named date will need to have back ticks around it.

    Code:
    INSERT INTO tp_matches (game, `date`, event, hometeam, awayteam, map1, map2, map3, map1_score, map2_score, map3_score, body) VALUES ('Counter Strike Source', '1314813600', 'i43', 1, 1, 'de_dust2', , , '30-0', '-', '-', '<p>Description of test match</p>');
    Copy and pasting your query exactly, I now get an unexpected token:
    Code:
    -bash: syntax error near unexpected token `('

  • #5
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,020
    Thanks
    75
    Thanked 4,323 Times in 4,289 Posts
    You can't have missing data in SQL statements.

    Code:
    ... 'de_dust2', , , '30-0', '-',...
    See the three comma in a row there?

    If you have no data for a given field, either omit the field ENTIRELY from the INSERT or supply a default value (often NULL) for it.

    *PROBABLY* you need
    Code:
    ... 'de_dust2',NULL ,NULL , '30-0', '-',...
    But if your map2 and map3 fields don't support NULL values, then you'll have to give some kind of value. Even just the blank string '' would possibly work.

    ********

    How come you have apostrophes around your date value??? Since clearly you aren't using a DATETIME field for the date field (why not?) you must be using an INT field. INT's do not get apostrophes. MySQL is sloppy enough to allow it, but you don't have to be sloppy enough to code it.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    BluePanther (09-02-2011)

  • #6
    Senior Coder
    Join Date
    Jul 2011
    Posts
    1,226
    Thanks
    3
    Thanked 171 Times in 171 Posts
    On reflection, I've changed my PHP to omit null fields that aren't required. I have also changed date to a DATETIME type, I wasn't thinking and had it set to varchar, inputting a timestamp from input formatted (and validated) in mysql's datetime format anyway. Don't actually know why I done this, but I did lol. Probably my inadequacy in mysql showing through.

    The ` encapsulation of the date field was on the recommendation of the first reply. I didn't think that date was a reserved word, but I tried it anyway. And yeah, I'm aware integer values in mysql, like many languages, don't get any encapsulation - '1' is a string, 1 is an integer. However, does mysql compare them the same? I try to follow best coding practises, so I won't do it obviously - just curious.

    P.S. In case you never worked out from the sub-text, you were completely right about the query . Much obliged.

  • #7
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,631
    Thanks
    4
    Thanked 147 Times in 138 Posts
    DATE is a weird case in mysql. It is a reserved word, at the same time it is allowed. See the RESERVED WORD manual page on exceptions to reserved words.

    if you quote a numeric value mysql has to convert it to a numeric value and compare it against whatever function or other comparison you are doing, and then convert it back to a string. You may notice slow downs as a result of this, though I don't know to what extent.

  • #8
    Senior Coder
    Join Date
    Jul 2011
    Posts
    1,226
    Thanks
    3
    Thanked 171 Times in 171 Posts
    Interesting. Weak reserved word rules is an odd concept to me haha.

    Interesting, also, about the numeric conversions. I imagine it would be a minimal slow down for small bits of information that doesn't 'talk' to each other often - but busy relational databases could experience quite a detrimental effect. Thanks for taking the time out to explain to me

  • #9
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,020
    Thanks
    75
    Thanked 4,323 Times in 4,289 Posts
    Wow! A person after my own heart!

    As an old compiler person, I 100% agree with you. MySQL is sloppy. No other way to describe it.

    "weak reserved rules" indeed. Only applies to function names, by the by. Apparently, MySQL assumes it is *NOT* a function name if you don't have the parentheses after it, so then you get away with it.

    And yes, MySQL is sloppy about number vs. string comparisons and assignments. 96% of the time it will "guess right" and do the work for you. But if you grew up on strongly typed languages, as I did, it bothers the heck out of you (or it does me).

    Interestingly, for the most part it is only PHP people who are sloppy about the use of apostrophes in MySQL. Probably because PHP is also sloppy about them. It's not universal, but as a rule JSP and ASP.NET people who use MySQL tend to get it "right", presumably because they are working in strongly typed host languages, already.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #10
    Senior Coder
    Join Date
    Jul 2011
    Posts
    1,226
    Thanks
    3
    Thanked 171 Times in 171 Posts
    In what way would you say PHP is sloppy about apostrophies? Not sure if I agree with you there.

    But I do agree that most PHP people can be quite lazy with their quotations. Puzzles me as to why. It could be that most people run straight into PHP without any consideration of other languages. I personally learned TrueBasic in school, and python, and java briefly. So I don't really have an excuse lol.

  • #11
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,020
    Thanks
    75
    Thanked 4,323 Times in 4,289 Posts
    In the same way MySQL is.

    You can do
    Code:
    $foo = '13' + '19";
    and $foo will contain 32. A number. Not a string.

    To be fair, one reason this works as it does in MySQL, too, is because neither language uses + to mean anything but arithmetic addition.

    In other languages, + can mean addition or it can mean string concatenation (and of course in C++ it can mean most anything!). But in PHP, you can only concatenate strings using period and in MySQL you can only concatenate string using the CONCAT(...) function.

    So one of the slopinesses is also one of the strengths. But it still *feels* sloppy, to me.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.


  •  

    Posting Permissions

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