product options and related sql joins
Hello WA forum
I am coding a clothing store site for a client using DWCS4, php, Mysql, ecart5 and the WA ecommerce database sql file which I have modified to allow for a wider range of size and colour option groups, basically creating optionsize, optioncolor in place of options and optiongroupsize, optiongroupcolor in place of optiongroups. Was this a bad idea?
I am really struggling with how to populate the productoptions table which seems to act as a link between the options and the product table. I currently only have one record entered in this table which I have been using as a test to return the correct data against a single productID before inserting and populating the other fields. Also despite many hours I'm unable to create a sql join query which allows the size and and colour options to display although I get no sql error messages so perhaps the sql query is correct and it is the table structure that is at fault?
The sequel queries are....
$paramItem_rsSize = "-1";
if (isset($row_WADAproducts['ProductID'])) {
$paramItem_rsSize = (get_magic_quotes_gpc()) ? $row_WADAproducts['ProductID'] : addslashes($row_WADAproducts['ProductID']);
}
mysql_select_db($database_dsTenn, $dsTenn);
$query_rsSize = sprintf("SELECT productoptions.ProductOptionID, optionssize.OptionSizeName, optionssize.OptionGroupSizeID, optiongroupsize.OptionGroupSizeName FROM optionssize INNER JOIN productoptions ON optionssize.OptionSizeID = productoptions.ProductOptionID INNER JOIN optiongroupsize ON optionssize.OptionGroupSizeID = optiongroupsize.OptionGroupSizeID WHERE optiongroupsize.OptionGroupSizeName = 'Mens-Waist' AND productoptions.ProductID = %s", GetSQLValueString($paramItem_rsSize, "int"));
$rsSize = mysql_query($query_rsSize, $dsTenn) or die(mysql_error());
$row_rsSize = mysql_fetch_assoc($rsSize);
$totalRows_rsSize = mysql_num_rows($rsSize);
$paramItem_rsColor = "-1";
if (isset($row_WADAproducts['ProductID'])) {
$paramItem_rsColor = (get_magic_quotes_gpc()) ? $row_WADAproducts['ProductID'] : addslashes($row_WADAproducts['ProductID']);
}
mysql_select_db($database_dsTenn, $dsTenn);
$query_rsColor = sprintf("SELECT productoptions.ProductOptionID, optionscolor.OptionColorName, optionscolor.OptionGroupColorID, optiongroupcolor.OptionGroupColorName FROM optionscolor INNER JOIN productoptions ON optionscolor.OptionColorID = productoptions.ProductOptionID INNER JOIN optiongroupcolor ON optionscolor.OptionGroupColorID = optiongroupcolor.OptionGroupColorID WHERE optiongroupcolor.OptionGroupColorName = 'A02050000' AND productoptions.ProductID = %s", GetSQLValueString($paramItem_rsColor, "int"));
$rsColor = mysql_query($query_rsColor, $dsTenn) or die(mysql_error());
$row_rsColor = mysql_fetch_assoc($rsColor);
$totalRows_rsColor = mysql_num_rows($rsColor);
----------------------------------------------------------------------------------
Also I don't really understand how these options are used to obtain a single productID which is then added to the basket? I would be grateful if anyone could explain.
I have read many forum threads and followed the WA product options documentation but still the penny hasn't quite dropped with this and I'm fast running out of time.
Can anyone help or recommend a freelance resource that could quickly help resolve these issues for me?
Many thanks for any assistance in advance.
