Re: Storing multiple items in one MySQL field?
Karl DeSaulniers <[email protected]>
| Newsgroups | gmane.comp.php.database |
|---|---|
| Message-ID | <[email protected]> |
Hi Niel,
Thanks for taking the time to respond. Comments below.
On Jan 18, 2012, at 11:14 AM, Niel Archer wrote:
>> 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
>
> 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.
:) Yeah figured that out too.. Oops
>
> SELECT c.pd_color_desc, s.pd_size
> FROM `product_options` INNER JOIN `colors` AS c USING (colorID) INNER
> JOIN `sizes` AS s USING (sizeID)
> WHERE productID = 1
> ORDER BY c.pd_color_desc, s.pd_size
I 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;
$sql = "SELECT DISTINCT c.color_id, c.pd_color, c.pd_color_desc
FROM ".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>
...
>
>> $result = $database->query($sql);
>> $row = mysql_fetch_assoc($result);
> --
> 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
>
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
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php