Searching and ordering based on 2 parts of a MySQL Database
I'm not sure if this should be in the Server Side Scripting forum, or here, because it is written in PHP but using MySQL.
Anyways, I have a script that reads from a MySQL table and writes the result to an HTML table.
In the table, I have 6 columns, 2 of which are games and win percent.
I'm trying to order the HTML table by these criteria: The user must have 5 games played, and the highest win % of those with over 5 games is first. If they dont' have 10 games, then they are at the bottom of the ranks. Any idea how to do this??
Thanks
Mark Hensler posted this at 05:00 — 6th December 2001.
He has: 4,048 posts
Joined: Aug 2000
I'm a bit confused about that... can you make some mock data for us to see?
nike_guy_man posted this at 21:05 — 6th December 2001.
They have: 840 posts
Joined: Sep 2000
OK...
user password email record percent
user1 password1 [email protected] 4-1 80
user2 passworda [email protected] 4-6 40
user3 pass [email protected] 3-0 100
user4 password [email protected] 6-0 100
user5 password [email protected] 1-2 67
I want it to count the total of the split record column, and if that is more than 5, order it by the percent column
So it would be ranked
user4
user1
user2
user3
user5
Even though user3 has a higher win percent, he hasn't competed 5 games so he doesn't qualify for the top position, unless no one else has any games played
Another thing, when I have users report that they lost, I want it to add 1 to the 2nd part of the record which I have split into the array $s so I want it to add 1 to $s[1], and add 1 to the opponents first part of record, which is $x[0]
However, when I run the script, it adds 1 to both $s[0] $s[1] and $x[0] and $x[1]
Any idea?
Mark Hensler posted this at 00:09 — 7th December 2001.
He has: 4,048 posts
Joined: Aug 2000
I would have had a win field and a loss field... if you could change that now, that would be best.
If not, you could try something like this:
SELECT *
FROM table_name
WHERE ( SUBSTRING_INDEX(record, '-', 1) + SUBSTRING_INDEX(record, '-', -1) ) >= 5
ORDER BY percent ASC
SELECT *
FROM table_name
WHERE ( SUBSTRING_INDEX(record, '-', 1) + SUBSTRING_INDEX(record, '-', -1) ) < 5
ORDER BY percent ASC
Mark Hensler
If there is no answer on Google, then there is no question.
nike_guy_man posted this at 01:57 — 7th December 2001.
They have: 840 posts
Joined: Sep 2000
Actually, now that that has been brought up, win columns and loss columns would make this a lot easier
Should I simply add the results of win column and loss column together and if thats > 5, then order by %s...
Great!
I'll try all this out, and come back when I make a stupid mistake!
This should be my sql query, correct?
<?php$sql = \"SELECT user, win, loss, percent FROM $table ORDER BY percent ASC \";
mysql_query($sql);
?>
Then how do I parse that into tables? a while loop?
Mark Hensler posted this at 06:18 — 7th December 2001.
He has: 4,048 posts
Joined: Aug 2000
SELECT user, win, loss, percent, if((win+loss)>=5,1,0) as sectionFROM table_name
ORDER BY section ASC, percent ASC
mySQL Docs:
6.3.1.4 Control Flow Functions (info on IF statements)
Mark Hensler
If there is no answer on Google, then there is no question.
nike_guy_man posted this at 21:30 — 7th December 2001.
They have: 840 posts
Joined: Sep 2000
That works great
)
But what if a user has less than 5 games?
I want those with less than 5 games still at the end, and ordered by their percent... Should I simply order it by percent DESC after that?? (I forgot, %s should be descending as the best percent is the highest
Mark Hensler posted this at 00:03 — 8th December 2001.
He has: 4,048 posts
Joined: Aug 2000
ORDER BY section ASC, percent DESC
That should work. section=1 is people with over 5 games. section=0 is for people with under 5 games. So this will sort by section first (5+ games at top), then percentage (highest percent at top).
Mark Hensler
If there is no answer on Google, then there is no question.
Want to join the discussion? Create an account or log in if you already have one. Joining is fast, free and painless! We’ll even whisk you back here when you’ve finished.