osCommerce MySQL 5 Fixes

[RFH Reseller: dv8host]

Hi Everybody,

I know that everybody has probably got their copies of osCommerce working again now that RFH have updated to MySQL 5, but just in case you haven't here are the fixes that I have had to use to get mine working.

They are:

1st Fix – Index.php

Change lines 183 – 187 to:

// 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_DESCRIPTION . " pd, " . TABLE_MANUFACTURERS . " m, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c JOIN ( " . TABLE_PRODUCTS . " p 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, " . 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 . "'";
 
// NEW SQL FROM ORIGINAL $listing_sql ABOVE
$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 JOIN (" . TABLE_PRODUCTS_TO_CATEGORIES . " p2c JOIN ((" . 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 ))) 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 . "'"; 

2nd Fix - advanced_search_results.php

Change lines 213 to:

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

3rd Fix – index..php

Change lines 178 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 . " 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'] . "'"; 

4th Fix – Run query on MySQL database

ALTER TABLE `{DATABASE_NAME_HERE}`.`customers` MODIFY COLUMN `customers_default_address_id` INTEGER NOT NULL DEFAULT 1;

5th Fix – Run query on MySQL database

 
ALTER TABLE `{DATABASE_NAME_HERE}`.`customers_basket` MODIFY COLUMN `final_price` DECIMAL(15,4) NOT NULL DEFAULT 0;
 
ALTER TABLE `{DATABASE_NAME_HERE}`.`orders_products` MODIFY COLUMN `final_price` DECIMAL(15,4) NOT NULL DEFAULT 0;

6th Fix – includes/application_top.php

Change lines 383 – 389 to:

for ($i=0, $n=sizeof($notify); $i<$n; $i++) {
if (strlen($notify[$i])>0) {
$check_query = tep_db_query("select count(*) as count from " . TABLE_PRODUCTS_NOTIFICATIONS . " where products_id = '" . $notify[$i] . "' and customers_id = '" . $customer_id . "'");
$check = tep_db_fetch_array($check_query);
if ($check['count'] < 1) {
tep_db_query("insert into " . TABLE_PRODUCTS_NOTIFICATIONS . " (products_id, customers_id, date_added) values ('" . $notify[$i] . "', '" . $customer_id . "', now())");
}
}
} 

Hope that helps everybody and saves them the hours that it took me to work it out!

Regards,

Chris

[RFH Reseller: dv8host]

I forgot to say that if anybody else knows of any changes that I have forgotten about I would appreciate it if they would let me know because i haven't tested every aspect of the osCommerce system to make sure that it is still working.

Thanks.

Chris