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

[Lonny]

Hi all

Running Oscmax v2.0 RC3 and my host decided to upgrade to MySQL5 without warning!

As a result I have problems with the search function on my site. The error message I get is:

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

select count(distinct p.products_id) as total from (products p left join products_to_products_extra_fields p2pef on p.products_id=p2pef.products_id) left join manufacturers m on m.manufacturers_id=p.manufacturers_id, products_description pd left join specials s on p.products_id = s.products_id, categories c, products_to_categories p2c, products_extra_fields pef where p.products_status = '1' and p.products_id = pd.products_id and pd.language_id = '1' and p.products_id = p2c.products_id and p2c.categories_id = c.categories_id and ((pd.products_name like '%grey%' or p.products_model like '%grey%' or m.manufacturers_name like '%grey%' or p2pef.products_extra_fields_value like '%grey%') ) and find_in_set('0', products_hide_from_groups) = 0 and find_in_set('0', categories_hide_from_groups) = 0

I have tried every fix I can find but nothing seems to work - the files I worked on were index.php and advanced_search_result.tpl.php

I also have the products extra fields contribution installed.

There was a problem with the articles but I managed to fix that with no problems.

Can anyone help as this is driving me nuts!

Thanks!

[Lonny]

Don't worry - fixed it myself.

It was in the advanced_search_result.tpl.php round line 132 in my version. Here is what I did in case anyone else has this problem:

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