SQL Error CREATE TABLE

Question

The answer was that I used the wrong quotation marks instead of backlinks. The silly hilighter syntax fooled me.

I was stuck in this simple (ish) thing in the last half hour, so I thought I could try to get a quick answer here.

What exactly is wrong with SQL syntax if I use 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 am getting 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 on how to use Zend_Auth for complete noob?

Thanks.

Answer 1

Table and column identifiers are quoted using backticks (or double quotes if you have customized them).

In addition, you have a comma at the end of the 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 use single quotes instead of backreferences for table and field names, which is incorrect. There should also be an equal 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;