Jump to content

Archived

This topic is now archived and is closed to further replies.

ehmer

Code generated column sort

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

Share this post


Link to post
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]

Share this post


Link to post
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

Share this post


Link to post
Share on other sites
You can do this on the MySQL end with GROUP BY. Post your current query and I will modify it for you.

Share this post


Link to post
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

Share this post


Link to post
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]

Share this post


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

Share this post


Link to post
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");

Share this post


Link to post
Share on other sites

×

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.