sort on mutilple fields

rtipton

I have a web page that keeps scores of teams that play soccer. I have got it to rank by the Total Points Earned. But it the points Earned are the same I need to resort it and go by the Total Points Allowed Then Total Points Made. Below is the php code that i am using for this.

 <?php do { ?>
        <tr>
          <td><div align="center"><?php echo $row_getSoccerScores['Week']; ?></div></td>
          <td nowrap="nowrap"><p align="center"><?php echo $row_getSoccerScores['HomeTeam']; ?></p>
          <p align="center">&nbsp;<?php echo $row_getSoccerScores['AwayTeam']; ?></p></td>
          <td><p align="center"><?php echo $row_getSoccerScores['HomePointsScore']; ?> <?php echo $row_getSoccerScores['HomeWinorLoss']; ?></p>
          <p align="center">&nbsp;<?php echo $row_getSoccerScores['AwayPointsScore']; ?> <?php echo $row_getSoccerScores['AwayWinorLoss']; ?></p></td>
          <td><p align="center"><?php echo $row_getSoccerScores['HomePointsEarned']; ?></p>
          <p align="center"><?php echo $row_getSoccerScores['AwayPointsEarned']; ?></p></td>
        </tr>
        <?php $myhash[$row_getSoccerScores['HomeTeam']] += $row_getSoccerScores['HomePointsEarned'] ;?>
        <?php $myhash[$row_getSoccerScores['AwayTeam']] += $row_getSoccerScores['AwayPointsEarned'] ;?>
        <?php $myhash1[$row_getSoccerScores['AwayTeam']] += $row_getSoccerScores['AwayPointsScore'] ;?>
        <?php $myhash1[$row_getSoccerScores['HomeTeam']] += $row_getSoccerScores['HomePointsScore'] ;?>
        <?php $myhash2[$row_getSoccerScores['HomeTeam']] += $row_getSoccerScores['AwayPointsScore'] ;?>
        <?php $myhash2[$row_getSoccerScores['AwayTeam']] += $row_getSoccerScores['HomePointsScore'] ;?>
  <?php } while ($row_getSoccerScores = mysql_fetch_assoc($getSoccerScores)); ?>
    </table>
    <p>
    </p>
    <h1>Ranking</h1>
    <table width="600" border="1">
      <tr>
        <td><div align="center">Rank</div></td>
        <td><div align="center">Team Name</div></td>
        <td><div align="center">Total Points Earned</div></td>
        <td><div align="center">Total Scored Allowed</div></td>
        <td><div align="center">Total Scored Made</div></td>
      </tr>
      <?php
		$count = 0;
		arsort ($myhash);
		foreach ($myhash as $key => $value)
		{ $count++; ?>
      <tr>
        <td><div align="center"><?php echo $count; ?></div></td>
        <td><div align="center"><?php echo $key; ?></div></td>
        <td><div align="center"><?php echo $value; ?></div></td>
        <td><div align="center"><?php echo $myhash2[$key]; ?></div></td>
        <td><div align="center"><?php echo $myhash1[$key]; ?></div></td>
      </tr>
      <?php } ?>

Here is a example of what i am trying to do.

I need some help in doing a resort on a hash that i have. Here is the table that I created when the web page comes up.

Ranking
Rank   Team Name               Total Points Earned    Total Scored Allowed      Total Scored Made
1        Bport 3/Reyes                 9                                     9                          18
2        Bport 1/Rosas                 9                                     2                          18
3        Paradise 1/Mitschke         6                                     7                          4
4        Decatur 2/Carrillo             6                                    1                          8

I have it right know putting in order by the most points earned. What i want to do is if the Total Points Earned are the same then I need it to re calculate and re order by the Total Scored Allowed. So the table needs to look like this

Ranking
Rank   Team Name               Total Points Earned    Total Scored Allowed      Total Scored Made
1        Bport 1/Rosas                 9                                    2                         18
2        Bport 3/Reyes                 9                                    9                         18
3        Decatur 2/Carrillo            6                                    1                          8
4        Paradise 1/Mitschke         6                                    7                         4

Here is the link of the web page
http://http://paradisepyra.com/Soccer/SoccerScoreDisplay.php?Year1=2009&Season1=Spring&AgeGroup1=8+and+Under&button=Submit

NogDog

Is this something that could be done at the database level? If so, it would probably be both simpler and faster to do it there. If not, you'll probably have to do something with [man]usort/man, defining a call-back function that will look at each value and do the desired sorting.

rtipton

I dont think you can do this from the database. I have all the soccer scores in a database and have to caculate the total points. I didnt think that it was possible from mysql

NogDog

I suspect it could still be done in the SQL using grouping functions such as SUM() and then sorting by the desired results. The specifics will, of course, depend on the actual DB schema.

rtipton

The problem I have is that there is 6 fields that i am using
HomeTeam HomePointScore HomePointsEarned Awayteam AwayPointScore AwayPointsEarned

so the team name can be either home or away so that is why i think that doing the count and the sort in SQL want work

NogDog

I think you may be underestimating the capabilities of SQL, though I'm not claiming it would necessarily be simple or easy, mind you. But if you're more comfortable with a PHP solution, you can certainly pursue one using [man]usort/man, for instance. All(?) you need is a callback function that will take as its two parameters two top-level entries from your array. It would then need to walk through your desired sorting logic and ultimately return "...an integer less than, equal to, or greater than zero if the first argument is considered to be respectively less than, equal to, or greater than the second."