joining two relatively simple tables

[galvin]

I have two tables like this...

 

"teams" (teamid, nickname):

Examples:

1, Eagles

2, Cowboys

3, Redskins

4, Giants

 

"schedule" (teamid, week1, week2, week3, week4, week5, etc, etc)

Examples:

1, @24, v21, @16, v03, @23, etc

2, @03, @13, v09, v08, BYE, etc

 

I simply want to output the schedules so it's like this...

 

Eagles:  @Browns, vRavens, @Cardinals, vGiants, @Steelers, etc

Cowboys:  @Giants, @Seahawks, vBuccaneers, vBears, BYE, etc

etc.etc.

 

I wrote the query below, but it's clearly wrong.  Can anyone shed any light to get me in the right direction?  It seems like I may have to query the database again during each loop, but that seems highly inefficient.

 

$sql = "SELECT *
FROM schedule, teams 
WHERE schedule.teamid = teams.teamid
ORDER BY teams.teamid";
$schedule= mysql_query($sql, $connection);
if (!$schedule) {
die("Database query failed: " . mysql_error());
} else {
while ($theschedule=mysql_fetch_array($schedule)) {
	$alldata .= $theschedule['nickname'] . "<br>";
	for ($w=1; $w<=17; $w++) {
		$alldata .= $theschedule['week' .$w] ."-" . $theschedule['nickname'];
	}
	$alldata .= "<br/>";
}
}

[xyph]

You want to change your database structure.

 

Change schedule to something like (game_id, home_id, away_id, week_no, season_no)

 

If the eagles (home) are playing the redskins (away) on week 5 season 1, you'd have a row like (auto#,1,3,5,1)

 

If you wanted to see all games played by the eagles, you'd have a query like

 

SELECT
  h.nickname as home,
  a.nickname as away,
  s.week_no
FROM
  schedule s
  LEFT JOIN teams h ON h.team_id = s.home_id
  LEFT JOIN teams a ON a.team_id = s.away_id
WHERE
  (s.home_id = 1 OR s.away_id = 1)
  AND
  s.season_no = 1
ORDER BY s.week_no

 

Which would give you something like

 

+----------+---------+---------+
| home     | away    | week_no |
+----------+---------+---------+
| eagles   | cowboys |       1 |
| redskins | eagles  |       2 |
| cowboys  | eagles  |       3 |
+----------+---------+---------+

 

Here's the tables/data I used

--
-- Table structure for table `schedule`
--

CREATE TABLE IF NOT EXISTS `schedule` (
  `game_id` int(11) NOT NULL AUTO_INCREMENT,
  `home_id` int(11) NOT NULL,
  `away_id` int(11) NOT NULL,
  `week_no` int(11) NOT NULL,
  `season_no` int(11) NOT NULL,
  PRIMARY KEY (`game_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=5 ;

--
-- Dumping data for table `schedule`
--

INSERT INTO `schedule` (`game_id`, `home_id`, `away_id`, `week_no`, `season_no`) VALUES
(1, 1, 3, 1, 1),
(2, 2, 1, 2, 1),
(3, 2, 3, 1, 1),
(4, 3, 1, 3, 1);

-- --------------------------------------------------------

--
-- Table structure for table `teams`
--

CREATE TABLE IF NOT EXISTS `teams` (
  `team_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `nickname` varchar(25) NOT NULL,
  PRIMARY KEY (`team_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=4 ;

--
-- Dumping data for table `teams`
--

INSERT INTO `teams` (`team_id`, `nickname`) VALUES
(1, 'eagles'),
(2, 'redskins'),
(3, 'cowboys');

[galvin]

Interesting. It seems like more work to set up the table initially, but you're saying it makes more sense to do it this way, so I trust you   Is this a specific, easy to explain reason why it's better to set it up this way?  I just want to understand it before I implement since my ultimate site will have several different areas pulling data from these tables.

 

Also, I'm curious how I would indicate a team's BYE week using this structure.  I bet you're going to tell me there should be a separate table for "byeweeks", right?

 

 

 

 

[Jessica]

I'd do it so if it's a BYE week then the away team id will be NULL.

[xyph]

You could also simply omit the row from the schedule, but this would require a little extra work on the PHP side.

 

jesirose's suggestion is good, but I would simply set the away team's ID to 0 (avoid using NULL if you can )

 

The reason it's better to do it this way is flexibility. Say in season 4, an extra week gets added to the schedule. You don't have to change anything on the database side to implement this, each team will simply get one more row in the schedule table. If you include scores this way, tracking very specific statistics becomes way easier as well.

 

Say you want to grab all games that team_id 1, 2 and 4 plays at home this season.

 

With your old system, you'd have to grab the rows, and parse each week for each row, determine if that specific game is at home, convert the opponent's ID to a nickname and display.

 

With a normalized system, like the one I showed you, you can do that ENTIRELY in your query

 

SELECT
  s.week_no,
  h.nickname as team,
  a.nickname as opponent
FROM
  schedule s
  LEFT JOIN teams h ON s.home_id = h.team_id
  LEFT JOIN teams a ON s.away_id = a.team_id
WHERE
  s.home_id IN (1,2,4)
  AND
  s.season_no = 1
ORDER BY
  s.home_id, s.week_no

 

It pretty much lets the database filter through the data for you, so the results you get don't have to be further parsed, filtered, or converted.

 

When it comes to getting team/player statistics, it's really the only way to do it

[galvin]

Well, you've completely sold me.  Makes perfect sense, thank you so much for the explanation.  I wasted a couple hours populating my schedule table but I did it on work's time, so it could have been worse  

Thanks again!

[xyph]

You can write a script to covert it over to my method quite easily

 

Once the data's entered, it just needs to be converted, not re-entered. Then again, if it's faster to just re-enter it, go that way

 

It's possible to over-normalize (multi-joins can get slow), but if it's any sort data you may want to filter or search later, it's worth it to normalize. It also becomes way easier to index effectively.

[awjudd]

There is no need to use a LEFT JOIN here if you know all teams must be in the teams table, you can just use a regular INNER JOIN (more efficient).

 

~awjudd

[xyph]

There might be a case where there is no reference ID (the case of a bye-week)

 

But yes, the home table could be an inner join. Thanks.