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

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
Josh1billion
Forum Contributor
Posts: 316
Joined: Tue Sep 11, 2007 3:25 pm

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

Post 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
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Post by VladSun »

Maybe the problem is that you are using MySQL keyword date as a field name. Try using backticks (`) around your field names.
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
Josh1billion
Forum Contributor
Posts: 316
Joined: Tue Sep 11, 2007 3:25 pm

Post 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...
miro_igov
Forum Contributor
Posts: 485
Joined: Fri Mar 31, 2006 5:06 am
Location: Bulgaria

Post 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.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

Instead of using backquotes/backticks.. rename the columns so they aren't keywords. This is why many use prefixes.
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Post 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.
User avatar
Josh1billion
Forum Contributor
Posts: 316
Joined: Tue Sep 11, 2007 3:25 pm

Post 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.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

timestamp is a type, avoid it. Figure out something a bit more descriptive.
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post 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
User avatar
Josh1billion
Forum Contributor
Posts: 316
Joined: Tue Sep 11, 2007 3:25 pm

Post 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. :)
Post Reply