ehmer Posted March 1, 2006 Share Posted March 1, 2006 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.ThanksDavid Quote Link to comment https://forums.phpfreaks.com/topic/3824-code-generated-column-sort/ Share on other sites More sharing options...
khburres Posted March 2, 2006 Share Posted March 2, 2006 If I understand the problem correctly, you would want to firstcalculate 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 thevalue in $userPoints[$username]['total'].After the sort is performed, you can output your rows using the sorted array.[code]//-- totalsforeach($userPoints as $username => $rounds){ $userPoints[$username]['total'] = 0; for($i = 0; $i < count($rounds); $i++) $userPoints[$username]['total'] += $rounds[$i];}//-- sortusort($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] Quote Link to comment https://forums.phpfreaks.com/topic/3824-code-generated-column-sort/#findComment-13574 Share on other sites More sharing options...
ehmer Posted March 3, 2006 Author Share Posted March 3, 2006 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.ThanksDavid Quote Link to comment https://forums.phpfreaks.com/topic/3824-code-generated-column-sort/#findComment-13756 Share on other sites More sharing options...
wickning1 Posted March 3, 2006 Share Posted March 3, 2006 You can do this on the MySQL end with GROUP BY. Post your current query and I will modify it for you. Quote Link to comment https://forums.phpfreaks.com/topic/3824-code-generated-column-sort/#findComment-13758 Share on other sites More sharing options...
ehmer Posted March 3, 2006 Author Share Posted March 3, 2006 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 1else 0 end) pointsFROM fixtures JOIN tips on (fixtures.round=tips.round AND fixtures.game=tips.game)GROUP BY tips.username, fixtures.roundLIMIT 0,50 Quote Link to comment https://forums.phpfreaks.com/topic/3824-code-generated-column-sort/#findComment-13772 Share on other sites More sharing options...
wickning1 Posted March 3, 2006 Share Posted March 3, 2006 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.roundSUM(tips.tip='draw' AND fixtures.result='draw' OR tips.tip=fixtures.result) as pointsFROM fixturesJOIN 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] Quote Link to comment https://forums.phpfreaks.com/topic/3824-code-generated-column-sort/#findComment-13780 Share on other sites More sharing options...
ehmer Posted March 3, 2006 Author Share Posted March 3, 2006 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) Quote Link to comment https://forums.phpfreaks.com/topic/3824-code-generated-column-sort/#findComment-13826 Share on other sites More sharing options...
ehmer Posted March 3, 2006 Author Share Posted March 3, 2006 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"); Quote Link to comment https://forums.phpfreaks.com/topic/3824-code-generated-column-sort/#findComment-13841 Share on other sites More sharing options...
wickning1 Posted March 3, 2006 Share Posted March 3, 2006 Use uasort() instead of usort(). Quote Link to comment https://forums.phpfreaks.com/topic/3824-code-generated-column-sort/#findComment-14011 Share on other sites More sharing options...
ehmer Posted March 4, 2006 Author Share Posted March 4, 2006 Thanksuasort did the job.David Quote Link to comment https://forums.phpfreaks.com/topic/3824-code-generated-column-sort/#findComment-14117 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.