Jump to content


Photo

Code generated column sort


  • Please log in to reply
9 replies to this topic

#1 ehmer

ehmer
  • New Members
  • Pip
  • Newbie
  • 8 posts

Posted 01 March 2006 - 01:05 PM

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

#2 khburres

khburres
  • Members
  • PipPip
  • Member
  • 12 posts

Posted 02 March 2006 - 03:45 PM

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.

//-- 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;
}


He who guards his mouth and his tongue keeps himself from calamity.

#3 ehmer

ehmer
  • New Members
  • Pip
  • Newbie
  • 8 posts

Posted 03 March 2006 - 01:02 AM

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

#4 wickning1

wickning1
  • Members
  • PipPipPip
  • Advanced Member
  • 405 posts

Posted 03 March 2006 - 01:04 AM

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

#5 ehmer

ehmer
  • New Members
  • Pip
  • Newbie
  • 8 posts

Posted 03 March 2006 - 01:30 AM

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



#6 wickning1

wickning1
  • Members
  • PipPipPip
  • Advanced Member
  • 405 posts

Posted 03 March 2006 - 01:48 AM

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).
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.round
And then I'd do the code like this. Switch the 1 and -1 in mysort() to reverse the sort.
<?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>";
}
?>


#7 ehmer

ehmer
  • New Members
  • Pip
  • Newbie
  • 8 posts

Posted 03 March 2006 - 05:08 AM

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)



#8 ehmer

ehmer
  • New Members
  • Pip
  • Newbie
  • 8 posts

Posted 03 March 2006 - 07:49 AM

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

#9 wickning1

wickning1
  • Members
  • PipPipPip
  • Advanced Member
  • 405 posts

Posted 03 March 2006 - 10:02 PM

Use uasort() instead of usort().

#10 ehmer

ehmer
  • New Members
  • Pip
  • Newbie
  • 8 posts

Posted 04 March 2006 - 06:56 AM

Thanks

uasort did the job.

David




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users