Re: Storing multiple items in one MySQL field?
| From: | Karl DeSaulniers | Date: | Wed, 18 Jan 2012 21:49:33 +0000 |
| Subject: | Re: Storing multiple items in one MySQL field? | ||
| References: | 1 2 3 | Groups: | php.db |
| Request: | Send a blank email to php-db+get-48172@lists.php.net to get a copy of this message | ||
Hi Niel,
Thanks for taking the time to respond. Comments below.
On Jan 18, 2012, at 11:14 AM, Niel Archer wrote:
:) Yeah figured that out too.. OopsOk, 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 | +—————+—————+————————+I'm not sure what you are trying to achieve here, your clauses are in the wrong order (WHERE comes after joins). See if this gets you what you're after.| 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
SELECT c.pd_color_desc, s.pd_size FROMI will try this.. thank you! What I am trying to do is fill some <input type="select"> with the colors and depending on the color selected, showing in another <input type="select"> the sizes associated with that color of shirt. So if the Black shirt has only mediums and larges and the white shirt has medium, large and extra large only those show in the sizes dropdown. I have it to working... kind-of. I have since split it up into two functions. Might be overkill. My problem was getting only the sizes for the selected color to show. I kept getting.. Black Black Black White White White Then I got it to just show.. Black White I think I'm close, but now it keeps selecting the last color in the list and not the color selected. My CODE: (Inside SESSION file) function getProductColors($pdId) { global $database;product_optionsINNER JOINcolorsAS c USING (colorID) INNER JOINsizesAS s USING (sizeID) WHERE productID = 1 ORDER BY c.pd_color_desc, s.pd_size
$sql = "SELECT DISTINCT c.color_id, c.pd_color, c.pd_color_descFROM ".TABLE_PROD_OPTIONS." po LEFT JOIN ".TABLE_COLORS." c ON po.color_id=c.color_id WHERE po.pd_id = ".mysql_real_escape_string($pdId)." ";
$result = $database->query($sql);
$col = array();
while ($row = mysql_fetch_assoc($result)) {
extract($row);
$col[] = array('colorid' => $color_id,
'color_abv' => $pd_color,
'c_desc' => $pd_color_desc);
}
return $col;
}
function getSizesInfo($clrId) {
global $database;
$sql = "SELECT DISTINCT s.size_id, s.pd_size, s.pd_size_desc
FROM ".TABLE_PROD_OPTIONS." po
LEFT JOIN ".TABLE_SIZES." s
ON s.size_id=po.size_id
WHERE po.color_id='".mysql_real_escape_string($clrId)."'";
$result = $database->query($sql);
$colsz = array();
while ($row = mysql_fetch_assoc($result)) {
extract($row);
$colsz[] = array('sizeid' => $size_id,
'size_abv' => $pd_size,
's_desc' => $pd_size_desc);
}
return $colsz;
}
(Then read it out with this on the product page)
$product_colors = $session->getProductColors($pdId);
// we have $color_id, $pd_color, $pd_color_desc
//extract($product_colors);
//Example procuct name : M_Ta_Emblm_Blk-T_01. $color_abv should == 'Blk' for this product.
$color_sizes;
foreach($product_colors as $key=>$value) {
if(strcasecmp("_".$product_colors[$key]['color_abv'], $pd_name) != -1 || $form->value("color") == $product_colors[$key]['color_abv']) {
$selected_color = $product_colors[$key]['colorid'];
$color_sizes = $session->getSizesInfo($selected_color);
}
}
(Display with this)
...
<p class="pd_color" style="width:60%;">Color: <select name="color" size="1" tabindex="2" style="float:right;">
<?php
$color_select = "<option id=\"0\" value=\"Select Color\"";
if(!$form->value("color") || $form->value("color") == "" || $form-value("color") == "Select Color") { $color_select .= " selected";} $color_select .= ">Select Color</option>"; foreach($product_colors as $color) { extract($color); $color_select .= "<option id=\"".$colorid."\" value=\"".$color_abv."\""; if($selected_color == $colorid) { $color_select .= " selected"; } $color_select .= ">".$c_desc."</option>"; } echo($color_select); ?> </select></p> <p class="pd_size" style="width:60%;">Size: <select name="size" size="1" tabindex="3" style="float:right;"> <?php $size_select = "<option id=\"0\" value=\"Select Size\""; if(!$form->value("size") || $form->value("size") == "") { $size_select .= " selected"; } $size_select .= ">Select Size</option>"; if(strcasecmp("_".$color_abv, $pd_name) != -1) { foreach($color_sizes as $size) { extract($size); $size_select .= "<option id=\"".$size_id."\" value=\"".$size_abv."\""; if($form->value("size") == $size_abv) { $size_select .= " selected"; } $size_select .= ">".$s_desc."</option>"; } } echo($size_select); ?> </select></p> ...
I am sure this looks like a mess an I am probably overdoing this. There is probably a one-liner to do this that I haven't figured out.. lol But I am learning and any pointers will be absorbed.. :) Thanks again! Best, Karl DeSaulniers Design Drumm http://designdrumm.com$result = $database->query($sql);-- Niel Archer niel.archer (at) blueyonder.co.uk -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php$row = mysql_fetch_assoc($result);