Advice requested on system.

[frizzle]

Hi there,

I have this soccer site, and in 1 part one
can manage the played matches:
Score, location, players who scored etc.

Now the last part is what i have a question about.
The players are in a MYSQL db. Each has it's own
unique ID.
Fields:
- id
- firstname
- lastname
- position

Matches are also stored in the DB.
Fields:
- id
- opponent
- location
- date
- homepoints
- visitorspoints

If i have a new match, and the score is 5-3 how should
i add 5 player's ID of players who scored (in a yet to
create DB-field), in that way that i can get the person
that scored the most ... (topscorer)
And 2nd, and 3rd etc.

I hope it's clear.

Thanks in advance.

Greetings frizzle.

[JDS]

On Wed, 31 Aug 2005 08:43:51 -0700, frizzle wrote:

If i have a new match, and the score is 5-3 how should
i add 5 player's ID of players who scored (in a yet to
create DB-field), in that way that i can get the person
that scored the most ... (topscorer)
And 2nd, and 3rd etc.

Seems clear.

For future expandability and programmability , I suggest that you create
the following additional tables (and rework the two existing tables to
point to the data in these new tables):

create table teams(id, team_name, [...other data about the team...])
create table box_scores ( id, match_id, player_id, points [... additional
info...])

And you would change the match field "opponent" to "home_team_ id" and
"visitor_team_i d"

And you would link the tables with joins on all the different ids.

Then you could use the box_scores table to generate the total score of the
match, the goals for each player, and other stuff that you can't really do
with your simpler table set up.

Now, this is a lot to bite off at one sitting for a newish MySQL/PHP
programmer, so go over what I've suggested and then come back with more
specific questions. Adding all these tables is called "normaliazation " --
the process of breaking out non-unique data into separate tables so that
you are never actually repeating the same data within a table.

Also, realize that there is more than one way to skin a cat and other folk
may suggest variations or completely different techniques to what I've
suggested.

later...

--
JDS | je*****@example .invalid
| http://www.newtnotes.com
DJMBS | http://newtnotes.com/doctor-jeff-master-brainsurgeon/

[Al]

On 31 Aug 2005 08:43:51 -0700, "frizzle" <ph********@gma il.com> wrote:

Hi there,

I have this soccer site, and in 1 part one
can manage the played matches:
Score, location, players who scored etc.

Now the last part is what i have a question about.
The players are in a MYSQL db. Each has it's own
unique ID.
Fields:
- id
- firstname
- lastname
- position

Matches are also stored in the DB.
Fields:
- id
- opponent
- location
- date
- homepoints
- visitorspoints

If i have a new match, and the score is 5-3 how should
i add 5 player's ID of players who scored (in a yet to
create DB-field), in that way that i can get the person
that scored the most ... (topscorer)
And 2nd, and 3rd etc.

The players who scored in the match could be stored in a table like
this:
Name: matchscores
Fields:
match_id
player_id
totalscored

To get the scores list for a match, you could do a select like this:

SELECT * FROM matchscores WHERE match_id = ## ORDER BY totalscored
DESC

The end solution will really depend on what you've got at the moment
for storing the players who scored and how many changes you want to
make to the existing code.

Al

[frizzle]

Wow, thank you both for the fast reply!
I haven't got anything yet, just building it in my head ;) .

JDS, thanks, what you describe is quite what i have in mind, which
would mean i am in the right direction, but doesn't really solve my
original problem if i understand it correctly.
Thanks for explaining so broadly!

What it comes to the home_team_id and visitor_team_id will
probably only be an INT wether the match is home(0), or out (1),
which tells me enough about who scored what.

AL also thanks a lot! I think this is quite what i want, but i'm
worried
that if this system is used for multiple years, the database will
become slow, since the system would create a new record for
each goal from our team ...
Or am i underestimating the power of PlaySt ..., erm, mySQL ?
Then another small problem i ran into: if players leave the team, and
are deleted from the list, what happens if he was the topscorer of a
certain year? That would return an empty record ...
is it a GOOD option not actually to delete them, but make them
'inactive'
(invisible for visitors (of the site, not a match ;) )

Thanks again and have a nice evening!

Greetings frizzle!

[Geoff Muldoon]

ph********@gmai l.com says...

Hi there,

I have this soccer site, and in 1 part one
can manage the played matches:
Score, location, players who scored etc.

Now the last part is what i have a question about.
The players are in a MYSQL db. Each has it's own
unique ID.
Fields:
- id
- firstname
- lastname
- position

Matches are also stored in the DB.
Fields:
- id
- opponent
- location
- date
- homepoints
- visitorspoints

If i have a new match, and the score is 5-3 how should
i add 5 player's ID of players who scored (in a yet to
create DB-field), in that way that i can get the person
that scored the most ... (topscorer)
And 2nd, and 3rd etc.

Suggestions if you really want to allow for increased data flexibility in
the future:

A player's position could change for match to match, so maybe it shouldn't
be a fixed attribute for the player, or maybe should be "usual_position ".

I'd use a third "player_mat ch" table,
Fields:
- match_id
- player_id
- played_position
- goals_scored
- .... other stuff you might want to record, field time (replacements
etc), cardings, a comments field ....

I'd probably look at recording all players not just goal scorers in this
table, more input time but more data to play with.

Assuming you are only going to record the details for you own club, not
the whole league, then alter your "matches" table and just have a column
for "opponent_goals ", then (presuming they refer to league table points)
homepoints and visitorspoints are just derived values.

Geoff M

[NC]

frizzle wrote:

I have this soccer site, and in 1 part one
can manage the played matches:
Score, location, players who scored etc.

Now the last part is what i have a question about.
The players are in a MYSQL db. Each has it's own
unique ID.
Fields:
- id
- firstname
- lastname
- position
Bad idea. If a player changes a position, it will affect
your records of all previous matches in which that player
participated. Not to mention players changing teams...
Matches are also stored in the DB.
Fields:
- id
- opponent
- location
- date
- homepoints
- visitorspoints

If i have a new match, and the score is 5-3 how should
i add 5 player's ID of players who scored (in a yet to
create DB-field), in that way that i can get the person
that scored the most ... (topscorer)
And 2nd, and 3rd etc.

Here's what I would do...

Table players:
id
firstname
lastname

Table teams:
id
name

Table matches:
id
hometeam (joins with teams.id)
visitor (joins with teams.id)
location
date

Table rosters:
id
match (joins with matches.id)
player (joins with players.id)
team (joins with teams.id)
position

Table goals:
id
match (joins with matches.id)
scored_by_team (joins with teams.id)
scored_by_playe r (joins with players.id)

Note that score is not recorded anywhere; it should be computed
by quering the `goals` table. Note also that this data design
allows you to record (and find) goals that players accidentally
scored against their own teams.

Now, figuring out top scorers becomes rather easy:

SELECT
players.firstna me AS first,
players.lastnam e AS last,
COUNT(goals.id) AS goals_scored
FROM goals LEFT JOIN players
ON goals.scored_by _player = players.id
GROUP BY players.id
ORDER BY goals_scored DESC;

Cheers,
NC

[frizzle]

Wow, again, thanks for all this effort to help!!

'Position'-field is the position of players mostly, there will also be
e.g. haircolor, favorite music etc., so that won't be recorderd per
match.

Also own goals aren't going to be counted, but thanks for reminding me
of them! (Hope there won't be any ... :) )

I am going to record the score, because i want to leave a margin for
matches where people might forget who scored what goal, now they
can fill out 'unknown' to prevent the score from being incomplete ...

I think i'm going to use the following approach:

TABLE Players:
- id
- active
- firstname
- lastname
- position
- etc.

TABLE matches
- id
- opponent (id)
- location
- date
- home_goals
- visitor_goals
- etc.

TABLE goals
- id
- match_id
- player_id

players.active = 0 would mean a player isn't in the team anymore,
and is invisible in the team-list, but his score is counted, and his
name still appears in matches prior to his departure.

I believe this should work quite correctly, and be quite flexible.

Again, thanks a lot!
Still surprised time after time of the power of PHP/mySQL (either!)

Greetings Frizzle.