Sort MySQL query in PHP

I’m trying to sort different users ‘points’ by descending order (highest first). But at the moment the query is being returned in order of the user’s ID (order they appear in the database). I’m not sure what is wrong with my code?

The user can be in multiple leagues, so it first queries to see what leagues that the particular user is in. With the league IDs, I query to see what users are in each of the leagues. Then I query what each users total points are within that league. Ultimately, I want to get the rank of the user for each league but at the moment the query to order by the points is not working.

The image shows how the points are coming out. ‘1635’ is the users points that is logged in. For the first league, I’m trying to get ‘rank 2’ displayed.

// SQL query to see what leagues user is in
    $query = mysqli_query($con, "SELECT * FROM UserLeague WHERE UserID='$userid'");
    $num = mysqli_num_rows($query);

    if($num == 0) {
      echo 'You are not in any leagues';
      return;
    } else {
      echo '<div class="pleague-table">';
      echo '<div class="pleague-table-header">';
      echo '<p>PRIVATE LEAGUE</p>';
      echo '<p>CURRENT RANK</p>';
      echo '</div>';
    }

    while($leagueid = mysqli_fetch_assoc($query)) {

        $lid = $leagueid['LeagueID'];

        // Get all league info that user is in

        $query2 = mysqli_query($con, "SELECT * FROM League WHERE LeagueID='$lid'");

        // Get all users that is in each league

        $queryposition = mysqli_query($con, "SELECT UserID FROM UserLeague WHERE LeagueID='$lid'");

        while($getpoints = mysqli_fetch_assoc($queryposition)) {

          $uid = $getpoints['UserID'];

          // Get each users points in each league

          $querypoints = mysqli_query($con, "SELECT * FROM Points WHERE UserID='$uid' ORDER BY total DESC");
          while($row = mysqli_fetch_assoc($querypoints)) {
            echo $row['total']. '</br>';
          }


        }



        while($leaguename = mysqli_fetch_assoc($query2)) {
          echo '<div class="league-link">';
          echo $leaguename['Name'];
          echo '<a href="#">Options</a>';
          echo '</div>';
        }

    }
'''

Answer

You are trying to combine 2 tables: UserLeague and Points to select users from UserLeague and order them by Points. For such cases there is JOIN syntax in SQL:

SELECT Points.* 
FROM Points 
RIGHT JOIN UserLeague ON Points.UserID=UserLeague.UserID 
WHERE UserLeague.LeagueID=? 
ORDER BY Points.total DESC

Advertisement

Answer