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

When ever I go to select a category from the main screen or when I try to select an individual product in viewing all products I get this error message: 1054 Unknown column 'p.products_id' in 'on clause'

I'm not the only one with admin rights and I'm not sure what the other person could have done to cause this error.

Does anyone have any idea what this error is and how I can fix it? I can no longer view any products in the catelog.

Any help would be appreciated.

Thanks,

Jeff Nordgren

This problem is usually caused by using mysql5 with zc v1.2.x. If this problem has recently shown up after your site has been working with no problems, then the likelihood is that your Host has upgraded mysql. If that is the case then I would be complaining to my host.

Mysql5 uses a much stricter SQL interpreter and some queries in ZC v1.2.7 and earlier will fail. Fixes were introduced into ZC v1.3.0 to fix this.

Wilt,

Thanks for the information.

I upgraded to zc 1.3 and that seemed to fix the problem.

Thanks again,

Jeff

I have the same problem, in the category products i get the message: 1054 Unknown column 'p.products_id' in 'on clause'. My host has upgraded to SQL 5. See https://www.vanhooffmodelbouw.nl

But i am a bit anxious to upgrade my zencart version, because i made a lot of custumizations a while ago.

Is there any simple (temporary) solution to fix this problem.

I fixed it, i used the help off this thread: forums.oscommerce.nl/index.php?showtopic=6565. I added some brackets in the default_filter file (in the includes/index_filter map).

please could you paste the code here, with instructions in english? l have the same problem with my new install, but my norweigian is not that good LOL ;-)

Please note that osCommerce and Zen Cart are not the same but do use some of the same functions. The Bug Fix to make osCommerce compatable with MySQL 5 is listed in full here:

oscommerce.com/ext/update-20051112.html#_Toc119473702

It may help to show you what you need to do to make your version of Zen Cart compatable with MySQL 5. If you can upgrade to the latest vrsion of Zen Cart then do so, and this should also fix the problem.

Vger

I got this error:

1054 Unknown column 'c.parent_id' in 'on clause'
in:
[SELECT c.categories_id as id, c.parent_id, cd.categories_name as cName, cd2.categories_name as pName FROM zce_categories c, zce_categories_description cd LEFT JOIN zce_categories_description cd2 ON c.parent_id=cd2.categories_id AND cd2.language_id='2' WHERE c.categories_id=cd.categories_id AND cd.language_id='2']

I did upgrade to the latest ZenCart 1.3.7 and the MySQL was upgraded to from 4 to 5, now i have did everything you say exept the OsCommerce thing because I am not proficient on the SQL management.
Anyway after the upgrading all seems to be the same, also the original install was 1.3.1 so I think there is something else.
Please HELP.
the site URL is https://www.electrogeno.com

Thanks in advance for your help.

I get the 1054 error message when I open the attributes controller in the admin area.
I have searched through many forums, but they didn't help.
I cannot update from zc 1.26 to 1.3.x because I have made many changes in the design of the shop.
Does someone know what I have to do so it works again?
Thanks for answers!

You will have to update. Most hosts will upgrade to PHP5 soon, and 1.2.x will not run on it. Upgrade now or you will regret later.

alot of hosts are making the jump to mysql5 at the same time they go to php5

this will cause issues with sites under 1.3.6 ( have to upgrade the /includes/functions/sessions.php )

You should always plan on having to upgrade at some time:
as technology changes, things go obsolete

this is the reason that the developers built in the over rides folders and pages, as well as the auto loading functions,

I will begin with the upgrade as soon as possible, but I haven't much time for it right now. So it will take 4-5 weeks until I can start upgrading. But now I want to add some attributes to some of my products in my zen-cart store and need to know what I have to do with the source code in the admin area so it works.
Thanks!

you dont understand it WONT work

There is an unsupported patch for v1.2.6 and v1.2.7 at the following site, which can be used to do partial bridging for a limited number of MySQL5 features.
Use at your own risk.
http://download.dataweb.no/

OK, I'm on Zen Cart 1.2, MySQL 5, I've fixed the error by updating the file includes/index_filters/default_filter.php, which is the equivalent of index.php that people mention in this thread:

forums.oscommerce.com/index.php?showtopic=230304

Here's what I changed:

From:

      if (isset($_GET['filter_id']) && zen_not_null($_GET['filter_id']))
      {
// We are asked to show only a specific category
        $listing_sql = "select " . $select_column_list . " p.products_id, p.manufacturers_id, p.products_price, p.products_tax_class_id, if(s.status = '1', s.specials_new_products_price, NULL) AS specials_new_products_price, IF(s.status = '1', s.specials_new_products_price, p.products_price) as final_price, p.products_sort_order from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_MANUFACTURERS . " m, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id where p.products_status = '1' and p.manufacturers_id = m.manufacturers_id and m.manufacturers_id = '" . (int)$_GET['manufacturers_id'] . "' and p.products_id = p2c.products_id and pd.products_id = p2c.products_id and pd.language_id = '" . (int)$_SESSION['languages_id'] . "' and p2c.categories_id = '" . (int)$_GET['filter_id'] . "'";
      } else {
// We show them all
        $listing_sql = "select " . $select_column_list . " p.products_id, p.manufacturers_id, p.products_price, p.products_tax_class_id, IF(s.status = '1', s.specials_new_products_price, NULL) as specials_new_products_price, IF(s.status = '1', s.specials_new_products_price, p.products_price) as final_price, p.products_sort_order from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_MANUFACTURERS . " m left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id where p.products_status = '1' and pd.products_id = p.products_id and pd.language_id = '" . (int)$_SESSION['languages_id'] . "' and p.manufacturers_id = m.manufacturers_id and m.manufacturers_id = '" . (int)$_GET['manufacturers_id'] . "'";
      }
    } else {
// show the products in a given categorie
      if (isset($_GET['filter_id']) && zen_not_null($_GET['filter_id']))
      {
// We are asked to show only specific catgeory
        $listing_sql = "select " . $select_column_list . " p.products_id, p.manufacturers_id, p.products_price, p.products_tax_class_id, IF(s.status = '1', s.specials_new_products_price, NULL) as specials_new_products_price, IF(s.status = '1', s.specials_new_products_price, p.products_price) as final_price, p.products_sort_order from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_MANUFACTURERS . " m, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id where p.products_status = '1' and p.manufacturers_id = m.manufacturers_id and m.manufacturers_id = '" . (int)$_GET['filter_id'] . "' and p.products_id = p2c.products_id and pd.products_id = p2c.products_id and pd.language_id = '" . (int)$_SESSION['languages_id'] . "' and p2c.categories_id = '" . (int)$current_category_id . "'";
      } else {
// We show them all
        $listing_sql = "select " . $select_column_list . " p.products_id, p.manufacturers_id, p.products_price, p.products_tax_class_id, IF(s.status = '1', s.specials_new_products_price, NULL) as specials_new_products_price, IF(s.status ='1', s.specials_new_products_price, p.products_price) as final_price, p.products_sort_order from " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_PRODUCTS . " p left join " . TABLE_MANUFACTURERS . " m on p.manufacturers_id = m.manufacturers_id, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id where p.products_status = '1' and p.products_id = p2c.products_id and pd.products_id = p2c.products_id and pd.language_id = '" . (int)$_SESSION['languages_id'] . "' and p2c.categories_id = '" . (int)$current_category_id . "'";
      }

To:

      if (isset($_GET['filter_id']) && zen_not_null($_GET['filter_id']))
      {
// We are asked to show only a specific category
        $listing_sql = "select " . $select_column_list . " p.products_id, p.manufacturers_id, p.products_price, p.products_tax_class_id, if(s.status = '1', s.specials_new_products_price, NULL) AS specials_new_products_price, IF(s.status = '1', s.specials_new_products_price, p.products_price) as final_price, p.products_sort_order from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_MANUFACTURERS . " m, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c left join " . TABLE_SPECIALS . " s on p2c.products_id = s.products_id where p.products_status = '1' and p.manufacturers_id = m.manufacturers_id and m.manufacturers_id = '" . (int)$_GET['manufacturers_id'] . "' and p.products_id = p2c.products_id and pd.products_id = p2c.products_id and pd.language_id = '" . (int)$_SESSION['languages_id'] . "' and p2c.categories_id = '" . (int)$_GET['filter_id'] . "'"; 
      } else {
// We show them all
        $listing_sql = "select " . $select_column_list . " p.products_id, p.manufacturers_id, p.products_price, p.products_tax_class_id, IF(s.status = '1', s.specials_new_products_price, NULL) as specials_new_products_price, IF(s.status = '1', s.specials_new_products_price, p.products_price) as final_price, p.products_sort_order from ". TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_MANUFACTURERS . " m, " . TABLE_PRODUCTS . " p left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id where p.products_status = '1' and pd.products_id = p.products_id and pd.language_id = '" . (int)$_SESSION['languages_id'] . "' and p.manufacturers_id = m.manufacturers_id and m.manufacturers_id = '" . (int)$_GET['manufacturers_id'] . "'";
      }
    } else {
// show the products in a given categorie
      if (isset($_GET['filter_id']) && zen_not_null($_GET['filter_id']))
      {
// We are asked to show only specific catgeory
        $listing_sql = "select " . $select_column_list . " p.products_id, p.manufacturers_id, p.products_price, p.products_tax_class_id, IF(s.status = '1', s.specials_new_products_price, NULL) as specials_new_products_price, IF(s.status = '1', s.specials_new_products_price, p.products_price) as final_price, p.products_sort_order from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_MANUFACTURERS . " m, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c left join " . TABLE_SPECIALS . " s on p2c.products_id = s.products_id where p.products_status = '1' and p.manufacturers_id = m.manufacturers_id and m.manufacturers_id = '" . (int)$_GET['filter_id'] . "' and p.products_id = p2c.products_id and pd.products_id = p2c.products_id and pd.language_id = '" . (int)$_SESSION['languages_id'] . "' and p2c.categories_id = '" . (int)$current_category_id . "'";
      } else {
// We show them all
        $listing_sql = "select " . $select_column_list . " p.products_id, p.manufacturers_id, p.products_price, p.products_tax_class_id, IF(s.status = '1', s.specials_new_products_price, NULL) as specials_new_products_price, IF(s.status ='1', s.specials_new_products_price, p.products_price) as final_price, p.products_sort_order from " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_PRODUCTS . " p left join " . TABLE_MANUFACTURERS . " m on p.manufacturers_id = m.manufacturers_id, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c left join " . TABLE_SPECIALS . " s on p2c.products_id = s.products_id where p.products_status = '1' and p.products_id = p2c.products_id and pd.products_id = p2c.products_id and pd.language_id = '" . (int)$_SESSION['languages_id'] . "' and p2c.categories_id = '" . (int)$current_category_id . "'";
      }

In particular, I changed the 1st, 3rd, and 4th query from p.products_id = s.products_id to p2c.products_id = s.products_id. In the 2nd query, I changed the FROM clause table select order from TABLE_PRODUCTS, TABLE_PRODUCTS_DESCRIPTION, TABLE_MANUFACTURERS to TABLE_PRODUCTS_DESCRIPTION, TABLE_MANUFACTURERS, TABLE_PRODUCTS.

Are there any other places in Zen Cart that I'll need to update? In this OSCommerce thread, post #6:

forums.oscommerce.com/index.php?showtopic=230304

It says the advanced_search_result.php file needs to be updated, but in Zen Cart that file doesn't contain any SQL code. What's the equivalent Zen Cart file that I need to update?

In /public_html/yoursite.com/includes/modules/meta-tags.php

I changed:

from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd left join " . TABLE_META_TAGS_PRODUCTS_DESCRIPTION . " mtpd on mtpd.products_id

TO:

from " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_PRODUCTS . " p left join " . TABLE_META_TAGS_PRODUCTS_DESCRIPTION . " mtpd on mtpd.products_id

Line 163

That seemed to do the trick to get all products to show up error free.

Good Luck!! Have a great day.

jewelee65:

In /public_html/yoursite.com/includes/modules/meta-tags.php

I changed:

from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd left join " . TABLE_META_TAGS_PRODUCTS_DESCRIPTION . " mtpd on mtpd.products_id

TO:

from " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_PRODUCTS . " p left join " . TABLE_META_TAGS_PRODUCTS_DESCRIPTION . " mtpd on mtpd.products_id

Line 163

That seemed to do the trick to get all products to show up error free.That will bandage one of about 30 different problems you'll encounter when trying to run Zen Cart v1.2.x on MySQL 5.

I know.......have had to find quite a few other files with problems in the select statements. Still working out the bugs. We are planning to upgrade, just for the security issues alone, but are trying to hang in there until 1.4 comes out.

The fix above seemed to clear up quite a few problems with products though.

Robo:

OK, I'm on Zen Cart 1.2, MySQL 5, I've fixed the error by updating the file includes/index_filters/default_filter.php, which is the equivalent of index.php that people mention in this thread:

forums.oscommerce.com/index.php?showtopic=230304

Here's what I changed:

the real search SQL code is in includes/block/blk_advanced_search_result.php, you can change like this

$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= p.products_id and mtpd.language_id = '" . $_SESSION['languages_id'] . "'";

For other changes need to be made, please look at my blog flyspirit99.blogspot.com/2008/05/1054-unknown-column-pproductsid-in-on.html

The real search SQL code is in includes/block/blk_advanced_search_result.php now