Leaderboard, sorting issues

Question

Program Ideal:

A user signs up a team with our program, and then enters the amount of miles they walk per day/week/etc.. Also, during the program we host small events. The events will have fliers with QR codes, and the user scanns the code, and enters in their information. Once they do that the user gets credit for being at that event. The website has a point system based off of what events they attend, and how many miles they walk.

Situation:

I have 2 tables that store two different variables that need to sum them up, and then per each one use a division to determine the point value for that sum, and then place it into a leader board table, and place it in descending order by points earned.

Currently, it sorts them in descending order, by miles walked.

Any ideals on how would I fix this? I was thinking maybe some sort of array magic, where I store each user's info into an array and then pull it out, but I have no ideal on how to even begin on that one.

Things to note:

The users name is stored is stored in the table: Team_Assign

The user's miles walked is stored in the table: Miles

The user's attended event is stored in the table: Events

I inner join these three tables together.

Example:

10 miles is 1 point and 2 events is 1 point.

John has 10 miles walked, and 4 events, which would be 3 points Jane has 20 miles and 1 event, which would be 2.5 points

Currently, it is sorting by who has the most miles, and I understand why it is, but I do not know how to redo the query to have it sort by points which is established during the while loop.

It should show:

John 3

Jane 2.5

instead it shows:

Jane 2.5

John 3

Code:

<?php
$Count = 0;
$LB_User_Query = "  SELECT Miles_User, Team_Assign_Name, SUM(Miles_Amount) AS 'Miles', SUM(Event_Count) AS 'Event_Count2'
                    FROM Team_Assign 
                    INNER JOIN Miles ON  Team_Assign_User = Miles_User
                    INNER JOIN Event ON Team_Assign_User = Event_User 
                    GROUP BY Miles_User, Team_Assign_User, Team_Assign_Name
                    ORDER BY Miles, Event_Count2 Desc";
$LB_User_stmt = $conn->prepare($LB_User_Query);
$LB_User_stmt->execute();                           
$LB_User_stmt->bind_result($Miles_User, $Team_Assign_Name, $Miles, $Event_Count2);

while (mysqli_stmt_fetch($LB_User_stmt)) 
{
    $User_Miles = $Miles;
    $Count = $Count + 1;

    $Miles_Points_indc = 10;
    $Event_Points_indc = 1;

    $Miles_Points_Total_1 = $User_Miles / $Miles_Points_indc;
    $Miles_Points_Total_2 = $Miles_Points_Total_1 / $Event_Count2;
    $Event_Points_Total =  $Event_Count2 / $Event_Points_indc;

    $Total_Points = $Miles_Points_Total_2 + $Event_Points_Total;
    $Total_Points_Round = round($Total_Points, 2);

    echo'<tr>';
            echo'<td>'.$Count.'</td>
                 <td>'.$Team_Assign_Name.'</a></td>
                 <td>'.$Total_Points_Round .'</td>';
    echo'</tr>';
    }
?>

Answer 1

You need to apply a separate desc modifier to each column you sort by:

$LB_User_Query = "  SELECT Miles_User, Team_Assign_Name, SUM(Miles_Amount) AS 'Miles', SUM(Event_Count) AS 'Event_Count2'
                    FROM Team_Assign 
                    INNER JOIN Miles ON  Team_Assign_User = Miles_User
                    INNER JOIN Event ON Team_Assign_User = Event_User 
                    GROUP BY Miles_User, Team_Assign_User, Team_Assign_Name
                    ORDER BY Miles DESC, Event_Count2 Desc";
# missing here --------------------^