SQL CREATE TABLE Error

Question

The Answer was that I was using incorrect quotation marks instead of backticks. Stupid syntax hilighter tricked me.

I've been stuck on this one simple(ish) thing for the last 1/2 hour so I thought I might try to get a quick answer here.

What exactly is incorrect about my SQL syntax, assuming I'm using mysql 5.1

CREATE TABLE 'users' (
    'id' INT(11) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    'username' VARCHAR(20) NOT NULL,
    'password' VARCHAR(40) NOT NULL,
    'salt' VARCHAR(40) DEFAULT NULL,
    'email' VARCHAR(80) NOT NULL,
    'created_on' INT(11) UNSIGNED NOT NULL,
    'last_login' INT(11) UNSIGNED DEFAULT NULL,
    'active' TINYINT(1) UNSIGNED DEFAULT NULL,
) 
ENGINE InnoDB;

The error I get is:

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 ''users';

CREATE TABLE 'users' (
    'id' INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,' at line 3


Elapsed Time:  0 hr, 0 min, 0 sec, 0 ms.

Also, does anyone have any good tutorials about how to use Zend_Auth for complete noobs?

Thanks.

Answer 1

Table and column identifiers are quoted using backticks (or double quotes if you've configured them).

Additionally you have a comma at the end of your column list.

CREATE TABLE `users` (
    `id` MEDIUMINT( 8 ) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    `username` VARCHAR( 20 ) NOT NULL,
    `password` VARCHAR( 40 ) NOT NULL,
    `salt` VARCHAR( 40 ) DEFAULT NULL,
    `email` VARCHAR( 80 ) NOT NULL,
    `created_on` INT( 11 ) UNSIGNED NOT NULL,
    `last_login` INT( 11 ) UNSIGNED DEFAULT NULL,
    `active` TINYINT( 1 ) UNSIGNED DEFAULT NULL
) ENGINE InnoDB

Answer 2

You are using single quotes instead of backticks for your table and field names, which is wrong. There should also be an equals sign between ENGINE and InnoDB.

Here's the fixed SQL:

CREATE TABLE `users` (
    `id` MEDIUMINT(8) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    `username` VARCHAR(20) NOT NULL,
    `password` VARCHAR(40) NOT NULL,
    `salt` VARCHAR(40) DEFAULT NULL,
    `email` VARCHAR(80) NOT NULL,
    `created_on` INT(11) UNSIGNED NOT NULL,
    `last_login` INT(11) UNSIGNED DEFAULT NULL,
    `active` TINYINT(1) UNSIGNED DEFAULT NULL
) 
ENGINE = InnoDB;