CODEWITHSUNDEEP
sqlphpmyadmin
m1243
m1243

Reputation: 157

SQL Product Table Creation - Incorrect Syntax

I am trying to create a products table running the following SQL query:

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 ,
  ‘sizes’ ENUM( ‘s’, ‘m’, ‘l’, ‘xl’ ) NOT NULL ,
  ‘colors’ ENUM( ‘red’, ‘blue’, ‘green’, ‘brown’, ‘white’, ‘black’ ) NOT NULL ,
  ‘grouping’ VARCHAR( 16 ) NOT NULL ,
  ‘status’ ENUM( ‘active’, ‘inactive’ ) NOT NULL ,
  ‘category_id’ INT NOT NULL ,
  ‘featured’ ENUM (‘true’, ‘false’) NOT NULL,
  ‘price’ FLOAT( 4, 2 ) NOT NULL,
PRIMARY KEY ( ‘id’ )
) TYPE = MYISAM ;

However this error occurs: 

#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 '‘s’, ‘m’, ‘l’, ‘xl’ ) NOT NULL , ‘colors’ ENUM( ‘red’, ' at line 8

Which type should I use? I am following an example from a book.

Upvotes: 0

Views: 2308

Answers (2)

ScottJShea
ScottJShea

Reputation: 7111

After some playing I think I can say it is the ‘’ characters that are throwing it off. I ran this without issue:

CREATE TABLE `products2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `sizes` enum('s','m','l','xl') NOT NULL,
  `colors` enum('red','blue','green','brown','white','black') 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(11) NOT NULL,
  `featured` enum('true','false') NOT NULL,
  `price` float(4,2) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Upvotes: 2

James Holwell
James Holwell

Reputation: 948

Using backticks and apostrophes instead of curly quotation marks seems to work for me

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 ,
`sizes` ENUM( 's', 'm', 'l', 'xl' ) NOT NULL ,
`colors` ENUM( 'red', 'blue', 'green', 'brown', 'white', 'black' ) NOT NULL ,
`grouping` VARCHAR( 16 ) NOT NULL ,
`status` ENUM( 'active', 'inactive' ) NOT NULL ,
`category_id` INT NOT NULL ,
`featured` ENUM ('true', 'false') NOT NULL,
`price` FLOAT( 4, 2 ) NOT NULL,
PRIMARY KEY ( `id` )
) ENGINE = MYISAM ;

Upvotes: 2

Related Questions