I am creating a sports team website. I would like to use php/mysql for the statistics information. I want to be able to enter information for a game, and have overall and average data updated. I have had some success doing this with Microsoft Excel, but that requires a lot more work and also does not automatically update the website. For example: Game A was today. Enter all stats for Game A for each player and total. Get season totals for each player and season averages for each player using php/mysql. I have basic understanding of php/mysql although I am not a programmer. I can create and edit tables, and I know how to connect to database with php to retrieve/edit data. I want this to be continuously updated automatically on website as i enter each game's data. I see this sort of thing done on every major sports website so I know there must be a way to do it, I just don't know what that is yet. Please help if you can or point me in the right direction. I have asked this question in several other forums for php and/or mysql, but have yet to get an answer other than "RTFM" and such. I don't expect anyone to write code for me, but I would like an explanation of how this can be done with references for me to investigate so that I can learn. Thanks in advance for any help/advice.

hello,
maybe if you post us your database structure, we can then help you more efficiently.
anyways, if you are able to add/edit the data, that means you just need to read that data in a way to get the needed results

and i think (if the data structure you have is good enough) you can get all of statistics by just using MySQL queries

using some Aggregate functions such as SUM(), AVG(), COUNT(),MIN(),MAX(),.. will help you accomplish that.

so, first specify what fields you want to output
think of a way to retrieve that data using the available functions and statements
once you're able to get the correct result, you get back to php and generate required html.

so, what is you're database looks like?

I plan to have three tables as such:

PLAYERS table: player_id, last_name, first_name, grade, position, jersey
GAMES table: game_id, game_date, opponent, result, score
STATS table: player_id, game_id, starter, minutes, fg_made, fg_att, 3p_made, 3p_att, ft_made, ft_att, oreb, dreb, assists, steals, points, fouls

These tables should be linked so that the player_id and game_id is consistent (but I am not sure about what should be primary key for the STATS table). The primary key for Players table would be player_id and the primary key for Games table would be game_id (player_id and game_id would be a foreign key in the Stats table).


Basically, after each game, I would enter the stats from that game into the STATS table. Then, I would have those game stats, as well as player averages (which updates using current game stats) displayed on the website. I just don't know how to use php/mysql to display the current game stats and the player averages on the website. Using mysql, how would I take the data to make averages? Or is this done with php? Do I need another table for averages?

I would like to just enter the information after each game into the database, and have the rest done "automagically" if that is possible.

I see many sports websites that have statistics on them so i know it can be done some way. The problem with sports websites that are out there, is that you can't "view source" to see how their statistics are displayed because the php or asp or cold fusion code will not be displayed. All you can see is the tabular data that is displayed.
That is one way that I learned HTML way back when, was from viewing the source code for websites I thought were cool to see how certain things were accomplished. With other languages such as php, the code is not there to look at, so I can't learn that way. And the information I have found thus far doesn't pertain to what i am trying to do. Any help is appreciated and please don't be afraid to criticise something I am doing if needed because I am new to this and don't fully understand what I need to do.

This post has been edited by lightningrod66: 28 May, 2009 - 08:29 AM

hello,



I think you have to add a new auto increment field and make it as primary key, this is usable for deleting and updating.

how would I take the data to make averages?
you have to execute mysql query to retreive the avarage then use php to read it and convert it to viewable html;
something like:

select game_id,count(game_id) as games_count,count(player_id) as players_count from STATS group by game_id

think of the above code as this:
first you group all rows with similar game_id together, and this will also group all player in the same game too
count is an agregate function
by applying it on(game_id) will return the game's count and becaue of the grouping is by game_id too, the count(player_id) will return the count of players(that have been recorded) on that specific game

of course you can add more counts as needed but I realy know nothing about sports..

and for example,if you want to get the count of each player's points:

select player_id,count(points) as pointscount from STATS group by player_id

here is an php example from php manual chm file( you can find it here http://www.php.net/docs.php ) with some modifications

<?php

$conn = mysql_connect("localhost", "mysql_user", "mysql_password");

if (!$conn) {
    echo "Unable to connect to DB: " . mysql_error();
    exit;
}
  
if (!mysql_select_db("mydbname")) {
    echo "Unable to select mydbname: " . mysql_error();
    exit;
}

//here you type the sql string you want to execute
$sql = "select game_id,count(game_id) as games_count,count(player_id) as players_count from STATS group by game_id";

$result = mysql_query($sql);

if (!$result) {
    echo "Could not successfully run query ($sql) from DB: " . mysql_error();
    exit;
}

if (mysql_num_rows($result) == 0) {
    echo "No rows found, nothing to print so am exiting";
    exit;
}

// While a row of data exists, put that row in $row as an associative array
// Note: If you're expecting just one row, no need to use a loop
// Note: If you put extract($row); inside the following loop, you'll
//       then create $games_count, $players_count, and $game_id

$html="<table>";
while ($row = mysql_fetch_assoc($result)) {
// here is the part where you extra the data from rows to generate html table
$html .="<tr>";
$html .="<td>".$row["game_id"]."</td>";
$html .="<td>".$row["games_count"]."</td>";
$html .="<td>".$row["players_count"]."</td>";
$html .="</tr>";
}
$html .="</table>"
echo $html;
mysql_free_result($result);

?> 

about calculating averages, i don't realy know how you want to do that, because i can think of multiple ways (average points per game, average points per player, average points per player per gane or over all averages...)
so, it's just a matter of what you need

note: php and mysql are available for free and there are a lot of free resources over the net

hope it helps