New Zenner
- Join Date:
- Aug 2006
- Location:
- Melbourne, Australia
- Posts:
- 30
- Plugin Contributions:
- 0
Advanced Search Error: 1054 Unknown column 'p.products_tax_class_id'
The Problem
When submitting an advanced search the following error is returned:
1054 Unknown column 'p.products_tax_class_id' in 'on clause'
in:
[select count(distinct p.products_id) as total FROM products p LEFT JOIN manufacturers m USING(manufacturers_id), products_description pd, categories c, products_to_categories p2c LEFT JOIN meta_tags_products_description mtpd ON mtpd.products_id= p2c.products_id AND mtpd.language_id = 1 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 = 13) AND (gz.zone_id IS null OR gz.zone_id = 0 OR gz.zone_id = 194) 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 ((pd.products_name LIKE '%mower%' OR p.products_model LIKE '%mower%' OR m.manufacturers_name LIKE '%mower%' OR (mtpd.metatags_keywords LIKE '%mower%' AND mtpd.metatags_keywords !='') OR (mtpd.metatags_description LIKE '%mower%' AND mtpd.metatags_description !='') OR pd.products_description LIKE '%mower%') )) AND (p.products_price_sorter * IF(gz.geo_zone_id IS null, 1, 1 + (tr.tax_rate / 100)) >= 1000) AND (p.products_price_sorter * IF(gz.geo_zone_id IS null, 1, 1 + (tr.tax_rate / 100)) <= 2000)]
Version
Zen Cart 1.3.5
Solution
I found a solution here http://glowhost.com/forums/showthread.php?t=473 and adapted it to suit my version of Zen Cart
Edit the following file:
/catalog/includes/modules/pages/advanced_search_result/header_php.php
Beginning on line 215 change...
$from_str = "FROM " . TABLE_PRODUCTS . " p
LEFT JOIN " . TABLE_MANUFACTURERS . " m
USING(manufacturers_id), " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_CATEGORIES . " c, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c
LEFT JOIN " . TABLE_META_TAGS_PRODUCTS_DESCRIPTION . " mtpd
ON mtpd.products_id= p2c.products_id
AND mtpd.language_id = :languagesID";
to...
$from_str = "FROM (" . TABLE_PRODUCTS . " p
LEFT JOIN " . TABLE_MANUFACTURERS . " m
USING(manufacturers_id), " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_CATEGORIES . " c, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c
LEFT JOIN " . TABLE_META_TAGS_PRODUCTS_DESCRIPTION . " mtpd
ON mtpd.products_id= p2c.products_id
AND mtpd.language_id = :languagesID**)**";
I don't understand why this works or if it's the preferred solution but it seems to do the trick. Maybe somebody can tell me if I have done the right thing.