items set up by color

[JBWebWorks]

I am doing a shopping cart with over 200 items. Most items are unique but there are several that come in two colors, white and bronze.
Question is should each item be set up individually in the database or with a color table and in the product pages have a list/menu to pick color?
I have not had any shopping carts with color or size choices, but i know there is a standard way most clothing or shoe sites list colors and sizes?

thanks for any help
Jim Balthrop

[Ray Borduin]

Are you doing inventory control? Do you use the same SKU in your store for both items? Are there any changes to price or other details besides the option selection?

There is no standard way, there are a variety of ways and it is generally best to use the one that is easiest for you that fits your application.

Most techniques would involve creating a table to store your options and maybe a second table to group your options, then either adding a third table to maintain the relationship between products and options, or if you would never have a product with more than one option you can just add an optiongroupID field directly to the products table.

This is all assuming you aren't doing inventory control, which complicates the database structure, but begins very similarly.

[JBWebWorks]

thanks Ray

no not doing inventory control. It is a site that sells replacement parts for screen pool enclosures and patios.
example of item would be an aluminum panel. item is exactly the same except for the color. i do not know for sure yet, but i think the sku would be different.
if that is the case, then the best way would be just a database row for each sku?

I am trying to think about the display of the items and each item would display just a different color.
A shopper would first be looking for the item and then the color.

I am having a hard time explaining it, but i want it to be easy to shop.

[Gretchen]

I have a similar question. What if you are doing inventory control, using PHP, MySQL and ecart, what is the easiest way to select size and color of say a shirt or a jacket and have that automatically deducted from the quantity in your database AND added to your ecart, cart.php page?

Please help! I know the basics, but when it comes to items with more than one option, I am lost.

[Ray Borduin]

Are you using powerstore inventory control model? Do you have inventory control working without options?

Start by defining the way you want to handle inventory. I would need a good explanation of your current inventory control to begin discussing how you might implement options into that system.

[Gretchen]

What is powerstore inventory control model?

[Ray Borduin]

Powerstore uses the inventory field in the database and a stored timestamp for when the inventory was updated. Then it has an updated query in the catelog that will look at the number sold since the inventory was last updated and marks items out of stock accordingly.

It is a standard cart checkout process and all of the inventory control logic is done in the catelog.

[Gretchen]

Thanks for all of your help Ray, but I am still confused... Is Powerstore an additional product of yours or is it included in ecart? If it is included, where can I find documentation on this?

[Ray Borduin]

Powerstore is a completed site that we created using all of the tools in our Super Suite including ecart. You can read about it here: http://www.webassist.com/solutions/powerstore/

[JBWebWorks]

Ray

I am not using inventory control. Using ecart4. item database setup and item display is my question.
shopping cart item example is a '5 inch aluminum gutter priced at $10.00 per each'.
Gutter comes in 9 different colors.
My concern is item display. Would like item to show once and a list/menu choice of color.
How to set up colors in database and make it shoppable in shopping cart of eCart4?

thanks for your help,
jim balthrop

[Ray Borduin]

you would just add a column to the cart for colors and mark it as unique. That is all you need from an eCart perspective.

In your database it is really up to you. Do products have more than one set of options?

It almost always involves creating an OptionGroup table with things like Size and Color, and an Options table with things like Small, medium, and large.

Then you either have the option group referenced directly from your products table if you only have one option per product, or you have another ProductOptions table to maintain that relationship.

From there it is just a matter of creating a recordset that returns the options on the cart page and displaying those in a list. Dreamweaver offers this functionality, so again it isn't really a matter of getting it working for the cart, it is a matter of getting it working in the catalog and adding to the cart will be the easy part.

[JBWebWorks]

thanks again Ray,

I think i get it, so if an item has colors as the only option, you only have to enter the item once in an items table and use an options table for colors and maintain a relationship?

[Ray Borduin]

That sounds right. Really you could use any number of database designs depending on how your products work and what you need. Any will work with eCart once you get them into a list, so the only difficult part is designing and populating your database and writing the correct query to create a recordset that you can use to populate that list.

By the time you get to eCart the work is already done.

[JBWebWorks]

Ray

could you help me with the database setup?
Now in my item table i have a column, itemCOLORID and a table, 'item color' but that is not right. In the table, item color, it has two columns, itemCOLORID and itemcolorname.
1 = white
2 = bronze
3= royal brown
and so forth

here is my sql query-
SELECT items.itemName, items.itemShortDesc, items.itemLongDesc, items.itemPrice, items.itemThumb, items.itemID, items.itemCatID, items.itemCOLORID, items.itemSKU, itemcolor.itemcolorname, itemcategory.itemCatName
FROM items, itemcolor, itemcategory
WHERE items.itemCatID = 3
ORDER BY items.itemName

i know i need some WHERE statement with a color group, but not sure how?

thanks for your help

[Ray Borduin]

It looks like you need to take a step back and do a little more research on database queries... specifically joining tables... I think it might help to go through this tutorial:
http://www.wellho.net/mouth/158_MySQL-LEFT-JOIN-and-RIGHT-JOIN-INNER-JOIN-and-OUTER-JOIN.html

[JBWebWorks]

thanks Ray
very good tutorial
But even with the correct JOIN, i think my database setup is not correct.

I use the WHERE itemCATID = 3 to go to the products page of all category 3 products.
On that page is where i want a list/menu for each item, where the shopper can pick the color of the product/products to add to the cart. In order to dynamically populate the list/menu with the proper choices of colors for that product, i must have a correct recordset.

So i think in my items table, i need a column of colorgroupID and a color table with colorgroupID saying what colors are available for that colorgroupID.

Am i on the right track?

[Ray Borduin]

Yes you are.

You could do something like:

TableProducts: ProductID, ProductName, ProductOptionGroup

TableOptionGroups: GroupID, GroupName

TableOptions: OptionID, OptionGroupID, OptionName

and then your SQL statement to find the options for a particular product is like:

SELECT * FROM TableProducts INNER JOIN TableOptionGroups ON TableProducts.ProductOptionGroup = TableOptionGroups.GroupID INNER JOIN TableOptions ON TableOptions.OptionGroupID = TableOptionGroups.GroupID

Now you would have a table that would have the GroupName from the option groups table that would return something like "Colors" and would have rows for each of the options in that group like "White" and "Grey" or whatever.

[JBWebWorks]

Thanks much for your help, Ray

using your example, i have column, itemOptionGroup, in table 'items'
have a table, OptionGroup, with columns 'GroupID' and 'GroupName'
have a table, Options, with columns 'OptionID' 'OptionGroupID' and 'OptionName'

as a test,
populating items.itemOptionGroup with value '1'
populating OptionGroup.GroupID with value '1' and OptionGroup.GroupName with value '1'
populating Options.OptionID and Options.OptionGroupID with value of '1'
populating Options.OptionName with value of 'white,bronze,royal brown'

clicking test in the Dreamweaver recordset returns the values of colors correctly.

i can't seem to get the list/menu to populate correctly using dynamic option.

[Ray Borduin]

You must not be filtering your recordset properly. There isn't much you can do wrong in adding dynamic options.

[JBWebWorks]

Ray

i am getting closer. I figured out that i was putting multiple values in my OptionName row, ie White,Bronze,Green, etc on one row, instead of having White on one row Bronze on one row, etc with the OptionsGroupID as '1' for each color in that group.

With your example of sql i get the values to show in browser and also show in the list/menu.

Now as a test i am showing one item and it should show 1 OptionGroup but it is showing two so i must not have it filtered correctly?

thank you very much for your help,

jim

[JBWebWorks]

Ray

In my items table i have a field 'itemOptionGroup' it is an index and an item would have a value of 1, 2, or 3


This is Options table

OptionID itemOptionGroup OptionName

1 1 White

2 1 Bronze

3 1 Royal Brown

4 1 Forest Green

5 2 Bronze

6 2 Almond

7 3 White

8 3 Bronze



my sql is

$query_rs_raingutters = "SELECT * FROM items, Options WHERE items.itemOptionGroup = Options.itemOptionGroup AND items.itemCatID = 3";



i have a repeat region to show 10 results

In the region is 4 dynamic text and 1 list/menu

In a browser it shows the same item 30 or more times and times out?

As a test, I removed the list/menu and the browser shows without timeing out. An item that has 4 colors, shows 4 times, item with 3 colors, shows 3 times, and so on?


Don't know if the problem is in my sql or the way i have my tables set up?


thanks for any help

jim balthrop

[Ray Borduin]

Try:

SELECT * FROM items INNER JOIN Options ON items.itemOptionGroup = Options.itemOptionGroup AND items.itemOptionGroup = SETUPAPARAMETER

Since you are using a repeat region, you are going to have to nest this recordset inside the repeat region so that you can have a unique recordeset for each row, since each will have it's own results.

You might want to start by getting it working on the details page. Then once it is come back and do the results. It will be similar, but more complex with manually moving recordsets and setting parameters manually.

[JBWebWorks]

Ray

thanks again for you help.

as i was searching for answers, i noticed a reference to BlueSky Footware tutorial from eCart3 and found it under the archived support for eCart.
It has specific tutorials relating to color choices. I just started the tutorial and saw that it has a recordset for the item listing and then a recordset for the color menu(one each for Mens and Womens shoes).
It mentioned the the recordset for the color option has to be wrapped inside the repeat region (I have not gotten to that tutorial yet)
One question i have is my products will have different color choices
item 1 may come in 9 colors
item 2 may come in 4 colors
item 3 may come in 2 colors

Do you know if i will need a recordset for each of those color sets?

[Ray Borduin]

No, a single recordset would do if you write the correct query and have the correct database setup.

[JBWebWorks]

Ray,

I was told by Eric Mittman that the menu that is dynamically populated by the colors recordset had to be nested inside the recordset.
Do you know where the php recordset code
<?php
if (!function_exists("GetSQLValueString")) {
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "")
{
if (PHP_VERSION < 6) {
$theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;
}

$theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);

switch ($theType) {
case "text":
$theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
break;
case "long":
case "int":
$theValue = ($theValue != "") ? intval($theValue) : "NULL";
break;
case "double":
$theValue = ($theValue != "") ? doubleval($theValue) : "NULL";
break;
case "date":
$theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
break;
case "defined":
$theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;
break;
}
return $theValue;
}
}

$currentPage = $_SERVER["PHP_SELF"];

$maxRows_rs_itemscat3 = 10;
$pageNum_rs_itemscat3 = 0;
if (isset($_GET['pageNum_rs_itemscat3'])) {
$pageNum_rs_itemscat3 = $_GET['pageNum_rs_itemscat3'];
}
$startRow_rs_itemscat3 = $pageNum_rs_itemscat3 * $maxRows_rs_itemscat3;

mysql_select_db($database_connstore, $connstore);
$query_rs_itemscat3 = "SELECT * FROM items WHERE items.itemCatID = 3 ORDER BY itemName ASC";
$query_limit_rs_itemscat3 = sprintf("%s LIMIT %d, %d", $query_rs_itemscat3, $startRow_rs_itemscat3, $maxRows_rs_itemscat3);
$rs_itemscat3 = mysql_query($query_limit_rs_itemscat3, $connstore) or die(mysql_error());
$row_rs_itemscat3 = mysql_fetch_assoc($rs_itemscat3);

if (isset($_GET['totalRows_rs_itemscat3'])) {
$totalRows_rs_itemscat3 = $_GET['totalRows_rs_itemscat3'];
} else {
$all_rs_itemscat3 = mysql_query($query_rs_itemscat3);
$totalRows_rs_itemscat3 = mysql_num_rows($all_rs_itemscat3);
}
$totalPages_rs_itemscat3 = ceil($totalRows_rs_itemscat3/$maxRows_rs_itemscat3)-1;

mysql_select_db($database_connstore, $connstore);
$query_rs_colors = "SELECT * FROM color_lookup, colors, items WHERE color_lookup.item_colorID = items.item_colorID";
$rs_colors = mysql_query($query_rs_colors, $connstore) or die(mysql_error());
$row_rs_colors = mysql_fetch_assoc($rs_colors);
$totalRows_rs_colors = mysql_num_rows($rs_colors);

$queryString_rs_itemscat3 = "";
if (!empty($_SERVER['QUERY_STRING'])) {
$params = explode("&", $_SERVER['QUERY_STRING']);
$newParams = array();
foreach ($params as $param) {
if (stristr($param, "pageNum_rs_itemscat3") == false &&
stristr($param, "totalRows_rs_itemscat3") == false) {
array_push($newParams, $param);
}
}
if (count($newParams) != 0) {
$queryString_rs_itemscat3 = "&" . htmlentities(implode("&", $newParams));
}
}
$queryString_rs_itemscat3 = sprintf("&totalRows_rs_itemscat3=%d%s", $totalRows_rs_itemscat3, $queryString_rs_itemscat3);
?>

and the php close recordset code
<?php
mysql_free_result($rs_itemscat3);

mysql_free_result($rs_colors);
?>


needs to be placed in the repeat region code
<?php do { ?>
<table width="90%" cellpadding="10" id="products">
<tr>
<td width="30%"><img src="<?php echo $row_rs_itemscat3['itemThumb']; ?>" /></td>
<td align="left" width="40%" valign="top"><?php echo $row_rs_itemscat3['itemName']; ?><br />
<br /><?php echo $row_rs_itemscat3['itemShortDesc']; ?><br />
<br /><?php echo $row_rs_itemscat3['itemLongDesc']; ?>

</td>
<td width="30%">&nbsp;</td>
</tr>
<tr>
<td>
<form id="form1" method="post" action="">
<p>Choose Color:
<select name="colors" id="colors">
<?php
do {
?>
<option value="<?php echo $row_rs_colors['color']?>"><?php echo $row_rs_colors['color']?></option>
<?php
} while ($row_rs_colors = mysql_fetch_assoc($rs_colors));
$rows = mysql_num_rows($rs_colors);
if($rows > 0) {
mysql_data_seek($rs_colors, 0);
$row_rs_colors = mysql_fetch_assoc($rs_colors);
}
?>
</select>
</p>
</form></td>
<td align="left"></td>
<td>$<?php echo $row_rs_itemscat3['itemPrice']; ?></td>
</tr>
</table>
<?php } while ($row_rs_itemscat3 = mysql_fetch_assoc($rs_itemscat3)); ?>

[Ray Borduin]

Eric mittman is correct. If you are on a results page and you want to display options you would want the recordset inside the repeat region so that it would show the correct options corresponding to the product you are currently displaying in that row.

[JBWebWorks]

thanks Ray

I have a support ticket working with Eric Mittman and i have tried to move the recordset code inside the repeat region, (including the php close recordset code) but i have either not placed it in the correct position or my sql query is incorrect.

you can see the page i am working on and how i am trying to use the menu as a color choice. I do not have the code moved on the page as i am not sure where to place it.

www.farrington-enterprises.com/rain-gutters.php

[Ray Borduin]

you can leave the close recordset code on the bottom of the page.

Move the open recordset code to just below the DO loop and make sure to set a parameter to filter based on a temporary $_POST variable that you make up.... since DW recordset code doesn't work when filtered with a parameter directly from another recordset, you need to use a temporary "fake" post variable and then add a line of code to set it like:

<?php
$_POST['myfakevar'] = $row_myRS['idField'];
?>

then you can filter the nested recordset on the post variable "myfakevar"

[JBWebWorks]

Ray
I really appreciate your help.
I made changes based on your suggestion and i must not be filtering properly.
I am getting this error
Parse error: syntax error, unexpected ';', expecting ',' or ')' in C:\vhosts\farrington-enterprises\rain-gutters.php on line 164

could you look at my code?

Here is code i used for setting fake variable
it is on line 2
<?php
$_POST['myfakevar'] = $row_rs_colors['items.item_colorID'];
?>

here is the recordset code
it starts on line 107
<?php do { ?>
<?php
if (!function_exists("GetSQLValueString")) {
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "")
{
if (PHP_VERSION < 6) {
$theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;
}

$theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);

switch ($theType) {
case "text":
$theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
break;
case "long":
case "int":
$theValue = ($theValue != "") ? intval($theValue) : "NULL";
break;
case "double":
$theValue = ($theValue != "") ? doubleval($theValue) : "NULL";
break;
case "date":
$theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
break;
case "defined":
$theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;
break;
}
return $theValue;
}
}

$currentPage = $_SERVER["PHP_SELF"];

$maxRows_rs_itemscat3 = 10;
$pageNum_rs_itemscat3 = 0;
if (isset($_GET['pageNum_rs_itemscat3'])) {
$pageNum_rs_itemscat3 = $_GET['pageNum_rs_itemscat3'];
}
$startRow_rs_itemscat3 = $pageNum_rs_itemscat3 * $maxRows_rs_itemscat3;

mysql_select_db($database_connstore, $connstore);
$query_rs_itemscat3 = "SELECT * FROM items WHERE items.itemCatID = 3 ORDER BY itemName ASC";
$query_limit_rs_itemscat3 = sprintf("%s LIMIT %d, %d", $query_rs_itemscat3, $startRow_rs_itemscat3, $maxRows_rs_itemscat3);
$rs_itemscat3 = mysql_query($query_limit_rs_itemscat3, $connstore) or die(mysql_error());
$row_rs_itemscat3 = mysql_fetch_assoc($rs_itemscat3);

if (isset($_GET['totalRows_rs_itemscat3'])) {
$totalRows_rs_itemscat3 = $_GET['totalRows_rs_itemscat3'];
} else {
$all_rs_itemscat3 = mysql_query($query_rs_itemscat3);
$totalRows_rs_itemscat3 = mysql_num_rows($all_rs_itemscat3);
}
$totalPages_rs_itemscat3 = ceil($totalRows_rs_itemscat3/$maxRows_rs_itemscat3)-1;

//this is line 162
$myfakevar_rs_colors = "-1";
if (isset($_POST['myfakevar'];)) {
$myfakevar_rs_colors = (get_magic_quotes_gpc()) ? $_POST['myfakevar']; : addslashes($_POST['myfakevar'];);
}
mysql_select_db($database_connstore, $connstore);
$query_rs_colors = sprintf("SELECT * FROM color_lookup, colors, items WHERE color_lookup.item_colorID = %s", GetSQLValueString($myfakevar_rs_colors, "int"));
$rs_colors = mysql_query($query_rs_colors, $connstore) or die(mysql_error());
$row_rs_colors = mysql_fetch_assoc($rs_colors);
$totalRows_rs_colors = mysql_num_rows($rs_colors);

$queryString_rs_itemscat3 = "";
if (!empty($_SERVER['QUERY_STRING'])) {
$params = explode("&", $_SERVER['QUERY_STRING']);
$newParams = array();
foreach ($params as $param) {
if (stristr($param, "pageNum_rs_itemscat3") == false &&
stristr($param, "totalRows_rs_itemscat3") == false) {
array_push($newParams, $param);
}
}
if (count($newParams) != 0) {
$queryString_rs_itemscat3 = "&" . htmlentities(implode("&", $newParams));
}
}
$queryString_rs_itemscat3 = sprintf("&totalRows_rs_itemscat3=%d%s", $totalRows_rs_itemscat3, $queryString_rs_itemscat3);
?>
<table width="90%" cellpadding="10" id="products">
<tr>
<td width="30%"><img src="<?php echo $row_rs_itemscat3['itemThumb']; ?>" /></td>
<td align="left" width="40%" valign="top"><h1><?php echo $row_rs_itemscat3['itemName']; ?></h1><br />
<br /><h2><?php echo $row_rs_itemscat3['itemShortDesc']; ?></h2><br />
<br /><?php echo $row_rs_itemscat3['itemLongDesc']; ?></td>
<td width="30%">&nbsp;</td>
</tr>
<tr>
<td>
<form id="form1" method="post" action="">
<p>Choose Color:
<select name="colors" id="colors">
<?php
do {
?>
<option value="<?php echo $row_rs_colors['color']?>"><?php echo $row_rs_colors['color']?></option>
<?php
} while ($row_rs_colors = mysql_fetch_assoc($rs_colors));
$rows = mysql_num_rows($rs_colors);
if($rows > 0) {
mysql_data_seek($rs_colors, 0);
$row_rs_colors = mysql_fetch_assoc($rs_colors);
}
?>
</select>
</p>
</form></td>
<td align="left"></td>
<td><h1>$<?php echo $row_rs_itemscat3['itemPrice']; ?></h1></td>
</tr>
</table>
<?php } while ($row_rs_itemscat3 = mysql_fetch_assoc($rs_itemscat3)); ?>
<p>&nbsp;</p>


</div>
<br class="clearFloat" />
</div>
<div id="footer">Footer</div>
</div>

</body>
</html>
<?php
mysql_free_result($rs_itemscat3);

mysql_free_result($rs_colors);
?>

[Ray Borduin]

The fakevar code would have to be in the DO loop as well.

You want it to be set to the current record so that when it is used to filter the nested recordset it is also filtered to the current record.

[JBWebWorks]

Ray

here is where i am now
myfakevar has runtime value of $_POST['myfakevar']
my sql is
SELECT *
FROM items, color_lookup, colors
WHERE color_lookup.item_colorID = myfakevar

code to call the variable
<?php
$_POST['myfakevar'] = $row_rs_itemscat3['items.item_colorID'];
?>

I have this code for the repeat region
<?php do { ?>
<?php
$_POST['myfakevar'] = $row_rs_itemscat3['items.item_colorID'];
?>
<?php
if (!function_exists("GetSQLValueString")) {
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "")
{
if (PHP_VERSION < 6) {
$theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;
}

$theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);

switch ($theType) {
case "text":
$theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
break;
case "long":
case "int":
$theValue = ($theValue != "") ? intval($theValue) : "NULL";
break;
case "double":
$theValue = ($theValue != "") ? doubleval($theValue) : "NULL";
break;
case "date":
$theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
break;
case "defined":
$theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;
break;
}
return $theValue;
}
}

$currentPage = $_SERVER["PHP_SELF"];

$maxRows_rs_itemscat3 = 10;
$pageNum_rs_itemscat3 = 0;
if (isset($_GET['pageNum_rs_itemscat3'])) {
$pageNum_rs_itemscat3 = $_GET['pageNum_rs_itemscat3'];
}
$startRow_rs_itemscat3 = $pageNum_rs_itemscat3 * $maxRows_rs_itemscat3;

mysql_select_db($database_connstore, $connstore);
$query_rs_itemscat3 = "SELECT * FROM items WHERE items.itemCatID = 3 ORDER BY itemName ASC";
$query_limit_rs_itemscat3 = sprintf("%s LIMIT %d, %d", $query_rs_itemscat3, $startRow_rs_itemscat3, $maxRows_rs_itemscat3);
$rs_itemscat3 = mysql_query($query_limit_rs_itemscat3, $connstore) or die(mysql_error());
$row_rs_itemscat3 = mysql_fetch_assoc($rs_itemscat3);

if (isset($_GET['totalRows_rs_itemscat3'])) {
$totalRows_rs_itemscat3 = $_GET['totalRows_rs_itemscat3'];
} else {
$all_rs_itemscat3 = mysql_query($query_rs_itemscat3);
$totalRows_rs_itemscat3 = mysql_num_rows($all_rs_itemscat3);
}
$totalPages_rs_itemscat3 = ceil($totalRows_rs_itemscat3/$maxRows_rs_itemscat3)-1;

$myfakevar_rs_colors = "0";
if (isset($_POST['myfakevar'])) {
$myfakevar_rs_colors = (get_magic_quotes_gpc()) ? $_POST['myfakevar'] : addslashes($_POST['myfakevar']);
}
mysql_select_db($database_connstore, $connstore);
$query_rs_colors = sprintf("SELECT * FROM items, color_lookup, colors WHERE color_lookup.item_colorID = %s", GetSQLValueString($myfakevar_rs_colors, "int"));
$rs_colors = mysql_query($query_rs_colors, $connstore) or die(mysql_error());
$row_rs_colors = mysql_fetch_assoc($rs_colors);
$totalRows_rs_colors = mysql_num_rows($rs_colors);

$queryString_rs_itemscat3 = "";
if (!empty($_SERVER['QUERY_STRING'])) {
$params = explode("&", $_SERVER['QUERY_STRING']);
$newParams = array();
foreach ($params as $param) {
if (stristr($param, "pageNum_rs_itemscat3") == false &&
stristr($param, "totalRows_rs_itemscat3") == false) {
array_push($newParams, $param);
}
}
if (count($newParams) != 0) {
$queryString_rs_itemscat3 = "&" . htmlentities(implode("&", $newParams));
}
}
$queryString_rs_itemscat3 = sprintf("&totalRows_rs_itemscat3=%d%s", $totalRows_rs_itemscat3, $queryString_rs_itemscat3);
?>
<table width="90%" cellpadding="10" id="products">
<tr>
<td width="30%"><img src="<?php echo $row_rs_itemscat3['itemThumb']; ?>" /></td>
<td align="left" width="40%" valign="top"><h1><?php echo $row_rs_itemscat3['itemName']; ?></h1><br />
<br /><h2><?php echo $row_rs_itemscat3['itemShortDesc']; ?></h2><br />
<br /><?php echo $row_rs_itemscat3['itemLongDesc']; ?></td>
<td width="30%">&nbsp;</td>
</tr>
<tr>
<td>
<form id="form1" method="post" action="">
<p>Choose Color:
<select name="colors" id="colors">
<?php
do {
?>
<option value="<?php echo $row_rs_colors['color']?>"><?php echo $row_rs_colors['color']?></option>
<?php
} while ($row_rs_colors = mysql_fetch_assoc($rs_colors));
$rows = mysql_num_rows($rs_colors);
if($rows > 0) {
mysql_data_seek($rs_colors, 0);
$row_rs_colors = mysql_fetch_assoc($rs_colors);
}
?>
</select>
</p>
</form></td>
<td align="left"></td>
<td><h1>$<?php echo $row_rs_itemscat3['itemPrice']; ?></h1></td>
</tr>
</table>
<?php } while ($row_rs_itemscat3 = mysql_fetch_assoc($rs_itemscat3)); ?>

Now in a browser, it shows duplicate items many times and the menu has duplicate choices.

[Ray Borduin]

your itemscat recordset should remain on the top of the page. Only the colors recordset should be inside the loop.

GetSQLValueString function declaration code should also only appear at the top. You are getting closer, but don't act blindly... try to understand what you are doing and why if you can otherwise this stuff will never get easier.

[JBWebWorks]

thanks Ray

i will try to move the correct code to the top of the page. Believe me, i try to understand exactly what code results in certain results. I have never had a clear understanding of writing variables in sql.
thanks for bearing with me.

[Ray Borduin]

The code works like this:

1) Create your products recordset to loop through
2) start to loop through the products recordset
3) create a recoredset to show the products options
4) loop and show the options in a select list
5) end the loop for the product recordset

the code should be in that order

[JBWebWorks]

My thought process (besides being fried) is this.
I have the recordset, itemscat3, to loop through filtered by itemcatID (category 3)
the repeat region loops the items recordset
create the recordset, colors, to show the items color options (not sure if my sql is correct) this is the sql (SELECT *
FROM items, colors, color_lookup
WHERE color_lookup.item_colorID = myfakevar) runtime value of myfakevar is $_POST['myfakevar']
(here i am fuzzy) i dynamically populate the select list with the colors recordset with label and value as colors
end the loop is at the bottom of the page

i moved what i think is the recordset, colors, inside the recordset (after the PHP do code)
as far as the myfake variable, my thinking is i am telling what the variable should be with the code
<?php
$_POST['myfakevar'] = $row_rs_itemscat3['item_colorID'];
?>
so in effect the sql filtering the recordset, colors, now says that color_lookup.item_colorID is equal to items.item_colorID which should filter the options of colors by the record of items.

[Ray Borduin]

SELECT *
FROM colors INNER JOIN color_lookup ON color_lookup.keyField = colors.keyfield
WHERE color_lookup.item_colorID = myfakevar

[JBWebWorks]

IT WORKS!
thanks very much Ray; what do you want for Christmas? and how can i send it to you.

[david390068]

Please help. I am new to eCart, but I have been reading a lot of posts regarding some of the items I am struggling with. If someone could help me out I would greatly appreciate it.

I am utilizing the sample BlueSkyCart with the sample dbase in PHP. What I am trying to accomplish is to have the available options for each product listed to display and allow the user to select the options and add to cart right from the music.php page. Each product might have more options than other. Any help would be greatly appreciated.

I have modified the default table structure to:

Items Table:
ItemID
ItemCatID
ItemName
ItemShortDesc
ItemLongDesc
ItemPrice
ItemSKU
ItemThumb
ItemImage
ItemWeight
ItemAvailable (varchar)
ItemInvCount (int)
ItemManufacturerID (int)

ItemOptions Table:
OptionID (int)
ItemID (int)
ItemOptionID (varchar)
ItemOptionValue (varchar)

Recordsets:
rsItems
SELECT *
FROM items
WHERE ItemCatID = 1 AND items.ItemAvailable = 'y' AND items.ItemInvCount > 0
ORDER BY ItemID ASC

rsOptions
SELECT *
FROM items INNER JOIN itemoptions ON items.ItemID = itemoptions.ItemID
WHERE items.ItemAvailable = 'y' AND items.itemInvCount > 0


music.php code:
<?php require_once('Connections/connBSM.php'); ?>
<?php
if (!function_exists("GetSQLValueString")) {
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "")
{
if (PHP_VERSION < 6) {
$theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;
}

$theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);

switch ($theType) {
case "text":
$theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
break;
case "long":
case "int":
$theValue = ($theValue != "") ? intval($theValue) : "NULL";
break;
case "double":
$theValue = ($theValue != "") ? doubleval($theValue) : "NULL";
break;
case "date":
$theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
break;
case "defined":
$theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;
break;
}
return $theValue;
}
}

mysql_select_db($database_connBSM, $connBSM);
$query_rsItems = "SELECT * FROM items WHERE ItemCatID = 1 AND items.ItemAvailable = 'y' AND items.ItemInvCount > 0 ORDER BY ItemID ASC";
$rsItems = mysql_query($query_rsItems, $connBSM) or die(mysql_error());
$row_rsItems = mysql_fetch_assoc($rsItems);
$totalRows_rsItems = mysql_num_rows($rsItems);

mysql_select_db($database_connBSM, $connBSM);
$query_rsOptions = "SELECT * FROM items INNER JOIN itemoptions ON items.ItemID = itemoptions.ItemID WHERE items.ItemAvailable = 'y' AND items.itemInvCount > 0";
$rsOptions = mysql_query($query_rsOptions, $connBSM) or die(mysql_error());
$row_rsOptions = mysql_fetch_assoc($rsOptions);
$totalRows_rsOptions = mysql_num_rows($rsOptions);
?><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml"><!-- InstanceBegin template="/Templates/siteassist_template.dwt.php" codeOutsideHTMLIsLocked="false" -->
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<!-- InstanceBeginEditable name="doctitle" -->
<title>Blue Sky Music - [PageTitle]</title>
<!-- InstanceEndEditable -->
<link rel="stylesheet" type="text/css" href="siteassist_css/style.css" />
<link rel="stylesheet" type="text/css" href="siteassist_css/color.css" />
<link rel="stylesheet" type="text/css" href="siteassist_css/template.css" />
<link rel="stylesheet" type="text/css" href="siteassist_css/navigation.css" />
<link rel="stylesheet" type="text/css" href="siteassist_css/navigation_color.css" />
<link rel="stylesheet" type="text/css" href="siteassist_css/my_styles.css" />

<!-- InstanceBeginEditable name="head" --><!-- InstanceEndEditable --><!-- InstanceParam name="Body_onLoad" type="text" value="" -->
</head><body id="subPage" onload="">
<div id="globalContainer">
<div id="borderTop"><img src="siteassist_images/spacer.gif" alt="" height="7"/></div>
<div id="logo"><img id="LogoImage" src="images/BSM_logo.gif" alt="Blue Sky Music" /></div>
<div id="topNavTop"><img src="siteassist_images/spacer.gif" alt="" height="4"/></div>
<div id="topNav">
<ul id="SAW_TopNavigation">
<li class="SAW_TopNav_Dark">
<div class="SAW_TopNavBorderLeft"><img alt="" src="siteassist_images/spacer.gif" width="1" height="1" /></div>
<a href="index.php">Home&nbsp;Page</a>
<div class="SAW_TopNavBorderRight"><img alt="" src="siteassist_images/spacer.gif" width="1" height="1" /></div>
</li>
<li class="SAW_TopNav_Dark">
<div class="SAW_TopNavBorderLeft"><img alt="" src="siteassist_images/spacer.gif" width="1" height="1" /></div>
<a href="about.php">About</a>
<div class="SAW_TopNavBorderRight"><img alt="" src="siteassist_images/spacer.gif" width="1" height="1" /></div>
</li>
<li class="SAW_TopNav_Dark">
<div class="SAW_TopNavBorderLeft"><img alt="" src="siteassist_images/spacer.gif" width="1" height="1" /></div>
<a href="products.php">Products</a>
<div class="SAW_TopNavBorderRight"><img alt="" src="siteassist_images/spacer.gif" width="1" height="1" /></div>
</li>
<li class="SAW_TopNav_Dark">
<div class="SAW_TopNavBorderLeft"><img alt="" src="siteassist_images/spacer.gif" width="1" height="1" /></div>
<a href="cart.php">Cart</a>
<div class="SAW_TopNavBorderRight"><img alt="" src="siteassist_images/spacer.gif" width="1" height="1" /></div>
</li>
</ul>
<div style="clear: left;"></div>
</div>
<div id="header"></div>

<div id="innerContainerWrapper">
<div id="innerContainer">
<div id="innerLeft">
<div id="innerLeftTop"><img src="siteassist_images/inner_left_top.jpg" width="184" height="9" alt="" /></div>
<div id="sideNav">
<!-- InstanceBeginEditable name="SideNavigation" -->
<div id="SAW_SideNavigation">
<ul>
<li class="SAW_SideNav1_Selected_Bevel"><a href="music.php">Music</a></li>
<li class="SAW_SideNav1_Bevel"><a href="gear.php">Gear</a></li>
</ul>
</div>
<!-- InstanceEndEditable --></div>
<div id="resources"></div>
</div>
<div id="innerRight"><!-- InstanceBeginEditable name="PageBody" -->
<div class="contentBodyContainer">
<div class="hr"></div>
<h1>How underground are you? Show your BSM colors!</h1>
<p>Lorem ipsum dolor sit amet, consectetuer dulce et decorum est e pluribus unum fiat lux semper fidelis ad infinitum. Lorem ipsum dolor sit amet. Lorem ipsum dolor sit amet, consectetuer dulce et decorum est e pluribus unum fiat lux semper fidelis ad infinitum. Lorem ipsum dolor sit amet. </p>
<?php do { ?>
<div id="catListing">
<table width="100%" border="0" cellspacing="2" cellpadding="2">
<tr>
<td width="104" rowspan="5"><span class="imageLeft"><a href="detail.php?ID=<?php echo $row_rsItems['ItemID']; ?>"><img src="images/thumbs/<?php echo $row_rsItems['ItemThumb']; ?>" alt="<?php echo $row_rsItems['ItemName']; ?>" width="99" height="97" border="0" /></a></span></td>
<td width="279"><a href="detail.php?ID=<?php echo $row_rsItems['ItemID']; ?>"><?php echo $row_rsItems['ItemName']; ?></a></td>
<td width="127">Option1</td>
</tr>
<tr>
<td><?php echo $row_rsItems['ItemShortDesc']; ?></td>
<td>Option2</td>
</tr>
<tr>
<td>$<span class="FeaturedItemBodyTextBold"><?php echo $row_rsItems['ItemPrice']; ?></span></td>
<td>Option3</td>
</tr>
<tr>
<td>&nbsp;</td>
<td>Option4</td>
</tr>
<tr>
<td>&nbsp;</td>
<td>Add to Cart</td>
</tr>
</table>
<p class="imageLeft">&nbsp;</p>
<h2>&nbsp;</h2>
<p>&nbsp;</p>
</div>
<?php } while ($row_rsItems = mysql_fetch_assoc($rsItems)); ?></div>
<!-- InstanceEndEditable --></div>
<div style="clear: left;"></div>
<div id="innerLeftBottom"><img src="siteassist_images/inner_left_bottom.jpg" width="184" height="4" alt="" /></div>
</div>
</div>
<div id="borderBottom"><img src="siteassist_images/spacer.gif" alt="" height="7"/></div>
</div>
<div id="footer">
<div class="siteFooterLinks"> <a href="sitemap.php">Sitemap</a> | <a href="termsofuse.php">Terms&nbsp;of&nbsp;Use</a> | <a href="privacypolicy.php">Privacy&nbsp;Policy</a> </div>
</div>
</body>
<!-- InstanceEnd --></html>
<?php
mysql_free_result($rsItems);

mysql_free_result($rsOptions);
?>