Question 21614 in Mysql Question posted in
The official MySQL documentation can be found here.

How to calculate rank of the player with most money? – Mysql

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)");
Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

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

     <table class="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>

  2. 0

    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
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search