current community

your communities

more stack exchange communities

Stack Exchange
tour help
stack overflow careers
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 1 down vote favorite

I've recently been making a stats system that reads from a database of players and sorts them in various ways. As you can see it works great, but my main problem being is that I need to sort the "Games Played" and "K/D Ratio" columns. But these columns are not stored in the MySQL database, the "Games Played" column is calculated by adding the "Wins" and "Losses" column. Likewise, the "K/D Ratio" column is calculated by dividing "Kills" by "Deaths".

I am using this to sort stats: SELECT * FROM stats ORDER BY <filter> DESC LIMIT 100 OFFSET <index> However, I also need to sort the two columns mentioned above. What would be the best way to go about this? I'm trying to avoid moving it to an array, but if it is unavoidable, I suppose I'll have to.

share|improve this question
    
Why not use javascript and let the client sort the list on its own? –  jdo 1 hour ago
1  
You definitely want to try to do the sorting in the database, as this will be exponentially faster than sorting in the JavaScript engine. –  Tim Biegeleisen 58 mins ago
    
Faster for whom? –  jdo 56 mins ago
    
The database engine was designed for, among other things, sorting data. –  Tim Biegeleisen 52 mins ago
1  
You're forcing the client to do it rather than the server to do it, which is bad practice. Also, either way it would need to query the server for each sort, which is redundant –  mattrick 22 mins ago

2 Answers 2

up vote 2 down vote accepted 
SELECT *, (Wins + Losses) AS GamesPlayed, (Kills / IFNULL(Deaths, Kills)) AS KDRatio FROM stats ORDER BY GamesPlayed DESC LIMIT 100 OFFSET <index>

You don't even needed to calculate the total games played in PHP, MySQL can be done for you and also usable for sorting.

share|improve this answer
    
Thank you, this was very helpful :) –  mattrick 39 mins ago
up vote -1 down vote 
SELECT *,(kills_column/deaths_column) AS kdratio FROM stats ORDER BY kdratio DESC
share|improve this answer
    
You absolutely need to check for division by zero in your query. –  Tim Biegeleisen 1 hour ago

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.