If this is your first visit, check our documentation. To post, register or login.
Forums / Bug Reports / Advanced Search Error: 1054 Unknown column 'p.products_tax_class_id'

Advanced Search Error: 1054 Unknown column 'p.products_tax_class_id'

Locked

Started by ben on 17 Sep 2006, 4:22 AM · Views: 3,915

Results 1 to 10 of 10
This thread is locked. New replies are disabled.
17 Sep 2006, 4:22 AM
#1
ben avatar

ben

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.

17 Sep 2006, 4:58 AM
#2
drbyte avatar

drbyte

Sensei

Join Date:
Jan 2004
Posts:
63,512
Plugin Contributions:
176

Re: Advanced Search Error: 1054 Unknown column 'p.products_tax_class_id'

  1. what mods do you have installed?
  2. what exact steps can be taken to reproduce this problem? (ideally, starting from a fresh install, perhaps with demo products)

.
Zen Cart - putting the dream of business ownership within reach of anyone!
Donate to: DrByte directly or to the Zen Cart team as a whole

Remember: Any code suggestions you see here are merely suggestions. You assume full responsibility for your use of any such suggestions, including any impact ANY alterations you make to your site may have on your PCI compliance.
Furthermore, any advice you see here about PCI matters is merely an opinion, and should not be relied upon as "official". Official PCI information should be obtained from the PCI Security Council directly or from one of their authorized Assessors.

17 Sep 2006, 6:27 AM
#3
ben avatar

ben

New Zenner

Join Date:
Aug 2006
Location:
Melbourne, Australia
Posts:
30
Plugin Contributions:
0

Re: Advanced Search Error: 1054 Unknown column 'p.products_tax_class_id'

Installed Modules

auspost v2.0.3a
cross sell v1.3.0
image-handler 2.0 (slightly modified to suit ZC v1.3.5)
improved attributes controller 1.0 (slightly modified to suit ZC v1.3.5)
login box 1.3
zencart world zones v1.4

I will get back to you sortly regarding steps to reproduce the problem

23 Sep 2006, 11:04 AM
#4
ben avatar

ben

New Zenner

Join Date:
Aug 2006
Location:
Melbourne, Australia
Posts:
30
Plugin Contributions:
0

Re: Advanced Search Error: 1054 Unknown column 'p.products_tax_class_id'

I am unable to reproduce this problem from a fresh install.

I have installed all the modules listed in the previous post on a fresh install (v1.3.5) and the advanced search works perfectly.

I can only reproduce this problem from a fresh install by importing my old database. This database was created in version 1.3.0.1. It seems my database has a problem.

How do I go about finding the source of this problem? I don't want to start again with a fresh database because I have spent countless hours adding products to it.

23 Sep 2006, 1:05 PM
#5
a_berezin avatar

a_berezin

Deceased

Join Date:
Aug 2004
Location:
Saint Petersburg, Russia
Posts:
1,782
Plugin Contributions:
5

Re: Advanced Search Error: 1054 Unknown column 'p.products_tax_class_id'

Thats not Your problem, Ben. It's zen error. This mistake is shown at given pfrom or pto and DISPLAY_PRICE_WITH_TAX == 'true'.

/catalog/includes/modules/pages/advanced_search_result/header_php.php
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");

NOTE: We are sorry that Andrew is no longer with us.
We are grateful for all his contributions to the Zen Cart community.

24 Sep 2006, 3:55 AM
#6
ben avatar

ben

New Zenner

Join Date:
Aug 2006
Location:
Melbourne, Australia
Posts:
30
Plugin Contributions:
0

Re: Advanced Search Error: 1054 Unknown column 'p.products_tax_class_id'

Hi a_berezin, thank you for your reply. It has been most helpful.

I am now able to reproduce the problem from a fresh install.

Step1: Install ZC v1.3.5 with demo products
Step2: In Admin > Configuration > My Store set "Display Prices with Tax" to "true"
Step3: Submit an advanced search
For Example
Keywords: Die Hard
Price From: 1
Price To: 100

The following error is returned:

1054 Unknown column 'p.products_tax_class_id' in 'on clause' in:

Your solution, however, did not solve this problem. The solution given at the top of this thread works for me.

24 Sep 2006, 11:32 AM
#7
a_berezin avatar

a_berezin

Deceased

Join Date:
Aug 2004
Location:
Saint Petersburg, Russia
Posts:
1,782
Plugin Contributions:
5

Re: Advanced Search Error: 1054 Unknown column 'p.products_tax_class_id'

Ben!
Yes! You are right!

$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))";

NOTE: We are sorry that Andrew is no longer with us.
We are grateful for all his contributions to the Zen Cart community.

24 Sep 2006, 7:13 PM
#8
wilt avatar

wilt

Oji-san

Join Date:
Jun 2003
Location:
Newcastle UK
Posts:
1,949
Plugin Contributions:
3

Re: Advanced Search Error: 1054 Unknown column 'p.products_tax_class_id'

Hi,

I guess you are using Mysql 5, as the problem is caused in the way it chooses operator precedence, in order to be ANSI SQL compatible,

Your fix works, by forcing mysql (using the brackets) to include the initial from table as equal to the joins in terms of precedence (i'm probably oversimplifying here)

I would suggest that

$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)";  

is a better solution, as i've seen in the past that when brackets are used to cover the joins as well it can break mysql 3.23 compatibility

24 Sep 2006, 11:05 PM
#9
theoracle avatar

theoracle

Suspended

Join Date:
Aug 2004
Posts:
3,180
Plugin Contributions:
1

Re: Advanced Search Error: 1054 Unknown column 'p.products_tax_class_id'

I remember discussing this from one of the archived topics on the old forum. The parenthese are, indeed, important regarding mySQL compatibilities. Otherwise, you could end up with strange mySQL server error messages. :wink2:

Here's an example:

SELECT y.field_id, y1.field_id
FROM your_table y, your_table1 y1

should be changed to:

SELECT y.field_id, y1.field_id
(FROM your_table y, your_table1 y1)

:wink2:

25 Sep 2006, 6:55 AM
#10
a_berezin avatar

a_berezin

Deceased

Join Date:
Aug 2004
Location:
Saint Petersburg, Russia
Posts:
1,782
Plugin Contributions:
5

Re: Advanced Search Error: 1054 Unknown column 'p.products_tax_class_id'

IMHO

$from_str = "FROM " . 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)," .
             TABLE_PRODUCTS . " p
               LEFT JOIN " . TABLE_MANUFACTURERS . " m
                 USING(manufacturers_id)";

NOTE: We are sorry that Andrew is no longer with us.
We are grateful for all his contributions to the Zen Cart community.