Project Detail

Description

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

I have recently installed a cre loaded v6.2 template on my server (simlar to oscommerce) only to find that it is not compatable with the current version of mySQL running on the server.

Problem:
MySQL 5.0 introduces Server SQL modes as part of its SQL 2003 standards support, and uses a more stricter approach to executing SQL queries. This is performed by default with setting STRICT_TRANS_TABLES as a Server SQL mode.

Due to this new setting, MySQL fails on certain SQL queries and produces error messages on the screen.

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

select count(distinct p.products_id) as total from products p left join manufacturers m using(manufacturers_id), products_description pd left join specials s on p.products_id = s.products_id, categories c, products_to_categories p2c where p.products_status = '1' and p.products_id = pd.products_id and pd.language_id = '2' and p.products_id = p2c.products_id and p2c.categories_id = c.categories_id and ((pd.products_name like '%1001%' or p.products_model like '%1001%' or m.manufacturers_name like '%1001%') )

TEP_DB_ERRORR

I am looking to pay for a valid fix or work around. Please see the one below - that does not work because Part of the work around requires that:
Lines 213-223 in advanced_search_result.php must be changed from:
$from_str = "from " ...
to:
$from_str = "from " . TABLE_PRODUCTS . " p left join " . TABLE_MANUFACTURERS ...

However, lines 213-223 in advanced_search_result.php, don't exist on my cre loaded template advanced_search_result.php - Do you know where I can
locate these lines of code, and or how I can modify the template to work with mySQL 5.0


Running on

MySQL 5.0.22
HP Version 4.4.4
CRE Loaded6 v6.2 Pro with patch:10.1