-- Populate product_attribute table
INSERT INTO `product_attribute` (`product_id`, `attribute_value_id`)
SELECT product_id, 1 AS attribute_value_id FROM product
UNION SELECT product_id, 2 AS attribute_value_id FROM product
UNION SELECT product_id, 3 AS attribute_value_id FROM product
UNION SELECT product_id, 4 AS attribute_value_id FROM product
UNION SELECT product_id, 5 AS attribute_value_id FROM product
UNION SELECT product_id, 6 AS attribute_value_id FROM product
UNION SELECT product_id, 7 AS attribute_value_id FROM product
UNION SELECT product_id, 8 AS attribute_value_id FROM product
CHAPTER 6 ?– PRODUCT ATTRIBUTES 179
UNION SELECT product_id, 9 AS attribute_value_id FROM product
UNION SELECT product_id, 10 AS attribute_value_id FROM product
UNION SELECT product_id, 11 AS attribute_value_id FROM product
UNION SELECT product_id, 12 AS attribute_value_id FROM product
UNION SELECT product_id, 13 AS attribute_value_id FROM product
UNION SELECT product_id, 14 AS attribute_value_id FROM product;
Finally, let??™s take a look at the catalog_get_product_attributes stored procedure. This stored procedure
receives as a parameter the ID of a product and returns a list of that product??™s attributes. This is the handy little
device that the business tier will call to get the list of attributes for our products so our customers can choose
a size and color for each t-shirt:
-- Create catalog_get_product_attributes stored procedure
CREATE PROCEDURE catalog_get_product_attributes(IN inProductId INT)
BEGIN
The SQL code in this procedure returns a list with the attribute_name, attribute_value_id, and attribute_
value for all the attributes of the mentioned product:
SELECT a.
Pages:
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282