MySQL - INSERT INTO says I have worng syntax with 'to'='$user2'

Question

$time=date("G:i:s j.n.Y");
$wholetime="$time";
mysql_query("INSERT INTO rivase_chat_posts SET sender='$user', content='$msg', time='$wholetime', 'to'='$affectuser'");
$msg="";

I am doing a private chat thing. That is my code. It results this error:

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 ''to'='gs'' at line 1
($user="gskartwii", $msg="HI", $affectuser='gs')

It may not help answer your question, but you should stop using `mysql_*` functions. They're being deprecated. Instead use [PDO](http://php.net/manual/en/book.pdo.php) (supported as of PHP 5.1) or [mysqli](http://php.net/manual/en/book.mysqli.php) (supported as of PHP 4.1). If you're not sure which one to use, [read this article](http://www.deprecatedphp.com/mysql_/). — Matt, Aug 29 '12 at 13:20

Fluffeh · Accepted Answer · 2012-08-29T13:25:02.523

9

For column names, use backticks rather than single-quotes:

`to`='$affectuser'

Single quotes are there for strings only. Backticks (normally left of the number 1 on your keyboard) are the things to use for column or table names in mysql.

Edit: As Michael Berkowski correctly points out, the reason you have to do this for the column name is because to is a reserved word in mysql - which is a lovely way of saying that it is a special word that mysql sees to mean something within a query normally. on that note, it really might not be the best idea to use the reserved words as columns in your table - you will have to backtick them in every single instance that you use them. You might want to consider renaming it to something like toUser which will probably make the rest of your project easier to SQL out :)

edited Aug 29 '12 at 13:25

answered Aug 29 '12 at 13:18

Fluffeh

33,228
16
67
80

Note that in this specific case, `TO` _must_ be quoted with backtickts since it is [a reserved keyword](http://dev.mysql.com/doc/refman/5.5/en/reserved-words.html) – Michael Berkowski Aug 29 '12 at 13:20
@MichaelBerkowski Yes, I should have said that in the answer. That was was ticked me off on it in the first place. And edited to include the more thorough answer. – Fluffeh Aug 29 '12 at 13:21

score 2 · Answer 2 · answered Aug 29 '12 at 13:18

2

You put the 'to' between single quotes. Column names are not quoted, or between backquotes. Single quotes are for strings. You cannot update a string, hence SET 'to'='user' is an error.

INSERT INTO rivase_chat_posts 
SET `sender`='$user', `content`='$msg', `time`='$wholetime', `to`='$affectuser'

UPDATE: comments say to is a reserved word and should always be escaped - using backquotes.

answered Aug 29 '12 at 13:18

Konerak

39,272
12
98
118

1

`TO` is also a reserved word. Must be quoted with backticks. – Michael Berkowski Aug 29 '12 at 13:19

score 2 · Answer 3 · answered Aug 29 '12 at 13:19

2

To is a reserved word. Escape it:

INSERT INTO rivase_chat_posts 
SET sender='$user', content='$msg', time='$wholetime', `to` ='$affectuser'

answered Aug 29 '12 at 13:19

Mahmoud Gamal

78,257
17
139
164

MySQL - INSERT INTO says I have worng syntax with 'to'='$user2'

3 Answers3