mysql query help *SOLVED*

[digitalgod]

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 order

clubs
superclubs
afterhours
lounges
dayclubs

any ideas how I can do that?

[AndyB]

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.

[toplay]

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=MyISAM

with 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` ASC

Which would produce the result you want of:

Clubs
Superclubs
Afterhours
Lounges
Dayclubs

That 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.

[digitalgod]

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?

[digitalgod]

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 4
END") 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?

[toplay]

Remove "name," after the ORDER BY

[digitalgod]

I still need it to be sorted alphabeticaly though.. like

type: nightclub

A
B
C

type superclub

A
B
C

etc

[akitchin]

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.

[digitalgod]

nice works perfectly thanks!

[toplay]

[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

[toplay]

[quote author=digitalgod link=topic=100937.msg399618#msg399618 date=1153273766]
I still need it to be sorted alphabeticaly though.. like

type: nightclub

A
B
C

type superclub

A
B
C

etc
[/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

[digitalgod]

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

[akitchin]

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?