Jump to content

Code generated column sort


ehmer

Recommended Posts

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
Link to comment
Share on other sites

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

[/code]
Link to comment
Share on other sites

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
Link to comment
Share on other sites

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

Link to comment
Share on other sites

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 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[/code]
And then I'd do the code like this. Switch the 1 and -1 in mysort() to reverse the sort.
[code]<?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>";
}
?>[/code]
Link to comment
Share on other sites

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");
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.