DaniWeb IT Discussion Community - View Single Post - Converting queries from MySQL 4 to MySQL 5

Converting queries from MySQL 4 to MySQL 5

Dec 1st, 2008

OK .. I was migrating an application from a staging server where the MySQL version is 4.1.10a to a live server where the MySQL version is 5.0.45. There apparently is quite a difference between query syntax on one versus the other.

For example, in version 4, this worked:

 Help with Code Tags 
 MySQL Syntax (Toggle Plain Text) 
SELECT uas.usr_id_users, uas.airport_id_airport, a.airport_code, a.airport_name, a.airport_id, MAX(if(service_id_service=1, 1, 0)) AS JET, MAX(if(service_id_service=2, 1, 0)) AS GROUND, MAX(if(service_id_service=3, 1, 0)) AS GLYCOL, u.usr_fname AS FIRSTNAME, u.usr_lname AS LASTNAME, u.usr_email AS EMAIL
FROM userairportservices uas, users u LEFT JOIN airport a ON a.airport_id = uas.airport_id_airport
WHERE uas.usr_id_users = usr_id
GROUP BY airport_id_airport LIMIT 0, 30
SELECT uas.usr_id_users, uas.airport_id_airport, a.airport_code, a.airport_name, a.airport_id, MAX(if(service_id_service=1, 1, 0)) AS JET, MAX(if(service_id_service=2, 1, 0)) AS GROUND, MAX(if(service_id_service=3, 1, 0)) AS GLYCOL, u.usr_fname AS FIRSTNAME, u.usr_lname AS LASTNAME, u.usr_email AS EMAIL
FROM userairportservices uas, users u LEFT JOIN airport a ON a.airport_id = uas.airport_id_airport
WHERE uas.usr_id_users = usr_id
GROUP BY airport_id_airport LIMIT 0, 30

However, on version 5 it does not.

Here is what I had to do to make it work:

 Help with Code Tags 
 MySQL Syntax (Toggle Plain Text) 
SELECT uas.usr_id_users, uas.airport_id_airport, a.airport_code, a.airport_name, a.airport_id, MAX( if( service_id_service =1, 1, 0 ) ) AS JET, MAX( if( service_id_service =2, 1, 0 ) ) AS GROUND, MAX( if( service_id_service =3, 1, 0 ) ) AS GLYCOL, u.usr_fname AS FIRSTNAME, u.usr_lname AS LASTNAME, u.usr_email AS EMAIL
FROM users u LEFT JOIN userairportservices uas ON uas.usr_id_users =usr_id LEFT JOIN airport a ON a.airport_id = uas.airport_id_airport
GROUP BY airport_id_airport LIMIT 0 , 30
SELECT uas.usr_id_users, uas.airport_id_airport, a.airport_code, a.airport_name, a.airport_id, MAX( if( service_id_service =1, 1, 0 ) ) AS JET, MAX( if( service_id_service =2, 1, 0 ) ) AS GROUND, MAX( if( service_id_service =3, 1, 0 ) ) AS GLYCOL, u.usr_fname AS FIRSTNAME, u.usr_lname AS LASTNAME, u.usr_email AS EMAIL
FROM users u LEFT JOIN userairportservices uas ON uas.usr_id_users =usr_id LEFT JOIN airport a ON a.airport_id = uas.airport_id_airport
GROUP BY airport_id_airport LIMIT 0 , 30

As an example, here is another query I had that did not work in VER: 5:

 Help with Code Tags 
 MySQL Syntax (Toggle Plain Text) 
SELECT usr_id, usr_access, usr_title, usr_fname, usr_lname, usr_email, access.access, title.title
FROM users INNER JOIN access, title ON access_id = usr_access AND title_id = usr_title
SELECT usr_id, usr_access, usr_title, usr_fname, usr_lname, usr_email, access.access, title.title
FROM users INNER JOIN access, title ON access_id = usr_access AND title_id = usr_title

But, it was suggested by dickersonka to try the following, which worked:

 Help with Code Tags 
 MySQL Syntax (Toggle Plain Text) 
SELECT u.usr_id, u.usr_access, u.usr_title, u.usr_fname, u.usr_lname, u.usr_email, a.access, t.title
FROM users u INNER JOIN access a ON a.access_id = u.usr_access INNER JOIN title t ON t.title_id = u.usr_title
SELECT u.usr_id, u.usr_access, u.usr_title, u.usr_fname, u.usr_lname, u.usr_email, a.access, t.title
FROM users u INNER JOIN access a ON a.access_id = u.usr_access INNER JOIN title t ON t.title_id = u.usr_title

So I post this here not to ask for help, which I have received plenty of here, but in the hopes this helps someone else who runs into this issue.

Cheers,

Dave