Welcome to WebmasterWorld Guest from

Forum Moderators: coopster & jatar k

Message Too Old, No Replies

multiple join query question



6:01 pm on Apr 20, 2006 (gmt 0)

10+ Year Member

I'm thinking of redoing the way I enter stats into my database for a hockey league website.

What I'd like to do is enter the info like this:

goalid ¦ teamid ¦ goal ¦ assist1 ¦ assist2 ¦ time

- teamid will match up with teams.teamid
- goal will match up with players.playerid
- assist1 will match up with players.playerid
- assist2 will match up with players.playerid

Now 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'm not sure how to create that query.


6:18 pm on Apr 20, 2006 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member

in general you can simply execute three queries to get the count of each one. lookup your database manual for so called aggregate functions (namely COUNT) and you should find some examples like this one:


Returns a count of the number of non-NULL values in the rows retrieved by a SELECT statement. The result is a BIGINT value.

COUNT() returns 0 if there were no matching rows.

mysql> SELECT student.student_name,COUNT(*)
-> FROM student,course
-> WHERE student.student_id=course.student_id
-> GROUP BY student_name;

(Source: [dev.mysql.com...]

3 Queries of this kind should do the job done.


12:12 am on Apr 21, 2006 (gmt 0)

10+ Year Member

okay, I finally found some time to play around with your suggestion.

this is what I have so far from the table `goalinfo`:

goalid teamid goal assist1 assist2

$query_goalstats = "SELECT players.playerid, COUNT(*) AS goals
FROM goalinfo,players
WHERE goalinfo.goal=players.playerid
GROUP BY players.playerid";
$goalstats = mysql_query($query_goalstats);
$query_assist1 = "SELECT players.playerid, COUNT(*) AS assist1
FROM goalinfo,players
WHERE goalinfo.assist1=players.playerid
GROUP BY players.playerid";
$assist1stats = mysql_query($query_assist1);
$query_assist2 = "SELECT players.playerid, COUNT(*) AS assist1
FROM goalinfo,players
WHERE goalinfo.assist2=players.playerid
GROUP BY players.playerid";
$assist2stats = mysql_query($query_assist2);
while ($row_goalstats = mysql_fetch_row($goalstats)) {
$goals [$row_goalstats[0]] = $row_goalstats[1];
while ($row_assist1stats = mysql_fetch_row($assist1stats)) {
$assist1 [$row_assist1stats[0]] = $row_assist1stats[1];
while ($row_assist2stats = mysql_fetch_row($assist2stats)) {
$assist2 [$row_assist2stats[0]] = $row_assist2stats[1];
echo "<pre>";
print_r ($goals);
print_r ($assist1);
print_r ($assist2);
echo "</pre>";

This outputs:

[057] => 1
[076] => 2
[115] => 1
[027] => 1
[057] => 1
[187] => 2
[027] => 1
[056] => 1
[163] => 1

now what? :-) How do I group those properly so I can list them out by points (sum of all three) including having the players names? And is it possible to have the page be sorted by any of the columns using $_GET?


8:16 am on Apr 21, 2006 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member

hi mooger35,

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.


table goalinfo
goalid teamid goal assist1 assist2 time
(rem: in my example I left out the time column)

table players
playerid name (& more unknown columns?)

table team

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:

$goalssum = array();
$arrays = array('goals', 'assist1', 'assist2');
foreach ($arrays AS $arrayname)
foreach (${$arrayname} AS $key => $value)
if (!isset($goalssum[$key]))
$goalssum[$key] = 0;

$goalssum[$key] += $value;
} // sum-up arrays
} // enumerate arrays

/* check what's done: */

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[2];)


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
- goalshooters
- players


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.


2:58 pm on Apr 21, 2006 (gmt 0)

10+ Year Member

After running this through my head for awhile last night, I think I will leave it as I currently have it. The league site works fine and the only thing I would be adding by doing it this new way would be the ability to show goal data per game. (ie. who scored and assisted on each goal). And yes, I left out a few columns in my example to try to simplify things.

The way I have it now I can break down goals, assists, pts etc by entire league, individual teams or by player and it works... so why mess with it. :-)

I just thought there may be an easier method in the query using either inner join or left join.

Thanks for all the thought you put into it.


3:21 pm on Apr 21, 2006 (gmt 0)

10+ Year Member

I deal with a lot of stats type stuff, and the best thing I've ever done if forced myself to forget about db normalization SOMEWHAT - and just store more data for stats that are difficult to compute on the fly. That way my visitors are using pretty simple queries on the live side, and I'm precompiling stats into their own tables.

For what I do, this costs me about 6 extra tables with data that's already there in raw form and COULD be coaxed out with queries, but heck - storage space is cheap and this really cuts the load on my db and makes my pages faster. I have three scripts that I run once a day or once a week as needed to update these compiled stats tables, and these scripts have some twenty line join queries - and storing the precompiled numbers keeps me from having to use these for most of my traffic.

Not everything that you hear about proper DB design translates perfectly to stats work. The stats portion of my site db is about 33-34 tables, as I said before - 6 of those are compilations of data that's in the others already in raw form. I use a different database for everything on the site that is not directly related to the stats, (such as the forums, logs etc.) This methodology has really saved me a lot of headaches and allowed me to focus more on content.


Featured Threads

Hot Threads This Week

Hot Threads This Month