Need help with defining attributes directly in mySQL

[nasdaqphil]

Hi all...I have a storefront where my products require A LOT of attributes so instead of plugging each into the backend CP of Zen, I went directly into MySQL to insert my attributes in the proper fields. Im not a SQL pro nor am I a Zen pro but this didn't seem too difficult. I opted to do it this way so I can use Excel to neatly create all my option pairs.

My problem now is that my option names are all there and my option values are all there but the two are not linked to one another. What links the two together? Is it one of the fields that have to have the same number?

For instance, this is a gun holster site. Here's a sample of some options:

Option Name: Left or Right Hand
Option VALUE: Left
Option VALUE: Right

Option Name: Color of Holster
Option VALUE: Black
Option VALUE: Mahogany
Option VALUE: Chestnut
Option VALUE: Tan

All the fields seem to be populated correctly in my database but the values are not showing up under the names in Zen's backend. Any idea what links the two to say that:
If Option Name "Left or Right Hand" is selected, link that to Option Value "Right" and Option Value "Left".

These are all of my total option NAMES:

"products_options_id";"language_id";"products_options_name";"products_options_so rt_order";"products_options_type";"products_options_length";"products_options_co mment";"products_options_size";"products_options_images_per_row";"products_optio ns_images_style";"products_options_rows";

"10";"1";"Color of Holster";"5";"0";"32";"NULL";"32";"0";"0";"0";
"20";"1";"Type of Gun";"1";"0";"32";"NULL";"32";"0";"0";"0";
"30";"1";"Surface Texturing";"6";"0";"32";"NULL";"32";"0";"0";"0";
"40";"1";"Your Initials Embossed";"7";"0";"32";"NULL";"32";"0";"0";"0";
"50";"1";"Right or Left Hand Draw";"3";"0";"32";"NULL";"32";"0";"0";"0";
"60";"1";"Size of Your Gun Belt";"4";"0";"32";"NULL";"32";"0";"0";"0";
"70";"1";"Length of Gun Barrel";"2";"0";"32";"NULL";"32";"0";"0";"0";

.....and these are my total option VALUES......How do I link the two?

"products_options_values_id";"language_id";"products_options_values_name";"produ cts_options_values_sort_order";

"10";"1";"BERETTA 92D CENTURION ";"2";
"20";"1";"BERETTA 92F ";"4";
"30";"1";"BERETTA COUGAR ";"6";
"40";"1";"BERETTA PX4 STORM 40 SW ";"8";
"50";"1";"BERETTA PX4 STORM 45 Auto ";"10";
"60";"1";"BERETTA PX4 STORM SUB COMPACT 9MM";"12";
"70";"1";"BERETTA VERTEC ";"14";
"80";"1";"BERSA THUNDER 380 ";"16";
"90";"1";"BERSA THUNDER 45 ";"18";
"100";"1";"BROWNING HI PWR";"20";
"110";"1";"COLT 1911 ";"22";
"120";"1";"COLT 1911 COMMANDER 4 Inch";"24";
"130";"1";"CZ75";"26";
"140";"1";"CZ75 COMPACT";"28";
"150";"1";"CZ75 P07 ";"30";
"160";"1";"GLOCK 17 22 31 ";"32";
"170";"1";"GLOCK 19 23 32 ";"34";
"180";"1";"GLOCK 21 ";"36";
"190";"1";"GLOCK 21 SF ";"38";
"200";"1";"GLOCK 26 27 33 ";"40";
"210";"1";"GLOCK 30 ";"42";
"220";"1";"GLOCK 34 ";"44";
"230";"1";"GLOCK 36 ";"46";
"240";"1";"GLOCK 37 ";"48";
"250";"1";"HK HK45 ";"50";
"260";"1";"HK MARK 23 SOCOM ";"52";
"270";"1";"HK P2000 European Version ";"54";
"280";"1";"HK P2000 US Version ";"56";
"290";"1";"HK P30 ";"58";
"300";"1";"HK P7M8";"60";
"310";"1";"HK USP 45 ";"62";
"320";"1";"HK USP 9MM";"64";
"330";"1";"HK USP 9MM COMPACT ";"66";
"340";"1";"KAHR P380";"68";
"350";"1";"KAHR PM9 ";"70";
"360";"1";"MAKAROV ";"72";
"370";"1";"RAVEN P25 ";"74";
"380";"1";"RUGER GP100 4 Inch";"76";
"390";"1";"RUGER KP95DAOPR15 ";"78";
"400";"1";"RUGER LCP ";"80";
"410";"1";"RUGER LCR ";"82";
"420";"1";"RUGER P89 ";"84";
"430";"1";"RUGER SP101 3 Inch";"86";
"440";"1";"RUGER SR9 ";"88";
"450";"1";"S and W J FRAME 2 Inch";"90";
"460";"1";"SW 4006 TSW ";"92";
"470";"1";"SW 4506 ";"94";
"480";"1";"SW 5906 ";"96";
"490";"1";"SW 6906 ";"98";
"500";"1";"SW J FRAME 2 Inch";"100";
"510";"1";"SW K FRAME 4 Inch";"102";
"520";"1";"SW MP 40 425 Inch";"104";
"530";"1";"SW MP 40 COMPACT 35 Inch";"106";
"540";"1";"SW MP 45 45 Inch";"108";
"550";"1";"SW MODEL 603 Inch";"110";
"560";"1";"SW SIGMA SW9V ";"112";
"570";"1";"SW SW99 ";"114";
"580";"1";"SIG MOSQUITO ";"116";
"590";"1";"SIG P220 No Rails";"118";
"600";"1";"SIG P220 WITH Rails";"120";
"610";"1";"SIG P225 No Rails";"122";
"620";"1";"SIG P226 ";"124";
"630";"1";"SIG P226 WITH Rails";"126";
"640";"1";"SIG P229 ";"128";
"650";"1";"SIG P229 WITH Rails";"130";
"660";"1";"SIG P229 DAK WITH Rails";"132";
"670";"1";"SIG P230 ";"134";
"680";"1";"SIG P239 ";"136";
"690";"1";"SIG P250 Compact ";"138";
"700";"1";"Sig Sauer P225";"140";
"710";"1";"SIGP228 ";"142";
"720";"1";"SIGPRO 2022 ";"144";
"730";"1";"SIGPRO 2340 ";"146";
"740";"1";"SPRINGFIELD 1911 A1 5 Inch";"148";
"750";"1";"SPRINGFIELD GI 45 1911 A1 5 Inch";"150";
"760";"1";"SPRINGFIELD MICRO COMPACT 1911 3 Inch";"152";
"770";"1";"SPRINGFIELD OPERATOR 1911A1 5 Inch";"154";
"780";"1";"SPRINGFIELD XD40 4 Inch";"156";
"790";"1";"SPRINGFIELD XD40 SUBCOMPACT 3 Inch";"158";
"800";"1";"SPRINGFIELD XD40 TACTICAL 5 Inch";"160";
"810";"1";"SPRINGFIELD XD45 4 Inch";"162";
"820";"1";"SPRINGFIELD XD9 4 Inch";"164";
"830";"1";"SPRINGFIELD XDM 40 ";"166";
"840";"1";"STEYR M9A1 ";"168";
"850";"1";"TAURUS 24 7 ";"170";
"860";"1";"TAURUS 4510 THE JUDGE 3 Inch";"172";
"870";"1";"TAURUS MODEL 85 ";"174";
"880";"1";"WALTHER P5 ";"176";
"890";"1";"WALTHER P99 ";"178";
"900";"1";"WALTHER PPK S ";"180";
"910";"1";"2 inch";"2";
"920";"1";"2 1 4 inch";"4";
"930";"1";"2 1 2 inch";"6";
"940";"1";"2 3 4 inch";"8";
"950";"1";"3 inch";"10";
"960";"1";"3 1 4 inch";"12";
"970";"1";"3 1 2 inch";"14";
"980";"1";"3 3 4 inch";"16";
"990";"1";"4 inch";"18";
"1000";"1";"4 1 4 inch";"20";
"1010";"1";"4 1 2 inch";"22";
"1020";"1";"4 3 4 inch";"24";
"1030";"1";"5 inch";"26";
"1040";"1";"5 1 4 inch";"28";
"1050";"1";"5 1 2 inch";"30";
"1060";"1";"5 3 4 inch";"32";
"1070";"1";"6 inch";"34";
"1080";"1";"6 1 4 inch";"36";
"1090";"1";"6 1 2 inch";"38";
"1100";"1";"6 3 4 inch";"40";
"1110";"1";"7 inch";"42";
"1120";"1";"7 1 4 inch";"44";
"1130";"1";"7 1 2 inch";"46";
"1140";"1";"7 3 4 inch";"48";
"1150";"1";"Not sure";"50";
"1160";"1";"Right Hand Draw";"2";
"1170";"1";"Left Hand Draw";"4";
"1180";"1";"1 inch wide belt";"2";
"1190";"1";"1 1 4 inch wide belt";"4";
"1200";"1";"1 1 2 inch wide belt";"6";
"1210";"1";"1 3 4 inch wide belt";"8";
"1220";"1";"2 inch wide belt";"10";
"1230";"1";"2 1 4 inch wide belt";"12";
"1240";"1";"Black, Total Holster";"2";
"1250";"1";"Saddle Tan, Total holster";"4";
"1260";"1";"Mahogany, Total Holster";"6";
"1270";"1";"Dark Indian Brown, Total Holster";"8";
"1280";"1";"Chestnut, Total Holster";"10";
"1290";"1";"Black Holster, Saddle Wrap Piece, Saddle Belt Loops";"12";
"1300";"1";"Saddle Holster, Black Wrap Piece, Black Belt Loops";"14";
"1310";"1";"Saddle Holster, Mahogany Wrap Piece, Mahogany Belt Loops";"16";
"1320";"1";"Saddle Holster, Indian Wrap Piece, Indian Belt Loops";"18";
"1330";"1";"Saddle Holster, Chestnut Wrap Piece, Chestnut Belt Loops";"20";
"1340";"1";"Mahogany Holster, Black Wrap Piece, Black Belt Loops";"22";
"1350";"1";"Mahogany Holster, Saddle Wrap Piece, Saddle Belt Loops";"24";
"1360";"1";"Indian Brown Holster, Black Wrap Piece, Black Belt Loops";"26";
"1370";"1";"Indian Brown Holster, Saddle Wrap Piece, Saddle Belt Loops";"28";
"1380";"1";"Indian Brown Holster, Chestnut Wrap Piece, Chestnut Belt Loops";"30";
"1390";"1";"Chestnut Holster, Black Wrap Piece, Black Belt Loops";"32";
"1400";"1";"Chestnut Holster, Saddle Wrap Piece, Saddle Belt Loops";"34";
"1410";"1";"Chestnut Holster, Mahogany Wrap Piece, Mahogany Belt Loops";"36";
"1420";"1";"No Surface Texture";"2";
"1430";"1";"Pebble effect on main gun area only";"4";
"1440";"1";"Pebble effect on wrap piece only";"6";
"1450";"1";"Pebble effect on belt loops only";"8";
"1460";"1";"Pebble effect on main gun area and belt loops";"10";
"1470";"1";"Pebble effect on wrap piece and belt loops";"12";
"1480";"1";"CrissCross Screen effect on main gun area only";"14";
"1490";"1";"CrissCross Screen effect on wrap piece only";"16";
"1500";"1";"CrissCross Screeneffect on belt loops only";"18";
"1510";"1";"CrissCross Screen effect on main gun area and belt loops";"20";
"1520";"1";"CrissCross Screen effect on wrap piece and belt loops";"22";
"1530";"1";"No Initials Embossed";"2";
"1540";"1";"2 initials embossed into wrap piece approx 3 4 inch tall";"4";
"1550";"1";"2 initials embossed into main gun area approx 3 4 inch tall";"6";

Any help would be greatly appreciated.

[kobra]

Im not a SQL pro nor am I a Zen pro

I suggest that you not mess in this area then

There is quite a bit of cross indexing and one slip and nothing works right

Hope you made backup BEFORE doing this

[nasdaqphil]

I suggest that you not mess in this area then

There is quite a bit of cross indexing and one slip and nothing works right

Hope you made backup BEFORE doing this

I'm not quite a pro but am comfortable enough back there. I've been using zen for about 6 years now. Everything is backed up and it's a brand new install.

Any ideas about my question?

[kobra]

I'm not quite a pro but am comfortable enough back there

This is the short list of tables but you need to know how they all interrelate to do what you want successfully

products_attributes
products_options
products_options_types
products_options_values
products_options_values_to_products_options

[nasdaqphil]

This is the short list of tables but you need to know how they all interrelate to do what you want successfully

products_attributes
products_options
products_options_types
products_options_values
products_options_values_to_products_options

Yes I got that far. Now I need to know which field or variable is used to link product option values to product option names.

Is it names ID? Sort order? What links the two?

[kobra]

Create one manually and see what the cross references are

[nasdaqphil]

Create one manually and see what the cross references are

I got it!!! Yay!!!

For anyone else who wants to use SQL to upload their attributes, the tables that link the attribute names and attribute values is "products_options_values_to_products_options"

The "products_options_id" is what binds the attribute value to the attribute name. I knew it was there somewhere. Took me forever to find it though.