Strange ORDER BY Question [SOLVED]

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
Saethyr
Forum Contributor
Posts: 182
Joined: Thu Sep 25, 2003 9:21 am
Location: Wichita, Kansas USA
Contact:

Strange ORDER BY Question [SOLVED]

Post by Saethyr »

Okay I have a field in my db called MemberLevel in this field you can be one of 5 levels.

Platinum
Gold
Silver
Paying
Non-Paying


now my question is, how would I ORDER BY MemberLevel and get it to come out in the above order? I have been racking my brains for a week on this one and any help will be appriciated.


Saethyr
Last edited by Saethyr on Mon Jan 12, 2004 1:35 pm, edited 2 times in total.
krash_control
Forum Newbie
Posts: 14
Joined: Mon Jan 12, 2004 10:02 am
Location: United Kingdom
Contact:

Post by krash_control »

I don't see any particular order to that, but you could add another single integer field and maybe call it something like intRegLevel (is that right? still getting to grips with PHP) and give each level a number e.g.

Platinum =1
Gold =2
Silver =3
Paying =4
Non-Paying =5

and you can order that by intRegLevel, but it could mean modifying your script to add that extra field :?
User avatar
xisle
Forum Contributor
Posts: 249
Joined: Wed Jun 25, 2003 1:53 pm

Post by xisle »

many ways to do it,
you could also set the field as an integer and have an array of discriptions in your data config file
User avatar
Saethyr
Forum Contributor
Posts: 182
Joined: Thu Sep 25, 2003 9:21 am
Location: Wichita, Kansas USA
Contact:

(SOLUTION)

Post by Saethyr »

Didn't know if anyone else might need this but I was given the solution.

SELECT * FROM tablename
ORDER BY MemberLevel='Platinum' DESC,
MemberLevel='Gold' DESC,
MemberLevel='Silver' DESC,
MemberLevel='Paying' DESC,
MemberLevel='Non-Paying' DESC;
User avatar
mrvanjohnson
Forum Contributor
Posts: 137
Joined: Wed May 28, 2003 11:38 am
Location: San Diego, CA

Post by mrvanjohnson »

That's good as long as you don't add new MemberLevels. Otherwise you either need to go through and update your code when you do add a new member level or you need to make that part dynamic as well.
User avatar
Saethyr
Forum Contributor
Posts: 182
Joined: Thu Sep 25, 2003 9:21 am
Location: Wichita, Kansas USA
Contact:

Post by Saethyr »

this is true, but since this is only in 1 place in my code it is not a big deal to go in and change it. Someone with multiple placements could do several things to remedy the situation.


Saethyr
Post Reply