|
OS Commerce: Add Product Options and Values Directly
Bypass the administrative interface
by Sam Mela article #00007
There are a lot of reasons why you my wish to add product options and product option values directly to OS Commerce. You may wish to export values and edit them and then re-import them, or you may simply have a large number to values to add.
It's easy to add options and option values directly using a SQL statement, but first it's important to understand how the options and option values are stored in the OS Commerce database.
Figure 1 below shows the tables that store options, option values, and the table that relates options to option values.
Figure 1
As shown below, in listing 1, it is a simple matter to create a SQL statement to define the types of options that will be
used in the store.
SQL Statement to insert option names into the data base
INSERT INTO `products_options` (`products_options_id`, `language_id`, `products_options_name`) VALUES
(1, 1, 'size'),
(2, 1, 'color'),
(3, 1, 'flavor');
Listing 1
Listing 2 below defines the option values associated with the option names that were defined in Listing 1. The first two, "small" and "large", will be applied to "size". The next seven, "blk", "blue"," "pink", "'purple", "'red", and "white", will be applied to "color" (obviously!). And finally, the last three, "chocolate", "vanilla", and "strawberry", will be applied to "flavor".
Note that there is not yet any connection between these two tables. That connection will be established by the SQL in Listing 3 (see further below).
SQL Statement to insert option names into the data base
INSERT INTO `products_options_values` (`products_options_values_id`, `language_id`, `products_options_values_name`) VALUES
(1, 1, 'small'),
(2, 1, 'large'),
(3, 1, 'blk'),
(4, 1, 'blue'),
(5, 1, 'clear'),
(6, 1, 'pink'),
(7, 1, 'purple'),
(8, 1, 'red'),
(9, 1, 'white'),
(10, 1, 'chocolate'),
(11, 1, 'vanilla'),
(12, 1, 'strawberry');
Listing 2
Finally, Listing 3 below ties everything together.
Each of the rows consists of a products_options_values_to_products_options_id, products_options_id, and the products_options_values_id, so it ties the first two tables together as shown in Figure 1.
SQL Statement to relate option names to option name values in the data base
INSERT INTO `products_options_values_to_products_options` (`products_options_values_to_products_options_id`, `products_options_id`, `products_options_values_id`) VALUES
#
# First relate the size option to size option names
#
(1, 1, 1),
(2, 1,2),
#
# Now relate the color option to color option names
#
(3, 2, 3),
(4, 2, 4),
(5, 2, 5),
(6, 2, 6),
(7, 2, 7),
(8, 2, 8),
(9, 2, 9),
#
# Finally relate the flavor option to flavor option names
#
(10, 3, 10),
(11, 3, 11),
(12, 3, 12);
Listing 3
Upon running the above queries, the OS Commerce Administration "Products Attributes" page should show something like Figure 2 and Figure 3.
Figure 2
Figure 3
|
