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

Hi everyone sorry for my english, I speak Italian I use google traslator, I ask you for help I updated installed zen cart 1.5.5e Italian version, I inserted the SBA module Version 1.5.4, I loaded all the files correctly and then installed from the path "stock_by_attr_install.php" after I ran the first "full upgrade install" script and immediately after also "non - stock DB Table Install" no error appears, at this point even if I try to create new articles with attributes if I go to the aka page SBA I see the search boxes but no list of articles appears, I get this message: WARNING: An Error occurred, please refresh the page and try again.
I had tested this form before making the transfer by purchasing a web space and it seemed to work, when I actually had to do the transfer it no longer works ... who can help me is 3 days that I try to get out of it but I can't, I'm not an expert, maybe it takes special permissions to folders?
I copy the server data if it can be useful to understand
Host Server: webxc300s02.ad.aruba.it (89.46.109.82)

OS server: Linux 3.10.0-862.2.3.el7.x86_64

Server Date: 05/17/2020 22:07:05

Server Up Time: 22:07:05 up 27 days, 8:37, 0 users, load average: 36.46, 34.61, 40.22

HTTP Server: Apache

PHP Version: 7.0.33 (Zend: 3.0.0)

PHP File Uploads: On

Upload Max Size: 50M

PHP Memory Limit: 256M

POST Max Size: 30M

Database: MySQL 5.7.29-32-log

Host Database: 89.46.111.190 (89.46.111.190)

Database Date: 05/17/2020 22:07:04

Database Data Size: 17,650 kB

Database Index Size: 4,331 kB

MySQL Slow Query Log Status: On

MySQL Slow Query Log File: / var / lib / mysql / mysql1 / slow_queries

MySQL Mode: ONLY_FULL_GROUP_BY, STRICT_TRANS_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, NO_AUTO_CREATE_USER, NO_ENGINE_SUBSTITUTION

There should be something in the logs folder.

You also need to check the settings in the two configure.php files. I think something is not right in one of them.

Also when say that downloaded version 1.5.4, could you please identify the Web address used to download the software?

Thanks so much for the quick reply.
the installed version is downloaded from https://github.com/mc12345678/Stock_By_Attributes_Combined
I used the folders for version 1_5_5 and then the admin and includes files.

I have many logs so I did a search for an item in catalog -> aka SBA in order to generate the error I copy below:

[18-May-2020 18:06:03 Europe/Rome] Request URI: /TEST2/password/products_with_attributes_stock.php?search=&pwas-search-button=Search&search_order_by=products_model, IP address: 151.48.100.0
#1 trigger_error() called at [/web/htdocs/www.miosito.com/home/TEST2/includes/classes/db/mysql/query_factory.php:167]
#2 queryFactory->show_error() called at [/web/htdocs/www.miosito.com/home/TEST2/includes/classes/db/mysql/query_factory.php:139]
#3 queryFactory->set_error() called at [/web/htdocs/www.miosito.com/home/TEST2/includes/classes/db/mysql/query_factory.php:266]
#4 queryFactory->Execute() called at [/web/htdocs/www.miosito.com/home/TEST2/password/products_with_attributes_stock.php:1012]

[18-May-2020 18:06:03 Europe/Rome] PHP Fatal error: 3065:Expression #1 of ORDER BY clause is not in SELECT list, references column 'Sql1427045_2.d.products_name' which is not in SELECT list; this is incompatible with DISTINCT :: select distinct pa.products_id FROM products_attributes pa, products_description d, products p WHERE d.language_id='2' and pa.products_id = d.products_id and pa.products_id = p.products_id AND ( p.products_id = '' OR d.products_name LIKE '%%' OR p.products_model LIKE '%' ) order by d.products_name ==> (as called by) /web/htdocs/www.miosito.com/home/TEST2/password/products_with_attributes_stock.php on line 1012 <== in /web/htdocs/www.miosito.com/home/TEST2/includes/classes/db/mysql/query_factory.php on line 167

While I haven't tested this suggested change yet and I recognize that the above query was last incorporated when the database was not set to provide STRICT type operations, do you think you could try this revision?

In the current fileset (unchanged for a while now), the query that is assembled to give that result begins on line 1123 which is 10 lines before the query is executed. I would say then that if you were to go to line 1002 of your current admin/products_with_attributes_stock.php file and change the code from:

$query_products = "SELECT distinct pa.products_id 

To:

$query_products = "SELECT distinct pa.products_id, d.products_name 

By adding the comma and the additional field name, that perhaps the issue in this query would go away?

Note also, that as of this "second" the github fileset has not yet been updated to reflect that change either. I'll take a look as soon I can especially at any new queries to ensure they are "strict"-like compatible.

Could you

after you suggested modification I have this log:

[18-May-2020 20:07:47 Europe/Rome] Request URI: /TEST2/password/products_with_attributes_stock.php?search_order_by=products_model, IP address: 151.48.100.0
#1 trigger_error() called at [/web/htdocs/www.miosito.com/home/TEST2/includes/classes/db/mysql/query_factory.php:167]
#2 queryFactory->show_error() called at [/web/htdocs/www.miosito.com/home/TEST2/includes/classes/db/mysql/query_factory.php:139]
#3 queryFactory->set_error() called at [/web/htdocs/www.miosito.com/home/TEST2/includes/classes/db/mysql/query_factory.php:266]
#4 queryFactory->Execute() called at [/web/htdocs/www.miosito.com/home/TEST2/password/includes/classes/products_with_attributes_stock.php:399]
#5 products_with_attributes_stock->displayFilteredRows() called at [/web/htdocs/www.miosito.com/home/TEST2/password/products_with_attributes_stock.php:1278]

[18-May-2020 20:07:47 Europe/Rome] PHP Fatal error: 3065:Expression #1 of ORDER BY clause is not in SELECT list, references column 'Sql1427045_2.po.products_options_sort_order' which is not in SELECT list; this is incompatible with DISTINCT :: SELECT DISTINCT pa.products_attributes_id
FROM products_attributes pa
LEFT JOIN products_options po on (po.products_options_id = pa.options_id)
WHERE pa.products_attributes_id in (1918)
order by LPAD(po.products_options_sort_order,11,"0"), po.products_options_name ==> (as called by) /web/htdocs/www.miosito.com/home/TEST2/password/includes/classes/products_with_attributes_stock.php on line 399 <== in /web/htdocs/www.miosito.com/home/TEST2/includes/classes/db/mysql/query_factory.php on line 167

Instead of addressing each one at a time, I have put together a commit/PR to address this issue that was introduced into the admin variant handler. It can be found here: https://github.com/mc12345678/Stock_By_Attributes_Combined/pull/70/files

The red represents areas "removed" the green are areas added... Also the line numbers are based off of the software there in github and may differ from what you have on your system, but the surrounding code should relatively relate.

Thanks for your time.
i downloaded latest version on https://github.com
I made the changes you sent.
I performed a new 1.5.5e Italian FE installation, I loaded the sample products in the shop, I uploaded the Stock_By_Attributes_Combined-master files, I ran SBA setup link -> full / upgrade DB install dynamic drop down and here I check the first one problem seen in photos Configuration -> Dynamic drop downs I can't find anything

after install from -> SBA setup link -> non-stock DB table install and now it appears

if now I go to Catalog -> aka SBA the list appears but without division of the attributes and quantity

if i try to load quantities in any item i return to error.
.

the only way to remove the error is to remove everything from the db.
help me understand what and where am i wrong i made the steps many times always same problem. copy last log:

[19-May-2020 02:31:15 Europe/Rome] Request URI: /TEST/Password/products_with_attributes_stock.php, IP address: 151.48.100.0
#1 trigger_error() called at [/web/htdocs/www.miosito.com/home/TEST/includes/classes/db/mysql/query_factory.php:167]
#2 queryFactory->show_error() called at [/web/htdocs/www.miosito.com/home/TEST/includes/classes/db/mysql/query_factory.php:139]
#3 queryFactory->set_error() called at [/web/htdocs/www.miosito.com/home/TEST/includes/classes/db/mysql/query_factory.php:266]
#4 queryFactory->Execute() called at [/web/htdocs/www.miosito.com/home/TEST/Password/includes/classes/products_with_attributes_stock.php:454]
#5 products_with_attributes_stock->displayFilteredRows() called at [/web/htdocs/www.miosito.com/home/TEST/Password/products_with_attributes_stock.php:1411]

[19-May-2020 02:31:15 Europe/Rome] PHP Fatal error: 3065:Expression #1 of ORDER BY clause is not in SELECT list, references column 'Sql1427045_5.po.products_options_sort_order' which is not in SELECT list; this is incompatible with DISTINCT :: SELECT DISTINCT pa.products_attributes_id
FROM products_attributes pa
LEFT JOIN products_options po on (po.products_options_id = pa.options_id)
WHERE pa.products_attributes_id in (1037)
ORDER BY LPAD(po.products_options_sort_order,11,"0"), po.products_options_name ==> (as called by) /web/htdocs/www.miosito.com/home/TEST/password/includes/classes/products_with_attributes_stock.php on line 454 <== in /web/htdocs/www.miosito.com/home/TEST/includes/classes/db/mysql/query_factory.php on line 167

Added some more changes to the same link above (meaning, recommend going back over those changes and incorporating them)...

Found another query that used distinct with order by/group by but did not include the field(s) in the select portion of the query and then as part of that found that some of the tables referenced used a different "prefix" or table reference than other references to the same table so I made the table references consistent and needed to add some field "controls".

Hopefully there are no others found, but if so, please report.

Thank you very much for your work, all the problems I had have disappeared thank you thank you, I found that the attributes are duplicated I attach photos. I keep testing if everything is fine, and then I confirm if everything is ok.
Thank you for your support.

snakes7575:

Thank you very much for your work, all the problems I had have disappeared thank you thank you, I found that the attributes are duplicated I attach photos. I keep testing if everything is fine, and then I confirm if everything is ok.
Thank you for your support.
Based on discussion of English being a second language, I assume that the site also has at least two languages? I believe it is because there are two or more languages (and not quite sufficient handling of the additional language(s)) that there are two option names shown for every record.

I am preparing another code change to possibly address this. I would be interested to see the result when a product has two or more option names assigned to it and the site uses two or more languages.

I hope that it would display as:
lang1: Option 1
lang1: Option 2
lang2: Option 1
lang2: Option 2

or in the sequence associated with that language as it is possible in different languages to have a different sort order for the option names. Currently it displays or is expected to display in the language of the current session which is further why I believe that what is being seen is from a site having multiple languages (and how the code is currently written).

Note that what is further seen is that regardless the language, there are only so many of a product and that quantity is not dependent on "language".

The PR to review this possible solution is: https://github.com/mc12345678/Stock_By_Attributes_Combined/pull/74/files

I understand, my shop has 2 languages English and Italian, to speed up I did not carry out the attributes in English if I had I would have realized that it is useful and the correct reference.
although it's okay as you made the double names initially, I modified as you suggested admin / includes / classes / products_with_attributes_stock.php but unfortunately I get the main error I attach photos

snakes7575:

I understand, my shop has 2 languages English and Italian, to speed up I did not carry out the attributes in English if I had I would have realized that it is useful and the correct reference.
although it's okay as you made the double names initially, I modified as you suggested admin / includes / classes / products_with_attributes_stock.php but unfortunately I get the main error I attach photos

What about the log file that is generated when that happens? Please sanitize the admin folder name out of the result. I am curious about what it identifies as my test site (which only has one language installed) is running with that code change and is not having an issue and I would like to be able to merge that change into the master fileset.

I just reloaded deleted the logs and rerun and your file on the first screen from error as a photo already sent, here is the log I hope is correct.

[21-May-2020 23:35:34 Europe/Rome] Request URI: /TEST2/password/products_with_attributes_stock.php?products_filter=279&current_category_id=97, IP address: 151.20.130.237
#1 trigger_error() called at [/web/htdocs/www.miosito.com/home/TEST2/includes/classes/db/mysql/query_factory.php:167]
#2 queryFactory->show_error() called at [/web/htdocs/www.miosito.com/home/TEST2/includes/classes/db/mysql/query_factory.php:139]
#3 queryFactory->set_error() called at [/web/htdocs/www.miosito.com/home/TEST2/includes/classes/db/mysql/query_factory.php:266]
#4 queryFactory->Execute() called at [/web/htdocs/www.miosito.com/home/TEST2/password/includes/classes/products_with_attributes_stock.php:468]
#5 products_with_attributes_stock->displayFilteredRows() called at [/web/htdocs/www.miosito.com/home/TEST2/password/products_with_attributes_stock.php:1417]

[21-May-2020 23:35:34 Europe/Rome] PHP Fatal error: 1055:Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'Sql1427045_2.pa.products_attributes_id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by :: SELECT DISTINCT pa.products_attributes_id, po.products_options_sort_order, po.products_options_name
, po.language_id
FROM products_attributes pa
LEFT JOIN products_options po on (po.products_options_id = pa.options_id)
WHERE pa.products_attributes_id in (1545,1547)
GROUP BY po.language_id
ORDER BY LPAD(po.products_options_sort_order,11,"0"), po.products_options_name ==> (as called by) /web/htdocs/www.miosito.com/home/TEST2/password/includes/classes/products_with_attributes_stock.php on line 468 <== in /web/htdocs/www.miosito.com/home/TEST2/includes/classes/db/mysql/query_factory.php on line 167

as soon as you press on catalog -> SBA aka the error occurs, on some articles pressing on search finds them as in the picture.
others find nothing, if instead I press catalog -> sba aka all the articles come out and if I turn page by page there are all the articles. only on search does not find them

[22-May-2020 00:18:53 Europe/Rome] Request URI: /password/products_with_attributes_stock.php?products_filter=311&current_category_id=72, IP address: 151.20.130.237
#1 trigger_error() called at [/web/htdocs/www.miosito.com/home/includes/classes/db/mysql/query_factory.php:167]
#2 queryFactory->show_error() called at [/web/htdocs/www.miosito.com/home/includes/classes/db/mysql/query_factory.php:139]
#3 queryFactory->set_error() called at [/web/htdocs/www.miosito.com/home/includes/classes/db/mysql/query_factory.php:266]
#4 queryFactory->Execute() called at [/web/htdocs/www.miosito.com/home/password/includes/classes/products_with_attributes_stock.php:468]
#5 products_with_attributes_stock->displayFilteredRows() called at [/web/htdocs/www.miosito.com/home/password/products_with_attributes_stock.php:1417]

[22-May-2020 00:18:53 Europe/Rome] PHP Fatal error: 1055:Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'Sql1427045_1.pa.products_attributes_id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by :: SELECT DISTINCT pa.products_attributes_id, po.products_options_sort_order, po.products_options_name
, po.language_id
FROM products_attributes pa
LEFT JOIN products_options po on (po.products_options_id = pa.options_id)
WHERE pa.products_attributes_id in (1675,1677)
GROUP BY po.language_id
ORDER BY LPAD(po.products_options_sort_order,11,"0"), po.products_options_name ==> (as called by) /web/htdocs/www.miosito.com/home/password/includes/classes/products_with_attributes_stock.php on line 468 <== in /web/htdocs/www.miosito.com/home/includes/classes/db/mysql/query_factory.php on line 167

snakes7575:

as soon as you press on catalog -> SBA aka the error occurs, on some articles pressing on search finds them as in the picture.
others find nothing, if instead I press catalog -> sba aka all the articles come out and if I turn page by page there are all the articles. only on search does not find them

[22-May-2020 00:18:53 Europe/Rome] Request URI: /password/products_with_attributes_stock.php?products_filter=311&current_category_id=72, IP address: 151.20.130.237
#1 trigger_error() called at [/web/htdocs/www.miosito.com/home/includes/classes/db/mysql/query_factory.php:167]
#2 queryFactory->show_error() called at [/web/htdocs/www.miosito.com/home/includes/classes/db/mysql/query_factory.php:139]
#3 queryFactory->set_error() called at [/web/htdocs/www.miosito.com/home/includes/classes/db/mysql/query_factory.php:266]
#4 queryFactory->Execute() called at [/web/htdocs/www.miosito.com/home/password/includes/classes/products_with_attributes_stock.php:468]
#5 products_with_attributes_stock->displayFilteredRows() called at [/web/htdocs/www.miosito.com/home/password/products_with_attributes_stock.php:1417]

[22-May-2020 00:18:53 Europe/Rome] PHP Fatal error: 1055:Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'Sql1427045_1.pa.products_attributes_id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by :: SELECT DISTINCT pa.products_attributes_id, po.products_options_sort_order, po.products_options_name
, po.language_id
FROM products_attributes pa
LEFT JOIN products_options po on (po.products_options_id = pa.options_id)
WHERE pa.products_attributes_id in (1675,1677)
GROUP BY po.language_id
ORDER BY LPAD(po.products_options_sort_order,11,"0"), po.products_options_name ==> (as called by) /web/htdocs/www.miosito.com/home/password/includes/classes/products_with_attributes_stock.php on line 468 <== in /web/htdocs/www.miosito.com/home/includes/classes/db/mysql/query_factory.php on line 167
Thanks for the log(s). I thought that I had the catalog side set to use ONLY_FULL_GROUP_BY, but definitely did not have my admin side set for that. I have updated the query (adding just the content between the quotes ', pa.products_attibutes_id' just after the newly added 'GROUP BY po.language_id' statement. so that line now reads:

GROUP BY po.language_id, pa.products_attributes_id

I have tested this rewritten query with the same settings identifying that it works as rewritten (latest rewrite) and had the same issue as reported above when written as I first suggested.

Again, thank you for identifying those issues.

thank you for your time and patience.
I also added the last modification you made in the previous post, I'm continuing to try and I noticed that if I make the quantity available in the warehouse appear on the selection of the attribute, they are replicated 2 times, I attach photos.

if instead the article has multiple attributes then it works I attach photos

snakes7575:

thank you for your time and patience.
I also added the last modification you made in the previous post, I'm continuing to try and I noticed that if I make the quantity available in the warehouse appear on the selection of the attribute, they are replicated 2 times, I attach photos.

if instead the article has multiple attributes then it works I attach photos

I haven't' been able to duplicate the problem in the first picture yet, but am continuing to try.

The issue there is something related with the process of generating/displaying the Single SBA Select List (Dropdown) Basic type selector as compared to Dynamic Dropdowns. I'll look to see what modifications are on my site that are not in the fileset, although I have been working on what is needed to support ZC 1.5.7 and to not need to edit the attributes.php file which means I may have some modification on my system not yet captured in the distribution, but this also seems like the first I've heard of this issue. Not sure if possibly related to the multiple languages aspect...

It does appear though that the option name type is a standard dropdown instead of the recommended SBA Select List (Simple Dropdown). This can be altered in catalog->Option Names Manager for the associated Option Name.

Thank you for your answer,
thinking I did something wrong in my shop I reinstalled the demo shop with only SBA module, the problem is repeated but other errors happen I send you photos

if I duplicate an article from the catalog, they double the attributes

then if I go to see in catalog -> SBA link an attribute with empty space is generated

if I duplicate an article with multiple attributes, this is what you see in the picture

I hope the information I send you will help you solve the problem. thanks for your help

I also find that if the dropdown set is on 1 or 3 I get an error