CODEWITHSUNDEEP
phpmysqlmath
Mansa
Mansa

Reputation: 2325

Putting math into the sql query

I am trying to create an golf live leaderboard which is based on how many points a player has equal to the ideal points.

The ideal points is 2 points for each hole. So if a player has played 3 holes he should have 6 points. If the player has 6 points the players points would be 0. If the player has earned more he would have +points and less -points.

The issue here is that the players would be on different holes, some have played 2, some 3 and maybe some have played 7. So somehow I need to count total points, count number of holes and subtract them I think and then order by the subtracted.

Here is what i got so far:

$result = mysqli_query($con,"SELECT SUM(ls.points) points
                                                 , COUNT(ls.fk_playerid) holes
                                                 , ls.fk_playerid
                                                 , u.fname
                                                 , u.lname 
                                    FROM ".$prefix."_livescore ls
                                         INNER JOIN ".$prefix."_users u
                                            ON ls.fk_playerid = u.userid
                                    WHERE fk_gameid=$gameid GROUP BY fk_playerid ORDER BY points DESC");
while($row = mysqli_fetch_array($result)){

    $idealpoints = $row['points'] - ($row['holes'] * 2);
    $players[] = array('uid' => $row['fk_playerid'], 'name' => $row['fname'].' '.$row['lname'], 'hole' => $row['holes'], 'points' => $idealpoints);

}

In this example I just count the points and order by it. And then in the loop I figure out what the ideal points is. This makes the leaderboard incorrect because the players who has played the most holes most likely would be in the lead...

Hope this makes sense and are hoping for help... :-)

Upvotes: 0

Views: 62

Answers (2)

Nurp
Nurp

Reputation: 1489

I'm not quite sure if you are asking for a formula to make a correct ranking or if you just want a way to have your existing formula in a sql expression. I'll go with the first assumption.

The formula is almost right. You also need to divide your $idealpoints with the number of holes played, like this:

$idealpoints = ($row['points'] - ($row['holes'] * 2)) / $row['holes'];

Thus, you will get a decimal value that your ranking can be based on. The lower the value - the better ranking; like this example:

Player 1 has 5 points after 2 holes (+1 than ideal) == (5 - 4) /2 = 0,5 idealpoints.
Player 2 has 7 points after 3 holes (+1 than ideal) == (7 - 6) /3 = 0,33 idealpoints.

Player 2 will be ranked higher because he is only +1 after three holes. Player 1 is +1 already after two holes.

Upvotes: 0

Barmar
Barmar

Reputation: 780842

SELECT fk_playerid, fname, lname, points, holes, points-2*holes diff
FROM (
    SELECT  SUM(ls.points) points
            COUNT(ls.dk_playerid) holes,
            ls.fk_playerid, u.fname, u.lname
    FROM {$prefix}_livescore ls
    INNER JOIN {$prefix}_users u
    ON ls.fk_playerid = u.userid
    WHERE fk_gameid = $gameid
    GROUP BY fk_playerid) x
ORDER BY diff desc

Upvotes: 2

Related Questions