Jorn Posted May 17, 2012 Share Posted May 17, 2012 Hey guys, Im trying to make a scoreboard for my pokersite so see who is the best player at the moment. I calculate this by counting the total points for a player earned this season, and devide it by the rounds played. This works well only I can't seem to figure out how to sort the table on the average score (points devided by rounds played). since the sorting at current code is done on a SQL column, I was wondering if it is possible to change that. The code automaticly adds nummers to the rows to see who i 1 through 6. here is the relevant code part: { // SELECT * FROM ".$score_table." WHERE tournamentid = '" . $cgi->htmlEncode ( $row [ "tournamentid" ] ) . "' ORDER BY points DESC $rows = $sql->execute ( "SELECT playerid,SUM(points) AS points,count(playerid) AS ronden FROM ".$score_table." GROUP BY playerid ORDER BY points DESC", SQL_RETURN_ASSOC ) or die ("$DatabaseError"); echo "<table border=\"1\" align=\"center\" cellpadding=\"4\" cellspacing=\"1\" bgcolor=\"#000000\" bordercolor=\"#e17400\"> <tr bgcolor=\"#000000\" bordercolor=\"#e17400\"><td>Positie</td><td>Poker King</td><td>Punten totaal</td><td>Gespeelde ronden</td><td>Gemiddelde</td></tr>"; $num = sizeof ( $rows ); for ( $i = 0; $i < $num; ++$i ) { $ronden = $rows [ $i ] [ "ronden" ]; $points = $rows [ $i ] [ "points" ]; $playerid = $rows [ $i ] [ "playerid" ]; $gem = round($points/$ronden,2); $pos = $i + 1; echo "<tr bgcolor=\"#000000\"> <td align=\"center\" >$pos</td> <td>"; $sql->execute ( "SELECT playerid FROM ".$player_table." AS playerid", SQL_RETURN_ASSOC ); $prow = $prows [ 0 ]; if ($prow['profile']) { echo "<a href=\"?op=showplayer&pid=$playerid\" title=\"View Player Information\" >$prow[name]</a>"; } else { echo "$prow[name]"; } echo"$playerid</td> <td align=\"center\" >$points</td> <td align=\"center\" >$ronden</td> <td align=\"center\" >$gem</td> </tr>"; } echo "</table>"; } } here is the result: As you can see player 6 should be at position 5. p.s. sorry for the dutch column names translation: Positie = Position , punten totaal = total points , Gespeelde ronden = rounds played , gemiddelde = average Quote Link to comment https://forums.phpfreaks.com/topic/262707-sorting-a-table-by-calculated-variable/ Share on other sites More sharing options...
requinix Posted May 17, 2012 Share Posted May 17, 2012 Add an AVG() and the rest should be obvious. SELECT playerid, SUM(points) AS points, COUNT(playerid) AS ronden, AVG(points) AS avgpoints FROM $score_table GROUP BY playerid ORDER BY avgpoints DESC Quote Link to comment https://forums.phpfreaks.com/topic/262707-sorting-a-table-by-calculated-variable/#findComment-1346481 Share on other sites More sharing options...
Jorn Posted May 18, 2012 Author Share Posted May 18, 2012 Worked like a charm! thanks Quote Link to comment https://forums.phpfreaks.com/topic/262707-sorting-a-table-by-calculated-variable/#findComment-1346566 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.