Query to link tables and extract team name linked to ID

[ade2901]

Firstly apologies if this has been posted elsewhere. I've spent a considerable amount of time trying to find an answer to this and seem to be getting nowhere!

 

I wonder if anyone can help me to do what I need to do. Basically I have a table that stores the fixtures of games to be played. The fixtures are stored with the by the IDs of the team from the teamTable (teamID). Obviously as there are two teams in one game of football I need to have the teamID put in twice (column names; teamA, teamB). This is stored correctly. I then want to extract the results from the table but for the viewer the teamIDs mean nothing, I therefore need to extract the team names linked to the teamIDs in the team table.

 

Below is the code I have so far;

 

<?php
// Make a MySQL Connection
mysql_connect("localhost", "username", "pwd") or die(mysql_error());
mysql_select_db("lsl") or die(mysql_error());

// Get all the data from the "example" table
$result = mysql_query("SELECT * FROM fixture") 
or die(mysql_error());  

//$teamNameA = mysql_query("SELECT teamName FROM fixture, team WHERE team.teamID = fixture.teamA")or die(mysql_error());  
//$teamNameB = mysql_query("SELECT teamName FROM fixture, team WHERE team.teamID = fixture.teamB")or die(mysql_error());  

echo "<table border='1'>";
echo "<tr> <th>Team 1</th> <th>Team 2</th> <th>Year</th><th>Venue</th> </tr>";
// keeps getting the next row until there are no more to get
while($row = mysql_fetch_array( $result )  ) {
// Print out the contents of each row into a table
echo "<tr><td>"; 
echo $row['teamA'];
echo "</td><td>"; 
echo $row['teamB'];
echo "</td>"; 
echo "<td>"; 
echo $row['fixtureYear'];
echo "</td><td>"; 
echo $row['fixtureVenue'];
echo "</td></tr>"; 

} 

echo "</table>";
?>

 

This returns the information from the fixtures table and the correct teams, venue and year, however it doesn't display the team name, which is what I need it to do. I've tried many different ways of restructuring the SELECT query such as including the team table however, I don't ever seem to get a working product. Also, it's important to stress teamA and teamB are just the teamIDs. There is foreign key set up for both of these columns and in a previous attempted query I have has the correct team names showing (although nothing in the right order) which shows that the tables are linked correctly and the foreign key constraints are working. (There are two foreign key constraints as there are two columns that require the teamID)

 

So, if anyone could help me to restructure what I have so that the teamNames are showing instead of the IDs I would be much appreciative, and I presume it'll be to do with my query. Once the query is sorted I'll then be able to change the fields it is printing out from teamA/teamB to teamName in both (which is the name of the column in the team table with the team name in).

 

I am a bit new to this level of PHP but if anyone can help me I'd be much appreciative!

 

Thanks in advance,

 

Aidan

[WebStyles]

maybe a JOIN will help. here's an example:

 

$result = mysql_query("SELECT `fixture`.*,`team`.`teamName` FROM `fixture` LEFT JOIN `team` ON `fixture`.`teamA` = `team`.`teamID`")or die(mysql_error()); 

 

This would select everything from fixture, and for each line it will grab the teamA id nr, and lookup the appropriate TeamName in a table called `team`.

 

Check out all mysql join functions: inner join, left join, right join...

 

hope this helps

[jtm62]

Firstly apologies if this has been posted elsewhere. I've spent a considerable amount of time trying to find an answer to this and seem to be getting nowhere!

 

I wonder if anyone can help me to do what I need to do. Basically I have a table that stores the fixtures of games to be played. The fixtures are stored with the by the IDs of the team from the teamTable (teamID). Obviously as there are two teams in one game of football I need to have the teamID put in twice (column names; teamA, teamB). This is stored correctly. I then want to extract the results from the table but for the viewer the teamIDs mean nothing, I therefore need to extract the team names linked to the teamIDs in the team table.

 

Below is the code I have so far;

 

<?php
// Make a MySQL Connection
mysql_connect("localhost", "username", "pwd") or die(mysql_error());
mysql_select_db("lsl") or die(mysql_error());

// Get all the data from the "example" table
$result = mysql_query("SELECT * FROM fixture") 
or die(mysql_error());  

//$teamNameA = mysql_query("SELECT teamName FROM fixture, team WHERE team.teamID = fixture.teamA")or die(mysql_error());  
//$teamNameB = mysql_query("SELECT teamName FROM fixture, team WHERE team.teamID = fixture.teamB")or die(mysql_error());  

?>

 

Doing something similar to the below should result in what you are looking for, with a caveat:

 

$sql="select f.fixtureYear, f.fixtureVenue, tt1.teamName as t1, tt2.teamName as t2 from fixture f, team tt1, team tt2 where f.teamID=tt1.teamID and f.teamID=tt2.teamID and tt1.t1<>tt2.t2";

 

The problem with the above is it should return two results per game... because either team can be team one or team two... so it will produce a result set with it both ways.  If you can come up with a way to define the home team as team one it will only return one result (but this will require additional tables... and if it is a neutral site you need more logic...)

[WebStyles]

another way to solve that (and speed up your query) would be to store the team names in fixture instead of the team ids (unless there are duplicate team names)

[ade2901]

Thanks for the replies.

 

WebStyles; I was wondering about something like a join however, I need to include both teamA and teamB which unfortunately makes the issue more complex. Also, I was thinking aobut just using team names but I can't guarantee that in the future there won't be a duplicate team name enter into the league. Otherwise I'd have just gone that way to vastly simplify things. Good suggestions though!

 

jtm62; I'll give that a go, but it is a neutral site as it is for a league and not a specific team so it probably will have me run into issues as you suggest..

[ade2901]

I have another little issue. I tried your solution jtm62 but realised I needed to alter it slightly to the following:

 

$sql= mysql_query ("SELECT f.fixtureYear, f.fixtureVenue, tt1.teamName as t1, tt2.teamName as t2 FROM fixture f, team tt1, team tt2 WHERE f.teamA=tt1.teamID and f.teamB=tt2.teamID and tt1.t1<>tt2.t2")or die(mysql_error());  

 

Where you had f.teamID I had to change one to teamA and one to teamB as I store the name IDs in teamA and teamB columns in the fixture table. I am receiving the following error now; Unknown column 'tt1.t1' in 'where clause'. I am aware of its meaning but can't understand why it would not be able to find the column..

 

If someone has an idea on this then I'd certainly appreciate the help!

 

Thanks,

 

Aidan

[ade2901]

If it is easier to change the structure of my tables then I would happily accept suggestions on that!

 

At the moment my team table looks like the following;

 

[team]

teamID

teamName

teamSecretary

teamContactNo

teamColours

 

[fixture] - used to store fixtures then these will be extracted and the user can input the scores from that will eventually lead onto a league table.

fixtureID

teamA (int; foreign key from teamID in team table)

teamB (int; foreign key from teamID in team table)

teamAScore

teamBScore

fixtureDay

fixtureMonth

fixtureYear

fixtureVenue

fixtureReferee

 

I'm aware I could use a date time field, however I need to run queries on just the month and it makes it a bit more fool proof for the end user.

[ade2901]

Solved the issue now with a join, got the details printing out as per the data in the database.

 

Solution:

 

$sql2= mysql_query ("SELECT fixtureVenue, t.TeamName as teamA, tt.TeamName as teamB
from team t inner join fixture f
on f.teamA = t.teamid
inner join team tt
on f.teamB = tt.teamid")or die(mysql_error());  

 

Thanks for the suggestions of the joins and all other suggestions here, you put me in the right path. Many thanks! I'll no doubt be posting again for help on other aspects!

[ade2901]

Since thinking I had solved it I clearly haven't!

 

I had the teamIDs output with the teamNames after having issues with updating and it appears as though each team in one row share the same ID.. E.g.

 

FixtureID | teamName teamID | teamAScore | teamBScore | teamB teamBScore

 

19 The Southfield4 0 - 0 Teal Arms4

 

The Southfield is actually ID number 1 and Team Arms are ID number 4...

 

Does anyone have an idea why this is happening despite the table join?