Call for price in product filter

[jagge]

Hi,
been messing around with this now for 2 hours and cant find a solution.
I have product filter in my productlisting to sort by different options. When sorting by price products with Call for price comes at top as cheapest since they dont have any price... How can I make them show a bottom of list instead when sorting by price? In the code example below its case 2 I want to change.

  if (isset($_GET['alpha_filter_id']) && (int)$_GET['alpha_filter_id'] > 0) {
    //$alpha_sort = " and pd.products_name LIKE '" . chr((int)$_GET['alpha_filter_id']) . "%' ";
 $filterid = (int)$_GET['alpha_filter_id'];
 switch($filterid)
 {
   case 1:
    $alpha_sort = " order by pd.products_viewed";
    break;
   case 2:
    $alpha_sort = " order by p.products_price";
    break;
   case 3:
    $alpha_sort = " order by p.products_price DESC";
    break;
   case 4:
    $alpha_sort = " order by pd.products_name";
    break;
   case 5:
    $alpha_sort = " order by m.manufacturers_name";
    break;
   case 6:
    $alpha_sort = " order by p.products_date_added DESC";
    break;
   case 7:
    $alpha_sort = " order by p.products_date_added";
    break;
 
 
 
 
 }
  } else {
    $alpha_sort = '';
  }
  if (!isset($select_column_list)) $select_column_list = "";
   // show the products of a specified manufacturer
  if (isset($_GET['manufacturers_id']) && $_GET['manufacturers_id'] != '' ) {
    if (isset($_GET['filter_id']) && zen_not_null($_GET['filter_id'])) {
// 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, 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
       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;
    } else {
// 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;
    }
  } else {
// show the products in a given category
    if (isset($_GET['filter_id']) && zen_not_null($_GET['filter_id'])) {
// 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;
    } else {
// 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;
    }
  }

[Ajeh]

You could try adding to the ORDER BY:

product_is_call ASC

that should put the Call for Price at the end ...

NOTE: if the Call for Price has a Price it will still be at the end ...

[jagge]

Thanks Ajeh for quick response.
I tried that but then the products which isnt call_for_price is showed in no order above.
They shall still be listed and ordered by price. I just want the call_for_price at bottom also.

Now it looks like this. How should I change this?

 
   case 2:
    $alpha_sort = " order by p.products_price";
    break;

[Ajeh]

Try using:

   case 2:
    $alpha_sort = " order by p.product_is_call ASC, p.products_price";
    break;

NOTE: it is better to use products_price_sorter than products_price as the products_price_sorter is more accurate on the actual lowest price of a Product based on Specials, Sales, etc.

[jagge]

Well arent you the best! WOrked like a charm!

[Ajeh]

You are most welcome ... thanks for the update that this worked for you ...

[leon2lil]

If you would be so kind could you post the code you used to do it please!
Thanks in advance