Ok, so getting closer to the goal.. slight hiccup. I am experimenting
with some UNION and INNER JOIN and don't know if I am doing this
correctly or if this is the correct way to do this.
PREFACE:
I have now moved colors and sizes to their own tables and added a
product options table that has id combinations that relate to what
options a product has.
Currently there is only colors and sizes, but this may expand
depending on needs.
DALEMA:
Now as I said above, I am trying to get the data out and using UNION
and INNER JOIN to mash everything up to a usable situation.
STRUCTURE: (will attempt the fancy tables)
COLORS TABLE:
+—————+—————+————————+
| colorid (P) | pd_color | pd_color_desc |
+—————+—————+————————+
| 1 | Blk | Black |
+—————+—————+————————+
| 2 | Wht | White |
+—————+—————+————————+
| 5 | Pnk | Pink |
+—————+—————+————————+
SIZES TABLE:
+—————+—————+———————+
| sizeid (P) | pd_size | pd_size_desc |
+—————+—————+———————+
| 4 | Md | Medium |
+—————+—————+———————+
| 5 | Lg | Large |
+—————+—————+———————+
| 6 | XL | Xtra Large |
+—————+—————+———————+
PRODUCT OPTIONS TABLE: (Eg: mens shirt - pd_id == 1; womens - pd_id ==
2)
+————+————+—————+————+
| optid (P) | pd_id | colorid | sizeid |
+————+————+—————+————+
| 1 | 1 | 1 |
4 |
+————+————+—————+————+
| 2 | 1 | 1 |
5 |
+————+————+—————+————+
| 3 | 1 | 1 |
6 |
+————+————+—————+————+
| 4 | 1 | 2 |
4 |
+————+————+—————+————+
| 5 | 1 | 2 |
5 |
+————+————+—————+————+
| 6 | 1 | 2 |
6 |
+————+————+—————+————+
| 7 | 2 | 1 |
4 |
+————+————+—————+————+
| 8 | 2 | 1 |
5 |
+————+————+—————+————+
etc.. etc..
This is where I am stuck. I want to call the product options table
with a product id, get the colors for that product, then get the sizes
for each color of said product.
combine them with the product details and return everything to be
extracted.
This is what I have currently and is causing the knot in my brain. If
anyone can help me at this point, I would greatly appreciate it.
The first part of the $sql is the part that works. It grabs the main
product info out of the product table. That works fine.
Everything after is what I was working on. From UNION down in the $sql
string.
CODE:
/* Get detail information of a product */
function getProductDetail($pdId, $catId)
{
global $database;
$_SESSION['shoppingReturnUrl'] = $_SERVER['REQUEST_URI'];
// get the product information from database
$sql = "SELECT pd_name, pd_series, pd_description, pd_price,
pd_image, pd_qty
FROM ".TABLE_PRODUCTS."
WHERE pd_id = ".mysql_real_escape_string($pdId)."
//Start Me
UNION
SELECT colorid, sizeid, c.pd_color, c.pd_color_desc, s.pd_size,
s.pd_size_desc
FROM ".TABLE_PROD_OPTIONS." po
WHERE pd_id = ".mysql_real_escape_string($pdId)."
INNER JOIN ".TABLE_COLORS." c, ".TABLE_SIZES." s
ON po.colorid = c.colorid AND po.sizeid = s.sizeid";
//End Me
$result = $database->query($sql);
$row = mysql_fetch_assoc($result);
extract($row);
//this is where I am trying to extract and order the colors and sizes.
if(mysql_num_rows($row['colorid']) >1) {
foreach($row['colorid'] as $color) {
... //fetch each size for $color
} else {
... //Get size for only color
}
$row['pd_description'] = nl2br($row['pd_description']);
if ($row['pd_image']) {
$row['pd_image'] = PRODUCT_IMAGE_DIR . $row['pd_image'];
} else {
$row['pd_image'] = WEB_ROOT . 'images/no-image-logo.png';
}
$row['cart_url'] = WEB_ROOT."cart.php?action=add&p=".$pdId."";
return $row;
}
TIA
Karl DeSaulniers
Design Drumm
http://designdrumm.com
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
