Re: Insert query problem - Brent Baisley - com.mysql.lists.mysql

8 messages in com.mysql.lists.mysqlRe: Insert query problem

From	Sent On	Attachments
Jeff	29 Nov 2005 08:42
SGr...@unimin.com	29 Nov 2005 08:47
Jeff	29 Nov 2005 08:47
Kristen G. Thorson	29 Nov 2005 08:47
Easyhorpak.com Easyhorpak.com	29 Nov 2005 08:49
mel list_php	29 Nov 2005 08:50
Rhino	29 Nov 2005 11:14
Brent Baisley	30 Nov 2005 06:42

Subject:	Re: Insert query problem
From:	Brent Baisley (bre...@landover.com)
Date:	11/30/2005 06:42:55 AM
List:	com.mysql.lists.mysql

You can use the back quote(`) to escape the field name. It's the ~ key on the keyboard. tickets(`from`,`department`,...

If you type SHOW CREATE TABLE tickets, you'll see mysql escapes all the field names to avoid reserve word conflicts.

On Nov 29, 2005, at 2:14 PM, Rhino wrote:

----- Original Message ----- From: "Jeff" <jsmf...@optonline.net> To: <mys...@lists.mysql.com> Sent: Tuesday, November 29, 2005 11:42 AM Subject: Insert query problem

All,

I can't get this query to run, it keeps compaining that there is a problem:

The Query:

insert into tickets (id,from,department,subject,body,lastaction,lastpost,priority,sta tus,created,fromname,lastpostname,attach,ct,uniq,notify,replyto) values(null,'jmckeon','1','test','test test',unix_timestamp(now()),'"je...@optonline.net" <je...@optonline.net>','Medium','Open',unix_timestamp(now()),'Jeff McKeon','Jeff McKeon','','','ks5hslajdfasd','1','je...@optonline.net')

The error:

You have an error in your SQL syntax near 'from,department,subject,body,lastaction,lastpost,priority,status,cre ate d,fromnam' at line 1

I suspect it doesn't like the fact that I have a field named "from" but I KNOW it's possible to write to this table and that field, I just can't figure out the correct syntax to get it to accept the fieldname "from".

One of my least favourite aspects of MySQL is the vague error messages, like the one you quote in your post. I keep hoping that they will make them much clearer but it doesn't seem to have happened yet....

In any case, you may well be right that MySQL doesn't like you having a column named 'from'. Most dialects of SQL tolerate that kind of thing but usually require escape characters of some kind around words like 'from', which are keywords, when they are used outside of their normal purpose. I've never had the desire to call a column 'from' so I've never learned the escape character technique for MySQL so I would suggest the following possibilities:

- change your column name so that _isn't_ a keyword. That would be my first choice if it was my database. - search the MySQL manual for 'escape character' and/or 'keyword'; with a bit of luck, it is documented there somewhere - search the MySQL mailing list archives. I'm virtually positive I've seen the matter come up in the list but I just don't remember the escape syntax at the moment.

If you escape the column named 'from' and the query still doesn't work, please repost so that we can investigate other possibilities.