digitalgod Posted July 17, 2006 Share Posted July 17, 2006 Hey guys,was just wondering how can I query a database and order by 'type' but I don't want it to be alphabetical, I want to have them ordered in this orderclubssuperclubsafterhoursloungesdayclubsany ideas how I can do that? Quote Link to comment Share on other sites More sharing options...
AndyB Posted July 18, 2006 Share Posted July 18, 2006 Check Barand's reply in http://www.phpfreaks.com/forums/index.php/topic,100799.0.html You should be able to adapt that for your query. Quote Link to comment Share on other sites More sharing options...
toplay Posted July 18, 2006 Share Posted July 18, 2006 Yes, that's one way. Another approach is to design the table(s) in the first place with this requirement in mind.You could have your establishment/clubs table defined with a numeric sort_order column and use that to sort with.Let's say you have this table created:CREATE TABLE `club_types` ( `id` mediumint(9) NOT NULL auto_increment, `club_type` varchar(35) NOT NULL default '', `sort_order` smallint(6) NOT NULL default '0', PRIMARY KEY (`id`)) TYPE=MyISAMwith these values:INSERT INTO `club_types` (`id`, `club_type`, `sort_order`) VALUES (1, 'Clubs', 100)INSERT INTO `club_types` (`id`, `club_type`, `sort_order`) VALUES (2, 'Superclubs', 200)INSERT INTO `club_types` (`id`, `club_type`, `sort_order`) VALUES (3, 'Afterhours', 300)INSERT INTO `club_types` (`id`, `club_type`, `sort_order`) VALUES (4, 'Lounges', 400)INSERT INTO `club_types` (`id`, `club_type`, `sort_order`) VALUES (5, 'Dayclubs', 500)Then you could run a query like this:SELECT `club_type` FROM `club_types` ORDER BY `sort_order` ASCWhich would produce the result you want of:ClubsSuperclubsAfterhoursLoungesDayclubsThat way if you ever want to change the sort order, it would be a matter of changing the sort order value in the table, rather than having to change the code. For instance, to make "Superclubs" be listed after "Lounges", you would simply change the "200" value to something like "450".hth. Quote Link to comment Share on other sites More sharing options...
digitalgod Posted July 18, 2006 Author Share Posted July 18, 2006 thanks guys,toplay, wouldn't I have to add a second query to pull out the club names in the order of the club_types? Which way takes less ressources, having a switch/case statement in the query or having 2 tables with 2 queries? Quote Link to comment Share on other sites More sharing options...
digitalgod Posted July 19, 2006 Author Share Posted July 19, 2006 ok tried Barand's way and it didn't work at all... here's my query[code]<?php$_SESSION['display_clubs'] = mysql_query("SELECT * FROM ".$prefix."clubs ORDER BY name, CASE WHEN type='nightclub' THEN 0 WHEN type='superclub' THEN 1 WHEN type='after-hours' THEN 2 WHEN type='lounge' THEN 3 WHEN type='dayclub' THEN 4END") or die(query_error());//other page$display_result = $_SESSION['display_clubs'];while ($clubs_allrow = mysql_fetch_array($display_result)) { echo '<div style="margin-left:10px; margin-top:10px "><img src="images/square1.jpg" align="absmiddle" style="margin-right:5px "><strong class="light_gray">'.$clubs_allrow['name'].'</strong></div> <div style="margin-left:9px; margin-top:7px; width:354px "><img src="../img/clubs/'.$clubs_allrow['name'].'/thumbnails/'.$clubs_allrow['logo'].'" align="left" style="margin-right:13px "> <div>Type: '.$clubs_allrow['type'].'</div> <div style="margin-top:7px " align="right"></div> </div> <div style="margin-left:8px; margin-top:10px; margin-right:8px; height:1px; background-image:url(images/dot.jpg) "><img src="images/spacer.gif"></div>'; }?>[/code]and it still shows me the clubs by alphabetical order... any ideas why? Quote Link to comment Share on other sites More sharing options...
toplay Posted July 19, 2006 Share Posted July 19, 2006 Remove "name," after the ORDER BY Quote Link to comment Share on other sites More sharing options...
digitalgod Posted July 19, 2006 Author Share Posted July 19, 2006 I still need it to be sorted alphabeticaly though.. liketype: nightclubABCtype superclubABCetc Quote Link to comment Share on other sites More sharing options...
akitchin Posted July 19, 2006 Share Posted July 19, 2006 then place the name portion of the ORDER BY after the CASE statement. not sure how syntactically correct that would be, never used CASE statements. Quote Link to comment Share on other sites More sharing options...
digitalgod Posted July 19, 2006 Author Share Posted July 19, 2006 nice works perfectly thanks! Quote Link to comment Share on other sites More sharing options...
toplay Posted July 19, 2006 Share Posted July 19, 2006 [quote author=digitalgod link=topic=100937.msg399443#msg399443 date=1153253869]thanks guys,toplay, wouldn't I have to add a second query to pull out the club names in the order of the club_types? Which way takes less ressources, having a switch/case statement in the query or having 2 tables with 2 queries?[/quote]Since I don't know what you're doing exactly it's hard to advice.I was coming from a place of incorporating requirements into the design, database normalization, future growth/enhancements and maintainability, practicality, etc.If one had a lot of club types, it's impractical to list them all in a "case" within each query. It becomes silly if it should grow and one has 10, 20, 50, 100+ club types.http://en.wikipedia.org/wiki/Database_normalization Quote Link to comment Share on other sites More sharing options...
toplay Posted July 19, 2006 Share Posted July 19, 2006 [quote author=digitalgod link=topic=100937.msg399618#msg399618 date=1153273766]I still need it to be sorted alphabeticaly though.. liketype: nightclubABCtype superclubABCetc[/quote]When posting please try and specify all the specific details right up front so members on this forum can help you better.As you can see akitchin gave you the answer.It's a matter of what you want things sorted by 1st, 2nd, 3rd, 4th, etc. You list them in that order.http://dev.mysql.com/doc/refman/4.1/en/select.html Quote Link to comment Share on other sites More sharing options...
digitalgod Posted July 19, 2006 Author Share Posted July 19, 2006 yeah I know what you mean, and this was just for testing purposes so I might end up doing it your way.I was just wondering how would the 2nd query look like, since the first query only gets the the club types, should it be stored in an array and then use that array in the 2nd query?thanks guys, toplay, sorry about that, getting very tired and I guess I need some fresh air hehe Quote Link to comment Share on other sites More sharing options...
akitchin Posted July 19, 2006 Share Posted July 19, 2006 with toplay's method, you wouldn't actually need to run a second query. you'd simply need to grab some extra info during the query and make a simple "join":[code]SELECT clubs.name AS name, clubs.otherstuff AS otherstuff, club_types.sort_order AS sort FROM clubs, club_types WHERE clubs.type = club_types.club_type ORDER BY sort ASC, name ASC[/code]you'd just want to make sure that each type used in `clubs` has an equivalent entry in `club_types`. as toplay said, in the long run if you expect to have a large number of club types, this is far more manageable.ps: welcome back toplay - haven't seen you here for a while. am i just blind, or were you actually gone? Quote Link to comment 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.