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

[gaspower]

Hello,

 

I am using the newest version of OSC MS2.2 RC1 and all other features are work fine, even advanced_search_result.php?

 

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

 

select count(p.products_id) as total from products p, products_description pd, manufacturers m left join specials_retail_prices s on p.products_id = s.products_id where p.products_status = '1' and pd.products_id = p.products_id and pd.language_id = '1' and p.manufacturers_id = m.manufacturers_id and m.manufacturers_id = '3'

 

Thanks JR

[ajsiem]

I have OSC 2.2 MS-2 PHP 5.2.0-8 And Mysql 5.0.27

 

And after we move one site to new server with this config we are getting, the above error message.

But ONLY on categorie with subcategories, nowhere else, what can I do to fix this?

[Polecat69]

I have OSC 2.2 MS-2 PHP 5.2.0-8 And Mysql 5.0.27

 

And after we move one site to new server with this config we are getting, the above error message.

But ONLY on categorie with subcategories, nowhere else, what can I do to fix this?

 

 

Do you have featured products installed? If so check out this that helped me.

http://www.oscommerce.com/forums/index.php?sho...p;#entry1151950

[Jan Zonjee]

I am using the newest version of OSC MS2.2 RC1 and all other features are work fine, even advanced_search_result.php?

 

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

 

select count(p.products_id) as total from products p, products_description pd, manufacturers m left join specials_retail_prices s on p.products_id = s.products_id where p.products_status = '1' and pd.products_id = p.products_id and pd.language_id = '1' and p.manufacturers_id = m.manufacturers_id and m.manufacturers_id = '3'

I'm glad you make us hunt for where that query is and which contributions you are using. Really helpful.

 

I can tell you I can't find it and it is most likely in SPPC because of the specials_retail_prices. Looks like a query on index.php but the syntax is different in old and new versions as far as I can tell.

[gaspower]

Hello Jan,

 

Thank you for the reply. What information would you like me to list for better detail. The version of SPPC I am using is 4.16.

 

Thank you for your time.

 

JR

[gaspower]

Hey Jan,

 

I see there is version 4.20. Do you have an update manual so I can upgrade from 4.16 to 4.20. This update to 4.20 will probably solve my issue.

 

Thanks JR

[Jan Zonjee]

The version of SPPC I am using is 4.16.

OK, found it in index.php and indeed that query is not MySQL5 compatible. If you can (in case you haven't added too many other contributions to index.php) try using the one in version 4.2.0. The queries in there are MySQL5 safe.

To be precise, you have this (around line 220):

	} else { // either retail or no need to get correct special prices
	$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_RETAIL_PRICES . " 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'] . "'";
} // end else { // either retail...
// EOF Separate Pricing Per Customer
  }
} else {
// show the products in a given categorie

And in 4.2.0 it is (there are more queries changed...)

	} else { // either retail or no need to get correct special prices -- changed for mysql 5
	$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_RETAIL_PRICES . " 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'] . "'";
} // end else { // either retail...
// EOF Separate Pricing Per Customer
  }
} else {
// show the products in a given categorie

[Jan Zonjee]

Do you have an update manual so I can upgrade from 4.16 to 4.20. This update to 4.20 will probably solve my issue.

Probably yes. No sorry, there is no manual update added to 4.2.0. Version 4.2.0 was such a large amount of work and took so much time that writing update instructions for version 4.1.1. to 4.1.6 would have delayed things even more...

[gaspower]

Yes, I see a ton of new features that I think are awesome... Did not think you could improve on it anymore. This is absolutely an non pressure question, just so I can plan my new sites, approximately how long be fore an upgrade manual? Would you suggest just trying to see the differences and trying to update, or way to many changes? Thanks again for a awesome contribution.

 

JR

 

Probably yes. No sorry, there is no manual update added to 4.2.0. Version 4.2.0 was such a large amount of work and took so much time that writing update instructions for version 4.1.1. to 4.1.6 would have delayed things even more...

[Jan Zonjee]

Yes, I see a ton of new features that I think are awesome... Did not think you could improve on it anymore. This is absolutely an non pressure question, just so I can plan my new sites, approximately how long be fore an upgrade manual? Would you suggest just trying to see the differences and trying to update, or way to many changes?

Part of the changes were added as manual updates to the SPPC contribution page (newsletter/email and group prices for attributes). Just the code for individualizing taxes (before it was all or nothing) wasn't released earlier, but they only effect a few files and most people will never need them (Canadians are the most likely users).

 

I haven't even started on writing an upgrade manual. Really can't give a window.

[gaspower]

Hello,

 

I went through the new installation instructions for 4.20 and they are very detailed, so a upgrade from 4.16 to 4.20 should not be to bad for me. Will be able to go through each section and check the differences. Would it be possible that you might be able to a upgrade.sql file. I am stuck there.

 

Thanks JR

[Jan Zonjee]

Would it be possible that you might be able to a upgrade.sql file. I am stuck there.

There are only a few changes, for the specific taxes and I moved the entry_company_tax_id to the table customers instead of being in address book. For the newsletter/email stuff and the products attributes there is also some sql needed. The sql should be this:

ALTER TABLE `customers`
ADD customers_specific_taxes_exempt varchar(255) NOT NULL default '',
ADD entry_company_tax_id VARCHAR(32) DEFAULT NULL;

ALTER TABLE `customers_groups`
ADD group_specific_taxes_exempt varchar(255) NOT NULL default '';

ALTER TABLE `newsletters`
ADD send_to_customer_groups VARCHAR(32) DEFAULT NULL;

ALTER TABLE `products_attributes`
ADD `attributes_hide_from_groups` varchar(255) NOT NULL default '@';

CREATE TABLE `products_attributes_groups` (
 `products_attributes_id` int(11) NOT NULL default '0',
 `customers_group_id` smallint(5) NOT NULL default '0',
 `options_values_price` decimal(15,4) NOT NULL default '0.0000',
 `price_prefix` char(1) NOT NULL default '',
 `products_id` int(11) NOT NULL default '0',
 PRIMARY KEY  (`customers_group_id`,`products_attributes_id`)
);

Now to copy the tax id from the default address of the customer from address_book to customers a script need to be run ONCE. I adapted a small script from txcrew I believe (it is in the SPPC thread).

Therefore we have to create a new file, call it move_tax_id.php, add this code, upload it to the server and call it in the browser window to run it once. if you see nothing, everything is fine, you can delete it from the server.

 

<?php
 require('includes/application_top.php');

$company_tax_id_query = tep_db_query("select ab.entry_company_tax_id, c.customers_id from " . TABLE_CUSTOMERS . " c, " . TABLE_ADDRESS_BOOK . " ab where ab.address_book_id = c.customers_default_address_id and ab.entry_company_tax_id <> '');
while ($tax_ids = tep_db_fetch_array($company_tax_id_query)) {
	  tep_db_query("update " . TABLE_CUSTOMERS . " set entry_company_tax_id = '" . $tax_ids['entry_company_tax_id'] . "' where customers_id = '" . (int)$tax_ids['customers_id'] . "'");
}
?>

That should pick up the bulk of the tax_id's. For a real upgrade there should be a page created where you can check if there are more than company_tax_id in different addresses e.g. and chose which one to migrate to the table customers. Haven't even started on that.

[gaspower]

Thank you,

Giving it a shot right now.

 

JR

[majika]

Thank you,

Giving it a shot right now.

 

JR

 

Did you get the fix for the above problem sorted ? I too am having the same problems.

 

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

 

???

[plaszma]

Hello,

 

If that's still an actually problem you just must to re-think the mysql query which produce that problem.So, please replace

$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'] . "'";

with this:

$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 outer join " . TABLE_SPECIALS . " s on p.products_id = s.products_id " .
	" inner join " .  TABLE_PRODUCTS_DESCRIPTION . " pd " . " on pd.products_id = p.products_id " .
	" inner join " . TABLE_MANUFACTURERS . " m " . " on m.manufacturers_id =  p.manufacturers_id " .
	" where p.products_status = '1' and  pd.language_id = '" . (int)$languages_id . "' and m.manufacturers_id = '" . (int)$HTTP_GET_VARS['manufacturers_id'] . "'";

 

in index.php at line 178