Forum Moderators: coopster
What I have is a table that shows who scored (goal), who assisted (assist1 and assist2) and the details of when and what game it occured in. I use this to show boxscores in a hockey league website. The table for this is set up like this:
infoid ¦ gameid ¦ season ¦ teamid ¦ period ¦ time ¦ goal ¦ assist1 ¦ assist2
- goal joins with playerid in the players table as does assist1 and assist2.
- team joins with teamid in the teams table.
Here is the current query I have:
if (empty($_GET)){
$game = 1;
} else {
$game = $_GET['gameid'];
}
$query_detail = "SELECT period, time, team.teamname, goal.firstname AS goalfirstname, goal.lastname AS goallastname, assist1.firstname AS assist1firstname, assist1.lastname AS assist1lastname, assist2.firstname AS assist2firstname, assist2.lastname AS assist2lastname
FROM goalinfo, teams
INNER JOIN teams AS team ON ( goalinfo.teamid = team.teamid )
LEFT JOIN players AS goal ON ( goalinfo.goal = goal.playerid )
LEFT JOIN players AS assist1 ON ( goalinfo.assist1 = assist1.playerid )
LEFT JOIN players AS assist2 ON ( goalinfo.assist2 = assist2.playerid )
WHERE gameid = $game
GROUP BY infoid
ORDER BY period ASC , time ASC";
PERIOD 1
1. Team1, goalscorer#1 (Assist#1, Assist#2) 5.35
2. Team2, goalscorer#2 (Assist#1, Assist#2) 7.56
PERIOD 2
3. Team1, goalscorer#3 (Assist#1, Assist#2) 1.05
4. Team2, goalscorer#4 (Assist#1, Assist#2) 16.44
What I would like to do is count the number of goals in the year up to and including the most current goal scored.
So, if goalscorer#1 and #3 are the same player and these are his 3rd and 4th goals of the season, I'd like to show that like this:
1. Team1, goalscorer#1 3 (Assist#1, Assist#2) 5.35
3. Team1, goalscorer#3 4 (Assist#1, Assist#2) 1.05
So for each goal I need a count for that player in the current season up to that goal. I'm not sure how do do that.
If I'm not being clear please let me know and I'll expand on it more.
Works great, but if anyone has a better way I'd love to hear it.
