records-- most goals

msnhockey

wondering what is the easiest way to code on getting most goals per season by a player and most goals all seasons.

thanks

dougal85

most goals in what?

You question isn't very well structured or clear. At least, it isn't to me.

msnhockey

most goals in hockey.. i track goals, assists, pim, games played and so on. looking at getting records for each thing i track.

bradgrafelman

Are you talking about data that you store in a database? If it's data you've stored, it'd be a simple:

SELECT player_name, goals FROM yourTable ORDER BY goals DESC LIMIT 1

msnhockey

i used this code to get total goal leaders

$stats_sql = "SELECT * FROM player_stats,teams,players WHERE 
					teams.id = player_stats.team_id AND "
					."players.id = player_stats.playerid";

i am now having problems getting the goal leaders from any givin season
i also have a table called seasons

naskar

I might be able to help you If you provide some information of the structure of your tables

msnhockey

all i really need is from the players table and player_stats table

all i need from the players table is
id
fname
lname

from player stats table
playerid is the same as in the players table
goals
then seasonid - used to query which player got most goals in a season

msnhockey

still can't figure this out.. either entire goals total or nothing..

naskar

You want most amount of goals per season right?

For example let's say you have

Stats

ID 6
Goals 7
SeasonID 2002

Lets say you want to find out how many goals he/she scored in a season (2002). The way you have it right now you might have to change the way you do Seasons. How about putting it in another table? That way you could possibly sort it easier.

msnhockey

basically right... go through the tables and find what player/players scored the most goals and in which season..

you have an idea for another table.... I am all ears/game for it if need be..

msnhockey

seasons does has its own table...
the table aspects are this
id
name
status

id is the season id, name is the season name and status is either 1 for open or 0 for closed

naskar

SELECT * FROM players,stats WHERE stat.seasonid = season.id ORDER BY stats.goals

have you tried something along those lines

msnhockey

nothing like that.. I will try

msnhockey

nothing at all..... unfortunately

msnhockey

anyone have any ideas on what i should try...

thanks

meigwil

If your tables are like this

players
id | fname | lname

stats
playerid | goals | seasonid

then try

$sql = "SELECT players.fname, players.lname, stats.goals FROM players, stats ".
       "INNER JOIN players.id = stats.playerid ".
       "WHERE stats.seasonid = $year ORDER BY stats.goals DESC LIMIT 1;";

$res = mysql_query($sql) or die('ZOMFG!: '.mysql_error() );

while($row=mysql_fetch_array($res)){
  echo 'The highest scorer of season '.$year.' is $row['fname'].' '.$row['lname'].' with '.$row['goals'].' goals.';
}

I'm unsure if the SQL is totally correct, but it's worth a try. The 'FROM players, stats' may give you an error.

Best,

Mei

msnhockey

can't seem to get to work... when I take out the echo part of things the page doesn't go white. just nothing displays because I took the echo part out.. with the echo part in it goes white...

I am going to list my tables exactly as they are just in case i missed something out...

seasons

id
name
status

players_stats

id
playerid
goals
assists
+ more stat fields
played
game_id
team_id
div_id
seasonid

players

id
lname
fname
teamid

php4me

I noticed a couple things wrong in this and have fixed. give it a try and see.

$sql = "SELECT players.fname, players.lname, stats.goals FROM players, stats ". 
       "INNER JOIN players.id = stats.playerid ." 
       "WHERE stats.seasonid = $year ORDER BY stats.goals DESC LIMIT 1"; 

$res = mysql_query($sql) or die('ZOMFG!: '.mysql_error() ); 

while($row=mysql_fetch_array($res)){ 
  echo 'The highest scorer of season '.$year.' is '.$row['fname'].' '.$row['lname'].' with '.$row['goals'].' goals.'; 
}

I believe your trying to get the player with the most goals in any season played?

try this as well. just using some of the code u had above, didn't see where $sid came into play. or where $sid is put this in seasons.id

$get_all_seasons = "SELECT * FROM seasons ORDER BY name ASC";
$all_the_seasons = mysql_query("$get_all_seasons");
$s = 0;
$sid = mysql_result($all_the_seasons,$s,"id");

$sql = "SELECT * FROM player_stats,teams,players WHERE player_stats.seasonid = '".$sid."' AND "
	."teams.id = player_stats.team_id AND "
	."players.id = player_stats.playerid AND players.position != 'G'";

$result_sql = mysql_query("$sql");
	if(!$result_sql) { echo mysql_errno().": ".mysql_error()."<br>"; }

bpat1434

Sounds to me like you need to rethink your table layouts. Initially I see 6 tables:
Players, Teams, Seasons, Games, Game Stats, Awards. Now, here's how I'd do it:

Players
[indent]player_id
team_id
first_name
last_name
jersey_number
height
weight
dominance

*NOTE: The "dominance" field is for whether they're right or left handed (or ambidextrous).[/indent]

Teams
[indent]team_id
team_name
year_founded
head_coach
owner
city
country
team_color1
team_color2
team_color3
mascot
arena_name
mascot_nickname
team_nickname[/indent]

Seasons
[indent]season_id
season_title
start_date
end_date[/indent]

Games
[indent]game_id
season_id
game_date
game_time
home_team_id
away_team_id[/indent]

Game Statistics
[indent]stat_id
game_id
team_id
player_id
season_id
statistic
amount[/indent]

Awards
[indent]award_id
season_id
player_id
award
description[/indent]

Now, the "Awards" table is completely useless in this instance, but it's there as an example of what you could do with setting it up like this. From here, you can query for individual games based upon the "Games" table, or seasons based upon the "Seasons" and "Games" table to find out total number of scores, total number of fights, etc.

So using the above as a guide, you could do something like this:

<?php

// Given that $sid is the season ID
$query = "SELECT CONCAT(p.`last_name`, ', ', p.`first_name`) AS `name`, gs.`amount`
FROM (SELECT MAX(SUM(`amount`)) AS `amount`, `player_id`
      FROM `game_stats`
      WHERE `season_id` = '" . intval($sid) . "'
      GROUP BY `player_id`) AS gs
LEFT JOIN `players` AS p
  ON gs.`player_id` = p.`player_id`
ORDER BY gs.`amount` DESC";

$result = mysql_query($query) or die('MySQL Error: ' . mysql_error());

if(!$result || mysql_num_rows($result) < 1)
    die('MySQL returned no results.  There might have been an error:<br />' . mysql_error());

$row = mysql_fetch_row($result);
echo 'The leading scorer for the requested season was ' . $row['name'] . ' with ' . $row['amount'] . ' total scores throughout the season.';

Now the SQL might need some tweaking, but that's how i'd go about doing it. You can get a lot more information from the tables if you set it up like this. You could combine some tables if you really wanted to; however, you then need to think about trades, a misspelling in a name (or names) and maintenance.

Hope I helped somehow...

msnhockey

well,

looks like a i have a few options to try here...
I really like what bpat came up with. it would just cause me alot of re-coding but it looks amazing and would be easier to query.

The scripts I am using i have mostly customized myself, but I bought this script off a site and didn't have most options on there so I am spicing it up. With bpats idea it would take me longer to do, but be easier in the long run to add and query things.