Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 2 of 2
  1. #1
    New to the CF scene
    Join Date
    Jun 2011
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Question Trouble with SELECT/UPDATE from multiple tables

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

  • #2
    New to the CF scene
    Join Date
    Jun 2011
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I realise that for updating it I need to include the fixtureID so that I can use the fixtureID in the where cluase, however, I still need the correct teamIDs so that also when I update I can update the leaguetable table.


  •  

    Tags for this Thread

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •