Cannot insert string into MySQL text column

Question

For some reason, my queries screw up when I write to a column of type "text". Here is an example:

Describe messages;

Format is: Field Type Null Key Default

id  int(11) NO PRI NULL auto_increment
title   varchar(255) YES  NULL 
body    text YES  NULL 
to  text YES  NULL 
content_type    varchar(255) YES  NULL 
is_sms  tinyint(1) YES  NULL 
user_id int(11) YES  NULL 
created_at  datetime YES  NULL 
updated_at  datetime YES  NULL

Then I try an insert:

INSERT INTO messages (id,title,body,to) VALUES ('1','Test Message','This is a test message. This is a test message. This is a test message. This is a test message.', 'an email' );

For some reason this causes a general MySQL syntax error. The query works fine if I remove the "to" column and it's corresponding value from the query.

Any ideas?

Answer 1

'to' is a reserved keyword in MySQL. You'll need to rename your column.

http://dev.mysql.com/doc/refman/5.1/en/reserved-words.html

However, Reserved words are permitted as identifiers if you quote them.

http://dev.mysql.com/doc/refman/5.1/en/identifiers.html

Answer 2

INSERT
INTO     messages (id,title,body,`to`)
VALUES   ('1','Test Message','This is a test message. This is a test message. This is a test message. This is a test message.', 'an email' );

Answer 3

Try this instead

INSERT INTO messages (`id`,`title`,`body`,`to`) 
   VALUES ('1','Test Message','This is a test message. 
   This is a test message. This is a test message. This is a test message.', 
   'an email' );

Answer 4

I believe if you surround the "to" with backtics like so:

INSERT INTO messages (id,title,body,`to`) VALUES ('1','Test Message','This is a test message. This is a test message. This is a test message. This is a test message.', 'an email' );

it will work - did for me anyway.