MySQL Problem with Product with Attribute Stock addon

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 products_with_attributes_stock table, the stock_attributes element is described as varchar(255).

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. 37,53 (Confirmed by using phpMyAdmin).

The program, products_with_attributes_stock.php, 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 “Product Variant was deleted” . . .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 “37,53" to “37/53", 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 products_with_attributes_stock.php.

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.

Someone . . .a "moderator"(?) moved this thread from the Bugs arena, I suppose because this is perceived more as an add-on problem (i.e., not associated with the core system); regardless, there is something strange about the execution of MySQL queries regarding the DELETE request when the query references a VARCHAR data type and the value is a comma separated list as in this construct:

```

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

I have submitted the preceding query-with-delete via the **Admin-->Tools-->Install_SQL_Patches-->SQL_Query_Executor** utility and the row (i.e., record) was NOT deleted; however the utility simply reports "1 statement processed", but not that MySQL failed to do anything.

From a Unix (FreeBSD) command line, I have executed the following:

mysql> use CAP_Store_AR042;
Database changed
mysql> DELETE FROM products_with_attributes_stock
where products_id="15"
and stock_attributes="37,53" limit 1;
Query OK, 0 rows affected (0.01 sec)

mysql>

Notice that the message issued by the MySQL operation states that zero rows were affected.  This indicates to me that **comma separated values** should not be stored in MySQL VARCHAR data types.   If you read the MySQL Manual, you will find that ENUM or SET data types are more suited for CSV strings.  I don't think that the string handling internals of MySQL know how to deal with a CSV in a VARCHAR data type.  In other words, a DELETE query cannot select from a VARCHAR element that contains an array such as 1,2,3,4,5,etc.  -- It will never match.

Regarding the Products with Attributes Stock add-on subsystem, I'm not sure that associating both **color** and **size** with a single product_id is manageable given the design of the system.  For example, if **four white,medium** and **five white,large** products are indicated in two respective rows, then the Online Catalog will display (in my implementation) the respective QOH in the dropdown **Size** menu, but an additional **Color** dropdown menu may list black, white, green, brown, etc., all with no indication of QOH.  (There may be idiosyncrasies at work here, too, given MySQL's inability to deal with the CSV in the VARCHAR data type.)  I think that cataloging sleeve within size within color will require separate categories until you get to a specific product_id i.e, the bottom of the hierarchy.  Unfortunately, this will require a lot of page refreshing while the customer wades through the process.

All said, consider this scenario:  Assume that there is only one significant attribute regarding size (i.e., no color, sleeve, etc.), but I want to add a radio button attribute to flag the product to indicate that additional custom processing is required.  (We loan uniforms to cadets and I want to track to whom the item was issued, without requiring the individual cadet to register as a customer.  The process will be managed with a single stock clerk account.)  This will require custom programming and the creation of an additional MySQL table containing (probably only) elements for cap_id and order_number.  Essentially, the radio button(s) are a YES or NO switch.

Regardless, I don't expect this to work given the CSV string that will be stored in the VARCHAR stock_attributes element.

Comments or criticism will be appreciated.