Page 1 of 1

Can you find the syntax error?

Posted: Sun Dec 05, 2004 12:18 pm
by patch2112
I'm creating this mysql statement from another mysql table using php. Here is the mysql error message and the sql that was created. It's long, but the entry in question is at the very end.

Any help would be great!

Code: Select all

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 'White_Red enum('Y','N') not null default "N"' at line 1 

create table product_colors (id int not null primary key auto_increment,Royal enum('Y','N') not null default "N",Red_Black_Offwhite enum('Y','N') not null default "N",Red_Black enum('Y','N') not null default "N",Red enum('Y','N') not null default "N",Raspberry enum('Y','N') not null default "N",Purple enum('Y','N') not null default "N",Powder_Blue enum('Y','N') not null default "N",Poppy enum('Y','N') not null default "N",Pink enum('Y','N') not null default "N",Pebble enum('Y','N') not null default "N",Pale_Blue enum('Y','N') not null default "N",Orange_Black enum('Y','N') not null default "N",Orange enum('Y','N') not null default "N",Olive enum('Y','N') not null default "N",Navy_Yellow enum('Y','N') not null default "N",Navy_Red_Yellow enum('Y','N') not null default "N",Navy_Red_Jade enum('Y','N') not null default "N",Navy_Deep_Navy enum('Y','N') not null default "N",Navy enum('Y','N') not null default "N",Natural_ enum('Y','N') not null default "N",Mid_Blue enum('Y','N') not null default "N",Marine_Navy enum('Y','N') not null default "N",Lime enum('Y','N') not null default "N",Lilac enum('Y','N') not null default "N",Light_Red enum('Y','N') not null default "N",Light_Oxford enum('Y','N') not null default "N",Light_Blue enum('Y','N') not null default "N",Lemon enum('Y','N') not null default "N",Laser_Blue enum('Y','N') not null default "N",Khaki enum('Y','N') not null default "N",Jade enum('Y','N') not null default "N",Ice_Green enum('Y','N') not null default "N",Ice enum('Y','N') not null default "N",Heather_Navy_Claret enum('Y','N') not null default "N",Heather enum('Y','N') not null default "N",Grey enum('Y','N') not null default "N",Green_Black enum('Y','N') not null default "N",Graphite enum('Y','N') not null default "N",Gold enum('Y','N') not null default "N",French_Navy enum('Y','N') not null default "N",Forest_Green enum('Y','N') not null default "N",Emerald enum('Y','N') not null default "N",Dusty_Blue enum('Y','N') not null default "N",Deep_Navy enum('Y','N') not null default "N",Dark_Heather enum('Y','N') not null default "N",Cream enum('Y','N') not null default "N",Classic_Red enum('Y','N') not null default "N",Claret_Navy enum('Y','N') not null default "N",Claret enum('Y','N') not null default "N",Charcoal enum('Y','N') not null default "N",Camouflage_Green enum('Y','N') not null default "N",Burgundy enum('Y','N') not null default "N",Brown enum('Y','N') not null default "N",Bright_Royal enum('Y','N') not null default "N",Bright_Blue enum('Y','N') not null default "N",Brick_Red enum('Y','N') not null default "N",Bottle_Green enum('Y','N') not null default "N",Black_Red_Offwhite enum('Y','N') not null default "N",Black_Putty_Silver enum('Y','N') not null default "N",Black enum('Y','N') not null default "N",Birch enum('Y','N') not null default "N",Big_Blue enum('Y','N') not null default "N",Begonia enum('Y','N') not null default "N",Baby_Green enum('Y','N') not null default "N",Azure enum('Y','N') not null default "N",Ash enum('Y','N') not null default "N",Aqua enum('Y','N') not null default "N",Air_Force_Blue enum('Y','N') not null default "N",Royal_Black enum('Y','N') not null default "N",Sand enum('Y','N') not null default "N",Silver_Grey enum('Y','N') not null default "N",Sky enum('Y','N') not null default "N",Slate_Grey enum('Y','N') not null default "N",Stone enum('Y','N') not null default "N",Sunflower enum('Y','N') not null default "N",White enum('Y','N') not null default "N",White_Black enum('Y','N') not null default "N",White_BlueMelange_Navy enum('Y','N') not null default "N",White_BrightRed enum('Y','N') not null default "N",White_Navy enum('Y','N') not null default "N",White_Ocean enum('Y','N') not null default "N",White_Red_Black enum('Y','N') not null default "N",White_Red_Navy enum('Y','N') not null default "N",Winter_Emerald enum('Y','N') not null default "N",Yellow enum('Y','N') not null default "N",Yellow_Black enum('Y','N') not null default "N",Zinc enum('Y','N') not null default "N",Powder_Pink enum('Y','N') not null default "N",Bright_Red enum('Y','N') not null default "N",Steel_Grey enum('Y','N') not null default "N",Black_Charcoal enum('Y','N') not null default "N",BrightRoyal_FrenchNavy enum('Y','N') not null default "N",ClassicRed_FrenchNavy enum('Y','N') not null default "N",FrenchNavy_BottleGreen enum('Y','N') not null default "N",FrenchNavy_FrenchNavy enum('Y','N') not null default "N",Khaki_FrenchNavy enum('Y','N') not null default "N",Black_Grey enum('Y','N') not null default "N",Grey_Navy enum('Y','N') not null default "N",Navy_Grey enum('Y','N') not null default "N",Royal_Grey enum('Y','N') not null default "N",Royal_Grey enum('Y','N') not null default "N",Yellow_Navy enum('Y','N') not null default "N",Black_Black enum('Y','N') not null default "N",BottleGreen_Black enum('Y','N') not null default "N",Navy_Black enum('Y','N') not null default "N",Grey_FrenchNavy enum('Y','N') not null default "N",BottleGreen_BottleGreen enum('Y','N') not null default "N",Burgundy_Camel enum('Y','N') not null default "N",Navy_Navy enum('Y','N') not null default "N",Red_Navy enum('Y','N') not null default "N",Royal_Navy enum('Y','N') not null default "N",Aubergine enum('Y','N') not null default "N",Mustard enum('Y','N') not null default "N",Ecru enum('Y','N') not null default "N",Kelly enum('Y','N') not null default "N",Bordeaux_Putty enum('Y','N') not null default "N",Navy_Putty enum('Y','N') not null default "N",Putty_Navy enum('Y','N') not null default "N",Black_White enum('Y','N') not null default "N",Black_White enum('Y','N') not null default "N",ClassicRed_White enum('Y','N') not null default "N",FrenchNavy_White enum('Y','N') not null default "N",Navy_White enum('Y','N') not null default "N",Sky_Navy enum('Y','N') not null default "N",Black_Red enum('Y','N') not null default "N",Navy_Red enum('Y','N') not null default "N",Navy_Taupe enum('Y','N') not null default "N",Grey_Black enum('Y','N') not null default "N",White_Royal enum('Y','N') not null default "N",White_Kelly enum('Y','N') not null default "N",White_Red enum('Y','N') not null default "N",junk int)

Posted: Sun Dec 05, 2004 12:33 pm
by jclarkkent2003
that formatting is just scaring me away from even looking :(

good luck

Posted: Sun Dec 05, 2004 12:35 pm
by kettle_drum
I get:

#1060 - Duplicate column name 'Royal_Grey'

and then:

#1060 - Duplicate column name 'Black_White'

And then it works.

Posted: Sun Dec 05, 2004 12:36 pm
by Benjamin
You have the columns 'Royal_Grey' and 'Black_White' in there twice.

Here is a fixed Query.

Code: Select all

CREATE TABLE product_colors(

id int NOT NULL PRIMARY KEY AUTO_INCREMENT ,
Royal enum( 'Y', 'N' ) NOT NULL default "N",
Red_Black_Offwhite enum( 'Y', 'N' ) NOT NULL default "N",
Red_Black enum( 'Y', 'N' ) NOT NULL default "N",
Red enum( 'Y', 'N' ) NOT NULL default "N",
Raspberry enum( 'Y', 'N' ) NOT NULL default "N",
Purple enum( 'Y', 'N' ) NOT NULL default "N",
Powder_Blue enum( 'Y', 'N' ) NOT NULL default "N",
Poppy enum( 'Y', 'N' ) NOT NULL default "N",
Pink enum( 'Y', 'N' ) NOT NULL default "N",
Pebble enum( 'Y', 'N' ) NOT NULL default "N",
Pale_Blue enum( 'Y', 'N' ) NOT NULL default "N",
Orange_Black enum( 'Y', 'N' ) NOT NULL default "N",
Orange enum( 'Y', 'N' ) NOT NULL default "N",
Olive enum( 'Y', 'N' ) NOT NULL default "N",
Navy_Yellow enum( 'Y', 'N' ) NOT NULL default "N",
Navy_Red_Yellow enum( 'Y', 'N' ) NOT NULL default "N",
Navy_Red_Jade enum( 'Y', 'N' ) NOT NULL default "N",
Navy_Deep_Navy enum( 'Y', 'N' ) NOT NULL default "N",
Navy enum( 'Y', 'N' ) NOT NULL default "N",
Natural_ enum( 'Y', 'N' ) NOT NULL default "N",
Mid_Blue enum( 'Y', 'N' ) NOT NULL default "N",
Marine_Navy enum( 'Y', 'N' ) NOT NULL default "N",
Lime enum( 'Y', 'N' ) NOT NULL default "N",
Lilac enum( 'Y', 'N' ) NOT NULL default "N",
Light_Red enum( 'Y', 'N' ) NOT NULL default "N",
Light_Oxford enum( 'Y', 'N' ) NOT NULL default "N",
Light_Blue enum( 'Y', 'N' ) NOT NULL default "N",
Lemon enum( 'Y', 'N' ) NOT NULL default "N",
Laser_Blue enum( 'Y', 'N' ) NOT NULL default "N",
Khaki enum( 'Y', 'N' ) NOT NULL default "N",
Jade enum( 'Y', 'N' ) NOT NULL default "N",
Ice_Green enum( 'Y', 'N' ) NOT NULL default "N",
Ice enum( 'Y', 'N' ) NOT NULL default "N",
Heather_Navy_Claret enum( 'Y', 'N' ) NOT NULL default "N",
Heather enum( 'Y', 'N' ) NOT NULL default "N",
Grey enum( 'Y', 'N' ) NOT NULL default "N",
Green_Black enum( 'Y', 'N' ) NOT NULL default "N",
Graphite enum( 'Y', 'N' ) NOT NULL default "N",
Gold enum( 'Y', 'N' ) NOT NULL default "N",
French_Navy enum( 'Y', 'N' ) NOT NULL default "N",
Forest_Green enum( 'Y', 'N' ) NOT NULL default "N",
Emerald enum( 'Y', 'N' ) NOT NULL default "N",
Dusty_Blue enum( 'Y', 'N' ) NOT NULL default "N",
Deep_Navy enum( 'Y', 'N' ) NOT NULL default "N",
Dark_Heather enum( 'Y', 'N' ) NOT NULL default "N",
Cream enum( 'Y', 'N' ) NOT NULL default "N",
Classic_Red enum( 'Y', 'N' ) NOT NULL default "N",
Claret_Navy enum( 'Y', 'N' ) NOT NULL default "N",
Claret enum( 'Y', 'N' ) NOT NULL default "N",
Charcoal enum( 'Y', 'N' ) NOT NULL default "N",
Camouflage_Green enum( 'Y', 'N' ) NOT NULL default "N",
Burgundy enum( 'Y', 'N' ) NOT NULL default "N",
Brown enum( 'Y', 'N' ) NOT NULL default "N",
Bright_Royal enum( 'Y', 'N' ) NOT NULL default "N",
Bright_Blue enum( 'Y', 'N' ) NOT NULL default "N",
Brick_Red enum( 'Y', 'N' ) NOT NULL default "N",
Bottle_Green enum( 'Y', 'N' ) NOT NULL default "N",
Black_Red_Offwhite enum( 'Y', 'N' ) NOT NULL default "N",
Black_Putty_Silver enum( 'Y', 'N' ) NOT NULL default "N",
Black enum( 'Y', 'N' ) NOT NULL default "N",
Birch enum( 'Y', 'N' ) NOT NULL default "N",
Big_Blue enum( 'Y', 'N' ) NOT NULL default "N",
Begonia enum( 'Y', 'N' ) NOT NULL default "N",
Baby_Green enum( 'Y', 'N' ) NOT NULL default "N",
Azure enum( 'Y', 'N' ) NOT NULL default "N",
Ash enum( 'Y', 'N' ) NOT NULL default "N",
Aqua enum( 'Y', 'N' ) NOT NULL default "N",
Air_Force_Blue enum( 'Y', 'N' ) NOT NULL default "N",
Royal_Black enum( 'Y', 'N' ) NOT NULL default "N",
Sand enum( 'Y', 'N' ) NOT NULL default "N",
Silver_Grey enum( 'Y', 'N' ) NOT NULL default "N",
Sky enum( 'Y', 'N' ) NOT NULL default "N",
Slate_Grey enum( 'Y', 'N' ) NOT NULL default "N",
Stone enum( 'Y', 'N' ) NOT NULL default "N",
Sunflower enum( 'Y', 'N' ) NOT NULL default "N",
White enum( 'Y', 'N' ) NOT NULL default "N",
White_Black enum( 'Y', 'N' ) NOT NULL default "N",
White_BlueMelange_Navy enum( 'Y', 'N' ) NOT NULL default "N",
White_BrightRed enum( 'Y', 'N' ) NOT NULL default "N",
White_Navy enum( 'Y', 'N' ) NOT NULL default "N",
White_Ocean enum( 'Y', 'N' ) NOT NULL default "N",
White_Red_Black enum( 'Y', 'N' ) NOT NULL default "N",
White_Red_Navy enum( 'Y', 'N' ) NOT NULL default "N",
Winter_Emerald enum( 'Y', 'N' ) NOT NULL default "N",
Yellow enum( 'Y', 'N' ) NOT NULL default "N",
Yellow_Black enum( 'Y', 'N' ) NOT NULL default "N",
Zinc enum( 'Y', 'N' ) NOT NULL default "N",
Powder_Pink enum( 'Y', 'N' ) NOT NULL default "N",
Bright_Red enum( 'Y', 'N' ) NOT NULL default "N",
Steel_Grey enum( 'Y', 'N' ) NOT NULL default "N",
Black_Charcoal enum( 'Y', 'N' ) NOT NULL default "N",
BrightRoyal_FrenchNavy enum( 'Y', 'N' ) NOT NULL default "N",
ClassicRed_FrenchNavy enum( 'Y', 'N' ) NOT NULL default "N",
FrenchNavy_BottleGreen enum( 'Y', 'N' ) NOT NULL default "N",
FrenchNavy_FrenchNavy enum( 'Y', 'N' ) NOT NULL default "N",
Khaki_FrenchNavy enum( 'Y', 'N' ) NOT NULL default "N",
Black_Grey enum( 'Y', 'N' ) NOT NULL default "N",
Grey_Navy enum( 'Y', 'N' ) NOT NULL default "N",
Navy_Grey enum( 'Y', 'N' ) NOT NULL default "N",
Royal_Grey enum( 'Y', 'N' ) NOT NULL default "N",
Yellow_Navy enum( 'Y', 'N' ) NOT NULL default "N",
Black_Black enum( 'Y', 'N' ) NOT NULL default "N",
BottleGreen_Black enum( 'Y', 'N' ) NOT NULL default "N",
Navy_Black enum( 'Y', 'N' ) NOT NULL default "N",
Grey_FrenchNavy enum( 'Y', 'N' ) NOT NULL default "N",
BottleGreen_BottleGreen enum( 'Y', 'N' ) NOT NULL default "N",
Burgundy_Camel enum( 'Y', 'N' ) NOT NULL default "N",
Navy_Navy enum( 'Y', 'N' ) NOT NULL default "N",
Red_Navy enum( 'Y', 'N' ) NOT NULL default "N",
Royal_Navy enum( 'Y', 'N' ) NOT NULL default "N",
Aubergine enum( 'Y', 'N' ) NOT NULL default "N",
Mustard enum( 'Y', 'N' ) NOT NULL default "N",
Ecru enum( 'Y', 'N' ) NOT NULL default "N",
Kelly enum( 'Y', 'N' ) NOT NULL default "N",
Bordeaux_Putty enum( 'Y', 'N' ) NOT NULL default "N",
Navy_Putty enum( 'Y', 'N' ) NOT NULL default "N",
Putty_Navy enum( 'Y', 'N' ) NOT NULL default "N",
Black_White enum( 'Y', 'N' ) NOT NULL default "N",
ClassicRed_White enum( 'Y', 'N' ) NOT NULL default "N",
FrenchNavy_White enum( 'Y', 'N' ) NOT NULL default "N",
Navy_White enum( 'Y', 'N' ) NOT NULL default "N",
Sky_Navy enum( 'Y', 'N' ) NOT NULL default "N",
Black_Red enum( 'Y', 'N' ) NOT NULL default "N",
Navy_Red enum( 'Y', 'N' ) NOT NULL default "N",
Navy_Taupe enum( 'Y', 'N' ) NOT NULL default "N",
Grey_Black enum( 'Y', 'N' ) NOT NULL default "N",
White_Royal enum( 'Y', 'N' ) NOT NULL default "N",
White_Kelly enum( 'Y', 'N' ) NOT NULL default "N",
White_Red enum( 'Y', 'N' ) NOT NULL default "N",
junk int
)

That Helped, but still doesn't work

Posted: Sun Dec 05, 2004 12:48 pm
by patch2112
I deleted the duplicate columns, but I'm still getting the same error message. I won't post the sql again, but it's the same but without the dup's. Any more ideas?

Posted: Sun Dec 05, 2004 12:50 pm
by kettle_drum
It worked for me without the duplicate columns. What is the error you get now? What are you doing to issue the sql command? is it through a shell? a php script? phpMyAdmin?

Posted: Sun Dec 05, 2004 12:50 pm
by patch2112
I added mysql_errno. Here is the complete error message:

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 'White_Red enum('Y','N') not null default "N"' at line 1

Thanks so much for helping me! I'd hate to have to enter this one line at a time.

Posted: Sun Dec 05, 2004 12:51 pm
by patch2112
Kettle - a PHP Script.

Posted: Sun Dec 05, 2004 12:53 pm
by kettle_drum
Also as a side note, what are you trying to do? It looks like you will be recording if a certain product has a certain color, is this correct?

As if so, why dont you create a table called color, with the fields ID and color. Then for each of your products you can just say that it has color 1,2,3,4,5,6 etc - which then relate to the ID's in the color table.

Posted: Sun Dec 05, 2004 12:54 pm
by Benjamin
Well there is probably an error in your php script then, use phpMyAdmin. You can install it very easily and I would recommend that for database administration.

Posted: Sun Dec 05, 2004 12:55 pm
by patch2112
It was indeed a PHP script error after we got rid of the dup's, where I was trying to get a look at the sql. Thanks SO MUCH!!!!

Posted: Sun Dec 05, 2004 12:57 pm
by kettle_drum
My guess with your problem in a PHP script is that you have both single and double quotes in the query, which will cause problems when you place the query in either single or double quotes to assign to a variable, or pass to a function.

http://uk.php.net/addslashes

Posted: Sun Dec 05, 2004 4:47 pm
by timvw
kettle_drum wrote:Also as a side note, what are you trying to do? It looks like you will be recording if a certain product has a certain color, is this correct?

As if so, why dont you create a table called color, with the fields ID and color. Then for each of your products you can just say that it has color 1,2,3,4,5,6 etc - which then relate to the ID's in the color table.
I agree, the current table design really smells. Something like below seems far more acceptable:

Code: Select all

table colors (
 int  color_id not null primary key auto_increment,
  name enum ('royal_enum', 'red_black', ................... )
);