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.
Bookmarks