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

[mmontalbano]

I have the same template installed on two seperate servers

 

www.yorkpahosting.com/oscommerce27 and www.i-pintura.com

 

The working version is on a host using MySQL 4.1.22 and the non working is using 5.0.27

 

I have made the necessary modifications to the index.php file and the advanced_search_results.php files as references here:

http://www.oscommerce.com/forums/index.php?sho...p;hl=1054+error

 

I still get the same error after making these changes. What am I missing that I need to change?

 

I appreciate your help!

[Nullachtfuffzehn]

The script which causes the error most likely contains a SQL query with a left join that needs to be rewritten in order to work on PHP5.

[mmontalbano]

The script which causes the error most likely contains a SQL query with a left join that needs to be rewritten in order to work on PHP5.

 

Any idea where that may be located? I could copy and paste whatever is needed to be looked at in order to fix.

[Nullachtfuffzehn]

Since I don't know where that error occurs, I can't tell you where that query is located. You need to give us some more detailed informations when the error comes up.

[mmontalbano]

Since I don't know where that error occurs, I can't tell you where that query is located. You need to give us some more detailed informations when the error comes up.

 

The error comes up only on the front page. I looked in column_right.php to see if commenting out any boxed would fix it but it didn't. I would think it is probably an index.php issue still? The only erro info I can post here is what I see on the front page of www.i-pintura.com

[Nullachtfuffzehn]

Most likely it is one of your boxes, I guess the new products box, which contains the query.

[mmontalbano]

Most likely it is one of your boxes, I guess the new products box, which contains the query.

 

There isn't a new products box inside the boxes folder. Here is the contents of column_right.php

 

<?php

/*

$Id: column_right.php,v 1.17 2003/06/09 22:06:41 hpdl Exp $

 

osCommerce, Open Source E-Commerce Solutions

http://www.oscommerce.com

 

Copyright © 2003 osCommerce

 

Released under the GNU General Public License

*/

 

require(DIR_WS_BOXES . 'shopping_cart.php');

 

if (isset($HTTP_GET_VARS['products_id'])) include(DIR_WS_BOXES . 'manufacturer_info.php');

 

if (tep_session_is_registered('customer_id')) include(DIR_WS_BOXES . 'order_history.php');

 

if (isset($HTTP_GET_VARS['products_id'])) {

if (tep_session_is_registered('customer_id')) {

$check_query = tep_db_query("select count(*) as count from " . TABLE_CUSTOMERS_INFO . " where customers_info_id = '" . (int)$customer_id . "' and global_product_notifications = '1'");

$check = tep_db_fetch_array($check_query);

if ($check['count'] > 0) {

include(DIR_WS_BOXES . 'best_sellers.php');

} else {

include(DIR_WS_BOXES . 'product_notifications.php');

}

} else {

include(DIR_WS_BOXES . 'product_notifications.php');

}

} else {

include(DIR_WS_BOXES . 'best_sellers.php');

}

 

if (isset($HTTP_GET_VARS['products_id'])) {

if (basename($PHP_SELF) != FILENAME_TELL_A_FRIEND) include(DIR_WS_BOXES . 'tell_a_friend.php');

} else {

include(DIR_WS_BOXES . 'specials.php');

}

 

require(DIR_WS_BOXES . 'reviews.php');

 

if (substr(basename($PHP_SELF), 0, 8) != 'checkout') {

include(DIR_WS_BOXES . 'languages.php');

include(DIR_WS_BOXES . 'currencies.php');

}

?>

[Nullachtfuffzehn]

The query is located in your index.php and submitted to one of your boxes.

 

It is the last of the four $listing_sql definitions and should look like 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_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 . "'";

[mmontalbano]

The query is located in your index.php and submitted to one of your boxes.

 

It is the last of the four $listing_sql definitions and should look like 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_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 . "'";

 

I was able to find that query. Here it is. Anything that needs changed?

 

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

[Nullachtfuffzehn]

The query in your index.php is wrong. You can comment that line out and insert the one that I've posted to check if the error disappeares then. But I'm sure it will.

[mmontalbano]

The query in your index.php is wrong. You can comment that line out and insert the one that I've posted to check if the error disappeares then. But I'm sure it will.

 

I changed that line to yours and the problem still exists. Is it possible one of the other 3 are still incorrect? Here is the code for all 4 reflecting your new code in the 4th query

 

 

// show the products of a specified manufacturer

if (isset($HTTP_GET_VARS['manufacturers_id'])) {

if (isset($HTTP_GET_VARS['filter_id']) && tep_not_null($HTTP_GET_VARS['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, 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'] . "'";

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

}

} else {

// show the products in a given categorie

if (isset($HTTP_GET_VARS['filter_id']) && tep_not_null($HTTP_GET_VARS['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, 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 . "'";

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

[Nullachtfuffzehn]

Those four queries seem to be correct and can't cause the error. So a similar query must be hidden somewhere else in your files. I checked my vanilla test installation, but the index.php was the only place where this query was. You may use a tool like Agent Ransack to search the entire directory for the statement 'as final_price' to find the culprit.

[mmontalbano]

Those four queries seem to be correct and can't cause the error. So a similar query must be hidden somewhere else in your files. I checked my vanilla test installation, but the index.php was the only place where this query was. You may use a tool like Agent Ransack to search the entire directory for the statement 'as final_price' to find the culprit.

 

I have a program. I'll search right now and see what I find.

[mmontalbano]

Those four queries seem to be correct and can't cause the error. So a similar query must be hidden somewhere else in your files. I checked my vanilla test installation, but the index.php was the only place where this query was. You may use a tool like Agent Ransack to search the entire directory for the statement 'as final_price' to find the culprit.

 

Only other place I see that statement is in advance_search_result.php line 201 and it reads:

 

$select_str = "select distinct " . $select_column_list . " m.manufacturers_id, p.products_id, pd.products_name, 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 ";

 

 

That help at all?

[Nullachtfuffzehn]

That's a different select statement. I know it may be a dumb question, but did you already upload and overwrite the index.php with the changed statement? Cause if there is no other place where such a statement occurs, it can only be the index.php file.

[mmontalbano]

That's a different select statement. I know it may be a dumb question, but did you already upload and overwrite the index.php with the changed statement? Cause if there is no other place where such a statement occurs, it can only be the index.php file.

 

Only changes I made to index.php were the ones mentioned in this post:

 

http://www.oscommerce.com/forums/index.php?sho...p;hl=1054+error

 

Then you took a look and had me change the 4th query. That is where it stands right now on the site.

 

When I search the index.php right now for the statement as final_price it is currently in 5 spots. It's in the 4 queries we've been looking at and then again on line 209 here:

 

$listing_sql = "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 = '".$current_category_id."' order by p.products_date_added DESC limit 2";

[Nullachtfuffzehn]

Ok, so finally we got that beast. It should look like this:

 

$listing_sql = "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 left join specials s on p.products_id = s.products_id, 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 = '1' and p2c.categories_id = '".$current_category_id."' order by p.products_date_added DESC limit 2";

[mmontalbano]

Ok, so finally we got that beast. It should look like this:

 

$listing_sql = "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 left join specials s on p.products_id = s.products_id, 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 = '1' and p2c.categories_id = '".$current_category_id."' order by p.products_date_added DESC limit 2";

 

YOU ARE THE ABSOLUTE MAN! FIXED! I have no friggin idea how you did that but it worked. Now I have to figure out what the change is in the two areas we changed so I can learn from it. I copied and pasted without paying attention to what you actually changed. So if my Host actually switched MySQL versions to 5.xxx any sites I host might have these issues?? Not looking forward to that. Guess more reason for me to learn what you did here.

 

Thanks a ton man! You stuck with me all day and got this fixed. I am truely appreciative!

 

Matt

[Nullachtfuffzehn]

It's just the order in which the tables to retrieve the data from appear in the SQL statement when using nested left joins. I put the products_to_categories table behind the last left join. Before it was behind the first left join which caused the error.

[mmontalbano]

Ok, so finally we got that beast. It should look like this:

 

$listing_sql = "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 left join specials s on p.products_id = s.products_id, 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 = '1' and p2c.categories_id = '".$current_category_id."' order by p.products_date_added DESC limit 2";

 

 

If I'm looking at this right here was the change to this last query that did the trick - removed the bolded section? That it?

 

$listing_sql = "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 = '".$current_category_id."' order by p.products_date_added DESC limit 2";

[mmontalbano]

It's just the order in which the tables to retrieve the data from appear in the SQL statement when using nested left joins. I put the products_to_categories table behind the last left join. Before it was behind the first left join which caused the error.

 

 

Ok I see. You added it back in there after the second join. Nice. I would have never found that. Thanks!!!!!