While perfroming advanced search with price query I get the following error:
The problem query will change based on parameters.Code: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 )
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
Line 162Code: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)"; }
AfterCode:$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) . "%'";
line 90-114
LIne 162Code: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)"; }
Code:$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




LinkBack URL
About LinkBacks






Bookmarks