SQL Syntax Error

[BluePanther]

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

[djm0219]

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.

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

[BluePanther]

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.

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:

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

[BluePanther]

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.

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:

-bash: syntax error near unexpected token `('

[Old Pedant]

You can't have missing data in SQL statements.

... '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

... 'de_dust2',[COLOR="Red"]NULL[/COLOR] ,[COLOR="Red"]NULL[/COLOR] , '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.

[BluePanther]

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.

[guelphdad]

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.

[BluePanther]

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

[Old Pedant]

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.

[BluePanther]

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.

[Old Pedant]

In the same way MySQL is.

You can do

$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.