Jump to content

mysql query help *SOLVED*


digitalgod

Recommended Posts

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.

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

[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
Link to comment
Share on other sites

[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

Link to comment
Share on other sites

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
Link to comment
Share on other sites

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?
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.