sql/php help

[homer15]

hey everyone,

i have an sql table that is laid out as follows:

|ID|Team|Date|Score|

in the ID column, only 2 are going to have the same ID. the ID stands for the game played.

i need to build a page that'd display this properly. for every team, i want to select the game id, appropriate teams and display the date and the scores (preforable, the higher score on top).

here is an example of what i'm talking about.

thanks for any help you can give.

[mairving]

Two tables should be the answer.

Table 1 ***teams***
id int(11) auto_increment primary key,
name varchar(50),
nickname varchar(50),
description text

Table 2 ***games***
id int(11) auto_increment primary key,
winner_id int(11),
loser_id int(11),
score varchar(5),
game_date date

So with some data in it, it would look like so
***teams***
id | name | nickname | description
1 | New York | Mets | Losers
2 | St Louis | Cardinals | Winners

***games***
id | winner_id | loser_id | score | game_date
1 | 2 | 1 | 10-1 | 20050418

[mairving]

Now if you wanted to do anything like calculate total runs scored and such, you would need to probably make something like
winner_score and loser_score, instead of just the score field.

You also don't need to separate out the nickname but it could be handy.

[homer15]

thanks mairving. do you have any idea how i could get this into the table format i want using php?

[faulkner132]

Assuming you make the winner and loser score like mairving was talking about, your SQL would look like this:

Games won:
SELECT w.name, l.name, g.winner_score, g.loser_score, g.date
FROM games g
INNER JOIN teams w ON g.winner_id=t.id
INNER JOIN teams l ON g.loser_id=t.id
WHERE w.name='team name'
ORDER BY g.date

Games lost:
SELECT w.name, l.name, g.winner_score, g.loser_score, g.date
FROM games g
INNER JOIN teams w ON g.winner_id=t.id
INNER JOIN teams l ON g.loser_id=t.id
WHERE l.name='team name'
ORDER BY g.date

This will pull all of a team's results (in two record sets) ordered by date. From here you can dynamically build your tables using php's built in SQL functions.