homepage Welcome to WebmasterWorld Guest from 54.145.183.169
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Pubcon Platinum Sponsor 2014
Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
Forum Library, Charter, Moderators: coopster & jatar k

PHP Server Side Scripting Forum

    
multiple join query question
mooger35

5+ Year Member



 
Msg#: 12586 posted 6:01 pm on Apr 20, 2006 (gmt 0)

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.

 

hakre

WebmasterWorld Senior Member 10+ Year Member



 
Msg#: 12586 posted 6:18 pm on Apr 20, 2006 (gmt 0)

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:

COUNT(expr)

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.

mooger35

5+ Year Member



 
Msg#: 12586 posted 12:12 am on Apr 21, 2006 (gmt 0)

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
+--------+--------+------+---------+---------+
___1_______05_____076____183______167___
___2_______05_____076____183_____NULL___
___3_______01_____115____057______027___
___4_______01_____057____027______056___

$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:


Array
(
[057] => 1
[076] => 2
[115] => 1
)
Array
(
[027] => 1
[057] => 1
[187] => 2
)
Array
(
[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?

hakre

WebmasterWorld Senior Member 10+ Year Member



 
Msg#: 12586 posted 8:16 am on Apr 21, 2006 (gmt 0)

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.

project:
database based statspage for a hockey league website

problem:
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.

database:

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
(ignored)

links:
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: */
print_r($goalssum);

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

BUT

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.

mooger35

5+ Year Member



 
Msg#: 12586 posted 2:58 pm on Apr 21, 2006 (gmt 0)

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.

slade7

10+ Year Member



 
Msg#: 12586 posted 3:21 pm on Apr 21, 2006 (gmt 0)

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.

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved