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

**Merlinpa1969** · 13 Dec 2007, 04:27 PM

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,

**erik89** · 13 Dec 2007, 05:12 PM

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!

**Merlinpa1969** · 13 Dec 2007, 06:09 PM

you dont understand it WONT work

**DrByte** · 13 Dec 2007, 06:16 PM

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/

**Robo** · 24 Feb 2008, 10:13 AM

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:

Code:

      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:

Code:

      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?

**jewelee65** · 19 Mar 2008, 12:28 AM

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.

**DrByte** · 19 Mar 2008, 12:50 AM

Originally Posted by 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.

**jewelee65** · 19 Mar 2008, 07:34 PM

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.

**flyspirit** · 6 May 2008, 02:12 AM

Originally Posted by 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

Code:

$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.##########################/2008/05/1054-unknown-column-pproductsid-in-on.html