By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,994 Members | 1,504 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,994 IT Pros & Developers. It's quick & easy.

Creating an array of unique entries from a database table.

P: 52
I'm trying to build a football leage table taking it's content from a results database,
Expand|Select|Wrap|Line Numbers
  1. CREATE TABLE conference (
  2.  id int NOT NULL auto_increment primary key,
  3.  season VARCHAR(50) NOT NULL,
  4.  gamedate DATE NOT NULL,
  5.  hometeam VARCHAR(50) NOT NULL,
  6.  homescore VARCHAR(30) NOT NULL,
  7.  awayscore VARCHAR(50) NOT NULL,
  8.  awayteam VARCHAR(30) NOT NULL);
  9.  
  10. INSERT INTO conference VALUES
  11. (1, '1986-87', '1986-08-16', 'Altrincham', '1', '1', 'Bath City'), 
I'm having trouble getting started, I'm thinkng I need to set up an array of the teams in a season and assign numbers to them so I can make the calculations for the league table. Problem is I'm having trouble finding out how to make the array of the teams and give them an id.

This is a bit of a mess but it's just to let you see where i'm at
[PHP]echo "<table border=\"0\" align=\"center\" cellspacing=\"1\" cellpadding=\"5\">";
echo"<tr>";
//--------------------------------------------------------------------------------------
$query = ("SELECT DISTINCT hometeam FROM conference WHERE season LIKE '2006-07' ");
$result = mysql_query($query) or die(mysql_error());
while($row = mysql_fetch_array($result))
{
$hometeam = $row['hometeam'];
//--------------------------------------------------------------------------------------
$numbers = explode(",", $row['hometeam']);
for ($i=0; $i < sizeof($numbers); $i++)
echo"<td>$numbers[$i]</td>";
//--------------------------------------------------------------------------------------

$team_array = array($row['hometeam']);
for($i=0; $i<3; $i++)
echo"<td><div align=\"right\">$team_array[$i]</div></td>";
//--------------------------------------------------------------------------------------[/PHP]

Hope you can help
Aug 1 '07 #1
Share this Question
Share on Google+
6 Replies


Motoma
Expert 2.5K+
P: 3,237
Here is how I would design the database:

Expand|Select|Wrap|Line Numbers
  1. CREATE TABLE conference (
  2.  id INT NOT NULL auto_increment primary key,
  3.  season VARCHAR(50) NOT NULL,
  4.  gamedate DATE NOT NULL,
  5.  hometeam INT NOT NULL,
  6.  homescore INT NOT NULL,
  7.  awayscore INT NOT NULL,
  8.  awayteam INT NOT NULL);
  9.  
  10. CREATE TABLE team(
  11.  id INT NOT NULL auto_increment primary key,
  12.  teamname VARCHAR(50) NOT NULL);
  13.  
NOTE: I'm not sure what the season will hold, so it's type might not need to be a varchar.

The hometeam and awayteam fields in the conference table will be the id field from the team table:

Expand|Select|Wrap|Line Numbers
  1. INSERT INTO team (teamname) VALUES ("Cougars")
  2. INSERT INTO team (teamname) VALUES ("Jaguars")
  3. INSERT INTO conference (season, gamedate, hometeam, awayteam, homescore, awayscore) VALUES ("season", NOW(), 1, 2, 36, 42)
  4.  
Aug 1 '07 #2

P: 52
I was hoping to keep the database as is because each season will have a handful of new teams, its also used by other things and it's much easer for me to update.

The second query gives me a Distinct list of all teams in a particular season. is it possible to use this list to start?
Aug 1 '07 #3

Motoma
Expert 2.5K+
P: 3,237
I was hoping to keep the database as is because each season will have a handful of new teams, its also used by other things and it's much easer for me to update.

The second query gives me a Distinct list of all teams in a particular season. is it possible to use this list to start?
Please read Mary's excellent article on Database Normalization.

In the scenario you've described, teams and conferences are two different entities, as such, they should be represented in two different tables.

For the application you have listed, your original layout is just fine, however, you will not be able to use it in any way you could use a flat file data set. Essentially, it defeats the purpose of using a relational database.
Aug 1 '07 #4

pbmods
Expert 5K+
P: 5,821
Heya, hinksta.

Creating a separate teams table will actually make your life a lot easier, as then you don't have to worry about data duplication.

Also, then you don't have to worry about a team 'disappearing' just because it hasn't played any games yet.

And finally, it actually makes your distinct team search run faster and more reliably.

If you have any questions, post back anytime :)

P.S., Changed thread title to better describe the problem.
Aug 1 '07 #5

P: 52
When I started the first part of the project (A simple football results listing) I started the database as you describe, sadly it was before finding this great site and I couldn't figure out how to get the two joined (still donít know how) and in many ways I thought it would be ok.

I read a comment in the article about confusion and problems regarding the way I have the database, maybe If I try to stick to the rules for better practice it will all become clearer.

The database is easy to change, its just getting them tables talking that's a problem.
Aug 1 '07 #6

P: 52
Thank you both for your help

While looking at the mysql
Expand|Select|Wrap|Line Numbers
  1. ("season", NOW(),
season is a number '2005-06', '2006-07' each season starts in August and ends in May the following year.
I'm guessing NOW() grabs the server time so I would want to input the date manually 2006-08-16

how does this look
Expand|Select|Wrap|Line Numbers
  1. CREATE TABLE premiership (
  2.  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  3.  season VARCHAR(50) NOT NULL,
  4.  gamedate DATE NOT NULL,
  5.  hometeam INT NOT NULL,
  6.  homescore INT NOT NULL,
  7.  awayscore INT NOT NULL,
  8.  awayteam INT NOT NULL);
  9.  
  10. INSERT INTO premiership (season, gamedate, hometeam, awayteam, homescore, awayscore) VALUES ("2005-06", "2005-08-15", 1, 2, 2, 3)
  11. INSERT INTO premiership (season, gamedate, hometeam, awayteam, homescore, awayscore) VALUES ("2005-06", "2005-08-18", 3, 4, 4, 1)
  12. INSERT INTO premiership (season, gamedate, hometeam, awayteam, homescore, awayscore) VALUES ("2006-07", "2006-08-16", 1, 2, 0, 3)
  13. INSERT INTO premiership (season, gamedate, hometeam, awayteam, homescore, awayscore) VALUES ("2006-07", "2006-08-16", 3, 4, 2, 1)
and how do you get code = mysql
Aug 1 '07 #7

Post your reply

Sign in to post your reply or Sign up for a free account.