Calculate SELECT statement ORDER BY result [solved]
Posted: Wed Apr 15, 2009 3:50 am
Hi,
Once again I need some MySQL advice.
A small function that calculates a user's ratio depending on how successful his betting has been. What I'm doing now is to calculate the ratio after I have fetched the users which works for a couple of users, but not very good when you have several hundreds or whatever.
I'm now using array_multisort to sort the users by ratio. But I realized that this is not a very good way to go.
Is it a way to calculate: 1 + (($row['userMoney'] - $row['turnover']) / $row['amount']); in my MySQL statement and then sort by that result limit 10?
The toplist is basic:
# name money ratio
Once again I need some MySQL advice.
Code: Select all
function topScorers($db) {
if($link = $db->dbConnect()) {
$sql = "SELECT a.userId, SUM(a.amount) AS amount, c.user_name AS name, c.userMoney, c.turnover
FROM ufc_bet AS a
JOIN user AS c ON a.userId = c.id
JOIN user_details AS d ON a.userid = d.user_id
GROUP BY a.userId";
if($result = mysql_query($sql)) {
//Store Fighter in Array
while ($row = mysql_fetch_assoc($result)) {
$row['ratio'] = 1 + (($row['userMoney'] - $row['turnover']) / $row['amount']);
$user[] = $row;
}
} else $user = mysql_error();
}
return $user;
}
I'm now using array_multisort to sort the users by ratio. But I realized that this is not a very good way to go.
Is it a way to calculate: 1 + (($row['userMoney'] - $row['turnover']) / $row['amount']); in my MySQL statement and then sort by that result limit 10?
The toplist is basic:
# name money ratio