Jump to content
  • Checkout
  • Login
  • Get in touch

osCommerce

The e-commerce.

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


ikaros_60

Recommended Posts

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

 

select count(distinct p.products_id) as total from products p left join manufacturers m using(manufacturers_id), products_description pd left join specials s on pd.products_id = s.products_id, categories c, products_to_categories p2c 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 = '84') and (gz.zone_id is null or gz.zone_id = '0' or gz.zone_id = '55') where p.products_status = '1' and p.products_id = pd.products_id and pd.language_id = '4' and p.products_id = p2c.products_id and p2c.categories_id = c.categories_id and ((pd.products_name like '%hjk%' or p.products_model like '%hjk%' or m.manufacturers_name like '%hjk%' or pd.products_description like '%hjk%') ) and (IF(s.status, s.specials_new_products_price, p.products_price) * if(gz.geo_zone_id is null, 1, 1 + (tr.tax_rate / 100) ) <= 1000)

 

can anyone help?

 

http://www.aidata.gr

Link to comment
Share on other sites

I believe your problem is related to your db being update by your isp from mysql version 4 to 5.

 

Patches to correct this are included with each release of Oscommerce and are generally in the extras directory. The files have names like update-yyyymmdd.html and update-yyyymmdd.txt.

 

These files contain a shed load of patches that should be applied to a stock install, but as this is not shown as a step in the documentation / installation instruction that I have read, most people won't until they run into a problem. Then they will find a solution to one problem via the forums.

 

Here is the index of mine.

 

osCommerce 2.2 Milestone 2 Update 051113

Update Package 13th November 2005

 

------------------------------------------------------------------------------

Table of Contents

------------------------------------------------------------------------------

 

## Update 051113

 

customer_country_id in addressbook

 

## Update 051112

 

Cannot re-assign $this

limit -20, 20

Database Input Enhancement

Adding Non-Existing Products To Cart

Session ID XSS Issue

Validate Session ID

File Manager Problem

HTTP Header Injection

E-Mail Header Injection

Contact Us Form XSS Issue

Open Redirector

Extra Slashes In New Products

Order Status Filtering

MySQL 5.0 Compatibility

 

And here is the MySQL 5.0 Compatibility entries

 

------------------------------------------------------------------------------

MySQL 5.0 Compatibility

------------------------------------------------------------------------------

 

Problem:

 

MySQL 5.0 introduces Server SQL modes as part of its SQL 2003 standards support, and uses a more stricter approach to executing SQL queries. This is performed by default with setting STRICT_TRANS_TABLES as a Server SQL mode.

 

Due to this new setting, MySQL fails on certain SQL queries and produces error messages on the screen.

 

Solution:

 

Lines 213-223 in catalog/advanced_search_result.php must be changed from:

 

$from_str = "from " . TABLE_PRODUCTS . " p left join " . TABLE_MANUFACTURERS . " m using(manufacturers_id) left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id, " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_CATEGORIES . " c, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c";

 

if ( (DISPLAY_PRICE_WITH_TAX == 'true') && (tep_not_null($pfrom) || tep_not_null($pto)) ) {

if (!tep_session_is_registered('customer_country_id')) {

$customer_country_id = STORE_COUNTRY;

$customer_zone_id = STORE_ZONE;

}

$from_str .= " left join " . TABLE_TAX_RATES . " tr on p.products_tax_class_id = tr.tax_class_id left join " . TABLE_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 = '" . (int)$customer_country_id . "') and (gz.zone_id is null or gz.zone_id = '0' or gz.zone_id = '" . (int)$customer_zone_id . "')";

}

 

$where_str = " where p.products_status = '1' and p.products_id = pd.products_id and pd.language_id = '" . (int)$languages_id . "' and p.products_id = p2c.products_id and p2c.categories_id = c.categories_id ";

 

to:

 

$from_str = "from " . TABLE_PRODUCTS . " p left join " . TABLE_MANUFACTURERS . " m using(manufacturers_id) left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id";

 

if ( (DISPLAY_PRICE_WITH_TAX == 'true') && (tep_not_null($pfrom) || tep_not_null($pto)) ) {

if (!tep_session_is_registered('customer_country_id')) {

$customer_country_id = STORE_COUNTRY;

$customer_zone_id = STORE_ZONE;

}

$from_str .= " left join " . TABLE_TAX_RATES . " tr on p.products_tax_class_id = tr.tax_class_id left join " . TABLE_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 = '" . (int)$customer_country_id . "') and (gz.zone_id is null or gz.zone_id = '0' or gz.zone_id = '" . (int)$customer_zone_id . "')";

}

 

$from_str .= ", " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_CATEGORIES . " c, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c";

 

$where_str = " where p.products_status = '1' and p.products_id = pd.products_id and pd.language_id = '" . (int)$languages_id . "' and p.products_id = p2c.products_id and p2c.categories_id = c.categories_id ";

 

The following lines must be replaced in catalog/index.php:

 

Line 175, from:

 

$listing_sql = "select " . $select_column_list . " p.products_id, p.manufacturers_id, p.products_price, p.products_tax_class_id, IF(s.status, s.specials_new_products_price, NULL) as specials_new_products_price, IF(s.status, s.specials_new_products_price, p.products_price) as final_price 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)$HTTP_GET_VARS['manufacturers_id'] . "' and p.products_id = p2c.products_id and pd.products_id = p2c.products_id and pd.language_id = '" . (int)$languages_id . "' and p2c.categories_id = '" . (int)$HTTP_GET_VARS['filter_id'] . "'";

 

to:

 

$listing_sql = "select " . $select_column_list . " p.products_id, p.manufacturers_id, p.products_price, p.products_tax_class_id, IF(s.status, s.specials_new_products_price, NULL) as specials_new_products_price, IF(s.status, s.specials_new_products_price, p.products_price) as final_price from " . TABLE_PRODUCTS . " p left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id, " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_MANUFACTURERS . " m, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c where p.products_status = '1' and p.manufacturers_id = m.manufacturers_id and m.manufacturers_id = '" . (int)$HTTP_GET_VARS['manufacturers_id'] . "' and p.products_id = p2c.products_id and pd.products_id = p2c.products_id and pd.language_id = '" . (int)$languages_id . "' and p2c.categories_id = '" . (int)$HTTP_GET_VARS['filter_id'] . "'";

 

Line 178, from:

 

$listing_sql = "select " . $select_column_list . " p.products_id, p.manufacturers_id, p.products_price, p.products_tax_class_id, IF(s.status, s.specials_new_products_price, NULL) as specials_new_products_price, IF(s.status, s.specials_new_products_price, p.products_price) as final_price 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)$languages_id . "' and p.manufacturers_id = m.manufacturers_id and m.manufacturers_id = '" . (int)$HTTP_GET_VARS['manufacturers_id'] . "'";

 

to:

 

$listing_sql = "select " . $select_column_list . " p.products_id, p.manufacturers_id, p.products_price, p.products_tax_class_id, IF(s.status, s.specials_new_products_price, NULL) as specials_new_products_price, IF(s.status, s.specials_new_products_price, p.products_price) as final_price from " . TABLE_PRODUCTS . " p left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id, " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_MANUFACTURERS . " m where p.products_status = '1' and pd.products_id = p.products_id and pd.language_id = '" . (int)$languages_id . "' and p.manufacturers_id = m.manufacturers_id and m.manufacturers_id = '" . (int)$HTTP_GET_VARS['manufacturers_id'] . "'";

 

Line 184, from:

 

$listing_sql = "select " . $select_column_list . " p.products_id, p.manufacturers_id, p.products_price, p.products_tax_class_id, IF(s.status, s.specials_new_products_price, NULL) as specials_new_products_price, IF(s.status, s.specials_new_products_price, p.products_price) as final_price 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)$HTTP_GET_VARS['filter_id'] . "' and p.products_id = p2c.products_id and pd.products_id = p2c.products_id and pd.language_id = '" . (int)$languages_id . "' and p2c.categories_id = '" . (int)$current_category_id . "'";

 

to:

 

$listing_sql = "select " . $select_column_list . " p.products_id, p.manufacturers_id, p.products_price, p.products_tax_class_id, IF(s.status, s.specials_new_products_price, NULL) as specials_new_products_price, IF(s.status, s.specials_new_products_price, p.products_price) as final_price from " . TABLE_PRODUCTS . " p left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id, " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_MANUFACTURERS . " m, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c where p.products_status = '1' and p.manufacturers_id = m.manufacturers_id and m.manufacturers_id = '" . (int)$HTTP_GET_VARS['filter_id'] . "' and p.products_id = p2c.products_id and pd.products_id = p2c.products_id and pd.language_id = '" . (int)$languages_id . "' and p2c.categories_id = '" . (int)$current_category_id . "'";

 

Line 187, from:

 

$listing_sql = "select " . $select_column_list . " p.products_id, p.manufacturers_id, p.products_price, p.products_tax_class_id, IF(s.status, s.specials_new_products_price, NULL) as specials_new_products_price, IF(s.status, s.specials_new_products_price, p.products_price) as final_price 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)$languages_id . "' and p2c.categories_id = '" . (int)$current_category_id . "'";

 

to:

 

$listing_sql = "select " . $select_column_list . " p.products_id, p.manufacturers_id, p.products_price, p.products_tax_class_id, IF(s.status, s.specials_new_products_price, NULL) as specials_new_products_price, IF(s.status, s.specials_new_products_price, p.products_price) as final_price from " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_PRODUCTS . " p left join " . TABLE_MANUFACTURERS . " m on p.manufacturers_id = m.manufacturers_id left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c where p.products_status = '1' and p.products_id = p2c.products_id and pd.products_id = p2c.products_id and pd.language_id = '" . (int)$languages_id . "' and p2c.categories_id = '" . (int)$current_category_id . "'";

 

Line 292 in catalog/admin/categories.php must be changed from:

 

tep_db_query("insert into " . TABLE_PRODUCTS . " (products_quantity, products_model,products_image, products_price, products_date_added, products_date_available, products_weight, products_status, products_tax_class_id, manufacturers_id) values ('" . tep_db_input($product['products_quantity']) . "', '" . tep_db_input($product['products_model']) . "', '" . tep_db_input($product['products_image']) . "', '" . tep_db_input($product['products_price']) . "', now(), '" . tep_db_input($product['products_date_available']) . "', '" . tep_db_input($product['products_weight']) . "', '0', '" . (int)$product['products_tax_class_id'] . "', '" . (int)$product['manufacturers_id'] . "')");

 

to:

 

tep_db_query("insert into " . TABLE_PRODUCTS . " (products_quantity, products_model,products_image, products_price, products_date_added, products_date_available, products_weight, products_status, products_tax_class_id, manufacturers_id) values ('" . tep_db_input($product['products_quantity']) . "', '" . tep_db_input($product['products_model']) . "', '" . tep_db_input($product['products_image']) . "', '" . tep_db_input($product['products_price']) . "', now(), " . (empty($product['products_date_available']) ? "null" : "'" . tep_db_input($product['products_date_available']) . "'") . ", '" . tep_db_input($product['products_weight']) . "', '0', '" . (int)$product['products_tax_class_id'] . "', '" . (int)$product['manufacturers_id'] . "')");

 

The following SQL queries need to be performed:

 

ALTER TABLE whos_online MODIFY COLUMN last_page_url VARCHAR(255) NOT NULL;

 

ALTER TABLE customers MODIFY COLUMN customers_default_address_id INTEGER;

 

ALTER TABLE customers_basket MODIFY COLUMN final_price DECIMAL(15,4);

 

These threadd might also be of use

 

http://www.oscommerce.com/community/contributions,4654

http://www.oscommerce.com/community/contributions,3727/

http://www.oscommerce.com/forums/index.php?showtopic=179130

Need help installing add ons/contributions, cleaning a hacked site or a bespoke development, check my profile

 

Virus Threat Scanner

My Contributions

Basic install answers.

Click here for Contributions / Add Ons.

UK your site.

Site Move.

Basic design info.

 

For links mentioned in old answers that are no longer here follow this link Useful Threads.

 

If this post was useful, click the Like This button over there ======>>>>>.

Link to comment
Share on other sites

What was wrong, best I don't lead others astray?

Need help installing add ons/contributions, cleaning a hacked site or a bespoke development, check my profile

 

Virus Threat Scanner

My Contributions

Basic install answers.

Click here for Contributions / Add Ons.

UK your site.

Site Move.

Basic design info.

 

For links mentioned in old answers that are no longer here follow this link Useful Threads.

 

If this post was useful, click the Like This button over there ======>>>>>.

Link to comment
Share on other sites

  • 2 weeks later...

I just changed the code in index.php as recommended but I still get these error when i click a category with a sub category in it:

 

1054 - Unknown column 'p.manufacturers_id' in 'on clause'

 

select distinct p.products_id, p.products_quantity, p.products_image, p.products_model, p.products_tax_class_id, p.products_weight, if(s.status, s.specials_new_products_price, p.products_price) as products_price, mnf.manufacturers_name, rvw.reviews_rating from products p left join specials s on p.products_id = s.products_id, products_to_categories p2c, categories c left join manufacturers mnf on p.manufacturers_id = mnf.manufacturers_id left join reviews rvw on p.products_id = rvw.products_id where p.products_image !='' and p.products_id = p2c.products_id and p2c.categories_id = c.categories_id and c.parent_id = '25' and p.products_status = '1' order by RAND() limit 9

 

[TEP STOP]

 

Any idea what could be wrong ....??

 

This error is only coming up, when I have a category with no Product or only a sub category in it.... :blink:

OSC Webmakers Edition modiefied with many other contribs and enhancements.

+ STS 4.5.7 for 2.2MS2 and RC1

Link to comment
Share on other sites

I just changed the code in index.php as recommended but I still get these error when i click a category with a sub category in it:

Any idea what could be wrong ....??

 

This error is only coming up, when I have a category with no Product or only a sub category in it.... :blink:

 

 

 

 

I'm trying to work on the same problem. Only I know VERY LITTLE about all of this. Can someone tell me where to start? Do I sign in to my website admin? I would really appreciate any help! I do have someone who should be able to help, if I know where to start. Thanks so much!

Link to comment
Share on other sites

Solved the Problem !

 

I changed code in includes/modules/new_products.php from:

 

<!-- new_products //-->

<?php

 

if ( (!isset($new_products_category_id)) || ($new_products_category_id == '0') ) {

$sql = "select distinct p.products_id, p.products_quantity, p.products_image, p.products_model, p.products_tax_class_id, p.products_weight, if(s.status, s.specials_new_products_price, p.products_price) as products_price, mnf.manufacturers_name, rvw.reviews_rating from " . TABLE_PRODUCTS . " p left join ".TABLE_SPECIALS." s on p.products_id = s.products_id left join ".TABLE_MANUFACTURERS." mnf on p.manufacturers_id = mnf.manufacturers_id left join ".TABLE_REVIEWS." rvw on p.products_id = rvw.products_id where p.products_image !='' and p.products_status = '1' order by RAND() limit ".MAX_DISPLAY_NEW_PRODUCTS;

$new_products_query = tep_db_query($sql);

 

} else {

$sql = "select distinct p.products_id, p.products_quantity, p.products_image, p.products_model, p.products_tax_class_id, p.products_weight, if(s.status, s.specials_new_products_price, p.products_price) as products_price, mnf.manufacturers_name, rvw.reviews_rating from " . TABLE_PRODUCTS . " p left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c, " . TABLE_CATEGORIES . " c left join ".TABLE_MANUFACTURERS." mnf on p.manufacturers_id = mnf.manufacturers_id left join ".TABLE_REVIEWS." rvw on p.products_id = rvw.products_id where p.products_image !='' and p.products_id = p2c.products_id and p2c.categories_id = c.categories_id and c.parent_id = '" . (int)$new_products_category_id . "' and p.products_status = '1' order by RAND() limit " . MAX_DISPLAY_NEW_PRODUCTS;

$new_products_query = tep_db_query($sql);

}

 

to this:

 

<!-- new_products //-->

<?php

$info_box_contents = array();

$info_box_contents[] = array('text' => sprintf(TABLE_HEADING_NEW_PRODUCTS, strftime('%B')));

 

new contentBoxHeading($info_box_contents);

 

if ( (!isset($new_products_category_id)) || ($new_products_category_id == '0') ) {

$new_products_query = tep_db_query("select p.products_id, p.products_image, p.products_tax_class_id, pd.products_name, if(s.status, s.specials_new_products_price, p.products_price) as products_price from " . TABLE_PRODUCTS . " p left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id, " . TABLE_PRODUCTS_DESCRIPTION . " pd where p.products_status = '1' and p.products_id = pd.products_id and pd.language_id = '" . (int)$languages_id . "' order by p.products_date_added desc limit " . MAX_DISPLAY_NEW_PRODUCTS);

} else {

$new_products_query = tep_db_query("select distinct p.products_id, p.products_image, p.products_tax_class_id, pd.products_name, if(s.status, s.specials_new_products_price, p.products_price) as products_price from " . TABLE_PRODUCTS . " p left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id, " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c, " . TABLE_CATEGORIES . " c where p.products_id = p2c.products_id and p2c.categories_id = c.categories_id and c.parent_id = '" . (int)$new_products_category_id . "' and p.products_status = '1' and p.products_id = pd.products_id and pd.language_id = '" . (int)$languages_id . "' order by p.products_date_added desc limit " . MAX_DISPLAY_NEW_PRODUCTS);

}

 

...and now it is working....

OSC Webmakers Edition modiefied with many other contribs and enhancements.

+ STS 4.5.7 for 2.2MS2 and RC1

Link to comment
Share on other sites

  • 3 weeks later...

Hi guys,

 

I am also getting this error in the BEST SELLERS colums:

 

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

 

select p.products_image, pd.products_name, p.products_id, p.manufacturers_id, p.products_price, p.products_tax_class_id, IF(s.status, s.specials_new_products_price, NULL) as specials_new_products_price, IF(s.status, s.specials_new_products_price, p.products_price) as final_price from products_description pd, products p left join manufacturers m on p.manufacturers_id = m.manufacturers_id, products_to_categories p2c left join 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 = '1' and p2c.categories_id = '61' order by p.products_date_added DESC limit 2

 

[TEP STOP]

 

Can anyone please give me the code which i can use to replace this.

 

My site url is: http://infinitecoregroup.com/index.php

 

Thnx.

Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...