name AS attribute_name,
av.attribute_value_id, av.value AS attribute_value
FROM attribute_value av
INNER JOIN attribute a
ON av.attribute_id = a.attribute_id
WHERE av.attribute_value_id IN
(SELECT attribute_value_id
FROM product_attribute
WHERE product_id = inProductId)
ORDER BY a.name;
To test that the procedure works as it should, you can execute the code of the stored procedure using phpMyAdmin,
replacing the parameter names with their values. For example, you could take the preceding SQL code, replace
inProductId with the ID of a product, and then execute the code in phpMyAdmin.
Alternatively, you can call the stored procedure itself, but this operation is not supported by phpMyAdmin at the
time of this writing. To execute a stored procedure you can use the MySQL console. To start the console in Windows,
you need to execute the following command in C:\xampp\mysql\bin (assuming that you have installed XAMPP
as explained in Chapter 1).
mysql -u root
If your MySQL server is secured with a password (with the default XAMPP installation, it is not), you??™ll need to use
the -p parameter as well, in which case you??™ll be asked for the password:
mysql -u root -p
After connecting to your MySQL server, you need to connect to the tshirtshop database:
USE tshirtshop;
CHAPTER 6 ?– PRODUCT ATTRIBUTES 180
Once tshirtshop is selected, you can execute the catalog_get_product_attributes procedure, by
supplying 1 as parameter, like this:
CALL catalog_get_product_attributes (1);
When executing the stored procedure like this, you get all the attributes and attribute values for the product with
the ID of 1 in your database:
+----------------+--------------------+-----------------+
| attribute_name | attribute_value_id | attribute_value |
+----------------+--------------------+-----------------+
| Color | 6 | White |
| Color | 7 | Black |
| Color | 8 | Red |
| Color | 9 | Orange |
| Color | 10 | Yellow |
| Color | 11 | Green |
| Color | 12 | Blue |
| Color | 13 | Indigo |
| Color | 14 | Purple |
| Size | 1 | S |
| Size | 2 | M |
| Size | 3 | L |
| Size | 4 | XL |
| Size | 5 | XXL |
+----------------+--------------------+-----------------+
Implementing the Business Tier
The business tier bit of the product attributes feature is very straightforward??”we only need
to write the code that calls the catalog_get_product_attributes stored procedure.
Pages:
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283