Introduction
displayStandings.php needs to diplay all old weeks. This won't be easy, but here's a plan of attack.
Plan of Attack
- Check users$year (users_2007) to find the name of each user table
- Grab the users table and get all information except betTeam which won't be necessary.
- Total bet amount = sum(bet1-4) and arrange in a matrix (array) like so:
| user1 | beginScore | totalBetAmount | gamesWon | won_lost | endScore |
| user2 | beginScore | totalBetAmount | gamesWon | won_lost | endScore |
| user3 | beginScore | totalBetAmount | gamesWon | won_lost | endScore |
- Sort by beginScore and add column for beginRank (Is there an easier way to do this?)
| user2 | beginScore | totalBetAmount | gamesWon | won_lost | endScore | 1 |
| user1 | beginScore | totalBetAmount | gamesWon | won_lost | endScore | 2 |
| user3 | beginScore | totalBetAmount | gamesWon | won_lost | endScore | 3 |
- Sort by endScore to get final ranking
| user3 | beginScore | totalBetAmount | gamesWon | won_lost | endScore | 3 |
| user2 | beginScore | totalBetAmount | gamesWon | won_lost | endScore | 1 |
| user1 | beginScore | totalBetAmount | gamesWon | won_lost | endScore | 2 |
- Now we can take this array and write into the Standings format.
Key Difficulties
- How do we sort "excel-style?" Sort one column but have it maintain it's dependencies. I can do this in MySQL if it's all in one table, but we're pulling from many tables. Do we have to create a temporary table? Or is there an easy way to do this in php?
- Is there a better way than sorting twice to get beginRank and endRank? Should we just have beginRank in the user table and updated through updateWeek? If so, we need to decide quick because the user table creation is going to have to be updated before anybody registers... perhaps I should just do this as a preventative measure?
Here's the answer... this looks right...
Example 261. Sorting database results For this example, each element in the data array represents one row in a table. This type of dataset is typical of database records. Example data: volume | edition -------+-------- 67 | 2 86 | 1 85 | 6 98 | 2 86 | 6 67 | 7 The data as an array, called data. This would usually, for example, be obtained by looping with mysql_fetch_assoc(). <?php $data[] = array('volume' => 67, 'edition' => 2); $data[] = array('volume' => 86, 'edition' => 1); $data[] = array('volume' => 85, 'edition' => 6); $data[] = array('volume' => 98, 'edition' => 2); $data[] = array('volume' => 86, 'edition' => 6); $data[] = array('volume' => 67, 'edition' => 7); ?> In this example, we will order by volume descending, edition ascending. We have an array of rows, but array_multisort() requires an array of columns, so we use the below code to obtain the columns, then perform the sorting. <?php // Obtain a list of columns foreach ($data as $key => $row) { $volume[$key] = $row['volume']; $edition[$key] = $row['edition']; } // Sort the data with volume descending, edition ascending // Add $data as the last parameter, to sort by the common key array_multisort($volume, SORT_DESC, $edition, SORT_ASC, $data); ?> The dataset is now sorted, and will look like this: volume | edition -------+-------- 98 | 2 86 | 1 86 | 6 85 | 6 67 | 2 67 | 7and I think I will and beginRank to the user table and not do the extra sort...