CODEWITHSUNDEEP
mysqlsql
Matthew Pollard
Matthew Pollard

Reputation: 13

SQL syntax error

CREATE TABLE  `users` (

 `id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
 `type` ENUM(  `member` ,  `admin` ) NOT NULL ,
 `username` VARCHAR( 30 ) NOT NULL ,
 `email` VARCHAR( 80 ) NOT NULL ,
 `pass` VARBINARY( 32 ) NOT NULL , 
 `first_name` VARCHAR( 20 ) NOT NULL , 
 `last_name` VARCHAR( 40 ) NOT NULL , 
 `date_expires` DATE NOT NULL , 
 `date_created` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ,
 `date_modified` TIMESTAMP NOT NULL DEFAULT `0000-00-00 00:00:00` ,
PRIMARY KEY (  `id` ) , 
UNIQUE KEY  `username` (  `username` ) ,
UNIQUE KEY  `email` (  `email` ) 
) ENGINE = MYISAM DEFAULT CHARSET = utf8;

MySQL said:

#1064 - 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 'member,admin) NOT NULL, username VARCHAR(30) NOT NULL, email VARCHAR(80)' at line 3

The shared server I am using, uses 4.4. Thanks for reading. I am an absolute novice, having only been learning php/mysql for one month, so please speak in layman's terms.

Upvotes: 1

Views: 5813

Answers (3)

Borislav Jordanov
Borislav Jordanov

Reputation: 1

okey fixed it here is it :)

CREATE TABLE `users` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
type ENUM( 'member' , 'admin' ) NOT NULL ,
`username` varchar(30) NOT NULL default '',
`password` varchar(255) NOT NULL default '',
`first_name` varchar (15) NOT NULL,
`last_name` varchar (30) NOT NULL,
`gender` ENUM('male',  'female') NOT NULL default 'male',
`email` varchar(50) NOT NULL default '',
`skype` varchar(50) NOT NULL default 'Not Specified',
`facebook` varchar(150) NOT NULL default 'Not Specified',
`location` varchar(100) NOT NULL default 'Not Specified',
PRIMARY KEY (`id`),
UNIQUE KEY (`email`),
KEY (`email`, `password`)
) ENGINE = MYISAM DEFAULT CHARSET = utf8;

Upvotes: 0

Anthony Grist
Anthony Grist

Reputation: 38345

This page indicates that your ENUM values should be strings, so need to be surrounded by single quotes.

type ENUM( 'member' , 'admin' ) NOT NULL

Upvotes: 1

Mchl
Mchl

Reputation: 62395

You need to quote ENUM values

type ENUM( 'member' , 'admin' ) NOT NULL ,

not backtick them as you do now

Same thing for this line

`date_modified` TIMESTAMP NOT NULL DEFAULT `0000-00-00 00:00:00`

should be

`date_modified` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00'

Upvotes: 5

Related Questions