1064 error in products_new

[Grinse]

Hi there,

 

after I installed a contribution (enable & disable categories) I get the following Error:

1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '*) as total from products p, categories c, products_to_categories p2c left join' at line 1

 

select count(distinct *) as total from products p, categories c, products_to_categories p2c left join manufacturers m on (p.manufacturers_id = m.manufacturers_id), products_description pd where c.categories_status=1 and p.products_id = p2c.products_id and c.categories_id = p2c.categories_id and p.products_status = '1' and p.products_id = pd.products_id and pd.language_id = '2'

 

[TEP STOP]

 

The corresponding code on this page (products_new.php) is:

// Modification: Enable & Disable Categories
 $products_new_query_raw = "select distinct p.products_id, pd.products_name, p.products_image, p.products_price, p.products_tax_class_id, p.products_date_added, m.manufacturers_name from " . TABLE_PRODUCTS . " p, " . TABLE_CATEGORIES . " c, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c left join " . TABLE_MANUFACTURERS . " m on (p.manufacturers_id = m.manufacturers_id), " . TABLE_PRODUCTS_DESCRIPTION . " pd where c.categories_status=1 and p.products_id = p2c.products_id and c.categories_id = p2c.categories_id and p.products_status = '1' and p.products_id = pd.products_id and pd.language_id = '" . (int)$languages_id . "' order by p.products_date_added DESC, pd.products_name";

// Original:
//  $products_new_query_raw = "select p.products_id, pd.products_name, p.products_image, p.products_price, p.products_tax_class_id, p.products_date_added, m.manufacturers_name from " . TABLE_PRODUCTS . " p left join " . TABLE_MANUFACTURERS . " m on (p.manufacturers_id = m.manufacturers_id), " . TABLE_PRODUCTS_DESCRIPTION . " pd where p.products_status = '1' and p.products_id = pd.products_id and pd.language_id = '" . (int)$languages_id . "' order by p.products_date_added DESC, pd.products_name";
// /Modification: Enable & Disable Categories

 

Can someone help me solving this problem, please?

[Teamjr]

Hello,

 

Try this link,

 

www.monikamathe.com/oscommerce/mysql5.txt

 

JR

[Grinse]

Thanks,

 

Unlikely there are no changes for product_new.php mentioned. I use osc 2.2 rc1 that's mysql5 compatible. But the author of enable & disable categories documented his changes in a mysql5 incompatible way.

 

When I remove the distinct i get the follwoing error so it seems as the statement is not mysql5 compatible:

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

select count(*) as total from products p, categories c, products_to_categories p2c left join manufacturers m on (p.manufacturers_id = m.manufacturers_id), products_description pd where c.categories_status=1 and p.products_id = p2c.products_id and c.categories_id = p2c.categories_id and p.products_status = '1' and p.products_id = pd.products_id and pd.language_id = '2'

[TEP STOP]

 

But I don't know what to change in the line.