populating select box via database (sometimes)

select boxes and databases

[caveman28]

have a shopping cart script mostly completed using PHP and MySQL. Say for example I want to sell clothing which comes in different sizes and different colors. I have a page with four links, one for each of the four catagories in the database. "Catagory" is a key in the database and when I click one of these links, it populates the next page with all of the products from that catagory. On the next page each product is displayed as a link to it's own page.

Now, when I click on that link, it displays the info for that product, ordered by sku, which is the key for product number. sku=255 has an associated photo called 255.jpg and sku=256 has one called 256.jpg. The associated price for sku=255 is pulled into the page as well, all simple shopping cart stuff right. Ok sorry for all the verbage, onto my question.

Some products require different sizes and colors and some don't. Since the info is displayed depending on the key "sku" is there a way to enter all the possible sizes in one text field in the database and all the colors in another, delimited by commas, and then have that row populate a select box? If there are no sizes or colors, to not display a select box at all? Make any sense what I am asking?

Examples or any help would be appreciated. I assume if I had a field called "sizes" and has its values as 'small, medium, large' I could somehow get that to a select box, and if it is null not display anything, right? ideas?

[wruk999]

Hi caveman28,

In this [webmasterworld.com...] thread, I asked for the code to do what your asking, and in actual fact - for EXACTLY the same reasons ;)

Here is what I ended up having. I have thee associated fields in the item table.

ExtraOpionEnable1
ExtraOption1
ExtraOptions1

These are the three dbase columns. The first one, ExtraOptionEnable1 is a TINYINT field, with either 1 or 0 as the value. ExtraOption1 is where the name of the variable is stored - ie: Colour or Size.
And ExtraOptions1 is where the options are stored, separated by commas.

Then, the code on the page looks something like this:

if ($ExtraOptionEnable1 == 1) {

echo "$ExtraOption1: ";

$itemlist = explode(",",$ExtraOptions1);

echo "<select name=\"$ExtraOption1\">\n";
foreach ($ExtraOptions1 as $selectitem) {
echo "<option value=\"$selectitem\">$selectitem</option>\n";
}
echo "</select>\n";
}

(I set the columns into associated vars so I didn't have to call through $row['ExtraOptions1'] etc.)

Then, to add a second set, I just did the same, except changed all the 1's to 2's and bingo, it worked perfect :)

Hope this helps.

wruk999.

[caveman28]

Hmmmmm... that makes sense! I will try that. One way around it is I populate my catalog catagory page with items but then the link under each item and/thumbnail could be like browse.php?sku=[$sku] or something like that, hard code the links so they just go to that url with the variable at the end, and then hard code each item page rather than having PHP pull the info from the database. So, I could do an html options box normally. I am scared I won't be able to add little things to my page, like if there is an accessory that goes with just that one item, I'd put alink to it down below...but don't want it on every page. Guess the only way to do that with a database is to add another three fields like ExtraOpionEnable3, ExtraOption3 and ExtraOptions3 and do the same but write the link to the accessory to the db huh?

So once you have this done, the select boxes filled from the database by the code you provided, you'd need to pass that info to the cart right? Did you just update the sku value like:

$sku = $sku . $ExtraOption1 . $ExtraOption2;

and still pass $sku onto the cart?

Thanks for the help!

[wruk999]

Hi,

My cart software I wrote is slightly different to your average cart.

I can send any number of fields, proceeded by Extra_ to the cart and it will register them in the details of the order.

For instance, I can send Extra_Size, Extra_Colour, Extra_AnythingILike and it will add them onto the product description.

Regarding your sku etc, I wouldn't know how to integrate. The code I provided is purely what I use to get the extra options to print into a select box.

Cheers,
wruk999

[caveman28]

Ok, so I tried your code and had some luck but not all, my select boxes show up if ColorOptionEnable = 1 but they are empty!

here is what I did: Created three new database fields,

- ColorOptionEnable = 1 or 0
- ColorOptionText = The string "Color:"
- ColorOptionColors = List of colors such as Blue,Red,Green

My code is below, any idea why my boxes are empty? Been scratching my head all day! Does $selectitem need to be declared or is it a static? Should I have used different types for my db fields or run functions on them when I set them in phpMyAdin?

<!-- Up Top -->

<?php

if (!$PHPSESSID) {
header("Location: main.php");
exit;
}

session_start();

?>

<?
$connection = mysql_connect("127.0.0.1", "login", "password")
or die ("Couldn't connect to server.");

$db = mysql_select_db("morphosnaturals_com_-_db", $connection)
or die ("Couldn't select database.");

$sql = "select description,thumb,photo,sku,price,verbage,ColorOptionEnable,ColorOptionText,ColorOptionColors
from products where sku='$sku'";
$sql_result = mysql_query($sql,$connection)
or die ("Couldn't get list!");
?>

Then....

<?php
$i = 0;
while ($row = mysql_fetch_array($sql_result)) {
$sku = $row["sku"];
$description = $row["description"];
$color = $row["color"];
$size = $row["size"];
$photo = $row["photo"];
$thumb = $row["thumb"];
$verbage = $row["verbage"];
$price = sprintf("%0.2f",$row["price"]);
$ColorOptionEnable = $row["ColorOptionEnable"];
$ColorOptionText = $row["ColorOptionText"];
$ColorOptionColors = $row["ColorOptionColors"];
$itemlist = explode(",",$ColorOptionColors);

echo "
<br>
<br>
$verbage
";

echo '
</td>
</tr>
<tr width="318" height="10">
<td colspan="4">
<hr width="318" color="#000000" align="center">
</td>
</tr>
<tr width="318" height="26">
<td>
';

echo "
<a href=\"addtocart.php?sku=$sku\">Add to cart</a>
";

if ($ColorOptionEnable == 1) {
echo "$ColorOptionText: ";

echo "<select name=\"$ColorOptionText\">\n";
foreach ($ColorOptionColors as $selectitem) {
echo "<option value=\"$selectitem\">$selectitem</option>\n";
}
echo "</select>\n";
}

echo '
</td>
</tr>
</table>
</td>
<td width="60" align="left" valign="top" bgcolor="#F5F5F5"></td>
<td width="200" align="left" valign="top" bgcolor="#F5F5F5">
<br>
<br>
';

echo "
<img src=\"$photo\">
";
}
?>

[wruk999]

I created the Enable field as a TINYINT(1), the next two as VARCHAR fields.

Are you getting any error messages? And could you past the outputted source of the page when viewing it in the browser. (Only the bit for the selct box required!).

wruk999

[caveman28]

I fixed it!

Changed:

foreach ($ColorOptionColors as $selectitem) {

To:

foreach ($Itemlist as $selectitem) {