I want to put together a view showing team standings broken down by player that contributed to them and by event making up the calendar. There are two tables contributing data:
-
standings: team_id, total points and position -
results: individual player results, number of events, individual sessions in each event (can be from one to four)
The driver listed as MUR competed for both EVAL and Bastvik. However, only the results of his EVAL participation are shown (and in case of the Bastvik row, incorrectly).
I am trying to to show only the results the driver had for that team. In this case, MUR would have 7 and 17 in the first two cells of his Bastvik row, and two blank cells for the third and fourth.
I have a row for each team, split by driver and listing result by result per session. The first column is the position, the second the team name, then the players (three letter tags from the surname) and then the results, in this case two events with two sessions each. The final column is the total score.
I have tried moving the players results array within the Team foreach, but the result doesn't change.
// Getting number of races per event
$query = "SELECT DISTINCT eventSession, eventId FROM results WHERE (eventSession!='T' AND eventSession!='P' AND eventSession!='Q') AND eventID IN (".implode(',',$eventsIds).")";
$result = mysql_query($query);
while ($row = mysql_fetch_assoc($result)) {
if (!isset($racesInEvent[$row['eventId']])) {
$racesInEvent[$row['eventId']]['totalEvents'] = 1;
}
else {
$racesInEvent[$row['eventId']]['totalEvents']++;
}
$racesInEvent[$row['eventId']]['sessions'][] = $row['eventSession'];
}
mysql_free_result($result);
// Getting standings for teams
$standingsTeams = array();
$query = "SELECT st.position, st.team, st.points, st.prevposition, t.completename FROM standings_teams st
LEFT JOIN teams t ON t.id=st.team
WHERE st.competition='".$comp."' AND st.year='".$year."'
ORDER BY st.position ASC";
$result = mysql_query($query);
while ($row = mysql_fetch_assoc($result)) {
$standingsTeams[$row['position']] = $row;
}
mysql_free_result($result);
foreach ($standingsTeams as $key => $value) {
$teamsIds[] = $value['team'];
}
// Getting the number of drivers per team
$numberOfDrivers = array();
$firstEvent = reset($events);
$lastEvent = end($events);
$query = "SELECT DISTINCT driverId, teamId FROM results WHERE teamId IN (".implode(',',$teamsIds).") AND (eventSession!='T' AND eventSession!='P' AND eventSession!='Q' AND eventSession!='Q1' AND eventSession!='Q2' AND eventSession!='QA') AND compId='".$comp."' AND (eventID>='".$firstEvent['id']."' AND eventID<='".$lastEvent['id']."')";
$result = mysql_query($query);
while ($row = mysql_fetch_assoc($result)) {
if (!isset($numberOfDrivers[$row['teamId']])) {
$numberOfDrivers[$row['teamId']] = 1;
}
else {
$numberOfDrivers[$row['teamId']]++;
}
}
mysql_free_result($result);
// Getting drivers positions for each race - note, there is a teamId field that specifies which team he was competing for in each event/session
$driverStandings = array();
$query = "SELECT DISTINCT driverId, teamId, eventID, eventSession, position, raceTime FROM results
WHERE teamId IN (".implode(',',$teamsIds).") AND (eventSession!='T' AND eventSession!='P' AND eventSession!='Q' AND eventSession!='Q1' AND eventSession!='Q2' AND eventSession!='QA') AND eventID IN (".implode(',',$eventsIds).") ORDER BY eventID ASC";
$result = mysql_query($query);
$i = 1;
while($row = mysql_fetch_assoc($result)) {
$driverStandings[$row['teamId']][$row['driverId']][$row['eventID']][$row['eventSession']] = $row['position'];
}
mysql_free_result($result);
foreach ($racesInEvent as $key => $value) {
foreach ($value['sessions'] as $k => $v)
$eventsFilled[] = $key;
}
$teamcolspan = 7+$numberofsessions; //manually because if we modify the one above we break drivers colspan that really is 6 + num events
// Generate html here
// Draw Table!
if ($comp!='11')
{
echo '<table width="100%" class="tableNoPadding" cellspacing="0" cellpadding="0" border="0">';
// Table title
echo '
<tr>
<th colspan="'.$teamcolspan.'">CONSTRUCTORS\' CHAMPIONSHIP TABLE</th>
</tr>';
// Events
echo '<tr>
<th width="15">Pos</th>
<th width="80">Team</th>
<th width="20">Driver</th>';
foreach ($events as $key => $value) {
$countryname=$value['country'];
$datacountry=mysql_query("SELECT id FROM nations WHERE country='$countryname'", $CONNECTW);
while ($row = mysql_fetch_row($datacountry))
{
$countryid=$row[0];
}
$nat_icon = "$RKP/media/flags/$countryid.png";
echo "<th width='15' colspan='".$racesInEvent[$key]['totalEvents']."' align='center'><img src='$nat_icon' width='20' title='$countryname'></th>";
}
echo '
<th width="20">Points</th>
<th width="20">Rel</th>
<th width="20">Par</th>
<th>Tot</th>
</tr>';
// Sessions
echo '
<tr>
<th width="15"> </th>
<th width="80"> </th>
<th width="10"> </th>';
foreach ($racesInEvent as $key => $value) {
foreach ($value['sessions'] as $k => $v) {
echo "<th width='15' align='center'>".$v."</th>";
}
}
echo'
<th width="20"> </th>
<th width="20"> </th>
<th width="20"> </th>
<th width="20"> </th>
</tr>';
// Teams and Standings
// We need to make a foreach of standingsTeams because
// we want a table with team positions sorted correctly
// in such case we have the array to start looping
foreach ($standingsTeams as $key => $value) {
$driversFound = $numberOfDrivers[$value['team']]; // how many drivers for this team
if ($value['prevposition']>$value['position']) { $IC_Rel="<div id='IC_StandingsRelUp'></div>"; }
elseif ($value['prevposition']>$value['position']) { $IC_Rel="<div id='IC_StandingsRelDown'></div>"; }
else { $IC_Rel="<div id='IC_StandingsRelSame'></div>"; }
$gap = false;
$firstGapPoints = false;
$prevPoints = 0;
$prevKey = $key-1;
if ($prevKey > 0) {
$prevPoints = $standingsTeams[$prevKey]['points'];
$gap = $value['points']-(int)$prevPoints;
$firstGapPoints = $value['points']-(int)$standingsTeams[1]['points'];
}
// generate drivers positions for all the sessions found
foreach ($driverStandings[$value['team']] as $driver => $ev) {
$drivers[$value['team']]['driverId'][] = $driver; // first we build an array with all drivers per session
// Later we get the position of that driver in the standings array
// each driver should contain with n elements where n is the number of total sessions "Japan R1, R2 and Australia R1, R2 make 4 sessions"
foreach ($racesInEvent as $race => $race2) {
foreach ($race2['sessions'] as $k => $v) {
$posis[$driver][] = $driverStandings[$value['team']][$driver][$race][$v];
}
}
}
$driverId = $drivers[$value['team']]['driverId'][0]; // first driver
// Generate the row for the first driver together some common fields like teamname, position, points, etc
// All this goes in the first tr element
$datad=mysql_query("SELECT CONCAT (firstName, ' ', lastName) AS username FROM users WHERE id='$driverId'", $CONNECTW);
while ($row = mysql_fetch_row($datad))
{
$fullname=$row[0];
}
echo "
<tr>
<td width='20' class='tx' rowspan='".$driversFound."'>".$key."</td>
<td width='80' rowspan='".$driversFound."'><a href='$RKP/services/database/team/?target=".$value['team']."'>".$value['completename']."</a></td>
<td width='10'><a href='$RKP/services/database/driver/?target=".$driverId."' title='$fullname'>".tempPatchToShowDriverName($driverId)."</a></td>";
for ($n=0; $n<$numberofsessions; $n++) {
echo "<td align='center'>".$posis[$drivers[$value['team']]['driverId'][0]][$n]."</td>";
}
echo "
<td width='40' class='tz' rowspan='".$driversFound."'><strong>".$value['points']."</strong></td>
<td width='40' class='ts' rowspan='".$driversFound."'>".$IC_Rel."</td>
<td width='40' class='ts' rowspan='".$driversFound."'>".$gap."</td>
<td width='40' class='ts' rowspan='".$driversFound."'>".$firstGapPoints."</td>
</tr>";
// Here all rows for the rest of drivers in such team
for ($i=1; $i<$driversFound; $i++){
echo '
<tr>';
$driverId = $drivers[$value['team']]['driverId'][$i];
$datad=mysql_query("SELECT CONCAT (firstName, ' ', lastName) AS username FROM users WHERE id='$driverId'", $CONNECTW);
while ($row = mysql_fetch_row($datad))
{
$fullname=$row[0];
}
echo "<td width='10'><a href='$RKP/services/database/driver/?target=".$driverId."' title='$fullname'>".tempPatchToShowDriverName($driverId)."</a></td>";
for ($n=0; $n<$numberofsessions; $n++) {
echo '<td align="center">'.$posis[$drivers[$value['team']]['driverId'][$i]][$n].'</td>';
}
echo '
</tr>';
}
}
echo '</table>';
}
Been at this, on-and-off, for the last 6 months and I still haven't figured out a solution. Any help is appreciated!
