This is a discussion on Advanced search on price gives SQL error within the osCMax v2 Installation issues forums, part of the osCMax v2.0 Forums category; While perfroming advanced search with price query I get the following error: Code: 1064 - You have an error IN ...
| |||||||
| Register | FAQ | Members List | Calendar | Mark Forums Read |
#1
| ||||
| ||||
| While perfroming advanced search with price query I get the following error: 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 Code: 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)";
}
Code: $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 Code: 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 |
| Sponsored Links | ||
| ||
| |
| Thread Tools | |
| |
| ||||
| Posted By | For | Type | Date | |
| SystemsManager Support > SELECT query error | This thread | Refback | 01-16-2008 11:52 PM | |
| osCommerce and osCMall Support -- SystemsManager Technologies | This thread | Refback | 01-04-2008 12:34 PM | |
| osCommerce and osCMall Support -- SystemsManager Technologies | This thread | Refback | 10-02-2007 11:49 AM | |
| SystemsManager Technologies Support Site for osCommerce and osCMall Systems | This thread | Refback | 04-12-2007 06:33 AM | |
| ||||
| Thread | Thread Starter | Forum | Replies | Last Post |
| How to add advanced search to frontpage | wshalo | osCommerce 2.2 Modification Help | 0 | 08-23-2006 09:35 AM |
| How to disable advanced search? | Bennyfax | osCMax v2 Customization/Mods | 0 | 05-15-2006 11:20 AM |
| Full text search in advanced search | Anonymous | osCMax v1.7 Discussion | 0 | 04-15-2004 02:00 PM |
| Advanced Search Error | jgkiefer | osCMax v1.7 Discussion | 4 | 09-16-2003 07:04 PM |