How to calculate rank of the player with most money?-CodePudding

I am building a leaderboard which is built by joining two different databases, the most important is the wallet column which is what I'll use to rank the players. I need to add a column ranked from the player with most money to least amount of money. What is the best way to do this?

$result = mysqli_query($conn, "SELECT * FROM darkrp_player inner join playerinformation on (darkrp_player.uid = playerinformation.uid)");

CodePudding user response：

Basically, you need to order by on your wallet column to short for showing most money to least amount of money of the user i.e DESC ordering. But if you want to get the rank number value of each user also apart from ordering you can do it this way with ROW_NUMBER() window function

SELECT *, rank FROM (
  SELECT *, ROW_NUMBER() OVER (ORDER BY wallet DESC) as rank
  FROM darkrp_player INNER JOIN playerinformation ON (darkrp_player.uid = playerinformation.uid)
) t

Expected Output: on $result variable

id    other........columns      wallet rank
1111  xyz............abc         100    1 
2222  xyz............abc          90    2
3333  xyz............abc          80    3

CodePudding user response：

alright these work! here's my code for anyone that might across something similar

 <table  data-order='[[ 0, "asc" ]]'>
    <thead>
        <tr>
            <th>Place</th>
            <th>Avatar</th>
            <th>Name</th>
            <th>Salary</th>
            <th>Wallet</th>
        </tr>
    </thead>

    <tbody>
<?php
$conn = mysqli_connect("bla bla bla"); 
$result = mysqli_query($conn, "SELECT * FROM darkrp_player inner join playerinformation on (darkrp_player.uid = playerinformation.uid) ORDER BY wallet DESC");

$rank = 1;
 while  ($row = mysqli_fetch_assoc($result)):
?>
<?php
$steamid2 = $row['steamID'];    
$slice = substr($steamid2, strpos($steamid2, ":")   1);    
$n = substr($slice, 0, 1);
$x = substr($slice, strpos($slice, ":")   1);
$steamid64 = 76561197960265728   2 * $x   $n;
$json = file_get_contents('http://api.steampowered.com/ISteamUser/GetPlayerSummaries/v0002/?key=keykeykeykeykeyk&steamids='.$steamid64.'');
$parsed = json_decode($json);

?>
 <tr id="rank<?php echo $rank  ; ?>">
<td><?php echo $rank - 1; ?></td>
<td><?php foreach($parsed->response->players as $player){
    echo "<a href=" . $player->profileurl . "><img src='" . $player->avatarmedium . "'></a>";
} ?></td>
<td><?php foreach($parsed->response->players as $player){
    echo "<a href=" . $player->profileurl . ">" . $player->personaname . "</a>";
} ?></td>
            <td><?php echo $row['salary']; ?></td>
            <td><?php echo $row['wallet']; ?></td>
         <?php endwhile; ?>
        </tr>


    </tbody>
</table>
<link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/v/dt/jq-3.6.0/dt-1.13.1/datatables.min.css"/>
<script type="text/javascript" src="https://cdn.datatables.net/v/dt/jq-3.6.0/dt-1.13.1/datatables.min.js"></script>

<script>
$(".table").DataTable();
</script>