5 replies to this topic

[isuru]

Everytime I execute following SQL statements I get error messages.

CREATE TABLE ‘categories’ (

‘id’ INT NOT NULL AUTO_INCREMENT ,

‘name’ VARCHAR( 255 ) NOT NULL ,

‘shortdesc’ VARCHAR( 255 ) NOT NULL ,

‘status’ ENUM( ‘active’, ‘inactive’ ) NOT NULL ,

‘parentid’ INT NOT NULL ,

PRIMARY KEY ( ‘id’ )

) TYPE = MYISAM ;

CREATE TABLE ‘products’ (

‘id’ INT NOT NULL AUTO_INCREMENT ,

‘name’ VARCHAR( 255 ) NOT NULL ,

‘shortdesc’ VARCHAR( 255 ) NOT NULL ,

‘longdesc’ TEXT NOT NULL ,

‘thumbnail’ VARCHAR( 255 ) NOT NULL ,

‘image’ VARCHAR( 255 ) NOT NULL ,

‘grouping’ VARCHAR( 16 ) NOT NULL ,

‘status’ ENUM( ‘active’, ‘inactive’ ) NOT NULL ,

‘category_id’ INT NOT NULL ,

‘featured’ ENUM (‘true’, ‘false’) NOT NULL,

PRIMARY KEY ( ‘id’ )

) TYPE = MYISAM ;

#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 '‘active’, ‘inactive’ ) NOT NULL , ‘category_id’ INT NOT NULL , ‘' at line 9

How do you debug above codes?

[WingedPanther]

Based on this: MySQL :: MySQL 5.0 Reference Manual :: 10.4.4 The ENUM Type I think you need to use ' instead of ‘ and ’ for your ENUM string delimiters. It could also be a limitation of TYPE = MYISAM.

[Alexander]

If you have copied the code off a website, or have a non-US character set and keyboard you likely have typed invalid characters.

You must replace each individual singular styled quote for names of tables or columns with the back tick (grave accent) character, which is: `

You must replace your ENUM constant's quotes with a singular quote, which is: '

[wwarren]

Yeah, looks like an encoding problem when you copied and pasted.

For the field names use a back-tick ` and for the values in the ENUM use a single quote '

So It should be like

CREATE TABLE `categories` (

`id` INT NOT NULL AUTO_INCREMENT ,

`name` VARCHAR( 255 ) NOT NULL ,

`shortdesc` VARCHAR( 255 ) NOT NULL ,

`status` ENUM('active', 'inactive') NOT NULL ,

`parentid` INT NOT NULL ,

PRIMARY KEY ( `id` )

) TYPE = MYISAM ;

and

CREATE TABLE `products` (

`id` INT NOT NULL AUTO_INCREMENT ,

`name` VARCHAR( 255 ) NOT NULL ,

`shortdesc` VARCHAR( 255 ) NOT NULL ,

`longdesc` TEXT NOT NULL ,

`thumbnail` VARCHAR( 255 ) NOT NULL ,

`image` VARCHAR( 255 ) NOT NULL ,

`grouping` VARCHAR( 16 ) NOT NULL ,

`status` ENUM( 'active', 'inactive' ) NOT NULL ,

`category_id` INT NOT NULL ,

`featured` ENUM ( 'true', 'false' ) NOT NULL,

PRIMARY KEY ( `id` )

) TYPE = MYISAM ;

Hope this fixes your problem!

[isuru]

wwarren said:

Yeah, looks like an encoding problem when you copied and pasted.

For the field names use a back-tick ` and for the values in the ENUM use a single quote '

So It should be like

CREATE TABLE `categories` (

`id` INT NOT NULL AUTO_INCREMENT ,

`name` VARCHAR( 255 ) NOT NULL ,

`shortdesc` VARCHAR( 255 ) NOT NULL ,

`status` ENUM('active', 'inactive') NOT NULL ,

`parentid` INT NOT NULL ,

PRIMARY KEY ( `id` )

) TYPE = MYISAM ;

and

CREATE TABLE `products` (

`id` INT NOT NULL AUTO_INCREMENT ,

`name` VARCHAR( 255 ) NOT NULL ,

`shortdesc` VARCHAR( 255 ) NOT NULL ,

`longdesc` TEXT NOT NULL ,

`thumbnail` VARCHAR( 255 ) NOT NULL ,

`image` VARCHAR( 255 ) NOT NULL ,

`grouping` VARCHAR( 16 ) NOT NULL ,

`status` ENUM( 'active', 'inactive' ) NOT NULL ,

`category_id` INT NOT NULL ,

`featured` ENUM ( 'true', 'false' ) NOT NULL,

PRIMARY KEY ( `id` )

) TYPE = MYISAM ;

Hope this fixes your problem!

Thanks! This works! Thanks for the help, it was with '.

[wwarren]

No problem :)