Jump to content
  • Checkout
  • Login
  • Get in touch

osCommerce

The e-commerce.

Advanced Search Error


timgaunt

Recommended Posts

Hi,

 

Quick question in regards the advanced search, I've found if you submit only a price range it doesn't select anything and instead throws an error on the advanced_search_result.php page:

 

1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'products_description pd, categories c,

 

select count(distinct p.products_id) as total from ( products p left join specials_retail_prices s using(products_id) ) left join manufacturers m on p.manufacturers_id = m.manufacturers_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 = '222') and (gz.zone_id is null or gz.zone_id = '0' or gz.zone_id = '214') 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) ) >= 2) 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) ) <= 23123) and (p.products_group_access like "%,G,%" or p.products_group_access like "G,%" or p.products_group_access like "%,G" or p.products_group_access="G")

 

TEP_DB_ERRORR

 

Any ideas?

 

TIA

 

Tim

Link to comment
Share on other sites

I've found how to sort it, if you get this error too (by only searching on the price) add a comma (,) to the end of the statement:

 

$from_str .= " left join " . TABLE_TAX_RATES . " tr on p.products_tax_class_id = tr.tax_class_id left join " . TABLE_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 = '" . (int)$customer_country_id . "') and (gz.zone_id is null or gz.zone_id = '0' or gz.zone_id = '" . (int)$customer_zone_id . "') ";

 

Making it:

$from_str .= " left join " . TABLE_TAX_RATES . " tr on p.products_tax_class_id = tr.tax_class_id left join " . TABLE_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 = '" . (int)$customer_country_id . "') and (gz.zone_id is null or gz.zone_id = '0' or gz.zone_id = '" . (int)$customer_zone_id . "'), ";

 

HTH

 

Tim

Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...