Product Listing Sorter Drop-Down [Support Thread]

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!

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.

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

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.

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

marton_1:

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

carlwhat:

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;

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.

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.

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.

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 [b].[/b]= " 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.

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 [b].[/b]= " 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.

I really appreciate your help :)

The good news is that after deleting the period then I do not get the error message!

The bad news is the sorter does not actually sort!

The debugger shows for case "sort ascending"
(includes/modules/pages/advanced_search_result/header_php.php line 366) $multi_sort= order by p.products_price_sorter
(includes/modules/pages/advanced_search_result/header_php.php line 375) $order_str= order by p.products_price_sorter

Tedious innit!

Ok, I have revisited this and found I had done mods and never uploaded them...

So, please use this new fileset and report back

https://github.com/torvista/zen-cart_product_listing_sorter

and note that regarding the issue with the extra dot or not, both statements should have the dot so it IS a bug in 1.55, fixed in 1.56.
https://github.com/zencart/zencart/blob/v156/includes/modules/pages/advanced_search_result/header_php.php

I tested this in a ZC155f new installation.

torvista:

Ok, I have revisited this and found I had done mods and never uploaded them...

So, please use this new fileset and report back

https://github.com/torvista/zen-cart_product_listing_sorter

and note that regarding the issue with the extra dot or not, both statements should have the dot so it IS a bug in 1.55, fixed in 1.56.
https://github.com/zencart/zencart/blob/v156/includes/modules/pages/advanced_search_result/header_php.php

I tested this in a ZC155f new installation.

Great, thanks.

No more error messages and the various sort options all work fine.

I just did a quick test by loading the files into my ZC155f test system without doing any of the required merging.
All the folders named classic I renamed as "my-template".

I will now withdraw my claim to be "the idiot"! :)

Many thanks everyone, really, really appreciated :)

No more error messages and the various sort options all work fine.

That's good to know, I could not face any more time staring at the ZC sorting code, it makes your head spin.

If you are sure that ALL indeed works fine, I'll upload this version....

torvista:

That's good to know, I could not face any more time staring at the ZC sorting code, it makes your head spin.

If you are sure that ALL indeed works fine, I'll upload this version....

I tried all the sort options and in each case the sort worked correctly.
I have ZC155f, green classic template and my test system runs PHP 5.6.24

Word of caution, I have not yet merged the the plug in with my own files. Need a clear head in the morning for that.

That PHP code might as well be in Chinese for my understanding :)

Thanks again

Did the merges and it works OK.

I have the SNAF addon for grid rows and columns and \includes\templates\YOUR_TEMPLATE\templates\tpl_index_product_list.php merge was a bit tricky but got there eventually; more luck than judgement :)

I am running ZC155f.
I have a bit of a problem with the sorter drop-down plugin.
I can get the sorter drop-down to work, but once it is installed. The sorter drop-down doesn't let my Dynamic Attribute filter I have installed function.
I believe I have narrowed it down to the default_filter.php file that both plugins use. I don't know how to combine the two sets filter sets in the default_filter.php so that that both functions to not conflict with each other.

Thanks again

Dynamic Attribute filter
You are using this
https://www.zen-cart.com/downloads.php?do=file&id=1361
the last version?

I am too busy to look at it, as the whole sorting thing needs a calmer head than I have at the moment, maybe someone else can have a go.

Yes, that is the Dynamic Attribute filter that is pre-installed in my Marcus - Zen Cart theme I purchased for ZC155f.

Her is the Dynamic Attribute filter code that looks to me that is conflicting with Product Listing Sorter Drop-Down code both are in the default_filter.php file.
Only one or the other default_filter.php will work, not both pieces of codes in the same file. When files are combined the first code worked but not the second.

Both Dynamic Attribute filter and Product Listing Sorter Drop-Down are the latest versions.

Here is the Dynamic Attribute filter code from default_filter.php
############################################################################
// bof dynamic filter 1 of 2
include(DIR_WS_MODULES . zen_get_module_directory(FILENAME_DYNAMIC_FILTER));
// eof dynamic filter 1 of 2

if (!isset($select_column_list)) $select_column_list = "";
// show the products of a specified manufacturer
if (isset($_GET['manufacturers_id']) && $_GET['manufacturers_id'] != '' ) {

// bof dynamic filter 2 of 2
if (isset($_GET['filter_id']) && zen_not_null($_GET['filter_id']) || isset($_GET[DYNAMIC_FILTER_PREFIX . $categoryGroup])) {
// We are asked to show only a specific category
$listing_sql = "SELECT DISTINCT " . $select_column_list . " p.products_id, p.products_type, p.master_categories_id,
p.manufacturers_id, m.manufacturers_name, 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";

  $listing_sql .= " FROM " . TABLE_PRODUCTS . " p" .
 " LEFT JOIN " . TABLE_SPECIALS . " s on p.products_id = s.products_id" .
 " LEFT JOIN " . TABLE_PRODUCTS_DESCRIPTION . " pd on p.products_id = pd.products_id" .
 " LEFT JOIN " . TABLE_MANUFACTURERS . " m on p.manufacturers_id = m.manufacturers_id" .
 " JOIN " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c on p.products_id = p2c.products_id" .
   ($filter_attr == true ? " JOIN " . TABLE_PRODUCTS_ATTRIBUTES . " p2a on p.products_id = p2a.products_id" .
   " JOIN " . TABLE_PRODUCTS_OPTIONS . " po on p2a.options_id = po.products_options_id" .
   " JOIN " . TABLE_PRODUCTS_OPTIONS_VALUES . " pov on p2a.options_values_id = pov.products_options_values_id" .
   (defined('TABLE_PRODUCTS_WITH_ATTRIBUTES_STOCK') ? " JOIN " . TABLE_PRODUCTS_WITH_ATTRIBUTES_STOCK . " p2as on p.products_id = p2as.products_id " : "") : '');

  $listing_sql .= " WHERE p.products_status = 1" .
     " AND m.manufacturers_id = '" . (int)$_GET['manufacturers_id'] . "'" .
     " AND pd.language_id = '" . (int)$_SESSION['languages_id'] . "'" .
     $filter .
     " GROUP BY p.products_id " .
     $having .
     $alpha_sort;
} else {

// We show them all
$listing_sql = "SELECT DISTINCT " . $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";

  $listing_sql .= " FROM " . TABLE_PRODUCTS . " p" .
 " LEFT JOIN " . TABLE_SPECIALS . " s on p.products_id = s.products_id" .
 " LEFT JOIN " . TABLE_PRODUCTS_DESCRIPTION . " pd on p.products_id = pd.products_id" .
 " LEFT JOIN " . TABLE_MANUFACTURERS . " m on p.manufacturers_id = m.manufacturers_id" .
   ($filter_attr == true ? " JOIN " . TABLE_PRODUCTS_ATTRIBUTES . " p2a on p.products_id = p2a.products_id" .
   " JOIN " . TABLE_PRODUCTS_OPTIONS . " po on p2a.options_id = po.products_options_id" .
 " JOIN " . TABLE_PRODUCTS_OPTIONS_VALUES . " pov on p2a.options_values_id = pov.products_options_values_id" .
 (defined('TABLE_PRODUCTS_WITH_ATTRIBUTES_STOCK') ? " JOIN " . TABLE_PRODUCTS_WITH_ATTRIBUTES_STOCK . " p2as on p.products_id = p2as.products_id " : "") : '');

  $listing_sql .= " WHERE p.products_status = 1
   and pd.language_id = '" . (int)$_SESSION['languages_id'] . "'
   and m.manufacturers_id = '" . (int)$_GET['manufacturers_id'] . "'" .
   $filter .
 " GROUP BY p.products_id " .
 $having .
 $alpha_sort;
}

} else {
// show the products in a given category
if (isset($_GET['filter_id']) && zen_not_null($_GET['filter_id']) || isset($_GET[DYNAMIC_FILTER_PREFIX . $manufacturerGroup])) {
// We are asked to show only specific category
$listing_sql = "SELECT DISTINCT " . $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";

  $listing_sql .= " FROM " . TABLE_PRODUCTS . " p" .
 " LEFT JOIN " . TABLE_SPECIALS . " s on p.products_id = s.products_id" .
 " LEFT JOIN " . TABLE_PRODUCTS_DESCRIPTION . " pd on p.products_id = pd.products_id" .
 " LEFT JOIN " . TABLE_MANUFACTURERS . " m on p.manufacturers_id = m.manufacturers_id" .
 " JOIN " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c on p.products_id = p2c.products_id" .
   ($filter_attr == true ? " JOIN " . TABLE_PRODUCTS_ATTRIBUTES . " p2a on p.products_id = p2a.products_id" .
   " JOIN " . TABLE_PRODUCTS_OPTIONS . " po on p2a.options_id = po.products_options_id" .
 " JOIN " . TABLE_PRODUCTS_OPTIONS_VALUES . " pov on p2a.options_values_id = pov.products_options_values_id" .
 (defined('TABLE_PRODUCTS_WITH_ATTRIBUTES_STOCK') ? " JOIN " . TABLE_PRODUCTS_WITH_ATTRIBUTES_STOCK . " p2as on p.products_id = p2as.products_id " : "") : '');

  $listing_sql .= " WHERE p.products_status = 1" .
   " and pd.language_id = '" . (int)$_SESSION['languages_id'] . "'" .
   " and p2c.categories_id = '" . (int)$current_category_id . "'" .
   $filter .
 " GROUP BY p.products_id " .
 $having .
   $alpha_sort;
} else {

// We show them all
$listing_sql = "SELECT DISTINCT " . $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";

  $listing_sql .= " FROM " . TABLE_PRODUCTS . " p" .
 " LEFT JOIN " . TABLE_SPECIALS . " s on p.products_id = s.products_id" .
 " LEFT JOIN " . TABLE_PRODUCTS_DESCRIPTION . " pd on p.products_id = pd.products_id" .
 " JOIN " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c on p.products_id = p2c.products_id" .
   ($filter_attr == true ? " JOIN " . TABLE_PRODUCTS_ATTRIBUTES . " p2a on p.products_id = p2a.products_id" .
   " JOIN " . TABLE_PRODUCTS_OPTIONS . " po on p2a.options_id = po.products_options_id" .
 " JOIN " . TABLE_PRODUCTS_OPTIONS_VALUES . " pov on p2a.options_values_id = pov.products_options_values_id" .
 (defined('TABLE_PRODUCTS_WITH_ATTRIBUTES_STOCK') ? " JOIN " . TABLE_PRODUCTS_WITH_ATTRIBUTES_STOCK . " p2as on p.products_id = p2as.products_id " : "") : '');

  $listing_sql .= " WHERE p.products_status = 1
   and pd.language_id = '" . (int)$_SESSION['languages_id'] . "'
   and p2c.categories_id = '" . (int)$current_category_id . "'" .
   $filter .
 " GROUP BY p.products_id " .
 $having .
   $alpha_sort;
}

}
// eof dynamic filter 2 of 2

Here is the Product Listing Sorter Drop-Down code from default_filter.php
#####################################################################

//MOD Product Listing Sorter
if (isset($_GET['product_listing_sorter_id']) && (int)$_GET['product_listing_sorter_id'] > 0) {
$sorter_list_search = explode(';', '0:reset_placeholder;' . PRODUCT_LISTING_SORTER_LIST);//this constant is the drop-down-list text options

for ($j = 0, $n = sizeof($sorter_list_search); $j < $n; $j++) {
    if ((int)$_GET['product_listing_sorter_id'] == $j) {//equate the id with the drop-down list to decide the sorting clause
        $elements_sorter_list = explode(':', $sorter_list_search[$j]);
        $pattern_multi = str_replace(',', '', $elements_sorter_list[1]);//@TODO steve eats commas
        $product_listing_sorter = " ORDER BY " . $pattern_multi;
        if ($debug) echo $debug_prefix . __LINE__ . ') ' . '$_GET[\'product_listing_sorter_id\']='.$_GET['product_listing_sorter_id'] . ': $product_listing_sorter=' . $product_listing_sorter . '<br />';
        break;
    }
}

} else {//default setting/no selection made/first page load
$product_listing_sorter = '';
if ($debug) echo $debug_prefix . LINE . ') ' . 'no selection, $product_listing_sorter=''<br>';
}
//eof PLS
if (!isset($select_column_list)) $select_column_list = "";
// show the products of a specified manufacturer
if (isset($_GET['manufacturers_id']) && $_GET['manufacturers_id'] != '' ) {
if ($debug) echo $debug_prefix.LINE.'<br />';//steve
if (isset($_GET['filter_id']) && zen_not_null($_GET['filter_id'])) {
if ($debug) echo $debug_prefix.LINE.'<br />';//steve
// We are asked to show only a specific category
$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 p.products_id = s.products_id , " .
TABLE_PRODUCTS_DESCRIPTION . " pd, " .
TABLE_MANUFACTURERS . " m, " .
TABLE_PRODUCTS_TO_CATEGORIES . " p2c
where p.products_status = 1
and p.manufacturers_id = m.manufacturers_id
and m.manufacturers_id = '" . (int)$_GET['manufacturers_id'] . "'
and p.products_id = p2c.products_id
and pd.products_id = p2c.products_id
and pd.language_id = '" . (int)$_SESSION['languages_id'] . "'
and p2c.categories_id = '" . (int)$_GET['filter_id'] . "'" .
$alpha_sort . $product_listing_sorter;
} else {
if ($debug) echo $debug_prefix.LINE.'<br />';//steve
// We show them all
$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 p.products_id = s.products_id, " .
TABLE_PRODUCTS_DESCRIPTION . " pd, " .
TABLE_MANUFACTURERS . " m
where p.products_status = 1
and pd.products_id = p.products_id
and pd.language_id = '" . (int)$_SESSION['languages_id'] . "'
and p.manufacturers_id = m.manufacturers_id
and m.manufacturers_id = '" . (int)$_GET['manufacturers_id'] . "'" .
$alpha_sort . $product_listing_sorter;
}
} else {
if ($debug) echo $debug_prefix.LINE.')<br />';//steve
// show the products in a given category
if (isset($_GET['filter_id']) && zen_not_null($_GET['filter_id'])) {
if ($debug) echo $debug_prefix.LINE.'<br />';//steve
// We are asked to show only specific category
$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 p.products_id = s.products_id, " .
TABLE_PRODUCTS_DESCRIPTION . " pd, " .
TABLE_MANUFACTURERS . " m, " .
TABLE_PRODUCTS_TO_CATEGORIES . " p2c
where p.products_status = 1
and p.manufacturers_id = m.manufacturers_id
and m.manufacturers_id = '" . (int)$_GET['filter_id'] . "'
and p.products_id = p2c.products_id
and pd.products_id = p2c.products_id
and pd.language_id = '" . (int)$_SESSION['languages_id'] . "'
and p2c.categories_id = '" . (int)$current_category_id . "'" .
$alpha_sort . $product_listing_sorter;//steve PLS mod
if ($debug) echo $debug_prefix.LINE.') $listing_sql:<br />' . $listing_sql . '<br /><br />';//stevee
} else {
if ($debug) echo $debug_prefix.LINE.')<br />';//steve
// We show them all
$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_DESCRIPTION . " pd, " .
TABLE_PRODUCTS . " p left join " . TABLE_MANUFACTURERS . " m on p.manufacturers_id = m.manufacturers_id, " .
TABLE_PRODUCTS_TO_CATEGORIES . " p2c left join " . TABLE_SPECIALS . " s on p2c.products_id = s.products_id
where p.products_status = 1
and p.products_id = p2c.products_id
and pd.products_id = p2c.products_id
and pd.language_id = '" . (int)$_SESSION['languages_id'] . "'
and p2c.categories_id = '" . (int)$current_category_id . "'" .
$alpha_sort . $product_listing_sorter;//steve PLS mod
}
// }
}

// set the default sort order setting from the Admin when not defined by customer
if (!isset($_GET['sort']) and PRODUCT_LISTING_DEFAULT_SORT_ORDER != '') {
$_GET['sort'] = PRODUCT_LISTING_DEFAULT_SORT_ORDER;
if ($debug) echo $debug_prefix.LINE.') '.'$_GET['sort']='.$_GET['sort'].'<br />';//steve
}
if ($debug) echo $debug_prefix.LINE.')<br />';//steve
if (isset($column_list) && (!isset($_GET['product_listing_sorter_id']) || $_GET['product_listing_sorter_id']==0)) {//are some columns defined? //MOD Product Listing Sorter
if ($debug) echo $debug_prefix.LINE.')<br />';//steve
if ((!isset($_GET['sort'])) || (isset($_GET['sort']) && !preg_match('/[1-8][ad]/', $_GET['sort'])) || (substr($_GET['sort'], 0, 1) > sizeof($column_list)) ) {
for ($i=0, $n=sizeof($column_list); $i<$n; $i++) {
if ($debug) echo $debug_prefix.LINE.') '.'$i='.$i.'<br />';//steve
if (isset($column_list[$i]) && $column_list[$i] == 'PRODUCT_LIST_NAME') {//get the column with the PRODUCT_LIST_NAME in it and set the $_GET['sort'] to match its position
$_GET['sort'] = $i+1 . 'a';
if ($debug) echo $debug_prefix.LINE.') '.'$_GET['sort']='.$_GET['sort'].'<br />';
$listing_sql .= " order by p.products_sort_order, pd.products_name";
break;//break out of the for loop when PRODUCT_LIST_NAME is found
} else {
if ($debug) echo $debug_prefix.LINE.') '.'$_GET['sort']='.$_GET['sort'].'<br />';
// sort by products_sort_order when PRODUCT_LISTING_DEFAULT_SORT_ORDER is left blank
// for reverse, descending order use:
// $listing_sql .= " order by p.products_sort_order desc, pd.products_name";
$listing_sql .= " order by p.products_sort_order, pd.products_name";
break;
}
}

Current users should check theirs is still sorting correctly...I found mine was not.

Note that modifications to this mod are only posted to the Github repository.