Hi all,
I wonder if anyone can help me.
I am creating a league management system and up to the point of creating a form to display all fixtures in the database with inputs allowing scores to be entered.
The fixtures are stored using the teamIDs of the teams in the fixture table which is realted to the teamID in the team table.
I have a join query which correctly displays the team names rather than their IDs for the user, however when displaying the teamIDs both the teams for one fixture share the same ID (I know for a fact that The Southfield is teamID 1 and Myton is teamID 3 E.g.
teamID | teamA | teamAScore | - | teamBScore | teamB | teamID
1 The Southfield 4 - 1 Myton 1
This doesn't make too much sense to me and I wondered if anyone could help as I think this is hindering my ability to update the table in the database as I can't supply it with one given ID, unless this is combatted in the update query itself..
PHP Code:
<?php
// Make a MySQL Connection
mysql_connect("localhost", "admin", "admin") or die(mysql_error());
mysql_select_db("lsl") or die(mysql_error());
//Query to join tables and obtain team names linked to IDs
$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());
//DO THE ABOVE BUT FOR UPDATE
echo "<table border='1' width='500'>";
echo "<form name='form1' method='post'>";
echo "<tr> <th>Team 1</th> <th>Score</th> <th> - </th> <th>Score</th> <th>Team 2</th> <th>Date</th></tr>";
while($row = mysql_fetch_array($sql2) ) {
// Print out the contents of each row i
echo "<tr><td>";
echo $row['teamA'] ;
echo "</td><td>";
echo "<input type='text' name='teamAScore[]' size='1' />";
echo "</td><td>";
echo " - ";
echo "</td><td>";
echo "<input type='text' name='teamBScore[]' size='1' />";
echo "</td><td>";
echo $row['teamB'];
echo "</td><td>";
echo $row['fixtureDay'], " ", $row['fixtureMonth']," ",$row['fixtureYear'] ;
echo "</td>";
echo "</tr>";
}
echo"<input type='submit' value='Submit Results' name='Submit'/>";
echo"</form>";
echo "</table>";
if($Submit){
for($i=0;$i<$count;$i++){
$sql1="UPDATE fixture, team SET fixture.teamAScore='teamAScore[$i]', fixture.teamBScore='teamBScore[$i]' WHERE team.teamID = fixture.teamA and team.teamID = fixture.teamB";
}
}
if($result1){
echo "Updated!";
}
mysql_close();
?>
So if anyone could look at the code and see where I have gone wrong and perhaps point me in the direction for updating the table too that would be great! (Just a quick note on updating, when you hit submit it does absolutely nothing...)
Once I get this sorted I then need to move on to using the update query to update the team in the league_table table based on their ID - I'll no doubt run into more problems!
All help would be appreciated.
Thanks in advance.
Aidan
N.B. I don't have an online version of this, I am working locally using XAMPP.
Before you post, read our: 
Trouble with SELECT/UPDATE from multiple tables
