$listing_sql is too long (over 1024 char and the query is not correct.

[Claudio_tls]

[Note: remember to include site URL, ZC version, list of plugins, PHP version, etc ... read the Posting Tips shown above for information to include in your post here. And, remove this comment before actually posting!]
Hello, i am quite green in PHP but learning .... as always.
I noted a strange behavior in the query below and scratching my head to understand it.
Then i copied the query in phpMyAdmin and noticed that the ". $and ." section was missing.
Checked the string length and it is 1023 bytes !!!
Then I taken out a couples of columns, reduce the length and ... YES it works fine.
Here are the information:


APACHE 2.4.7
MYSQL 5.6.15
PHP 5.5.8

Zip file: zen-cart-v1.5.6c-07162019

File: includes\index_filters\default_filter.php

/**
 * default_filter.php  for index filters
 *
 * index filter for the default product type
 * show the products of a specified manufacturer
 *
 * @package productTypes
 * @copyright Copyright 2003-2019 Zen Cart Development Team
 * @copyright Portions Copyright 2003 osCommerce
 * @todo Need to add/fine-tune ability to override or insert entry-points on a per-product-type basis
 * @license http://www.zen-cart.com/license/2_0.txt GNU Public License V2.0
 * @version $Id: Scott C Wilson 2019 Jun 23 Modified in v1.5.6c $
 */

//
//
//
line 53

$listing_sql = "SELECT " . $select_column_list . " p.products_id, p.products_type, p.master_categories_id, p.manufacturers_id, p.products_price, p.products_tax_class_id, pd.products_description,
                       IF(s.status = 1, s.specials_new_products_price, NULL) AS specials_new_products_price,
                       IF(s.status = 1, s.specials_new_products_price, p.products_price) AS final_price,
                       p.products_sort_order, p.product_is_call, p.product_is_always_free_shipping, p.products_qty_box_status
                FROM " . TABLE_PRODUCTS . " p
                LEFT JOIN " . TABLE_SPECIALS . " s ON s.products_id = p.products_id
                LEFT JOIN " . TABLE_PRODUCTS_DESCRIPTION . " pd ON pd.products_id = p.products_id
                  AND pd.language_id = " . (int)$_SESSION['languages_id'] . "
                LEFT JOIN " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c ON p2c.products_id = p.products_id
                LEFT JOIN " . TABLE_MANUFACTURERS . " m ON m.manufacturers_id = p.manufacturers_id
                WHERE p.products_status = 1
                " . $and . "
                " . $alpha_sort; 

Otherwise my system works.

Keep me posted please.
Claudio

[lat9]

There are a couple of conditional sections (if/then/else) in the lines preceding that $listing_sql assignment that determine the values for the $and clause as well as the $alpha_sort. That's why the SELECT clause isn't directly pasteable into phpMyAdmin.

[Claudio_tls]

There are a couple of conditional sections (if/then/else) in the lines preceding that $listing_sql assignment that determine the values for the $and clause as well as the $alpha_sort. That's why the SELECT clause isn't directly pasteable into phpMyAdmin.

Thank you for the answer, but i pasted the finished query, and that is why i realized that was not complete.
I still thinking that the query is simply too long, i.e. >1024 char.

[lat9]

Why do you think that the 1024 (1K) character length is an issue? If you copy/paste the following query into your site's phpMyAdmin, what is the current setting?

SHOW VARIABLES LIKE 'max_allowed_packet';

[Claudio_tls]

Why do you think that the 1024 (1K) character length is an issue? If you copy/paste the following query into your site's phpMyAdmin, what is the current setting?

SHOW VARIABLES LIKE 'max_allowed_packet';

hi,
This is it :
SHOW VARIABLES LIKE 'max_allowed_packet'
Profiling [ Inline ] [ Edit ] [ Create PHP Code ] [ Refresh ]



+ Options
Variable_name Value
max_allowed_packet 1048576
====
To answer your question, i have shortened up the query - some fields where not of my interest, and it did worked bang on!

As i said i am quite "green" in PHP / html but i have a long experience in C# and ASM (well this was long ago LOL), so my approach is try and correct. That correction worked but i would like to understand it.

Question: "packet" means data sent or received or includes SQL queries as well?
Thank you for your time.

[DrByte]

SHOW VARIABLES LIKE 'max_allowed_packet'

Variable_name Value
max_allowed_packet 1048576

MySQL until 5.6.5: default was 1048576 (1MB)
From 5.6.6 until 8.0.2 default was 4194304 (4M)
Since 8.0.3 default is 67108864 (64M)
https://dev.mysql.com/doc/refman/8.0...allowed_packet

MariaDB:
16777216 (16M) >= MariaDB 10.2.4
4194304 (4M) >= MariaDB 10.1.7
1048576 (1MB) <= MariaDB 10.1.6
https://mariadb.com/kb/en/library/se...allowed_packet

You can safely increase your default setting to a more modern value.