Topic: Code generated column sort

[ehmer]

I wish to display a series of rows in a table, ordered by the column 'order total'. 'order total' is calculated in PHP rather than being available as a database field, therefore I can't use the 'order by' option in my query.

The code I'm using to display the table rows is:

$userPoints = array();
while ($row = mysql_fetch_assoc($result)) {
$userPoints[$row["username"]][$row["round"]] = $row["points"];
}
foreach($userPoints as $username => $rounds) {
echo "<tr>";
echo "<td>".$username."</td>";
$total = 0;
for($i = 1; $i < 27; $i++) {
echo "<td>".($rounds[$i] ? $rounds[$i] : "-")."</td>";
$total += $rounds[$i];
}
echo "<td><strong>".$total."</strong></td>";
echo "</tr>";
}

Is it possible to order (highest to lowest) the rows using a PHP sort function like arsort?

If so how does that function fit into the code structure above and what is the correct syntax. I'm having trouble finding useful examples of this beyond the very basic intro in the PHP manual.

Thanks
David

[khburres]

If I understand the problem correctly, you would want to first
calculate the totals for each username before outputing your rows.
Make this total part of your $userPoints array ($userPoints[$username]['total']).

Then you should be able to use a PHP sort function that is designed to sort based on the
value in $userPoints[$username]['total'].

After the sort is performed, you can output your rows using the sorted array.

Code: [Select]

//-- totals
foreach($userPoints as $username => $rounds)
{
   $userPoints[$username]['total'] = 0;
   for($i = 0; $i < count($rounds); $i++)
      $userPoints[$username]['total'] += $rounds[$i];
}

//-- sort
usort($userPoints, "desc"); //if you want ascending order, change 'desc' to 'asc'

//-- now print rows in descending order (or ascending)
// {your code here}

//=======================
// Functions
// ------------------------
// can be placed elsewhere
//=======================
function desc($a, $b)
{
    if($a['total'] == $b['total'])
        return 0;
    else
        return ($a['total'] > $b['total']) ? -1 : 1;
}

function asc($a, $b)
{
    if($a['total'] == $b['total'])
        return 0;
    else
        return ($a['total'] < $b['total']) ? -1 : 1;
}



[ehmer]

I've tried unsuccessfully to incorporate your code into what I have now. Is it correct to put your 'foreach' and usort codeblocks before my existing 'foreach' block?

Not sure how my foreach block needs to be modified. I've got varied results experimenting with this including sorting by username index (ie 0-7) and displaying integers in place of the username.

Would appreciate any further help.
Thanks
David

[wickning1]

You can do this on the MySQL end with GROUP BY.  Post your current query and I will modify it for you.

[ehmer]

This is the current query, but the database does not include a total for each user. That is calculated in the code. So I thought the query would not be useful for sorted by total.  

SELECT tips.username, fixtures.round,
SUM(CASE  
WHEN tips.tip='draw' AND fixtures.result='draw' then 1           
WHEN tips.tip=fixtures.result then 1
else 0 end) points
FROM fixtures
JOIN tips on (fixtures.round=tips.round AND fixtures.game=tips.game)
GROUP BY tips.username, fixtures.round
LIMIT 0,50

[wickning1]

I see your point.  I'd still change the query a little bit for clarity, and removing the LIMIT 50, as that will limit you to only the first two users (and the second one will have an incomplete total).

Code: [Select]

SELECT tips.username, tips.round
SUM(tips.tip='draw' AND fixtures.result='draw' OR tips.tip=fixtures.result) as points
FROM fixtures
JOIN tips on (fixtures.round=tips.round AND fixtures.game=tips.game)
GROUP BY tips.username, tips.roundAnd then I'd do the code like this.  Switch the 1 and -1 in mysort() to reverse the sort.

Code: [Select]

<?php
$userPoints = array();
while ($row = mysql_fetch_assoc($result)) {
    $userPoints[$row['username']][$row['round']] = $row['points'];
    $userPoints[$row['username']]['total'] += $row['points'];
}

function mysort ($a, $b) {
    return ($a['total'] == $b['total'] ? 0 : ($a['total'] > $b['total'] ? -1 : 1));
}

usort ($userPoints, "mysort");

foreach($userPoints as $username => $rounds) {
    echo "<tr>";
    echo "<td>".$username."</td>";
    for($i = 1; $i < 27; $i++) {
        echo "<td>".($rounds[$i] ? $rounds[$i] : "-")."</td>";
    }
    echo "<td><strong>".$rounds['total']."</strong></td>";
    echo "</tr>";
}
?>

[ehmer]

That's looking quite promising now.  The sort order is working fine.  I'm still getting the index number for the username column. (ie 0-7 rather than the names)

[ehmer]

It seems to be the usort function which is giving the problems with accessing the username. Not sure what specifically it is yet though.

      function mysort ($a, $b) {
          return ($a['total'] == $b['total'] ? 0 : ($a['total'] > $b['total'] ? -1 : 1));
      }

      usort ($userPoints, "mysort");

[wickning1]

Use uasort() instead of usort().

[ehmer]

Thanks

uasort did the job.

David