1054 Unknown column 'p.products_id' in 'on clause'

[yoster]

1054 Unknown column 'p.products_id' in 'on clause'

This is what i get after i try to add any product to any category

I am using the basic template that come with the shopping cart

Can anyone point me in the right direction?
What code and where do i need to put tem to fix this?

[DrByte]

What version of Zen Cart are you using?
What version of PHP ?
What version of MySQL ?
Have you installed any contributions/mods/customizations, whether related to template or not?


That's a typical problem with MySQL 5 and older versions of Zen Cart or older contributions.

[MARcoDEN]

I'm still using Zen Cart v1.2.7 and I solved this problem by fixing one query in the includes/modules/meta_tags.php:

Find (around line 181):

case (strstr($_GET['main_page'], 'product_') or strstr($_GET['main_page'], 'document_')):
    $sql= "select pd.products_name, p.products_model, p.products_price_sorter, p.products_tax_class_id,
                                      p.metatags_title_status, p.metatags_products_name_status, p.metatags_model_status,
                                      p.products_id, p.metatags_price_status, p.metatags_title_tagline_status,
                                      pd.products_description,
                                      mtpd.metatags_title, mtpd.metatags_keywords, mtpd.metatags_description
                              from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd left join " . TABLE_META_TAGS_PRODUCTS_DESCRIPTION . " mtpd on mtpd.products_id = p.products_id and mtpd.language_id = '" . (int)$_SESSION['languages_id'] . "'
                              where p.products_id = '" . (int)$_GET['products_id'] . "'
                              and p.products_id = pd.products_id
                              and pd.language_id = '" . (int)$_SESSION['languages_id'] . "'";

change to:

case (strstr($_GET['main_page'], 'product_') or strstr($_GET['main_page'], 'document_')):
    $sql= "select pd.products_name, p.products_model, p.products_price_sorter, p.products_tax_class_id,
                                      p.metatags_title_status, p.metatags_products_name_status, p.metatags_model_status,
                                      p.products_id, p.metatags_price_status, p.metatags_title_tagline_status,
                                      pd.products_description, p.product_is_free,
                                      mtpd.metatags_title, mtpd.metatags_keywords, mtpd.metatags_description
                              from (" . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd) left join " . TABLE_META_TAGS_PRODUCTS_DESCRIPTION . " mtpd on mtpd.products_id = p.products_id and mtpd.language_id = '" . (int)$_SESSION['languages_id'] . "'
                              where p.products_id = '" . (int)$_GET['products_id'] . "'
                              and p.products_id = pd.products_id
                              and pd.language_id = '" . (int)$_SESSION['languages_id'] . "'";

Regards.

[DrByte]

thanks MARcoDEN, but in this case, the cause has nothing to do with meta-tags.

[emmdee]

Can you tell me Dr Byte what you think it has to do with??
1054 Unknown column 'p.products_id' in 'on clause'
I'm getting the same error on v 1.2.7 locally iis on XP mysql 5.0
Regards
emmdee

[Chuckl]

it has to do with running version 1.2.7 with MySQL 5. Not impossible to fix, but probably easier to use version 1.3.7? Version 1.2.7 is just so last year.
Seriously, MySQL 5 has a my.ini setting to behave more like version 4.x. In my.ini look for a sql-mode flag and change to sql-mode="MYSQL40", or disable strict mode.
A search on the forums here for 1054 will probably bring up a lot of fixesas well, although 1.2.7 results may be in the archived section.

[foxdeluxe]

I'm still running 1.2.7 and encountered this problem for the first time today.
My host says they upgraded to MySQL 5.0 back in October.
If that's the case, wouldn't the problem have occurred much sooner?

[DrByte]

In theory yes. Perhaps it "has" been occurring in limited ways but nobody has reported it to you.

However, if your hosting company did the upgrade but did *not* enable the MySQL5 "strict-trans-tables" setting (and another problematic one), then you may not be bitten by the sting of the stricter construction requirements.

Either way, you should be planning an upgrade sooner than later...