Re: Storing multiple items in one MySQL field?

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