Stock by Attribute v4.0 addon for v1.3.5-1.3.9

**mc12345678** · 11 Sep 2015, 10:41 AM

Originally Posted by mc12345678

Not in front of a computer, but unless every product in your store is also tracked by attributes, my guess is the product not stocked by attributes is no longer locateable by search.

For one thing the SBA table needs to be left joined on the query. But then if a search is performed on a sba tracked item on a field not in the sba table for a product tracked by sba, then one row for each variant of the product will be returned. Pair this with the second query returns the customid (when not doing a search) and if it does so for some purpose of display then would want to do the same thing in the search query.

My thought though and I still need to run the query through a database test, would be to either apply distinct to the first (search) query, left join the sba table on products_id, apply a like instead of = on the search, and remove pas.customid from the second query, but duplicate the above to it as well...

Otherwise, might add an additional query ahead of these two such that the sba table is searched, when it has a positve result of a product_id, then feed that into the search queryresult as one of the where comparisons.

Just a thought...

So my eyes certainly deceived me... The pas.customid was in both queries...

Here for your specific situation is how I would rewrite this query... Mind you the products_upc field is not standard to ZC, so if/when this modification is incorporated, that field/others are likely to not be included with SBA out-of-the box.

Code:

$products_query_raw = ("select DISTINCT p.products_type, p.products_id, pd.products_name, p.products_quantity,
                                       p.products_image, p.products_price, p.products_date_added,
                                       p.products_last_modified, p.products_date_available,
                                       p.products_status, p2c.categories_id,
                                       p.products_model,
                                       p.products_quantity_order_min,  p.products_quantity_order_units, p.products_priced_by_attribute,
                                       p.product_is_free,  p.product_is_call, p.products_quantity_mixed,  p.product_is_always_free_shipping,
p.products_upc, pas.customid,
                                       p.products_quantity_order_max, p.products_sort_order,
                                       p.master_categories_id
                                from " . TABLE_PRODUCTS . " p LEFT JOIN "
                                       . TABLE_PRODUCTS_WITH_ATTRIBUTES_STOCK . " pas on (pas.products_id = p.products_id), " . TABLE_PRODUCTS_DESCRIPTION . " pd, "
                                       . TABLE_PRODUCTS_TO_CATEGORIES . " p2c, "
                                       . TABLE_PRODUCTS_WITH_ATTRIBUTES_STOCK . " pas
                                where p.products_id = pd.products_id
and pd.language_id = '" . (int)$_SESSION['languages_id'] . "'
                                and (p.products_id = p2c.products_id
                                and p.master_categories_id = p2c.categories_id)
                                and (
                                pd.products_name like '%" . zen_db_input($_GET['search']) . "%'
                                or pd.products_description like '%" . zen_db_input($_GET['search']) . "%'
                                or p.products_id = '%" . zen_db_input($_GET['search']) . "%'
                                or p.products_upc = '%" . zen_db_input($_GET['search']) . "%'
                                or pas.customid like '%" . zen_db_input($_GET['search']) . "%'
                                or p.products_model = '%" . zen_db_input($_GET['search']) . "%')" .
                                $order_by);

    } else {
      $products_query_raw = ("select p.products_type, p.products_id, pd.products_name, p.products_quantity,
                                       p.products_image, p.products_price, p.products_date_added,
                                       p.products_last_modified, p.products_date_available,
                                       p.products_status, p.products_model,
                                       p.products_quantity_order_min,  p.products_quantity_order_units, p.products_priced_by_attribute,
                                       p.product_is_free,  p.product_is_call, p.products_quantity_mixed,  p.product_is_always_free_shipping,
p.products_upc, pas.customid,
                                       p.products_quantity_order_max, p.products_sort_order
                                from " . TABLE_PRODUCTS . " p LEFT JOIN "
                                       . TABLE_PRODUCTS_WITH_ATTRIBUTES_STOCK . " pas on (pas.products_id = p.products_id), " .  TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_PRODUCTS_TO_CATEGORIES . "  p2c, " . TABLE_PRODUCTS_WITH_ATTRIBUTES_STOCK . " pas
                                where p.products_id = pd.products_id
                               and pas.products_id = p.products_id
                                and pd.language_id = '" . (int)$_SESSION['languages_id'] . "'
                                and p.products_id = p2c.products_id
                                and p2c.categories_id = '" . (int)$current_category_id . "'" .
                                $order_by);
    }

The above will produce results that will list all variants of a product when either listing the products in a category or when searching on a value that is not in the customid of the sba variant(s). The returned data has an additional field (pas.customid) that if not used should be removed to restore the "normal" returned dataset but searchable by the customid. If the returned pas.customid is kept in the returned data, then suggest changing up the row's display action as the normal product_info page has no direct relatonship with the tracked SBA quantities.

Btw, I too had some trouble using the search function with the original posted code, but found there were some spaces whether intially entered or some odd posting fluke known to occasionally happen I do not know, but the above worked in a sql query.

**cefyn** · 12 Sep 2015, 12:00 AM

Now you're being too kind mc , any spaces in the original code are entirely due to an inaccurate and unreliable operator guessing what the code should be .
Your amendments have it working just as you say, and the next thing for me to try and do is to change the rows display action. Thank you (once again) for giving so generously of your time and expertise . Thank you also for encouraging me to look for a solution here . Zen Cart rocks .

**cefyn** · 12 Sep 2015, 10:33 AM

In case anyone else finds this useful,I found the above , as is, was giving me table alias errors. I am using:

$products_query_raw = ("select DISTINCT p.products_type, p.products_id, pd.products_name, p.products_quantity,
p.products_image, p.products_price, p.products_date_added,
p.products_last_modified, p.products_date_available,
p.products_status, p2c.categories_id,
p.products_model,
p.products_quantity_order_min, p.products_quantity_order_units, p.products_priced_by_attribute,
p.product_is_free, p.product_is_call, p.products_quantity_mixed, p.product_is_always_free_shipping,
p.products_upc, pas.customid,
p.products_quantity_order_max, p.products_sort_order,
p.master_categories_id
from " . TABLE_PRODUCTS . " p LEFT JOIN "
. TABLE_PRODUCTS_WITH_ATTRIBUTES_STOCK . " pas on (pas.products_id = p.products_id), " . TABLE_PRODUCTS_DESCRIPTION . " pd, "
. TABLE_PRODUCTS_TO_CATEGORIES . " p2c

where p.products_id = pd.products_id
and pd.language_id = '" . (int)$_SESSION['languages_id'] . "'
and (p.products_id = p2c.products_id
and p.master_categories_id = p2c.categories_id)
and (
pd.products_name like '%" . zen_db_input($_GET['search']) . "%'
or pd.products_description like '%" . zen_db_input($_GET['search']) . "%'
or p.products_id = '%" . zen_db_input($_GET['search']) . "%'
or p.products_upc = '%" . zen_db_input($_GET['search']) . "%'
or pas.customid like '%" . zen_db_input($_GET['search']) . "%'
or p.products_model = '%" . zen_db_input($_GET['search']) . "%')" .
$order_by);

} else {
$products_query_raw = ("select p.products_type, p.products_id, pd.products_name, p.products_quantity,
p.products_image, p.products_price, p.products_date_added,
p.products_last_modified, p.products_date_available,
p.products_status, p.products_model,
p.products_quantity_order_min, p.products_quantity_order_units, p.products_priced_by_attribute,
p.product_is_free, p.product_is_call, p.products_quantity_mixed, p.product_is_always_free_shipping,
p.products_upc, pas.customid,
p.products_quantity_order_max, p.products_sort_order
from " . TABLE_PRODUCTS . " p LEFT JOIN "
. TABLE_PRODUCTS_WITH_ATTRIBUTES_STOCK . " pas on (pas.products_id = p.products_id), " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c
where p.products_id = pd.products_id
and pas.products_id = p.products_id
and pd.language_id = '" . (int)$_SESSION['languages_id'] . "'
and p.products_id = p2c.products_id
and p2c.categories_id = '" . (int)$current_category_id . "'" .
$order_by);
}

**adb34** · 16 Sep 2015, 02:17 PM

I am having a problem with this plugin and I know I have done something, again, wrong but please can you help?

I have installed as per the instructions and then ran stock_by_attr_install.php. Selected full/upgrade DB install. All was fine.

However, I had a problem with my shop so I went back to stock_by_attr_install.php and selected & ran remove all from the DB. Now this is where it all goes wrong. The problem I had was not caused by the full/upgrade DB install so, as I thought, ran full/upgrade DB install again. Now I am getting the error message 'WARNING: An Error occurred, please refresh the page and try again.'

I have tried to do a restore of the DB but that is throwing up

Error

SQL query:

--
-- Table structure for table `ecs_address_book`
--

DROP TABLE IF EXISTS `ecs_address_book`;

MySQL said: Documentation
#1046 - No database selected

So I am stumped their. Is it possible to "pick" out the DB changes myself to see what is going on? If so what are added to the DB?

**mc12345678** · 16 Sep 2015, 03:01 PM

How about the error message generated by trying to do the install again? The other issue is related to the file made for the backup not to this module.

See the following for more guidance: http://www.zen-cart.com/content.php?124-blank-page

**adb34** · 16 Sep 2015, 05:13 PM

Originally Posted by mc12345678

How about the error message generated by trying to do the install again? The other issue is related to the file made for the backup not to this module.

See the following for more guidance: http://www.zen-cart.com/content.php?124-blank-page

As requested

Code:

[16-Sep-2015 17:09:48 Europe/London] #1 trigger_error() called at [/homepages/20/d503465712/htdocs/shops/ecs/includes/classes/db/mysql/query_factory.php:155]
#2 queryFactory->show_error() called at [/homepages/20/d503465712/htdocs/shops/ecs/includes/classes/db/mysql/query_factory.php:132]
#3 queryFactory->set_error() called at [/homepages/20/d503465712/htdocs/shops/ecs/includes/classes/db/mysql/query_factory.php:262]
#4 queryFactory->Execute() called at [/homepages/20/d503465712/htdocs/shops/ecs/backoffice/stock_by_attr_install.php:682]
#5 insertDynDropdownsConfiguration() called at [/homepages/20/d503465712/htdocs/shops/ecs/backoffice/stock_by_attr_install.php:2272]

[16-Sep-2015 17:09:48 Europe/London] PHP Fatal error: 1062:Duplicate entry 'PRODINFO_ATTRIBUTE_DYNAMIC_STATUS' for key 'unq_config_key_zen' :: INSERT INTO `ecs_configuration` (configuration_title, configuration_key, configuration_value, 
configuration_description, configuration_group_id, sort_order, 
date_added, use_function, set_function) 
VALUES 
('Enable Dynamic Dropdowns', 'PRODINFO_ATTRIBUTE_DYNAMIC_STATUS', '2', 'Selects status of using this portion of the SBA plugin (Dynamic Dropdowns).', 50, 10, now(), NULL, 'zen_cfg_select_drop_down(array(array(\'id\'=>\'0\', \'text\'=>\'Off\'), array(\'id\'=>\'1\', \'text''=>\'On for All SBA Tracked\'), array(\'id\'=>\'2\', \'text''=>\'On for Multi-Attribute Only\'), array(\'id\'=>\'3\', \'text''=>\'On for Single-Attribute Only\'), ),'),
('Product Info Single Attribute Display Plugin', 'PRODINFO_ATTRIBUTE_PLUGIN_SINGLE', 'multiple_dropdowns', 'The plugin used for displaying attributes on the product information page.', 50, 20, now(), NULL, 'zen_cfg_select_option(array(\'single_radioset\', \'single_dropdown\',\'multiple_dropdowns\',\'sequenced_dropdowns\',\'sba_sequenced_dropdowns\'),'),
('Product Info Multiple Attribute Display Plugin', 'PRODINFO_ATTRIBUTE_PLUGIN_MULTI', 'sba_sequenced_dropdowns', 'The plugin used for displaying attributes on the product information page.', 50, 30, now(), NULL, 'zen_cfg_select_option(array(\'single_radioset\', \'single_dropdown\',\'multiple_dropdowns\',\'sequenced_dropdowns\',\'sba_sequenced_dropdowns\'),'),
('Use ZC default HTML Attribute Tags', 'SBA_ZC_DEFAULT', 'true', 'Controls whether to use ZC HTML tags around attributes or to use the Dynamic Dropdown Version of the tags to support modifications made by others over the years but also compatibility with other ZC plugins.<br /><br />Options:<br />true (Default)<br />false.', 50, 40, now(), NULL, 'zen_cfg_select_option(array(\'true\', \'false\'),'),
('Show Out of Stock Attributes', 'PRODINFO_ATTRIBUTE_SHOW_OUT_OF_STOCK', 'True', 'Controls the display of out of stock attributes.', 50, 50, now(), NULL, 'zen_cfg_select_option(array(\'True\', \'False\'),'),
('Mark Out of Stock Attributes', 'PRODINFO_ATTRIBUTE_MARK_OUT_OF_STOCK', 'Right', 'Controls how out of stock attributes are marked as out of stock.', 50, 60, now(), NULL, 'zen_cfg_select_option(array(\'None\', \'Right\', \'Left\'),'),
('Display Out of Stock Message Line', 'PRODINFO_ATTRIBUTE_OUT_OF_STOCK_MSGLINE', 'True', 'Controls the display of a message line indicating an out of stock attributes is selected.', 50, 70, now(), NULL, 'zen_cfg_select_option(array(\'True\', \'False\'),'),
('Prevent Adding Out of Stock to Cart', 'PRODINFO_ATTRIBUTE_NO_ADD_OUT_OF_STOCK', 'True', 'Prevents adding an out of stock attribute combination to the cart.', 50, 80, now(), NULL, 'zen_cfg_select_option(array(\'True\', \'False\'),'),
('SBA Number of Records to Displayed', 'STOCK_SET_SBA_NUMRECORDS', '25', 
'Number of records to show on page:',
50, 60, now(), NULL, NULL),
('Display Javascript Popup for Out-of-Stock Selection', 'PRODINFO_ATTRIBUTE_POPUP_OUT_OF_STOCK', 'True', 'Controls whether to display or not the message for when a products attribute is out-of-stock.', 50, 90, now(), NULL, 'zen_cfg_select_option(array(\'True\', \'False\'),')
; ==> (as called by) /homepages/20/d503465712/htdocs/shops/ecs/backoffice/stock_by_attr_install.php on line 682 <== in /homepages/20/d503465712/htdocs/shops/ecs/includes/classes/db/mysql/query_factory.php on line 155

**mc12345678** · 16 Sep 2015, 05:42 PM

Welp, looks like when adding new options to the Dynamiic Dropdown menu in function insertDynDropdownsConfigurationMenu, equivalent remove code was not added to the install file in the function removeDynDropdownsConfiguration.

As to the other issue somewhat depends on how you are tryng to upload it. If in phpmyadmin, need to first select the database to which it is to apply then upload/import.

**adb34** · 16 Sep 2015, 07:44 PM

Originally Posted by mc12345678

Welp, looks like when adding new options to the Dynamiic Dropdown menu in function insertDynDropdownsConfigurationMenu, equivalent remove code was not added to the install file in the function removeDynDropdownsConfiguration.

As to the other issue somewhat depends on how you are tryng to upload it. If in phpmyadmin, need to first select the database to which it is to apply then upload/import.

Does this mean that the data is still in the DB? Also if this is the case or not, why when I click on catalog -> Stock By Attribute (SBA), at the bottom of the page after Sync All Quantities I have the message WARNING: An Error occurred, please refresh the page and try again.

The debug files are;

[16-Sep-2015 19:41:05 Europe/London] #1 trigger_error() called at [/homepages/20/d503465712/htdocs/shops/ecs/includes/classes/db/mysql/query_factory.php:155]
#2 queryFactory->show_error() called at [/homepages/20/d503465712/htdocs/shops/ecs/includes/classes/db/mysql/query_factory.php:132]
#3 queryFactory->set_error() called at [/homepages/20/d503465712/htdocs/shops/ecs/includes/classes/db/mysql/query_factory.php:262]
#4 queryFactory->Execute() called at [/homepages/20/d503465712/htdocs/shops/ecs/admin/includes/classes/products_with_attributes_stock.php:284]
#5 products_with_attributes_stock->displayFilteredRows() called at [/homepages/20/d503465712/htdocs/shops/ecs/admin/products_with_attributes_stock.php:730]

[16-Sep-2015 19:41:05 Europe/London] PHP Fatal error: 1146:Table 'db588038315.ecs_products_with_attributes_stock' doesn't exist :: select * from ecs_products_with_attributes_stock where products_id="529"
order by sort ASC; ==> (as called by) /homepages/20/d503465712/htdocs/shops/ecs/admin/includes/classes/products_with_attributes_stock.php on line 284 <== in /homepages/20/d503465712/htdocs/shops/ecs/includes/classes/db/mysql/query_factory.php on line 155

[16-Sep-2015 19:41:02 Europe/London] #1 trigger_error() called at [/homepages/20/d503465712/htdocs/shops/ecs/includes/classes/db/mysql/query_factory.php:155]
#2 queryFactory->show_error() called at [/homepages/20/d503465712/htdocs/shops/ecs/includes/classes/db/mysql/query_factory.php:132]
#3 queryFactory->set_error() called at [/homepages/20/d503465712/htdocs/shops/ecs/includes/classes/db/mysql/query_factory.php:262]
#4 queryFactory->Execute() called at [/homepages/20/d503465712/htdocs/shops/ecs/admin/includes/classes/products_with_attributes_stock.php:96]
#5 products_with_attributes_stock->update_all_parent_products_stock() called at [/homepages/20/d503465712/htdocs/shops/ecs/admin/products_with_attributes_stock.php:425]

[16-Sep-2015 19:41:02 Europe/London] PHP Fatal error: 1146:Table 'db588038315.ecs_products_with_attributes_stock' doesn't exist :: select sum(quantity) as quantity, products_id from ecs_products_with_attributes_stock where products_id = 529 ==> (as called by) /homepages/20/d503465712/htdocs/shops/ecs/admin/includes/classes/products_with_attributes_stock.php on line 96 <== in /homepages/20/d503465712/htdocs/shops/ecs/includes/classes/db/mysql/query_factory.php on line 155

[16-Sep-2015 19:40:58 Europe/London] #1 trigger_error() called at [/homepages/20/d503465712/htdocs/shops/ecs/includes/classes/db/mysql/query_factory.php:155]
#2 queryFactory->show_error() called at [/homepages/20/d503465712/htdocs/shops/ecs/includes/classes/db/mysql/query_factory.php:132]
#3 queryFactory->set_error() called at [/homepages/20/d503465712/htdocs/shops/ecs/includes/classes/db/mysql/query_factory.php:262]
#4 queryFactory->Execute() called at [/homepages/20/d503465712/htdocs/shops/ecs/admin/includes/classes/products_with_attributes_stock.php:284]
#5 products_with_attributes_stock->displayFilteredRows() called at [/homepages/20/d503465712/htdocs/shops/ecs/admin/products_with_attributes_stock.php:730]

[16-Sep-2015 19:40:58 Europe/London] PHP Fatal error: 1146:Table 'db588038315.ecs_products_with_attributes_stock' doesn't exist :: select * from ecs_products_with_attributes_stock where products_id="529"
order by sort ASC; ==> (as called by) /homepages/20/d503465712/htdocs/shops/ecs/admin/includes/classes/products_with_attributes_stock.php on line 284 <== in /homepages/20/d503465712/htdocs/shops/ecs/includes/classes/db/mysql/query_factory.php on line 155

**mc12345678** · 16 Sep 2015, 08:23 PM

No, what it means is that when the remove was performed, the code did everything it was programmed to do; however, there were three keys that had been added that were not incorporated into the remove process. Therefore, the keys remain in the database. Further, the install has not been written to allow such failures, but instead to provide an error like what you provided. As a result of the error, program execution of the installation stopped dead in its tracks and therefore, the table has not been recreated which is why this latest error is seen. So… Below is the code to incorporate into the removeDynDropdownsConfiguration function inside of admin/stock_by_attr_install.php (in SBA version 1.5.4 this is around line 113) I would suggest adding the code below (three sets of lines) after this line:

Code:

array_push($resultMmessage, '&bull; Deleted STOCK_SET_SBA_NUMRECORDS ' . $msg);

The following three sets of lines need to be added into the function removeDynDropdownsConfiguration()

Code:

 $sql = "DELETE IGNORE FROM `".TABLE_CONFIGURATION."` WHERE `configuration_key` = 'PRODINFO_ATTRIBUTE_DYNAMIC_STATUS'";
 $db->Execute($sql);
 if($db->error){ 
  $msg = ' Error Message: ' . $db->error;
 }
 array_push($resultMmessage, '&bull; Deleted PRODINFO_ATTRIBUTE_DYNAMIC_STATUS  ' . $msg);

 $sql = "DELETE IGNORE FROM `".TABLE_CONFIGURATION."` WHERE `configuration_key` = 'SBA_ZC_DEFAULT'";
 $db->Execute($sql);
 if($db->error){ 
  $msg = ' Error Message: ' . $db->error;
 }
 array_push($resultMmessage, '&bull; Deleted SBA_ZC_DEFAULT ' . $msg);

 $sql = "DELETE IGNORE FROM `".TABLE_CONFIGURATION."` WHERE `configuration_key` = 'PRODINFO_ATTRIBUTE_POPUP_OUT_OF_STOCK'";
 $db->Execute($sql);
 if($db->error){ 
  $msg = ' Error Message: ' . $db->error;
 }
 array_push($resultMmessage, '&bull; Deleted PRODINFO_ATTRIBUTE_POPUP_OUT_OF_STOCK ' . $msg);

After adding this code, if you uninstall again and then reinstall, it should reinstall successfully… Otherwise, one could place the following code in the install SQL patches window found in the admin panel under tools:

Code:

DELETE IGNORE FROM `configuration` WHERE `configuration_key` = 'PRODINFO_ATTRIBUTE_DYNAMIC_STATUS';
DELETE IGNORE FROM `configuration` WHERE `configuration_key` = 'SBA_ZC_DEFAULT';
DELETE IGNORE FROM `configuration` WHERE `configuration_key` = 'PRODINFO_ATTRIBUTE_POPUP_OUT_OF_STOCK';

And then be able to install again; however, without the above additional code then a subsequent removal will result in the same condition again until the code can be updated on github.

**mc12345678** · 16 Sep 2015, 08:46 PM

FYI, the repository for SBA 1.5.4 (compatible with ZC 1.5.3 and ZC 1.5.4) has been updated with the above code. It will be a bit for SBA 1.5.3 to be updated to suit as there are possibly other improvements to incorporate and it is getting to the point where it seems that the two versions can to some extent be merged into a single package as the overlap with core/templated ZC files is relatively low and from there it is a lot easier to keep the software up-to-date for all applicable versions...

Thread: Stock by Attribute v4.0 addon for v1.3.5-1.3.9

Thread Tools

Search Thread

Display

Re: Stock by Attribute v4.0 addon for v1.3.5-1.3.9

Re: Stock by Attribute v4.0 addon for v1.3.5-1.3.9

Re: Stock by Attribute v4.0 addon for v1.3.5-1.3.9

Re: Stock by Attribute v4.0 addon for v1.3.5-1.3.9

Re: Stock by Attribute v4.0 addon for v1.3.5-1.3.9

Re: Stock by Attribute v4.0 addon for v1.3.5-1.3.9

Re: Stock by Attribute v4.0 addon for v1.3.5-1.3.9

Re: Stock by Attribute v4.0 addon for v1.3.5-1.3.9

Re: Stock by Attribute v4.0 addon for v1.3.5-1.3.9

Re: Stock by Attribute v4.0 addon for v1.3.5-1.3.9

Similar Threads

Problems with addon: Dynamic Drop Downs for Stock By Attribute

MySQL Problem with Product with Attribute Stock addon

Hide Zero Quantity Attributes with attribute-stock addon

500 Internal Server Error when checking out after installing Attribute Stock addon

Price Products in the grid by 'Stock by Attribute' addon?

Bookmarks

Bookmarks

Posting Permissions