Upgraded to MYSQL 5

[MR1]

Hi to all members of osCommerce,

 

I have just upgraded to MYSQL 5, i got some errors, but i got rid of them by searching, but this error i cant find a solution to, can some one please here take some time and explain to me how to go on about this problem,

 

The problem is:-

 

When i go to my catalog and then click on a product and when i click on "PDF Data Sheet Maker" it comes up with this error,

 

-------------------------------------------------------------------------------------------------------------

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

 

select p.products_id, pd.products_name, pd.products_description, p.products_image, p.products_model, p.products_price, p.products_tax_class_id, p.products_date_added, p.products_date_available, p.products_status, IF(s.status, s.specials_new_products_price, NULL) as specials_new_products_price, s.expires_date, m.manufacturers_name from products p left join manufacturers m on p.manufacturers_id = m.manufacturers_id, products_description pd left join specials s on p.products_id = s.products_id where p.products_id = '176' and p.products_id = pd.products_id and pd.language_id = '1'

 

[TEP STOP]

---------------------------------------------------------------------------------------------------------------

 

I know its because of MYSQL5 because it used to work on the old one, but not on this one.

 

Thank you for your time reading this and replying

MR1

[Jack_mcs]

Take a look at this.

 

Jack

[MR1]

Hi Jack thanks for your reply, do i need to follow these steps?

 

For 1. I altered line 186 in index.php from:

 

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

 

to

 

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

 

 

To make it clearer, I change the query from:

 

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 [sNIP irrelevant where 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 >>>p2c<<<.products_id = s.products_id

WHERE [sNIP irrelevant where clause]

 

 

The second query had a similar issue,

 

I changed line 177 from:

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

 

to:

 

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

 

If i do this should my PDF Data Sheet work?

 

Thanks

MR1

[MR1]

As far as i know it seems like its not for my problem, can you please help me out, I had a PDF Datasheet maker , and i have just moved to another hosting which use MYSQL 5, i had some errors which i got rid of em, and got it working, but the ONLY problem which is left is this, so can some one please take some time and help me out, if any one wants to see the error, i am more than happy to give it,

 

Thank you - for reading this post and help.

 

MR1

[Jack_mcs]

That link only shows the code change for the index file. You have to make that change to any sql statement that is causing the problem.

 

Jack

[Nick Weisser]

I've just uploaded the MySQL5 fix to the contributions website.