Page 1 of 1

Calculate SELECT statement ORDER BY result [solved]

Posted: Wed Apr 15, 2009 3:50 am
by papa
Hi,

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;
}
 
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

Re: Calculate SELECT statement ORDER BY result

Posted: Wed Apr 15, 2009 4:27 pm
by papa
A lot easier than I thought.. :)

Code: Select all

 
function topScorers($db) {
    if($link = $db->dbConnect()) {
        $sql = "SELECT a.userId, 1+(c.userMoney - c.turnover) / SUM(a.amount) AS ratio, c.user_name AS name, c.userMoney 
                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 ORDER BY ratio, c.userMoney DESC LIMIT 10";
 
        if($result = mysql_query($sql)) {
            //Store Fighter in Array
            while ($row = mysql_fetch_assoc($result)) {
                $user[] = $row;
            }
        } else $user = mysql_error();
    }
    return $user;
}