Sorting Both MySQL Columns and PHP Arrays

Question

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.

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
– Tim Biegeleisen, Commented Apr 2, 2015 at 4:28
The database engine was designed for, among other things, sorting data. — Tim Biegeleisen
– Tim Biegeleisen, Commented Apr 2, 2015 at 4:34
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
– mattrick, Commented Apr 2, 2015 at 5:04

AkiEru · Accepted Answer · 2015-04-02 04:26:57Z

2

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.

edited Apr 2, 2015 at 4:26

answered Apr 2, 2015 at 4:24

AkiEru

7881 gold badge9 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

ReallyMadeMeThink · Accepted Answer · 2015-04-02 04:24:37Z

-1

SELECT *,(kills_column/deaths_column) AS kdratio FROM stats ORDER BY kdratio DESC

answered Apr 2, 2015 at 4:24

ReallyMadeMeThink

1,0818 silver badges13 bronze badges

1 Comment

Tim Biegeleisen Over a year ago

You absolutely need to check for division by zero in your query.

Collectives™ on Stack Overflow

Sorting Both MySQL Columns and PHP Arrays

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related