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

[theblip]

Hi,

 

I have just install OsC and I am getting errors when I try to click into a subcategory (eg. CD-ROM Drives). Clicking into a Category like 'hardware' is fine but clicking into a subcategory is not.

 

The error I am getting is:

 

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

 

select count(p.products_id) as total 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 = '17'

 

[TEP STOP]

 

 

This is the configuration I am running: Apache/2.0.55 (Win32) , PHP 4.4.1, MySQL 5.0.15-nt

 

I downloaded and installed the osCommerce 2.2 Milestone 2 from the oscommerce site and have not made any modifications to its code.

 

I was hoping that this would have been quite simple to install because it was a milestone release.

 

I've had to increase the 'last_page_url' datatype size or it would give me errors on the first click.

 

Is osCommerce usually this hard to get working properly first time? or am I doing something wrong?

[Guest]

Hi,

 

 

Yesterday, I was testing os commerce rc2 on Linux Debian with Apache/1.3.33 mysql 5.0.13-rc , and I had the same probl?me.

 

Today, I tested os commerce rc2 on a windows XP computer, with easyphp, and it works great.

 

Do you have any solution ?

[MarcoZorro]

Is osCommerce usually this hard to get working properly first time? or am I doing something wrong?

 

Since your using code which was completed 3 years ago with a database system that was only released a week ago.... I wonder why its not a simple install :-"

[gmf]

Since your using code which was completed 3 years ago with a database system that was only released a week ago.... I wonder why its not a simple install :-"

 

Its been in non-production forever, available to everyone.

 

As to the fix.

 

I have the same error on my install, but im running CREreloaded, its built on oscommerce, so this fix SHOULD work.

 

The problem is Mysql 5:

 

"Please notice that from version 5.0.12 on there is a change in

the syntax of JOIN ... ON (currently being documented) that

makes the syntax SQL:2003 compliant. The essense of the

change is that the ON condition of a JOIN ... ON clause may

reference only tables that are in one of the operands of the

JOIN."

 

So you have to change the SQL. I have only changed a few of these, there are a bunch.

Now this MAY not be the same as a regular oscommerce install. If its not, you have to dig around and find the sql and change it.

 

<code>

advanced_search_result.tpl.php

 

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

 

 

/index.php

 

// THERE ARE 3 OTHER SQL STRINGS HERE THAT I DID NOT EDIT, YOU MAY HAVE TO DO IT YOURSELF

 

 

// 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, 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_MANUFACTURERS . " m, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c JOIN ( " . TABLE_PRODUCTS . " p 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 . "'";

} 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, 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 . "'";

 

// NEW SQL FROM ORIGINAL $listing_sql ABOVE

$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 JOIN (" . TABLE_PRODUCTS_TO_CATEGORIES . " p2c JOIN ((" . 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 ))) 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 . "'";

 

</code>

[OEMTF]

Hi! I am new to osCommerce, took over a site for a friend not to long ago. They are still running version 4.0 on that site and I need to know where to go to upgrade to version 5.0. Can someone please lead me in the right direction, I dont know why I cant find it, but I cant......confused