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;

Field         Type          Null  Key  Default  Extra
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

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 3

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.

Answer 5

Use a variable is not defined in MySQL; for example : not use 'to', 'not', 'join' ...

INSERT INTO messages (id,title,body,test) 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' );