MySQL

ORDER BY

Please support our MySQL advertiser: PostgreSQL or MySQL? Compare and contrast the two most popular open source databases

•

Join Date: Nov 2008

Posts: 73

Reputation: filch is an unknown quantity at this point

Solved Threads: 1

filch

Offline

Junior Poster in Training

ORDER BY

Sep 25th, 2009

Hello all,

I have the following SQL statement.

 Help with Code Tags 
 MySQL Syntax (Toggle Plain Text) 
SELECT a.airport_code AS AIRPORTCODE, a.airport_name AS AIRPORT, u.usr_id AS USERID, u.usr_fname AS FIRSTNAME, u.usr_lname AS LASTNAME, u.usr_email AS EMAIL,u.usr_airline AS AIRLINE, u.usr_airline_iatacode AS IATA, t.title AS TITLE
FROM users u LEFT JOIN userairportservices uas ON uas.usr_id_users = u.usr_id LEFT JOIN airport a ON a.airport_id = uas.airport_id_airport LEFT JOIN title t ON t.title_id = u.usr_title
WHERE a.airport_code= apcode
SELECT a.airport_code AS AIRPORTCODE, a.airport_name AS AIRPORT, u.usr_id AS USERID, u.usr_fname AS FIRSTNAME, u.usr_lname AS LASTNAME, u.usr_email AS EMAIL,u.usr_airline AS AIRLINE, u.usr_airline_iatacode AS IATA, t.title AS TITLE
FROM users u LEFT JOIN userairportservices uas ON uas.usr_id_users = u.usr_id LEFT JOIN airport a ON a.airport_id = uas.airport_id_airport LEFT JOIN title t ON t.title_id = u.usr_title
WHERE a.airport_code= apcode

I want to sort by a.airport_name but every time I add ORDER BY a.airport_name ASC to the end of this query, it fails to load the page. Is there an issue with adding and ORDER BY to the end of this statement or should it go somewhere else in it?

Thanks

Dave

Last edited by filch; Sep 25th, 2009 at 1:49 am.

•

Join Date: Jun 2009

Posts: 44

Reputation: guru12 is an unknown quantity at this point

Solved Threads: 6

guru12

Offline

Light Poster

Re: ORDER BY

Sep 25th, 2009

Hi

What is the WHERE a.airport_code= apcode? if any alias for apcode

Joomla Web Design
Joomla Web Development

•

Join Date: Jan 2009

Posts: 1,350

Reputation: almostbob has a spectacular aura about

Solved Threads: 163

almostbob

Offline

Nearly a Posting Virtuoso

Re: ORDER BY

Sep 25th, 2009

try

 Help with Code Tags 
 sql Syntax (Toggle Plain Text) 
SELECT a.airport_code AS airportcode, a.airport_name as airport, u.usr_id as userid, u.usr_fname as firstname, u.usr_lname as lastname, u.usr_email as email,u.usr_airline as airline, u.usr_airline_iatacode as iata, t.title as title FROM users u LEFT JOIN userairportservices uas on uas.usr_id_users = u.usr_id LEFT JOIN airport a on a.airport_id = uas.airport_id_airport LEFT JOIN title t on t.title_id = u.usr_title WHERE airportcode = '%s' ORDER BY airport asc, $apcode
SELECT a.airport_code AS airportcode, a.airport_name as airport, u.usr_id as userid, u.usr_fname as firstname, u.usr_lname as lastname, u.usr_email as email,u.usr_airline as airline, u.usr_airline_iatacode as iata, t.title as title from users u left join userairportservices uas on uas.usr_id_users = u.usr_id left join airport a on a.airport_id = uas.airport_id_airport left join title t on t.title_id = u.usr_title where airportcode = '%s' order by airport asc, $apcode

Last edited by almostbob; Sep 25th, 2009 at 9:45 am.

Failure is not an option It's included free, you don't have to do anything to get it
If at first you dont succeed, join the club
Of course its always in the last place you look, you dont keep looking after you find it

Please mark solved problems, solved

•

Join Date: Nov 2008

Posts: 73

Reputation: filch is an unknown quantity at this point

Solved Threads: 1

filch

Offline

Junior Poster in Training

Re: ORDER BY

Sep 25th, 2009

•

Originally Posted by almostbob

try
Help with Code Tags

sql Syntax (Toggle Plain Text)
SELECT a.airport_code AS airportcode, a.airport_name as airport, u.usr_id as userid, u.usr_fname as firstname, u.usr_lname as lastname, u.usr_email as email,u.usr_airline as airline, u.usr_airline_iatacode as iata, t.title as title FROM users u LEFT JOIN userairportservices uas on uas.usr_id_users = u.usr_id LEFT JOIN airport a on a.airport_id = uas.airport_id_airport LEFT JOIN title t on t.title_id = u.usr_title WHERE airportcode = '%s' ORDER BY airport asc, $apcode
SELECT a.airport_code AS airportcode, a.airport_name as airport, u.usr_id as userid, u.usr_fname as firstname, u.usr_lname as lastname, u.usr_email as email,u.usr_airline as airline, u.usr_airline_iatacode as iata, t.title as title from users u left join userairportservices uas on uas.usr_id_users = u.usr_id left join airport a on a.airport_id = uas.airport_id_airport left join title t on t.title_id = u.usr_title where airportcode = '%s' order by airport asc, $apcode

This has it. It is weird because this is more-or-less what I had and the page just came up blank. turns out that it has nothing to do with the query but something else on the page is cause the page not to render. ""sigh""

Thanks for your help