millsy007 Posted February 28, 2009 Share Posted February 28, 2009 Hi I have some results that I would like to order but I am not sure how this is done.I have a query to bring out a list of passengers on a journey. To make it clearer for the user I would like to be able to order the way in which the contents of a particular field are shown. Currently the passengers are shown in the passenger_name field in a seemingly random way: departs spaces passengers 2009-02-27 10:00:00 4 lee | rob | scott | paul 2009-02-27 10:25:00 4 lee | paul | scott |rob 2009-02-27 10:55:00 7 lee For clarity I would like to order the contents of this field alphabetically: departs spaces passengers 2009-02-27 10:00:00 4 lee | paul | rob | scott 2009-02-27 10:25:00 4 lee | paul | rob | scott 2009-02-27 10:55:00 7 lee I know there is an order by clause but I think that this just orders all the results vertically as opposed to within the actual field? This is my sql: (SELECT tb2.journey_dttm as departure, ('8' - tb2.occupancy) AS spaces, (SELECT IF(GROUP_CONCAT(passenger_name SEPARATOR '|') IS NULL, '', GROUP_CONCAT(passenger_name SEPARATOR '|')) FROM passengers WHERE journey_id = tb2.id) AS passengers FROM shuttle AS tb1 LEFT JOIN journey AS tb2 ON ( tb1.id = tb2.shuttle_id ) LEFT JOIN trip_route AS tb3 ON ( tb2.route_id = tb3.id ) WHERE tb1.depart_dttm = '$depart_dttm' ORDER BY tb2.id ASC, tb2.route_id ASC) Is there something I can add to achieve what I need? Quote Link to comment https://forums.phpfreaks.com/topic/147292-solved-ordering-contents-of-a-field-alphabetically/ Share on other sites More sharing options...
corbin Posted February 28, 2009 Share Posted February 28, 2009 I would normalize the schema a bit. Something like: CREATE TABLE flights ( flight_id int NOT NULL AUTO_INCREMENTING PRIMARY KEY, departure_time datetime NOT NULL, spaces smallint ); CREATE TABLE flight_passengers ( flight_id int NOT NULL, passenger_name VARCHAR(255) ); Then it would be easier to extract data. (You could go one step farther and have a passengers table with IDs mapped to passengers then map flight IDs to passenger IDs.) I can try to help you do it your way though, if you want to stick with what you have. Quote Link to comment https://forums.phpfreaks.com/topic/147292-solved-ordering-contents-of-a-field-alphabetically/#findComment-773318 Share on other sites More sharing options...
fenway Posted February 28, 2009 Share Posted February 28, 2009 group concat can take an order by! Quote Link to comment https://forums.phpfreaks.com/topic/147292-solved-ordering-contents-of-a-field-alphabetically/#findComment-773320 Share on other sites More sharing options...
corbin Posted February 28, 2009 Share Posted February 28, 2009 Really? Wow.... Never knew that. Edit: Oh, I just realized that I think I entirely misunderstood the OP's table layout.... So pretty much just ignore my posts in this thread ;p. Quote Link to comment https://forums.phpfreaks.com/topic/147292-solved-ordering-contents-of-a-field-alphabetically/#findComment-773336 Share on other sites More sharing options...
millsy007 Posted February 28, 2009 Author Share Posted February 28, 2009 Hi thanks for the posts, this is pretty much the last issue I have with my program so would preferably not like to change too much. But it sounds like there isnt an 'easy' way to order the values in the passenger_name field? Quote Link to comment https://forums.phpfreaks.com/topic/147292-solved-ordering-contents-of-a-field-alphabetically/#findComment-773476 Share on other sites More sharing options...
fenway Posted March 1, 2009 Share Posted March 1, 2009 Hi thanks for the posts, this is pretty much the last issue I have with my program so would preferably not like to change too much. But it sounds like there isnt an 'easy' way to order the values in the passenger_name field? Did you try using ORDER BY inside the GROUP_CONCAT() ??? Quote Link to comment https://forums.phpfreaks.com/topic/147292-solved-ordering-contents-of-a-field-alphabetically/#findComment-773703 Share on other sites More sharing options...
millsy007 Posted March 1, 2009 Author Share Posted March 1, 2009 I tried: (SELECT tb1.depart_dttm as departure, tb3.route_desc as route, ('7' - tb2.occupancy) AS spaces, (SELECT IF(GROUP_CONCAT(passenger_name SEPARATOR '|') IS NULL, '', GROUP_CONCAT(passenger_name SEPARATOR '|')) FROM passengers WHERE journey_id = tb2.id ORDER BY passenger_name) AS passengers FROM shuttle AS tb1 LEFT JOIN journey AS tb2 ON ( tb1.id = tb2.shuttle_id ) LEFT JOIN trip_route AS tb3 ON ( tb2.route_id = tb3.id ) WHERE DATE(tb1.depart_dttm) = '2009-01-30' ORDER BY tb2.id ASC, tb2.route_id ASC) Quote Link to comment https://forums.phpfreaks.com/topic/147292-solved-ordering-contents-of-a-field-alphabetically/#findComment-773737 Share on other sites More sharing options...
Mchl Posted March 1, 2009 Share Posted March 1, 2009 How about GROUP_CONCAT(passenger_name ORDER BY passenger_name SEPARATOR '|') Quote Link to comment https://forums.phpfreaks.com/topic/147292-solved-ordering-contents-of-a-field-alphabetically/#findComment-773746 Share on other sites More sharing options...
millsy007 Posted March 1, 2009 Author Share Posted March 1, 2009 Yes that does it, I was adding the order by to the wrong part, cheers Quote Link to comment https://forums.phpfreaks.com/topic/147292-solved-ordering-contents-of-a-field-alphabetically/#findComment-773783 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.