Advanced search on price gives SQL error

[kopoba11]

While perfroming advanced search with price query I get the following error:

1064 - You have an error IN your SQL syntax near 'left join specials_retail_prices s on p.products_id = s.products_id left join ta'at line1 SELECT count(  DISTINCT p.products_id )  AS total
FROM products p
LEFT  JOIN manufacturers m
USING ( manufacturers_id ) , 
LEFT  JOIN specials_retail_prices s ON p.products_id = s.products_id
LEFT  JOIN tax_rates tr ON p.products_tax_class_id = tr.tax_class_id
LEFT  JOIN zones_to_geo_zones gz ON tr.tax_zone_id = gz.geo_zone_id AND ( gz.zone_country_id IS  NULL  OR gz.zone_country_id =  '0' OR gz.zone_country_id =  '223' ) AND ( gz.zone_id IS  NULL  OR gz.zone_id =  '0' OR gz.zone_id =  '4' ), products_description pd, categories c, products_to_categories p2c
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 ( 
IF ( s.status AND s.customers_group_id =  '0', s.specials_new_products_price, p.products_price ) * 
IF ( gz.geo_zone_id IS  NULL , 1, 1 + ( tr.tax_rate /100  )  ) >=200 ) AND ( 
IF ( s.status AND s.customers_group_id =  '0', s.specials_new_products_price, p.products_price ) * 
IF ( gz.geo_zone_id IS  NULL , 1, 1 + ( tr.tax_rate /100  )  ) <=400 )

The problem query will change based on parameters.

I tested on stock install of OSCMAX 2 same results.
It does not work with manufactures ID or without, this is where the problem is just by looking at the code.

I assume it is a bug, but I will not report until confirmed.

Anyone would like to comment or provide more permanent fix


I made changes below (removed some references to manufacturers) so search using price will work
KEEP IN MIND, This code will work only if you do not have manufacturers (it is a temporary hack, I am looking for better solution).


Make changes in advanced_search_reslut.tpl.php


Before
line 90-114

if ($status_tmp_product_prices_table == true) {
   $select_str = "select distinct " . $select_column_list . " m.manufacturers_id, p.products_id, pd.products_name, tmp_pp.products_price, p.products_tax_class_id, if(tmp_pp.status, tmp_pp.specials_new_products_price, NULL) as specials_new_products_price, IF(tmp_pp.status, tmp_pp.specials_new_products_price, tmp_pp.products_price) as final_price ";
   } elseif ($status_tmp_special_prices_table == true) {
     $select_str = "select distinct " . $select_column_list . " m.manufacturers_id, p.products_id, pd.products_name, p.products_price, p.products_tax_class_id, if(s.status, s.specials_new_products_price, NULL) as specials_new_products_price, if(s.status, s.specials_new_products_price, p.products_price) as final_price ";	
   } else {
     $select_str = "select distinct " . $select_column_list . " m.manufacturers_id, p.products_id, pd.products_name, p.products_price, p.products_tax_class_id, NULL as specials_new_products_price, NULL as final_price ";	
   }
   // next line original select query
   // $select_str = "select distinct " . $select_column_list . " m.manufacturers_id, p.products_id, pd.products_name, p.products_price, p.products_tax_class_id, IF(s.status, s.specials_new_products_price, NULL) as specials_new_products_price, IF(s.status, s.specials_new_products_price, p.products_price) as final_price ";

  if ( (DISPLAY_PRICE_WITH_TAX == 'true') && (tep_not_null($pfrom) || tep_not_null($pto)) ) {
    $select_str .= ", SUM(tr.tax_rate) as tax_rate ";
  }


// LINES CHANGED: MS2 update 501112
// Moved to below: " . TABLE_PRODUCTS_DESCRIPTION . " pd
// and : TABLE_CATEGORIES . " c, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c
// $from_str = "from " . TABLE_PRODUCTS . " p left join " . TABLE_MANUFACTURERS . " m using(manufacturers_id), " . TABLE_PRODUCTS_DESCRIPTION . " pd left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id, " . TABLE_CATEGORIES . " c, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c";
      if ($status_tmp_product_prices_table == true) {
  $from_str = "from " . TABLE_PRODUCTS . " p left join " . TABLE_MANUFACTURERS . " m using(manufacturers_id), left join " . $product_prices_table . " as tmp_pp using(products_id)";
      } elseif ($status_tmp_special_prices_table == true) {
  $from_str = "from " . TABLE_PRODUCTS . " p left join " . TABLE_MANUFACTURERS . " m using(manufacturers_id), left join " . TABLE_SPECIALS_RETAIL_PRICES . " s on p.products_id = s.products_id";
      } else {
  $from_str = "from " . TABLE_PRODUCTS . " p left join " . TABLE_MANUFACTURERS . " m using(manufacturers_id)";
      }

Line 162

          $where_str .= "(pd.products_name like '%" . tep_db_input($keyword) . "%' or p.products_model like '%" . tep_db_input($keyword) . "%' or m.manufacturers_name like '%" . tep_db_input($keyword) . "%'";

After
line 90-114

 if ($status_tmp_product_prices_table == true) {
   $select_str = "select distinct " . $select_column_list . "p.products_id, pd.products_name, tmp_pp.products_price, p.products_tax_class_id, if(tmp_pp.status, tmp_pp.specials_new_products_price, NULL) as specials_new_products_price, IF(tmp_pp.status, tmp_pp.specials_new_products_price, tmp_pp.products_price) as final_price ";
   } elseif ($status_tmp_special_prices_table == true) {
     $select_str = "select distinct " . $select_column_list . "p.products_id, pd.products_name, p.products_price, p.products_tax_class_id, if(s.status, s.specials_new_products_price, NULL) as specials_new_products_price, if(s.status, s.specials_new_products_price, p.products_price) as final_price ";	
   } else {
     $select_str = "select distinct " . $select_column_list . "p.products_id, pd.products_name, p.products_price, p.products_tax_class_id, NULL as specials_new_products_price, NULL as final_price ";	
   }
   // next line original select query
   // $select_str = "select distinct " . $select_column_list . " m.manufacturers_id, p.products_id, pd.products_name, p.products_price, p.products_tax_class_id, IF(s.status, s.specials_new_products_price, NULL) as specials_new_products_price, IF(s.status, s.specials_new_products_price, p.products_price) as final_price ";

  if ( (DISPLAY_PRICE_WITH_TAX == 'true') && (tep_not_null($pfrom) || tep_not_null($pto)) ) {
    $select_str .= ", SUM(tr.tax_rate) as tax_rate ";
  }

// LINES CHANGED: MS2 update 501112
// Moved to below: " . TABLE_PRODUCTS_DESCRIPTION . " pd
// and : TABLE_CATEGORIES . " c, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c
// $from_str = "from " . TABLE_PRODUCTS . " p left join " . TABLE_MANUFACTURERS . " m using(manufacturers_id), " . TABLE_PRODUCTS_DESCRIPTION . " pd left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id, " . TABLE_CATEGORIES . " c, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c";
      if ($status_tmp_product_prices_table == true) {
  $from_str = "from " . TABLE_PRODUCTS . " p left join " . $product_prices_table . " as tmp_pp using(products_id)";
      } elseif ($status_tmp_special_prices_table == true) {
  $from_str = "from " . TABLE_PRODUCTS . " p left join " . TABLE_SPECIALS_RETAIL_PRICES . " s on p.products_id = s.products_id";
      } else {
  $from_str = "from " . TABLE_PRODUCTS . " p left join " . TABLE_MANUFACTURERS . " m using(manufacturers_id)";
      }

LIne 162

          $where_str .= "(pd.products_name like '%" . tep_db_input($keyword) . "%' or p.products_model like '%" . tep_db_input($keyword) . "%'";

If someone has better way to fix, please post it.

Thank you