Jump to content

joining two relatively simple tables


galvin

Recommended Posts

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/>";
}
}

Link to comment
Share on other sites

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');

Link to comment
Share on other sites

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?

 

 

 

 

Link to comment
Share on other sites

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 :D

Link to comment
Share on other sites

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!

Link to comment
Share on other sites

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

 

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 :D

 

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.

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.