MySQL custom sort

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
User avatar
Todd_Z
Forum Regular
Posts: 708
Joined: Thu Nov 25, 2004 9:53 pm
Location: U Michigan

MySQL custom sort

Post by Todd_Z »

How can i include into a mysql select sql a custom sort... for example

A+, A, A-, A-/B+, B+, B, B-, etc.

And also, I would like to alphabetize results from a sql based on the string after a possible (60)... for example:

Hannibal
(60) Hero
Hybrid Theory
User avatar
Skara
Forum Regular
Posts: 703
Joined: Sat Mar 12, 2005 7:13 pm
Location: US

Post by Skara »

I'd sort them after fetching them. I'd also store the (60) as a separate boolean column.
Will that not work?
User avatar
Todd_Z
Forum Regular
Posts: 708
Joined: Thu Nov 25, 2004 9:53 pm
Location: U Michigan

Post by Todd_Z »

It's true, it would work -> It's also a good idea, I may convert them.

About the sort after the fetch, I have a sort method at the moment, but I feel like it could be a lot faster in the mysql query itself.
User avatar
onion2k
Jedi Mod
Posts: 5263
Joined: Tue Dec 21, 2004 5:03 pm
Location: usrlab.com

Post by onion2k »

Store the mark in a seperate table and use a join to get the text. That way your mark's will be INTs, and easily sortable. EG

Code: Select all

Results table:
RESULT_ID NAME
1         A+
2         A
3         A-
4         B+

Marks table:
ID NAME         RESULT_ID
1  John Smith   2
2  Fred Bloggs  4
3  Freda Jones  1

SQL:
select m.*, r.name as result_name from marks m left join results r on m.result_id = r.result_id order by m.result_id

ID NAME         RESULT_ID RESULT_NAME
3  Freda Jones  1         A+
1  John Smith   2         A
2  Fred Bloggs  4         B+
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post by timvw »

might want to try

Code: Select all

SELECT *
FROM foo
ORDER BY FIELD(column, 'A+', 'A-', 'B+')
Post Reply