Jump to content
dev osCommerce
Forum
  • Checkout
  • Login
  • Contact Us
  • Get in touch

osCommerce

The e-commerce.

New Demo
Our Partners

1054 - Unknown column 'p.products_id' -- I see several stores are down, is anyone working on this?

petercascio

By petercascio
in General Support

Recommended Posts

petercascio

petercascio

Posted
Posted

Hi,

 

the infamous "1054 - Unknown column 'p.products_id' in 'on clause'" problem -- I see many are having this problem with all sorts of contributions, is anyone working on finding a solution for this?

 

I noticed several people asking about this in the forums and their stores are still down weeks later.

 

In my case I set up a new store today using oscommerce-2.2ms2-051113. MySQL is 5.0.16.

 

I installed the Quantity Price Breaks Per Product contribution and it broke the store. Any help would be much appreciated.

 

Peter

 

 

Here's the error message:

 

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

 

select pd.products_name, p.products_model, p.products_image, p.products_id, p.manufacturers_id, p.products_price, p.products_weight, p.products_price1,p.products_price2,p.products_price3,p.products_price4, p.products_price5,p.products_price6,p.products_price7,p.products_price8, p.products_price1_qty,p.products_price2_qty,p.products_price3_qty,p.products_pri

ce4_qty, p.products_price5_qty,p.products_price6_qty,p.products_price7_qty,p.products_pri

ce8_qty, p.products_qty_blocks, 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 = '19' and pd.products_id = '19' and pd.language_id = '1'

 

[TEP STOP]

mrgtec

mrgtec

Posted
Posted

Hi,

 

1054, 1064, & another one means you didnt install the db file, it looks to me.

 

Michael

Michael

petercascio

petercascio

Posted
  • Author
Posted
Hi,

 

1054, 1064, & another one means you didnt install the db file, it looks to me.

 

Michael

 

Yes, I did a complete install, including the sample database. I did this as I had done a previous install with many mods, and then the problem developed when I dropped in the Quantity Price Breaks Per Product contribution.

 

The freshly installed sample store is http://www.spiritedshipper.com/catalog/

Guest

Guest

Posted
Posted
Yes, I did a complete install, including the sample database. I did this as I had done a previous install with many mods, and then the problem developed when I dropped in the Quantity Price Breaks Per Product contribution.

 

The freshly installed sample store is http://www.spiritedshipper.com/catalog/

 

 

I JUST HAD THIS SAME PROBLEM! It turns out this error occurs because of some problems with the programming and MySQL 5+

 

I reinstalled 4.2 (i think that is the version) Or if you are not lazy like I am check for the latest version of OSC and update it to work with MySQP 5+

Guest

Guest

Posted
Posted

I got the same error number/messages ever since my web hosting company upgrades MySQL from 3.x to 5.x.

 

Apparently ALL queries which use ANSI's LEFT JOIN don't work anymore.

 

I don't know in detail about MySQL. I there any global database setting to support this (backward compatibility)? Does MySQL 5 now have different SQL syntax?

 

Can't imagine I have to change ALL the queries (from ANSI's LEFT JOIN to inner join)! :'(

 

Any better advise?

Jan Zonjee

Jan Zonjee

Posted
Posted
However, this is way beyond me.. hope someone can put up some pointers as to where to make the changes.
What happens if you change the function loadProduct in the includes/classes/PriceFormatter.php (around line 165 and further) to:

  function loadProduct($product_id, $language_id=1)
 {
$sql="select pd.products_name, p.products_model, p.products_image, p.products_id," .
	" p.manufacturers_id, p.products_price, p.products_weight," .
	" p.products_price1,p.products_price2,p.products_price3,p.products_price4, p.products_price5,p.products_price6,p.products_price7,p.products_price8," .
	" p.products_price1_qty,p.products_price2_qty,p.products_price3_qty,p.products_pri
ce4_qty, p.products_price5_qty,p.products_price6_qty,p.products_price7_qty,p.products_pri
ce8_qty," .
	" p.products_qty_blocks," .
	" 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_PRODUCTS_TO_CATEGORIES . " p2c using(products_id)" .
" left join " . TABLE_SPECIALS . " s using(products_id), " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . 
"" . TABLE_MANUFACTURERS . " m " .
	" where p.products_status = '1'" .
" and pd.products_id = p.products_id " .
" and p.manufacturers_id = m.manufacturers_id " .
	"   and p.products_id = '" . (int)$product_id . "'" .
	"   and pd.products_id = '" . (int)$product_id . "'" .
	"   and pd.language_id = '". (int)$language_id ."'";

$product_info_query = tep_db_query($sql);
$product_info = tep_db_fetch_array($product_info_query);
$this->parse($product_info);

return $product_info;
 }

Jan Zonjee

Jan Zonjee

Posted
Posted
Yeah -- definite progress!

 

Individual items work: I added discounts to 'Die Hard' and it works.

OK, that means that that query in PriceFormatter is now MySQL 5 compatible.
However, if you click on subcategories such as index.php?cPath=3_10 the error's still there.
Yes, but you could and should have fixed that yourself with the November 12 update (see the link on the top of the first page on the forum, leads you in the end to the download) that gives instructions on how to change the queries on the index page to make them MySQL 5 compatible.
Guest

Guest

Posted
Posted
Yeah -- definite progress!

 

Individual items work: I added discounts to 'Die Hard' http://www.spiritedshipper.com/catalog/pro...?products_id=12 and it works.

 

However, if you click on subcategories such as http://www.spiritedshipper.com/catalog/index.php?cPath=3_10 the error's still there.

 

Peter

 

 

Why are you making your life hard? There is an update to fix this problem already. Go get it and install it or change your MySQL to <5.0

petercascio

petercascio

Posted
  • Author
Posted
Why are you making your life hard? There is an update to fix this problem already. Go get it and install it or change your MySQL to <5.0

 

What update? I'm running osCommerce 2.2 Milestone 2 Update 051113 which works fine by itself; it is the Quantity Price Breaks Per Product contrib that is causing the problem.

 

My store is not the only one that is having problems with this -- I found quite a few stores that don't work because of the problem -- so I don't think it is unreasonable to get a solution for this. Just google osCommerce + 1054 to find a whole bunch of broken stores.

 

Finally, I don't think it is reasonable to go back to an older version of MySQL: my host wouldn't do it and I'm sure most others wouldn't either.

 

Peter

Jan Zonjee

Jan Zonjee

Posted
Posted
What update? I'm running osCommerce 2.2 Milestone 2 Update 051113 which works fine by itself; it is the Quantity Price Breaks Per Product contrib that is causing the problem.
Ah, yes, I spoke too soon. The QPBPP contribution also tells you change the queries in index.php, so you changed them back to the MySQL 5 incompatible ones.

 

Actually, it is pretty easy. I don't know how many contributions you already have added, or have a backup from before you installed QPBPP but every time p.products_price, is in the sql, insert after it: p.products_price1, p.products_price2, p.products_price3, p.products_price4, p.products_price5, p.products_price6, p.products_price7, p.products_price8, p.products_price1_qty, p.products_price2_qty, p.products_price3_qty, p.products_price4_qty, p.products_price5_qty, p.products_price6_qty, p.products_price7_qty, p.products_price8_qty, p.products_qty_blocks, and you should be fine.

Archived

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

Go to topic listing
×
×
  • Create New...