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 [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:
Code:
$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.
Re: MySQL Problem with Product with Attribute Stock addon
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:
Code:
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 [FONT="Courier New"]Admin-->Tools-->Install_SQL_Patches-->SQL_Query_Executor[/FONT] utility and the row (i.e., record) was NOT deleted; however the utility simply reports "[FONT="Courier New"]1 statement processed[/FONT]", but not that MySQL failed to do anything.
From a Unix (FreeBSD) command line, I have executed the following:
Code:
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 [FONT="Courier New"]four white,medium[/FONT] and [FONT="Courier New"]five white,large[/FONT] 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 [FONT="Courier New"]cap_id[/FONT] and [FONT="Courier New"]order_number[/FONT]. 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 [FONT="Courier New"]stock_attributes[/FONT] element.
Comments or criticism will be appreciated.
