I may have figured it out. I hadn't really looked through all of the stuff on GitHub other than a little bit at highway and user data to track changes. But, I did find my way to region.php. And to lib/tmphpfuncs.php, which it calls for tm_fetch_user_row_with_rank among other functions. I'm a C# guy, not PHP, but I was able to muddle my way through it to trace what it was doing.
It looks like the rankings are done with calls to that function and sending in the result set and what column it wants to check. I copied all of the calls so I could keep things straight. The numbers at the end are the line numbers.
$row = tm_fetch_user_row_with_rank($activeRes, 'activePercentage'); 189
$row = tm_fetch_user_row_with_rank($activePreviewRes, 'activePreviewPercentage'); 212
$row = tm_fetch_user_row_with_rank($activeDrivenRes, 'clinchedPct'); 257
$row = tm_fetch_user_row_with_rank($activeDrivenRes, 'drivenPct'); 264
$row = tm_fetch_user_row_with_rank($activePreviewDrivenRes, 'clinchedPct'); 290
$row = tm_fetch_user_row_with_rank($activePreviewDrivenRes, 'drivenPct'); 297
Since they all call the same function, how come 4 out of 6 come out correctly? Here's the while loop in the function.
while($row['traveler'] != $tmuser && $row = $res->fetch_assoc()) {
if ($score != $row[$rankBy])
{
$score = $row[$rankBy];
$rank = $nextRank;
}
$nextRank++;
//error_log("($rank, {$row['traveler']}, {$row[$rankBy]})");
}
It keeps going where there are still rows and it hasn't hit the current user. Both the clinched and driven ranks are found via the same recordset ($activeDrivenRes or $activePreviewDrivenRes). Looking at the ORDER BY in the SQL that it calls for them, they are both sorting on clinchedPct. That works fine for the clinched ranking. But, since it is still sending it in sorted by cliched for the driven, it is counting down until it gets to you but the numbers aren't sorted for driven. I believe that is why the numbers are coming out wrong. There could also be some issues on how it is counting the ranking when there are ties. It really comes down to what order you are in the list when you all have the same clinched route numbers. There is no secondary sort on traveler.
I'm trying to "run" the queries in my head as I don't have your database, but I think you may be able to use the SQL RANK() function to rank everybody in the main query. Then, you could just grab the rank numbers off of the row for the current user to display like you do for the other values instead of iterating through the results in the tm_fetch_user_row_with_rank function. I usually have to play with what columns are in the PARTITION BY and ORDER BY a bit to get it to group the way I want it. I might not have this quite right as I'm just doing the query in my head and on a table with a similar scenario that I have access to. Since we want all of the users in the region sorted, I don't think it needs any PARTITION BY. Here's my attempt on one of the queries with the ranks in bold.
-- $activePreviewDrivenRes (Overall [] Region Statistics - Row 2-3, Right Side)
SELECT traveler,
COUNT(cr.route) AS driven,
SUM(cr.clinched) AS clinched,
ROUND(COUNT(cr.route) / $totalActivePreviewRoutes * 100, 2) as drivenPct,
ROUND(sum(cr.clinched) / $totalActivePreviewRoutes * 100, 2) as clinchedPct,
RANK() OVER (ORDER BY COUNT(cr.route)) AS drivenRank,
RANK() OVER (ORDER BY SUM(cr.clinched)) AS clinchedRank
FROM routes AS r
LEFT JOIN clinchedRoutes AS cr ON cr.route = r.root
LEFT JOIN systems ON r.systemName = systems.systemName
WHERE (r.region = '$region' AND (systems.level='preview' OR systems.level='active'))
GROUP BY traveler
ORDER BY clinchedPct DESC;
Mike
