What's wrong with this PHP-MySQL CREATE TABLE query?

Question

First, I'm just starting to learn MySQL with PHP.

My query copy/paste directly from my IDE:

$query = "CREATE TABLE IF NOT EXISTS $table_messages (
                id       int(11)        unsigned  NOT NULL  auto_increment,
                show     tinyint(1)     unsigned  NOT NULL  default '0',
                to       varchar(255)             NOT NULL  default '',
                from     varchar(255)             NOT NULL  default '',
                type     varchar(255)             NOT NULL  default '',
                message  varchar(255)             NOT NULL  default '',
                PRIMARY KEY(id)
             ) DEFAULT CHARSET=utf8 AUTO_INCREMENT=1";

$result = mysql_query( $query, $link ) OR exit ( mysql_error() );

Results in this error:

You have an error in your SQL syntax; near 'show tinyint(1) unsigned NOT NULL default '0' , to varchar(255) N' at line 4

... so I add one character to show (e.g. showz) and get this error:

You have an error in your SQL syntax; near 'to varchar(255) NOT NULL default '' , from varchar(255) NOT NUL' at line 5

... so I add one character to to (e.g. toz) and get this error:

You have an error in your SQL syntax; near 'from varchar(255) NOT NULL default '' , type varchar(255) NOT NU' at line 6

... so I add one character to from (e.g. fromz) and IT WORKS!?

What is going on? Lol

If this question is too blatantly obvious, I'll remove it if the community thinks it would be prudent, but in the meantime I'm stumped.

BTW, I've messed with spacing, case and other things without any success.

Answer 1

SHOW, TO and FROM are reserved MySQL keywords. You must quote them with backticks to make them work as column names:

$query = "CREATE TABLE IF NOT EXISTS $table_messages (
    `id` int(11) unsigned NOT NULL auto_increment,
    `show` tinyint(1) unsigned NOT NULL default '0' ,
    `to` varchar(255) NOT NULL default '' ,
    `from` varchar(255) NOT NULL default '' ,
    `type` varchar(255) NOT NULL default '' ,
    `message` varchar(255) NOT NULL default '' ,
    PRIMARY KEY(id)
) DEFAULT CHARSET=utf8 AUTO_INCREMENT=1";

It's usually good practice (though unneeded) to quote every column name this way to prevent accidental collisions with keywords as there are hundreds of them. For a full list, see http://dev.mysql.com/doc/refman/5.1/en/reserved-words.html.

Answer 2

Just a stab in the dark, but are to and from reserved words in mysql? Could you either wrap those words in [] like [to] and [from] or, like you did, change the terms to toperson or fromperson?

Answer 3

You might be interested in this list of reserved words in MySQL statements. In short, if you want to use any of these as a column name (or anywhere in following queries), you have to quote them, usually in backticks:

`show` TINYINT(1) UNSIGNED NOT NULL,

...and later:

SELECT `show` FROM `varchar` WHERE `to`="France"