Converting Osc shop from MySQL 4 to MySQL 5

[deliwasista]

Ok so ive read and re read posts on how to start changing files so my shop will work in MySQL5 and I really want to try do this myself. I have downloaded this great contribution - However php is not my forte I am the first to admit - so some of the instances I see of left join do not fit the bill of other people replies.

 

I really want to understand what is required so that I can the get in there and fix any additional 1054 errors that crop up in the contributions I have installed.

 

 

Can someone just give me a heads up as to whether I understand what to change .. before I go and screw up multiple files :-"

 

I know that I need to wrap multiple table declarations prior to a LEFT JOIN in parenthesis.

 

so if I understand correctly a double instance of left join like-->

 

$from_str = "from " . TABLE_PRODUCTS . " p left join " . TABLE_MANUFACTURERS . " m using(manufacturers_id) left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id";

 

becomes-->

 

$from_str = "from ((" . TABLE_PRODUCTS . " p )left join " . TABLE_MANUFACTURERS . " m using(manufacturers_id) )left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id";

 

 

 

and then -->

 

 

$specials_query_raw = "select A.products_id, B.products_name, A.products_price, C.specials_id, C.specials_new_products_price, C.specials_date_added, C.specials_last_modified, C.expires_date, C.date_status_change, C.status, F.special_id from (" .TABLE_PRODUCTS . " A, " . TABLE_PRODUCTS_DESCRIPTION . " B , products_to_categories E ) left join " . TABLE_SPECIALS . " C on C.products_id = A.products_id left join special_product F on A.products_id = F.product_id left join special_category D on E.categories_id = D.categ_id where E.categories_id = $categ_id and E.products_id = A.products_id and A.products_id = B.products_id and B.language_id = '" . (int)$languages_id . "' order by F.special_id, C.specials_id, B.products_name ";

 

 

... becomes:

 

 

$specials_query_raw = "select A.products_id, B.products_name, A.products_price, C.specials_id, C.specials_new_products_price, C.specials_date_added, C.specials_last_modified, C.expires_date, C.date_status_change, C.status, F.special_id from (" .TABLE_PRODUCTS . " A, " . TABLE_PRODUCTS_DESCRIPTION . " B , products_to_categories E ) left join ((" . TABLE_SPECIALS . " C on C.products_id = A.products_id) left join special_product F on A.products_id = F.product_id) left join special_category D on E.categories_id = D.categ_id where E.categories_id = $categ_id and E.products_id = A.products_id and A.products_id = B.products_id and B.language_id = '" . (int)$languages_id . "' order by F.special_id, C.specials_id, B.products_name ";

 

 

any insight into this would be most appreciated. Its frustrating when one minor lack of understanding prevents one from just trucking on :)

[deliwasista]

Can someone who knows a little about this issue please give me some direction?

 

im quite keen to try fix this. I want to learn how to make the changes myself. I have downloaded the contribution but it is still not 100% clear to me.

 

 

 

Does-->

 

from " .TABLE_PRODUCTS . " A, " . TABLE_PRODUCTS_DESCRIPTION . " B , products_to_categories E left join " . TABLE_SPECIALS . "C on C.products_id = A.products_id left join special_product F on A.products_id = F.product_id left join special_category D on E.categories_id = D.categ_id where E.categories_id = $categ_id and E.products_id = A.products_id and A.products_id = B.products_id and B.language_id = '" . (int)$languages_id . "' order by F.special_id, C.specials_id, B.products_name ";

 

 

then become-->

 

 

from (((" . TABLE_PRODUCTS . " A, " . TABLE_PRODUCTS_DESCRIPTION . " B , products_to_categories E)left join " . TABLE_SPECIALS . "C on C.products_id = A.products_id) left join special_product F on A.products_id = F.product_id) left join special_category D on E.categories_id = D.categ_id where E.categories_id = $categ_id and E.products_id = A.products_id and A.products_id = B.products_id and B.language_id = '" . (int)$languages_id . "' order by F.special_id, C.specials_id, B.products_name ";

 

 

 

anybody? :-"

[MrPhil]

You're not understanding it. () are needed only if you have a comma-separated list before a JOIN. What happened is that with version 5, MySQL came into conformance with SQL standards. The JOIN is now of higher precedence than the comma operator. See http://www.oscommerce.com/forums/topic/335136-osc-and-mysql-5-1054-errors for details.

 

$from_str = "from " . TABLE_PRODUCTS . " p left join " . TABLE_MANUFACTURERS . " m using(manufacturers_id) 
left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id";

needs no parentheses. There are no commas.

 

from (((" .TABLE_PRODUCTS . " A, " . TABLE_PRODUCTS_DESCRIPTION . " B , products_to_categories E) 
left join " . TABLE_SPECIALS . "C on (C.products_id = A.products_id)) left join special_product F 
on (A.products_id = F.product_id)) left join special_category D on (E.categories_id = D.categ_id) 
where E.categories_id = $categ_id and E.products_id = A.products_id and A.products_id = B.products_id and 
B.language_id = '" . (int)$languages_id . "' order by F.special_id, C.specials_id, B.products_name ";

is the proper parentheses added.

[deliwasista]

Once again thank you Mr Phil :)

 

Right .. so I need to learn how to spot a comma seperated list! Which sounds fairly self explanatory

 

I also notice you have added some additional parenthesis around some other items on the list

 

eg:

C on C.products_id = A.products_id left join

 

has now become

 

C on (C.products_id = A.products_id)) left join

 

what is the rule there?

 

I will go back and revert all the files I have changed and have another go :)

[MrPhil]

I find it clearer to wrap "on" clauses in parentheses -- it's not really necessary. It just makes what's going on more visible.

 

I also tend to wrap all MySQL 4 style comma-separated table lists in parentheses, even if a table isn't used in an "on" clause. While not always necessary, I figure "better safe than sorry" -- the list of tables will now be interpreted exactly as it was in MySQL 4 (comma operator and JOIN at same precedence), just as a little extra margin of safety.

 

In both cases, other people will code in a different way. It's a matter of style, not of absolute requirements.

[deliwasista]

so Im trying now to figure out what a comma separated list is - google isnt really helping.

 

Would it be:

 

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

 

and this one?

 

    $customers_query = tep_db_query("select c.customers_id, c.customers_gender, c.customers_firstname, c.customers_lastname, c.customers_dob, c.customers_email_address, a.entry_company, a.entry_street_address, a.entry_suburb, a.entry_postcode, a.entry_city, a.entry_state, a.entry_zone_id, a.entry_country_id, c.customers_telephone, c.customers_fax, c.customers_newsletter, c.customers_default_address_id from " . TABLE_CUSTOMERS . " c left join " . TABLE_ADDRESS_BOOK . " a on c.customers_default_address_id = a.address_book_id where a.customers_id = c.customers_id and c.customers_id = '" . (int)$HTTP_GET_VARS['cID'] . "'");

[deliwasista]

actually - ive just taken the leap and changed over my servers to MySQL5 - and sooo far so good! so perhaps I only needed the changes mentioned for index.php and advanced_search_result.php

 

lol, ive put this off for months becuase I thought it was some highly complicated.

 

Further testing will confirm 100% if any of my contributions are effected - I certainly hope not!

[MrPhil]

... from " . TABLE_PRODUCTS . " p left join " . TABLE_MANUFACTURERS . " m on (p.manufacturers_id = m.manufacturers_id), " . TABLE_PRODUCTS_DESCRIPTION . " pd where...

has tables p LEFT JOIN m, pd which is a comma-separated list of tables. However, it's after the JOIN, so nobody cares. The change in MySQL 5 is that

... FROM a, b LEFT JOIN c...

is interpreted as

... FROM a, (b LEFT JOIN c)...

rather than the desired (MySQL 4 and earlier) behavior of

... FROM (a, B) LEFT JOIN c...

Thus, the parentheses added around the list.

 

... from " . TABLE_CUSTOMERS . " c left join " . TABLE_ADDRESS_BOOK . " a on c.customers_default_address_id = a.address_book_id where ...

has no commas of interest to this problem:

 ... FROM c LEFT JOIN a ...

 

Not every set of parentheses I gave in my referenced thread are absolutely necessary, but they shouldn't hurt, and do clarify for the reader how things are being grouped by precedence.

[deliwasista]

Awesome thanks MrPhil - there may be some glitches in my set up that have yet to rear their ugly heads - so will be pouring over this page in the near future again and again no doubt! - thank you so much for taking the time to help clarify this issues for me - MUCH appreciated! :D

[♥geoffreywalton]

Try reading this, http://addons.oscommerce.com/info/4654, it is a really simple explanation.

 

HTH

 

G