Managing a sports league

I have a mysql table which gives results of matches played:
mysql> desc PLG;
+----------+------------------+------+-----+---------+------ ----------+
| Field | Type | Null | Key | Default | Extra |
+----------+------------------+------+-----+---------+------ ----------+
| matchid | int(10) unsigned | | PRI | 0 | auto_increment |
| gamedate | date | YES | | NULL | |
| homeTeam | varchar(30) | YES | | NULL | |
| htg | smallint(2) | YES | | NULL | |
| atg | smallint(2) | YES | | NULL | |
| awayTeam | varchar(30) | YES | | NULL | |
+----------+------------------+------+-----+---------+------ ----------+

where htg = home team goals
where atg = away team goals

Is there a [simple*] way to create a league table?
(using PHP 4, mysql 3.22.32)
Any rhoughts?
Thanks.
Mick

Re: Managing a sports league

mick white wrote:
> I have a mysql table which gives results of matches played:
> mysql> desc PLG;
> +----------+------------------+------+-----+---------+------ ----------+
> | Field | Type | Null | Key | Default | Extra |
> +----------+------------------+------+-----+---------+------ ----------+
> | matchid | int(10) unsigned | | PRI | 0 | auto_increment |
> | gamedate | date | YES | | NULL | |
> | homeTeam | varchar(30) | YES | | NULL | |
> | htg | smallint(2) | YES | | NULL | |
> | atg | smallint(2) | YES | | NULL | |
> | awayTeam | varchar(30) | YES | | NULL | |
> +----------+------------------+------+-----+---------+------ ----------+
>
> where htg = home team goals
> where atg = away team goals
>
> Is there a [simple*] way to create a league table?
> (using PHP 4, mysql 3.22.32)
> Any rhoughts?
> Thanks.
> Mick

First create a "team" table:

teamid
name

Change your matches table so that homeTeam and awayTeam are foreign
keys to the team table rather than the name of the team.

Now create a "league" table:

leagueid
name

Can a single team be in more than one league? If not, add a leagid
column to your team table. If a single team can be in multiple
leagues, create a leagueTeam table:

leagueid
teamid

If you have a row in there, it indicates that the team represented by
teamid is a member of the league represented by leagueid.

Re: Managing a sports league

ZeldorBlat wrote:

> mick white wrote:
>
>>I have a mysql table which gives results of matches played:
>>mysql> desc PLG;
>>+----------+------------------+------+-----+---------+---- ------------+
>>| Field | Type | Null | Key | Default | Extra |
>>+----------+------------------+------+-----+---------+---- ------------+
>>| matchid | int(10) unsigned | | PRI | 0 | auto_increment |
>>| gamedate | date | YES | | NULL | |
>>| homeTeam | varchar(30) | YES | | NULL | |
>>| htg | smallint(2) | YES | | NULL | |
>>| atg | smallint(2) | YES | | NULL | |
>>| awayTeam | varchar(30) | YES | | NULL | |
>>+----------+------------------+------+-----+---------+---- ------------+
>>
>>where htg = home team goals
>>where atg = away team goals
>>
>>Is there a [simple*] way to create a league table?
>>(using PHP 4, mysql 3.22.32)
>>Any rhoughts?
>>Thanks.
>>Mick
>
>
> First create a "team" table:
>
> teamid
> name
>
> Change your matches table so that homeTeam and awayTeam are foreign
> keys to the team table rather than the name of the team.
>
> Now create a "league" table:
>
> leagueid
> name
>
> Can a single team be in more than one league? If not, add a leagid
> column to your team table. If a single team can be in multiple
> leagues, create a leagueTeam table:
>
> leagueid
> teamid
>
> If you have a row in there, it indicates that the team represented by
> teamid is a member of the league represented by leagueid.
>
Thanks, Z, Ill play with it.
Mick