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!

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.

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

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..

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