i come back on your first problem and pull it apart so it might be easier to better understand for me what you were doing.
database based statspage for a hockey league website
on my stats page I'd like the query to count the number of times playerid shows up in goal and in both assist columns. / I'd like to display a player based goal statistic.
goalid ¦ teamid ¦ goal ¦ assist1 ¦ assist2 ¦ time
(rem: in my example I left out the time column)
playerid ¦ name (& more unknown columns?)
goalinfo.teamid < n - 1 > teams.teamid
goalinfo.goal < n - 1 > players.playerid
goalinfo.assist1 < n - 1 > players.playerid
goalinfo.assist2 < n - 1 > players.playerid
so it's a need to query the goalinfo table by grouping playerid and counting the goals (rows) per group:
$query = "SELECT goalinfo.goal, COUNT(*) AS goalscount FROM goalinfo,players WHERE goalinfo.goal = players.playerid GROUP BY goalinfo.goal";
this is exactly like you did and it returns even the same data.
now it depends on what you want. you can simply sum up by using some math (like the + operator in php) and you're fine. but i am not shure if that is exactly what you'd like to do. anyway, it looks like this:
summing all three arrays ($goals, $assist1, $assist2) into a new array defined by key:
this is for the sum only. and this is in php only.
for the names, which need to be requested from the database first (I assume it's the players.name, see table players on top), you just can add it to your query and request it as third (zero based: 2nd) column afterwars (i.e. $row_goalstats;)
maybe it makes more sense to do all this within the mysql query and its good to think about the database design at all.
for example: aren't there three tables?
goals.id < 1 - n > goalshooters.goalid
players.id < 1 - n > goalshooters.playerid
team.id < 1 - n > goals.teamid
and table goalshooters contains the role of the linked player like 0-shooter, 1-assist1 and 2-assist2 (use an enum field here)?
so you can request whatever you want directly from the mysql and apply groupby and aggregate functions on it directly.
and you save data: 2x times the time and team column.
what do you think about that approach? you can even catch up with up to n "assists" for a goal theoretically.
even more, when it comes to sorting, your database does this quite quite well and fast. and paged results, this is done by the db, too. so i would strongly recommend to work on the database layout a bit more to optimise the queries to have data @ hand directly after the mysql query to reduce the code of your php script (less code less error more fun).
by the way, next to teams isn't there a game database table? and aren't there multiple leagues available? anyway, it might be a good idea to know which database version you're using (mysql version) at all.