Page 1 of 1

Quick MySQL question- what's causing this simple syntax err?

Posted: Sun Oct 07, 2007 5:00 am
by Josh1billion
I must be overlooking something.. but after looking through it for 10+ minutes, I can't find it! It's probably something simple, too.

Code: Select all

INSERT into messages (sender_name, recipient_name, date, date_string, notified, read, subject, text) values
('Josh1billion', 'Josh2billion', '1191750860', 'Oct. 7 at 4:54 am', '0', '0', 'hey', 'testasdfasdf')
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 'read, subject, text) values ('Josh1billion', 'Josh2billion', '1191750860', 'Oct.' at line 1

Posted: Sun Oct 07, 2007 5:09 am
by VladSun
Maybe the problem is that you are using MySQL keyword date as a field name. Try using backticks (`) around your field names.

Posted: Sun Oct 07, 2007 5:31 am
by Josh1billion
Thanks for the input, but that's not the problem now it seems.. after extensive testing, I can see that it's apparently due to some code before that which modifies the $text and $subject variables (it replaces text within those variables) and when it gets to that query, something in that text is not liked by the query?? Very odd... but commenting out that block of modification text makes it so that everything works. So I'll have to test this even more to see if I can find exactly the problem.

edit

ok, solved, but I'm still very confused by this. Here's what I had to change to fix it.. the difference between what works and what doesn't work:

shown in quote tags so that differences can be bolded:

works:
$result = mysql_query("INSERT into messages (sender_name, recipient_name, date, date_string, subject, text) VALUES ('$sender_name', '$recipient_name', '$date', '$date_string', '$subject', '$text')");
doesn't work (syntax error as described above):
$result = mysql_query("INSERT into messages (sender_name, recipient_name, date, date_string, subject, text, read, notified) VALUES ('$sender_name', '$recipient_name', '$date', '$date_string', '$subject', '$text', '0', '0')");

:?: :?: :?:

Well, at least it works now... I'll just have those two fields set to 0 as default instead of through this query...

Posted: Sun Oct 07, 2007 7:57 am
by miro_igov
In order to prevent column names duplicated with key words use back quotes for column names in your quesries:

INSERT into messages (`sender_name`, `recipient_name`, `date`, `date_string`, `subject`, `text`, `read`, `notified`) VALUES ('$sender_name', '$recipient_name', '$date', '$date_string', '$subject', '$text','0','0');


In your example text is also reserved word so this is why it gives you the error.

Posted: Sun Oct 07, 2007 8:18 am
by feyd
Instead of using backquotes/backticks.. rename the columns so they aren't keywords. This is why many use prefixes.

Posted: Sun Oct 07, 2007 3:15 pm
by califdon
feyd wrote:Instead of using backquotes/backticks.. rename the columns so they aren't keywords. This is why many use prefixes.
Absolutely!! If you are designing the tables, or have responsibility for the database, avoid reserved words like date, text, read like the plague! They are almost guaranteed to give you fits. Even if it means going back and changing a reasonable number of other programs, I would recommend doing so. Obviously, if there are a large number of scripts/programs that have already been written to refer to such a database, it may be impractical to do this. Consult the list of reserved words for PHP, SQL and any other language you use!

SQL: http://dev.mysql.com/doc/refman/5.1/en/ ... words.html
PHP: http://www.php.net/manual/en/reserved.php

I was just reading through those myself and discovered, to my surprise, that "text" and "date" are not included in the reserved words for either SQL or PHP! Hmmm! Still, I would advise avoiding any common words like those, to be used as names of fields or variables or functions, etc.

Posted: Sun Oct 07, 2007 8:01 pm
by Josh1billion
Ahh thank you very much you guys, this is greatly appreciated (feyd especially thanks). I have renamed the various fields, and the formerly-non-functioning query is now working (the query is the same, except with the new field names of course).

I renamed:
text ---> message_text
date ---> timestamp [this isn't reserved, is it? either way, it's working]
read ---> has_been_read
notified ---> has_been_notified [may not have been a necessary change, but this way the name matches with has_been_read]

Phew.. I spent almost an hour this early morning (~4 to 5 am) trying to deal with that error. What a shocking experience that was.

Posted: Sun Oct 07, 2007 9:42 pm
by feyd
timestamp is a type, avoid it. Figure out something a bit more descriptive.

Posted: Sun Oct 07, 2007 9:49 pm
by John Cartwright
Usually on fields that involve storing the creation of the row, I'll have two seperate date fields: created_on, modified_on

Posted: Sun Oct 07, 2007 10:30 pm
by Josh1billion
Good advice Feyd and Jcart, thanks for that. I have now changed timestamp to timestamp_val.

Whew.. my last web-based game didn't take nearly this much work, but then again, my last web-based game didn't have nearly as many features. :)