MySQL Problem with Product with Attribute Stock addon

[rtwingfield]

Zen-Cart v1.3.9h

At this point, this may be more of a MySQL idiosyncracy; regardless, the following scenario:

I have setup a product with two attributes, size and color, that are selectable via two respective drop-down menus. (I'm not sure this can be made to work because this will disconnect the relationship between the size-per-color QOH). Regardless, as an academic exercise, consider the following:

In the [FONT="Courier New"]products_with_attributes_stock[/FONT] table, the [FONT="Courier New"]stock_attributes[/FONT] element is described as [FONT="Courier New"]varchar(255)[/FONT].

The size and color attributes are stored in the element as a comma separated values string, i.e., the value 37 followed by a comma and the value 53, i.e. [FONT="Courier New"]37,53[/FONT] (Confirmed by using phpMyAdmin).

The program, [FONT="Courier New"]products_with_attributes_stock.php[/FONT], provides a delete option but fails if the product has associated multiple attributes such as color and size. For example, the following effected query:

	$query=DELETE from products_with_attributes_stock 
	where products_id="15" 
	and stock_attributes="37,53" limit 1

After clicking to confirm the deletion, the program continues to report that the “[FONT="Courier New"]Product Variant was deleted[/FONT]” . . .but it isn’t.

Adding to the aggravation, if the option to change the quantity is requested, the quantity is not changed but an additional table row is inserted with the ”change” quantity! (In non-SQL speak, an additional record is added to the file.) I have determined that if I manually change (via phpMyAdmin) the element value from “[FONT="Courier New"]37,53[/FONT]" to “[FONT="Courier New"]37/53[/FONT]", then the quantity can be edited, i.e., changed and an additional record is not added; however, the second attribute following the slash is ignored, as in not displayed by [FONT="Courier New"]products_with_attributes_stock.php[/FONT].

This apparently indicates that MySQL is not happy with a comma separated values list in the VARCHAR data type element. Similarly, if the comma is removed or replaced with a slash or semicolon, then the product can be deleted.

Finally regarding the online catalogue, if the product with two attributes is selected for addition to the cart, then the product is displayed as out of stock.

My conclusion is that the storage of comma separated values in the varchar(255) stock_attributes element is a violation of the MySQL data type.