Product Listing Sorter Drop-Down [Support Thread]

[torvista]

I checked your site, and tried something similar on mine. Mine is ok.
It is two years since I have looked at this code. The whole sorting issue is a word of pain, as I remember.
The code you are using is the 2.1 version from the plugins? I didn't know anyone else had updated it.

[marton_1]

Hi, I have been using this addon for some time and am happy with it.
I have zencart 155f, standard green template and PHP 5.6

When I select a category then the sorter enter field shows up and I can sort fine.

But if I select a set of products using the built in zen cart search option, like, for example, "coat" then the relevant 46 products and the sorter enter field show up fine.
But when I try to sort I get a PHP error

[07-Aug-2018 17:00:38 Europe/Zurich] PHP Fatal error:  1064:You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'order by p.products_sort_order, pd.products_name limit 20' at line 1 :: SELECT DISTINCT p.products_image, p.products_quantity ,  p.products_sort_order, m.manufacturers_id, p.products_id, pd.products_name, p.products_price, p.products_tax_class_id, p.products_price_sorter, p.products_qty_box_status, p.master_categories_id FROM (products p              LEFT JOIN manufacturers m              USING(manufacturers_id), products_description pd, categories c, products_to_categories p2c )              LEFT JOIN meta_tags_products_description mtpd              ON mtpd.products_id= p2c.products_id              AND mtpd.language_id = 1 WHERE (p.products_status = 1                AND p.products_id = pd.products_id                AND pd.language_id = 1                AND p.products_id = p2c.products_id                AND p2c.categories_id = c.categories_id  AND ((pd.products_name LIKE '%coat%'                                          OR p.products_model                                          LIKE '%coat%'                                          OR m.manufacturers_name                                          LIKE '%coat%' OR (mtpd.metatags_keywords                         LIKE '%coat%'                         AND mtpd.metatags_keywords !='') OR (mtpd.metatags_description                         LIKE '%coat%'                         AND mtpd.metatags_description !='') OR pd.products_description                           LIKE '%coat%') )) order by p.products_price_sorter order by p.products_sort_order, pd.products_name limit 20 ==> (as called by) /home/httpd/vhosts/simmar.ch/httpdocs/catalog/includes/modules/responsive_simmar/product_listing.php on line 117 <== in /home/httpd/vhosts/simmar.ch/httpdocs/catalog/includes/classes/db/mysql/query_factory.php on line 171

My website is https://www.simmar.ch/catalog

You may want to select "english language"

If I filter by manufacturer then the sorter works fine!

[marton_1]

I checked your site, and tried something similar on mine. Mine is ok.
It is two years since I have looked at this code. The whole sorting issue is a word of pain, as I remember.
The code you are using is the 2.1 version from the plugins? I didn't know anyone else had updated it.

Many thanks for your lightning fast reply.

If it works OK on your site then probably I am the idiot :)

I will do some more research!

Yes, I am using the 2.1 version from the plugins

[marton_1]

I checked your site, and tried something similar on mine. Mine is ok.
It is two years since I have looked at this code. The whole sorting issue is a word of pain, as I remember.
The code you are using is the 2.1 version from the plugins? I didn't know anyone else had updated it.

I discovered I could set *debug" then I got this error log :)
Now if I had a clue what it meant :)
I am using ZC 155f, green classic template, PHP 5.6.37

[11-Sep-2018 16:13:56 Europe/Berlin] Request URI: /zencart155f/catalog/index.php?main_page=advanced_search_result&search_in_description=1&keyword=mantel&inc_subcat=0&sort=6&product_listing_sorter_id=3, IP address: ::1
#1  trigger_error() called at [C:\xampp\htdocs\zencart155f\catalog\includes\classes\db\mysql\query_factory.php:171]
#2  queryFactory->show_error() called at [C:\xampp\htdocs\zencart155f\catalog\includes\classes\db\mysql\query_factory.php:143]
#3  queryFactory->set_error() called at [C:\xampp\htdocs\zencart155f\catalog\includes\classes\db\mysql\query_factory.php:270]
#4  queryFactory->Execute() called at [C:\xampp\htdocs\zencart155f\catalog\includes\modules\responsive_xxxxxx\product_listing.php:117]
#5  include(C:\xampp\htdocs\zencart155f\catalog\includes\modules\responsive_xxxxxx\product_listing.php) called at [C:\xampp\htdocs\zencart155f\catalog\includes\templates\responsive_xxxxxx\templates\tpl_modules_product_listing.php:12]
#6  require(C:\xampp\htdocs\zencart155f\catalog\includes\templates\responsive_xxxxxx\templates\tpl_modules_product_listing.php) called at [C:\xampp\htdocs\zencart155f\catalog\includes\templates\responsive_xxxxxx\templates\tpl_advanced_search_result_default.php:51]
#7  require(C:\xampp\htdocs\zencart155f\catalog\includes\templates\responsive_xxxxxx\templates\tpl_advanced_search_result_default.php) called at [C:\xampp\htdocs\zencart155f\catalog\includes\templates\responsive_xxxxxx\common\tpl_main_page.php:291]
#8  require(C:\xampp\htdocs\zencart155f\catalog\includes\templates\responsive_xxxxxx\common\tpl_main_page.php) called at [C:\xampp\htdocs\zencart155f\catalog\index.php:97]


[11-Sep-2018 16:13:56 Europe/Berlin] PHP Fatal error:  1064:You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'order by p.products_sort_order, pd.products_name limit 20' at line 1 :: SELECT DISTINCT p.products_image, p.products_quantity ,  m.manufacturers_id, p.products_id, pd.products_name, p.products_price, p.products_tax_class_id, p.products_price_sorter, p.products_qty_box_status, p.master_categories_id FROM (products p              LEFT JOIN manufacturers m              USING(manufacturers_id), products_description pd, categories c, products_to_categories p2c )              LEFT JOIN meta_tags_products_description mtpd              ON mtpd.products_id= p2c.products_id              AND mtpd.language_id = 2 WHERE (p.products_status = 1                AND p.products_id = pd.products_id                AND pd.language_id = 2                AND p.products_id = p2c.products_id                AND p2c.categories_id = c.categories_id  AND ((pd.products_name LIKE '%mantel%'                                          OR p.products_model                                          LIKE '%mantel%'                                          OR m.manufacturers_name                                          LIKE '%mantel%' OR (mtpd.metatags_keywords                         LIKE '%mantel%'                         AND mtpd.metatags_keywords !='') OR (mtpd.metatags_description                         LIKE '%mantel%'                         AND mtpd.metatags_description !='') OR pd.products_description                           LIKE '%mantel%') )) order by p.products_price_sorter order by p.products_sort_order, pd.products_name limit 20 ==> (as called by) C:\xampp\htdocs\zencart155f\catalog\includes\modules\responsive_xxxxxx\product_listing.php on line 117 <== in C:\xampp\htdocs\zencart155f\catalog\includes\classes\db\mysql\query_factory.php on line 171

[carlwhat]

I discovered I could set *debug" then I got this error log :)
Now if I had a clue what it meant :)
I am using ZC 155f, green classic template, PHP 5.6.37

the error is very obvious. as the log says, you have a problem with your sql syntax.

if you look at the sql statement, you can see that there are 2 ORDER BY clauses. that will not work.

how your code/this mod put in 2 ORDER BY clauses, i can not say.... but unlikely it came "out of the box" like that.... although i have not looked inside the box....

SELECT DISTINCT p.products_image, p.products_quantity , m.manufacturers_id, p.products_id,
    pd.products_name, p.products_price, p.products_tax_class_id, p.products_price_sorter,
    p.products_qty_box_status, p.master_categories_id
    FROM (products p LEFT JOIN manufacturers m USING(manufacturers_id), products_description pd,
        categories c, products_to_categories p2c)
    LEFT
    JOIN meta_tags_products_description mtpd ON mtpd.products_id = p2c.products_id AND
        mtpd.language_id = 2
    WHERE (p.products_status = 1 AND p.products_id = pd.products_id AND pd.language_id = 2 AND
        p.products_id = p2c.products_id AND p2c.categories_id = c.categories_id AND
        ((pd.products_name LIKE '%mantel%' OR p.products_model LIKE '%mantel%' OR
        m.manufacturers_name LIKE '%mantel%' OR (mtpd.metatags_keywords LIKE '%mantel%' AND
        mtpd.metatags_keywords != '') OR (mtpd.metatags_description LIKE '%mantel%' AND
            mtpd.metatags_description != '') OR pd.products_description LIKE '%mantel%')))
    ORDER BY p.products_price_sorter
    ORDER BY p.products_sort_order, pd.products_name LIMIT 20

[marton_1]

the error is very obvious. as the log says, you have a problem with your sql syntax.

if you look at the sql statement, you can see that there are 2 ORDER BY clauses. that will not work.

how your code/this mod put in 2 ORDER BY clauses, i can not say.... but unlikely it came "out of the box" like that.... although i have not looked inside the box....

SELECT DISTINCT p.products_image, p.products_quantity , m.manufacturers_id, p.products_id,
    pd.products_name, p.products_price, p.products_tax_class_id, p.products_price_sorter,
    p.products_qty_box_status, p.master_categories_id
    FROM (products p LEFT JOIN manufacturers m USING(manufacturers_id), products_description pd,
        categories c, products_to_categories p2c)
    LEFT
    JOIN meta_tags_products_description mtpd ON mtpd.products_id = p2c.products_id AND
        mtpd.language_id = 2
    WHERE (p.products_status = 1 AND p.products_id = pd.products_id AND pd.language_id = 2 AND
        p.products_id = p2c.products_id AND p2c.categories_id = c.categories_id AND
        ((pd.products_name LIKE '%mantel%' OR p.products_model LIKE '%mantel%' OR
        m.manufacturers_name LIKE '%mantel%' OR (mtpd.metatags_keywords LIKE '%mantel%' AND
        mtpd.metatags_keywords != '') OR (mtpd.metatags_description LIKE '%mantel%' AND
            mtpd.metatags_description != '') OR pd.products_description LIKE '%mantel%')))
    ORDER BY p.products_price_sorter
    ORDER BY p.products_sort_order, pd.products_name LIMIT 20

Great, thanks

The original code in the plugin looks like this, case was 'PRODUCT_LIST_PRICE':

else {
  $sort_col = substr($_GET['sort'], 0 , 1);
  $sort_order = substr($_GET['sort'], -1);
  $order_str = ' order by ';
  switch ($column_list[$sort_col-1]) {
    case 'PRODUCT_LIST_MODEL':
    $order_str .= "p.products_model " . ($sort_order == 'd' ? "desc" : "") . ", pd.products_name";
    break;
    case 'PRODUCT_LIST_NAME':
    $order_str .= "pd.products_name " . ($sort_order == 'd' ? "desc" : "");
    break;
    case 'PRODUCT_LIST_MANUFACTURER':
    $order_str .= "m.manufacturers_name " . ($sort_order == 'd' ? "desc" : "") . ", pd.products_name";
    break;
    case 'PRODUCT_LIST_QUANTITY':
    $order_str .= "p.products_quantity " . ($sort_order == 'd' ? "desc" : "") . ", pd.products_name";
    break;
    case 'PRODUCT_LIST_IMAGE':
    $order_str .= "pd.products_name";
    break;
    case 'PRODUCT_LIST_WEIGHT':
    $order_str .= "p.products_weight " . ($sort_order == 'd' ? "desc" : "") . ", pd.products_name";
    break;
    case 'PRODUCT_LIST_PRICE':
    //        $order_str .= "final_price " . ($sort_order == 'd' ? "desc" : "") . ", pd.products_name";
    $order_str .= "p.products_price_sorter " . ($sort_order == 'd' ? "desc" : "") . ", pd.products_name";
    break;

[carlwhat]

in the read me for this add-on:

\includes\modules\pages\advanced_search_result\header_php.php - CORE file overwrite, merge differences (creates "order by" sql clause)

i would suggest that you possibly made an error in the merge differences.

i would carefully review what you did there. again you have two order by clauses.... it should not be too hard to figure it out. you can also turn debug on, to see when your sql statement goes adrift.

the odds are strong that the problem resides in your merge as opposed to the package.

[marton_1]

in the read me for this add-on:

\includes\modules\pages\advanced_search_result\header_php.php - CORE file overwrite, merge differences (creates "order by" sql clause)

i would suggest that you possibly made an error in the merge differences.

i would carefully review what you did there. again you have two order by clauses.... it should not be too hard to figure it out. you can also turn debug on, to see when your sql statement goes adrift.

the odds are strong that the problem resides in your merge as opposed to the package.

Good suggestion, unfortunately when I use the vanilla original file header_php.php I get the same error message.

I downloaded the file new from the plugin section of the forum and loaded it into my test site without any merging.

[marton_1]

Good suggestion, unfortunately when I use the vanilla original file header_php.php I get the same error message.

I downloaded the file new from the plugin section of the forum and loaded it into my test site without any merging.

I tried downloading the whole plug in and loaded it into my test site without any merging but still get the same error message.

Even though when I run the debug header_php.php is the only one shown as running.

[carlwhat]

ok. i'm going to go with i was wrong.... and you are too marton_1; you are not an idiot as you say! ;)

this looks to be a bug in the code! especially since you loaded it without merging. in:

\includes\modules\pages\advanced_search_result\header_php.php

lines 425 -439:

//die('I SEE ' . $_GET['sort'] . ' - ' . PRODUCT_LISTING_DEFAULT_SORT_ORDER);
if ((!isset($_GET['sort'])) || (!preg_match('/[1-8][ad]/', $_GET['sort'])) || (substr($_GET['sort'], 0 , 1) > sizeof($column_list))) {
  for ($col=0, $n=sizeof($column_list); $col<$n; $col++) {
    if ($column_list[$col] == 'PRODUCT_LIST_NAME') {
      $_GET['sort'] = $col+1 . 'a';
      $order_str = ' order by pd.products_name';
      break;
    } else {
      // sort by products_sort_order when PRODUCT_LISTING_DEFAULT_SORT_ORDER ia left blank
      // for reverse, descending order use:
      //       $listing_sql .= " order by p.products_sort_order desc, pd.products_name";
      $order_str .= " order by p.products_sort_order, pd.products_name";
      break;
    }
  }

specifically, line 436. there is an extra period. try removing the period (in red above) to:

 $order_str = " order by p.products_sort_order, pd.products_name";

while i have not implemented this add-on, that period definitely looks wrong to me, and fits the description of your error.

let us know if that solves it!

best.