Stock by Attributes 5.0 Beta (for v1.5.0)

[mc12345678]

FYI - I do use Detailed Products Attributes (detailed multi-line)from EasyPopulate 4.0, downloadable from Github https://github.com/chaddro/EasyPopulate-4.0. Support thread is http://www.zen-cart.com/showthread.p...Support-Thread. (BB LINK function does not work here)



My problem is that, I do not know any thing about SQL statements!


98% of time any given product have 2-6 size attribute values with different prices and quantities.


I can get around code (an old programmer) with no knowledge SQL and SQL statements!

What you have stated makes sense, but I need a little more clear direction. Thank you.

So, was thinking about this some more... Based on having only a single attribute (size) for the product that has multiple selections within, the first question is, if you assigned three sizes to a product, and then populated the stock such that there was one that indicated a zero quantity, would the customer see the third option on your site, or would they only see two options available? If the answer is yes they only see two options, then the following will probably be the quickest easiest way to accomplish your add in.

Look at your total number of sizes available, regardless of to what product they are to be assigned.

Use EP to add that sized attribute to every product. (ultimately this should not be difficult/time consuming as if there are 7 sizes in the entire grouping, then every item will have 7 records to be created and through a spreadsheet program you can use relational equations to produce the results repetatively.)
Ideally all items that are of size 1 are the same price, all items of size 2 are the same price (but different than size 1), etc... If not, then would recommend populating that field in some methodic manner. Perhaps 80% of them are the same, so populate 100% with that price and then go fix the 20% that are different.

Download the attribute information as performed before focusing on just the items that are to be populated. Again, because you only have a single attribute to address, the remaining portion of this will be simpler than previously described.

Create a new column to populate the quantity for that item's attribute. (This part may take a while depending on what you have to do to populate it.) Again assuming that if an attribute has a zero quantity that the attribute will not be shown to the customer, then assign 0 for values you do not have, and assign positive numbers for those you do have.

Access your SQL database, open the products_with_attributes table, identify the exact name of that table (remembering there may be a prefix to that table name and all table names in your database) this will be needed for your SQL statement that is to be generated next. Also identify the fields that makeup your table, noting the entire name including any underscores etc...

If I am correct the general structure of the table has remained the same, it has been all about how it is populated and operated on that has changed throughout the various versions of stock by attribute. If they are different than I previously posted, may need to modify the following approach.

Assuming that no data exists in your products_with_attributes table that include the product numbers (product_id) for the items/attributes just added, then the following SQL statement can be generated concatenating the data that is in the sheet produced from the dump before.

Your SQL statement will be to Insert a new row of data to the (prefix_)products_with_attributes table assigning the row's column data equal to a value in the row of your spreadsheet. Future SQL statements using essentially this process would be Update or Replace statements to increase the quantities of the items in the (prefix_)products_with_attributes table.

INSERT INTO (prefix_)products_with_attributes (products_id, stock_attributes, quantity, sort) VALUES (`column1`, `column2`, `column3`, `column4`);

Note: (prefix_) would be replaced by whatever the prefix for the table is. column1 would be the value associated with products_id (v_products_id) which would be in your table, column2 would be the stock_attributes (v_products_attributes_id), column3 the quantity in the column created above, and column4 would be the sort order that possibly also should be added, but could be set as 10 for the first attribute, 20 for the second, etc..)

In Excel, if I had v_products_id in column A, v_products_attributes_id in column B, the quantity in column C, and a sort order in Column D, with the first row of data in row 2 (Titles in row 1), then in E2 I would put the following:

="INSERT INTO products_with_attributes (products_id, stock_attributes, quantity, sort) VALUES (`"&A2&"`, `"&B2&"`, `"&C2&"`, `"&D2&"`);"

Then I would copy this down to the end of the list of items/attributes to be added.

I would then copy and paste column E to a new worksheet and export the data to a CSV file (to support text capturing the data to feed back into the database). with the delimiter being an enter or carriage return or similar.

Then as said before either copy all of that into the applicable space to populate the tables (admin panel or mySQL admin area).

As a side note and depending on how you get your data together, it is possible to have a single SQL statement that adds multiple rows at once, the SQL statement could be:

="INSERT INTO products_with_attributes (products_id, stock_attributes, quantity, sort) VALUES (`"&A2&"`, `"&B2&"`, `"&C2&"`, `"&D2&"`), (`"&A3&"`, `"&B3&"`, `"&C3&"`, `"&D3&"`);"

This statement would add two rows, each additional entry has to be separated by a comma and have the same sized groupings throughout.

There are alternate ways to formulate the statement as well if that doesn't work on your server. (Ie. do the above work for a row or two, try it out and then get into a groove of updating... :) )