Defining a default sort order of table results from sql

[msaz87]

Hi all,

 

I'm very inexperienced with PHP -- so I was hoping to get help with something I'm trying to modify. I had a programmer design a script for me which handles standings for a league and then also calls these standings for view. The thing is, when the table is populated, it goes in alphabetical (or at least ID) order -- whereas I'd like it to be ordered like a real standings table might, with it going from greatest number of wins to most points scored, etc.

 

You can see the current setup example here:

http://www.fasports.com/manager/view_standings.php?location_id=5&league_id=8&division_id=25&week=-1

 

The code on the page with the table being generated is:

 

<div>
        <p style="margin-top: 0; margin-bottom: 0" align="center"><font face="Arial" size="2">
        <br/>
        </font><font face="Arial" size="5">
        <b>Current Standings</b></font><font face="Arial" size="2"><br/>
        <br/>
        </font></p>
        <div align="center">
        <table cellpadding="2" width="750" border="2" bordercolor="#003366">
            <tr>
                  <th style="width:236px;" bgcolor="#003366">
                <p style="margin-top: 0; margin-bottom: 0" align="left">
                <font color="#FFFFFF" face="Arial" size="2">Team</font></th>
                <th style="width:60px;text-align:center" bgcolor="#003366">
                <p style="margin-top: 0; margin-bottom: 0">
                <font color="#FFFFFF" face="Arial" size="2">W</font></th>
                <th style="text-align:center;" bgcolor="#003366">
                <p style="margin-top: 0; margin-bottom: 0">
                <font color="#FFFFFF" face="Arial" size="2">L</font></th>
                <th style="text-align:center;" bgcolor="#003366" width="119">
                <p style="margin-top: 0; margin-bottom: 0">
                <font color="#FFFFFF" face="Arial" size="2">PTS Scored</font></th>
                <th style="text-align:center;" bgcolor="#003366" width="143">
                <p style="margin-top: 0; margin-bottom: 0">
                <font color="#FFFFFF" face="Arial" size="2">PTS Given Up</font></th>
                <th style="text-align:center;" bgcolor="#003366" width="77">
                <p style="margin-top: 0; margin-bottom: 0">
                <font color="#FFFFFF" face="Arial" size="2">Difference</font></th>
           </tr>
    <?            
                $teams = $db->db_select("select * from team where location_id = $location_id and league_id = $league_id and division_id = $division_id order by team");
                while ( $team = mysql_fetch_object($teams) ) {
                    $standing = calculate_standing($team->team_id);                    
    ?>
            <tr>
                <td>
        <font face="Arial" size="2"><p align="left" style="margin-top: 0; margin-bottom: 0"><?=get_team_name($team->team_id)?></p></font></td>
                <td align="center">
        <p style="margin-top: 0; margin-bottom: 0">
        <font face="Arial" size="2"><?=$standing['win']?></font></td>
                <td align="center">
        <p style="margin-top: 0; margin-bottom: 0">
        <font face="Arial" size="2"><?=$standing['loose']?></font></td>
                <td align="center" width="119">
        <p style="margin-top: 0; margin-bottom: 0">
        <font face="Arial" size="2"><?=$standing['pts_scored']?></font></td>
                <td align="center" width="143">
        <p style="margin-top: 0; margin-bottom: 0">
        <font face="Arial" size="2"><?=$standing['pts_given_up']?></font></td>
                <td align="center" width="77">
        <p style="margin-top: 0; margin-bottom: 0">
        <font face="Arial" size="2"><?=$standing['difference']?></font></td>
            </tr>
    <?                    
                }
    ?>
        </table>
        </div>
    </div>

 

And the function is defined:

 

    function calculate_standing ( $team_id ,$week = -1 ) {
        $result = array("win"=>0,"loose"=>0,"pts_scored"=>0,"pts_given_up"=>0,"difference"=>0);
        $db = new cls_db();
        $sql = "select sum(win) as win, sum(loose) as loose, sum(pts_scored) as pts_scored, sum(pts_given_up) as pts_given_up, (sum(pts_scored)-sum(pts_given_up)) as difference from standing inner join game on standing.game_id = game.game_id where (game.week = $week or $week = -1) and standing.team_id = $team_id";
        $rs = $db->db_select($sql);
        if ( $row = mysql_fetch_object($rs) ) {
            $result['win'] = $row->win;
            $result['loose'] = $row->loose;
            $result['pts_scored'] = $row->pts_scored;
            $result['pts_given_up'] = $row->pts_given_up;
            $result['difference'] = $row->difference;
        }
        foreach($result as $k=>$v) {
            if (  empty($v) ) {
                $result[$k] = 0;    
            }
        }
        return $result;        
    }

 

Hopefully I've identified the right pieces of code to edit... and if anyone can help I'd greatly appreciate it!

 

Thanks in advance!

[john-formby]

Try changing your query to:

 

$sql = "select sum(win) as win, sum(loose) as loose, sum(pts_scored) as pts_scored, sum(pts_given_up) as pts_given_up, (sum(pts_scored)-sum(pts_given_up)) as difference from standing inner join game on standing.game_id = game.game_id where (game.week = $week or $week = -1) and standing.team_id = $team_id ORDER BY sum(pts_scored) DESC";

[msaz87]

I tried changing the query to what you specified and it made no difference...

 

I also tried changing it to:

		$sql = "select sum(win) as win, sum(loose) as loose, sum(pts_scored) as pts_scored, sum(pts_given_up) as pts_given_up, (sum(pts_scored)-sum(pts_given_up)) as difference from standing inner join game on standing.game_id = game.game_id where (game.week = $week or $week = -1) and standing.team_id = $team_id ORDER BY win DESC";

 

and no luck there either. As far as I can tell, none of the changes to query made any difference to the output whatsoever (no shuffling in any way) -- they still ouput alphabetically/by ID.

 

Any other ideas? Thanks for the help!