There are several common problems in osCommerce with join syntax when upgrading MySQL from 4.1 to 5.0.x.
The exact fix for each join problem depends of the version of oscommerce being used and the patches or contributions that have been applied.
The general problem is that the comma operator precedence has changed so that list syntax joins can’t be mixed with explicit joins in the same way.
Where this worked previously:
|
1 2 3 4 |
select t1.id , t2.some_column from some_table as t1, another_table as t2 where t1.some_column = t2.some_column |
It now needs to be:
|
1 2 3 4 5 6 |
select t1.id , t2.some_column from some_table as t1 inner join another_table as t2 on t1.some_column = t2.some_column |
We’ve identified the following files that frequently need to be updated:
catalog/index.php
There are four complex queries in this file that need to be updated as follows. The queries are shown in the order that they appear in the code.
PRODUCTS BY MFG + CATEGORY
Change from:
|
1 2 |
$listing_sql = "select " . $select_column_list . " pd.products_notes, p.products_packing_info, pd.products_description, p.products_id, p.products_model, 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, " . TABLE_CATEGORIES_DESCRIPTION . " cd, " . TABLE_CATEGORIES . " c, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id where p.products_status = '1' and c.categories_id = p2c.categories_id and c.categories_id = cd.categories_id and p.manufacturers_id = m.manufacturers_id and m.manufacturers_id = '" . (int)$HTTP_GET_VARS['manufacturers_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)$HTTP_GET_VARS['filter_id'] . "'"; |
to:
|
1 2 3 4 5 6 7 8 9 |
$listing_sql = "select " . $select_column_list . " pd.products_notes, p.products_packing_info, pd.products_description, p.products_id, p.products_model, 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 " . "INNER JOIN " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c ON p.products_id = p2c.products_id " . "INNER JOIN " . TABLE_PRODUCTS_DESCRIPTION . " pd ON pd.products_id = p2c.products_id and pd.language_id = '" . (int)$languages_id . "' " . "INNER JOIN " . TABLE_MANUFACTURERS . " m ON p.manufacturers_id = m.manufacturers_id " . "INNER JOIN " . TABLE_CATEGORIES . " c ON c.categories_id = p2c.categories_id " . "INNER JOIN " . TABLE_CATEGORIES_DESCRIPTION . " cd ON c.categories_id = cd.categories_id " . "LEFT JOIN " . TABLE_SPECIALS . " s on p.products_id = s.products_id " . "where p.products_status = '1' and m.manufacturers_id = '" . (int)$HTTP_GET_VARS['manufacturers_id'] . "' and p2c.categories_id = '" . (int)$HTTP_GET_VARS['filter_id'] . "'"; |
PRODUCTS BY MFG
Change from:
|
1 |
$listing_sql = "select " . $select_column_list . " pd.products_notes, p.products_packing_info, pd.products_description, p.products_id, p.products_model, 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_CATEGORIES_DESCRIPTION . " cd, " . TABLE_CATEGORIES . " c, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c, " . TABLE_MANUFACTURERS . " m left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id where p.products_status = '1' and c.categories_id = p2c.categories_id and c.categories_id = cd.categories_id and p.products_id = p2c.products_id 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:
|
1 2 3 4 5 6 7 8 9 |
$listing_sql = "select " . $select_column_list . " pd.products_notes, p.products_packing_info, pd.products_description, p.products_id, p.products_model, 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 " . "INNER JOIN " . TABLE_PRODUCTS_DESCRIPTION . " pd ON pd.products_id = p.products_id AND pd.language_id = '" . (int)$languages_id . "' " . "INNER JOIN " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c ON p.products_id = p2c.products_id " . "INNER JOIN " . TABLE_CATEGORIES . " c ON c.categories_id = p2c.categories_id " . "INNER JOIN " . TABLE_CATEGORIES_DESCRIPTION . " cd ON c.categories_id = cd.categories_id " . "INNER 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 m.manufacturers_id = '" . (int)$HTTP_GET_VARS['manufacturers_id'] . "'"; |
PRODUCTS BY CATEGORY + MFG
Change from:
|
1 2 |
$listing_sql = "select " . $select_column_list . " pd.products_notes, pd.products_description, p.products_packing_info, p.products_id, p.products_model, 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, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c, " . TABLE_CATEGORIES_DESCRIPTION . " cd, " . TABLE_CATEGORIES . " c left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id where p.products_status = '1' and c.categories_id = cd.categories_id 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 c.categories_id = p2c.categories_id and pd.language_id = '" . (int)$languages_id . "' and (p2c.categories_id = '" . (int)$current_category_id . "' or c.parent_id = '" . (int)$current_category_id . "')"; |
to:
|
1 2 3 4 5 6 7 8 9 |
$listing_sql = "select " . $select_column_list . " pd.products_notes, pd.products_description, p.products_packing_info, p.products_id, p.products_model, 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 " . "INNER JOIN " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c ON p.products_id = p2c.products_id " . "INNER JOIN " . TABLE_PRODUCTS_DESCRIPTION . " pd ON pd.products_id = p2c.products_id AND pd.language_id = '" . (int)$languages_id . "'" . "INNER JOIN " . TABLE_MANUFACTURERS . " m ON p.manufacturers_id = m.manufacturers_id " . "INNER JOIN " . TABLE_CATEGORIES . " c ON c.categories_id = p2c.categories_id " . "INNER JOIN " . TABLE_CATEGORIES_DESCRIPTION . " cd ON c.categories_id = cd.categories_id " . "LEFT JOIN " . TABLE_SPECIALS . " s on p.products_id = s.products_id " . "where p.products_status = '1' and m.manufacturers_id = '" . (int)$HTTP_GET_VARS['filter_id'] . "' and (p2c.categories_id = '" . (int)$current_category_id . "' or c.parent_id = '" . (int)$current_category_id . "')"; |
ALL PRODUCTS
Change from:
|
1 2 |
$listing_sql = "select " . $select_column_list . " pd.products_notes, pd.products_description, p.products_packing_info, p.products_id, p.products_model, 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, " . TABLE_CATEGORIES_DESCRIPTION . " cd, " . TABLE_CATEGORIES . " c left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id where p.products_status = '1' and c.categories_id = cd.categories_id and p.products_id = p2c.products_id and c.categories_id = p2c.categories_id and pd.products_id = p2c.products_id and pd.language_id = '" . (int)$languages_id . "' and (p2c.categories_id = '" . (int)$current_category_id . "' or c.parent_id = '" . (int)$current_category_id . "')"; |
to:
|
1 2 3 4 5 6 7 8 9 |
$listing_sql = "select " . $select_column_list . " pd.products_notes, pd.products_description, p.products_packing_info, p.products_id, p.products_model, 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_TO_CATEGORIES . " p2c " . "INNER JOIN " . TABLE_PRODUCTS_DESCRIPTION . " pd ON pd.products_id = p2c.products_id and pd.language_id = '" . (int)$languages_id . "' " . "INNER JOIN " . TABLE_PRODUCTS . " p ON p.products_id = p2c.products_id " . "LEFT JOIN " . TABLE_MANUFACTURERS . " m on p.manufacturers_id = m.manufacturers_id " . "INNER JOIN " . TABLE_CATEGORIES . " c ON c.categories_id = p2c.categories_id " . "INNER JOIN " . TABLE_CATEGORIES_DESCRIPTION . " cd ON c.categories_id = cd.categories_id " . "LEFT JOIN " . TABLE_SPECIALS . " s on p.products_id = s.products_id " . "where p.products_status = '1' and (p2c.categories_id = '" . (int)$current_category_id . "' or c.parent_id = '" . (int)$current_category_id . "')"; |
catalog/advanced_search_result.php
The search query is formed by too much code to try and really sort out so the easy fix is to find this line:
|
1 |
$from_str = "from " . TABLE_PRODUCTS . " p left join " . TABLE_MANUFACTURERS . " m using(manufacturers_id), " . TABLE_PRODUCTS_DESCRIPTION . " pd left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id, " . TABLE_CATEGORIES . " c, " . TABLE_CATEGORIES_DESCRIPTION . " cd, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c "; |
and change it to:
|
1 |
$from_str = "from " . TABLE_PRODUCTS . " p left join " . TABLE_MANUFACTURERS . " m using(manufacturers_id), " . TABLE_PRODUCTS_DESCRIPTION . " pd left join " . TABLE_SPECIALS . " s on <em>pd</em>.products_id = s.products_id, " . TABLE_CATEGORIES . " c, " . TABLE_CATEGORIES_DESCRIPTION . " cd, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c "; |
Notice that the change is from p.products_id to pd.products_id in the specials table join condition.
catalog/admin/orders.php
From:
|
1 |
$orders_query_raw = "select o.orders_id from " . TABLE_ORDERS . " o, " . TABLE_CUSTOMERS . " c left join " . TABLE_ORDERS_TOTAL . " ot on (o.orders_id = ot.orders_id), " . TABLE_ORDERS_STATUS . " s where c.customers_id = o.customers_id and o.orders_status = s.orders_status_id and s.language_id = '" . (int)$languages_id . "' and ot.class = 'ot_total'"; |
To:
|
1 2 3 4 5 6 |
$orders_query_raw = "select o.orders_id " . "from " . TABLE_ORDERS . " o " . "INNER JOIN " . TABLE_CUSTOMERS . " c ON c.customers_id = o.customers_id " . "left join " . TABLE_ORDERS_TOTAL . " ot on (o.orders_id = ot.orders_id) " . "INNER JOIN " . TABLE_ORDERS_STATUS . " s ON o.orders_status = s.orders_status_id and s.language_id = '" . (int)$languages_id . "' " . "where ot.class = 'ot_total'"; |
From:
|
1 |
$orders_query_raw = "1select c.customers_type, c.customers_shop, o.orders_id, o.customers_id, o.orders_amazon_num, o.customers_name, o.orders_store, o.orders_shipped_date, o.payment_method, o.orders_purchase_number, o.date_purchased, o.last_modified, o.currency, o.currency_value, s.orders_status_name, ot.text as order_total from " . TABLE_ORDERS . " o, " . TABLE_CUSTOMERS . " c left join " . TABLE_ORDERS_TOTAL . " ot on (o.orders_id = ot.orders_id), " . TABLE_ORDERS_STATUS . " s where c.customers_id = o.customers_id and o.orders_status = s.orders_status_id and s.language_id = '" . (int)$languages_id . "' and ot.class = 'ot_total'"; |
To:
|
1 2 3 4 5 6 |
$orders_query_raw = "select c.customers_type, c.customers_shop, o.orders_id, o.customers_id, o.orders_amazon_num, o.customers_name, o.orders_store, o.orders_shipped_date, o.payment_method, o.orders_purchase_number, o.date_purchased, o.last_modified, o.currency, o.currency_value, s.orders_status_name, ot.text as order_total " . "from " . TABLE_ORDERS . " o " . "INNER JOIN " . TABLE_CUSTOMERS . " c ON c.customers_id = o.customers_id " . "left join " . TABLE_ORDERS_TOTAL . " ot on (o.orders_id = ot.orders_id) " . "INNER JOIN " . TABLE_ORDERS_STATUS . " s on o.orders_status = s.orders_status_id and s.language_id = '" . (int)$languages_id . "' " . "where ot.class = 'ot_total'"; |