Advice requested on system.

Do you have a question? Post it now! No Registration Necessary.  Now with pictures!

Threaded View
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.
    - id
    - firstname
    - lastname
    - position

Matches are also stored in the DB.
    - 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.

Re: Advice requested on system.

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

Quoted text here. Click to load it

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

And you would change the match field "opponent" to "home_team_id" and

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


    JDS | jeffrey@example.invalid

Re: Advice requested on system.

Quoted text here. Click to load it

The players who scored in the match could be stored in a table like
Name: matchscores

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

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

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.


Re: Advice requested on system.

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
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
(invisible for visitors (of the site, not a match ;) )

Thanks again and have a nice evening!

Greetings frizzle!

Re: Advice requested on system. says...
Quoted text here. Click to load it

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_match" table,
 - 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

Re: Advice requested on system.

frizzle wrote:
Quoted text here. Click to load it

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

Quoted text here. Click to load it

Here's what I would do...

Table players:

Table teams:

Table matches:
  hometeam (joins with
  visitor (joins with

Table rosters:
  match (joins with
  player (joins with
  team (joins with

Table goals:
  match (joins with
  scored_by_team (joins with
  scored_by_player (joins with

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:

  players.firstname AS first,
  players.lastname AS last,
  COUNT( AS goals_scored
  FROM goals LEFT JOIN players
    ON goals.scored_by_player =
  ORDER BY goals_scored DESC;  


Re: Advice requested on system.

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

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 = 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.

Site Timeline