Need to perform an equation before pulling results

[RickyFord]

Hey guys,

 

I'm new here and so far the community looks like quite an invaluable resource when it comes to my daily PHP needs. I'm hoping my newness to the forum won't scare off any potential help I can receive here because I'm at quite a stand-still in my development process.

 

I'm currently working on a text-based browser game and am stuck. What I am doing is displaying a leaderboard. In my database I am holding battle stats such as the user's current wins and their current losses in two different rows. So, we've got a table layout like the following:

 

users_id, users_username, users_password, users_battle_w, users_battle_l, users_active

 

...where users_battle_w and users_battle_l are the user's battle wins and losses respectively. Well, to get to their win percentage I'm adding the two together and then dividing the total number of their battles by the wins and multiplying by 100, easy multiplication there. And that's easy to do on the PHP side when displaying their profile.

 

However, the issue is coming when I'm trying to display the leaderboard. I need to sort that leaderboard by the percentage. So that the mysql_query call needs to have that silly little "order by ____" clause with the "percentage" at the end of it, not the users_battle_w or the users_battle_l because who cares if a user has won 100 battles if they've lost 1,000,000. They obviously aren't very good. It's all about the percentage.

 

Can anyone help me out in coming up with an elegant solution in pulling out these records and doing this equation on the MySQL end before displaying them on-page in PHP?

 

Thanks a ton!

Ricky

Edited December 30, 2013 by RickyFord

[Barand]

SELECT users_username

, users_battle_w

, users_battle_l

, users_battle_w * 100 / (users_battle_w + users_battle_l) as winpcent

FROM stats

ORDER BY winpcent DESC

[RickyFord]

SELECT users_username
, users_battle_w
, users_battle_l
, users_battle_w * 100 / (users_battle_w + users_battle_l) as winpcent
FROM stats
ORDER BY winpcent DESC

Quick, easy and straight to the point! Thanks a million!

 

This brings up one more question though, which I didn't even think of before until just now. Is there one more "stipulation" I could add to the query which would make it so that maybe it would order the list by win percentage as well as the number of battles they've been in? So let's say this scenario..

 

We have user1 who was in 1 fight and he won it, his win percentage is 100%.

 

Then we have user2 who was in 10 fights and lost 1 of those fights, which makes his win percentage 90%.

 

Well, user1, on paper, looks like a better fighter because his percentage is higher, but yet user2 has won more fights than user1 has, so in reality, user2 should be ranked higher than user1. Is there a way to maybe factor in two "order by" clauses so that we first order by number of fights and then we order by the win percentage? Does that make sense?

 

Thank you so much for your quick response! If the second part of my question isn't able to be done, at least I'll know that the first part was solvable.

Edited December 30, 2013 by RickyFord

[Barand]

do you mean

SELECT users_username
, users_battle_w
, users_battle_l
, users_battle_w + users_battle_l as fights
, users_battle_w * 100 / (users_battle_w + users_battle_l) as winpcent
FROM stats
ORDER BY fights DESC, winpcent DESC

[RickyFord]

 

do you mean

SELECT users_username
, users_battle_w
, users_battle_l
, users_battle_w + users_battle_l as fights
, users_battle_w * 100 / (users_battle_w + users_battle_l) as winpcent
FROM stats
ORDER BY fights DESC, winpcent DESC

Hmm.. I'll keep that bookmarked and use it once I get a little more data built up from some players. Thank you VERY much for your responses! I appreciate them very much!