How to display previous selected dropdown values

Question

I'm a PHP newbie so hopefully my question is easy to comprehend.

I am trying to display the previously selected option from dropdown menu on my Edit/Update page. The dropdown menu displays all my teams that I have it fetch from a table called "team". I never use this table to insert data into - only get data from.

team

|id|name |
|--| --- |
|1 | MTL |
|2 | BOS |
|3 | CGY |
|4 | EDM |

There is also a second table called "crud" where all my submit results get inserted to. The 'home' and 'away' tabs show the team's ID from 'team' table. So for example in row 1, the home team is CGY and away team is MTL:

crud

|id|game#|home|away|goalsHome|goalsAway
|1 | 1   |3   |1   |         |
|2 | 2   |1   |2   |         |
|3 | 3   |2   |4   |         |
|4 | 4   |4   |3   |         |
|5 |     |    |    |         |

Currently, when I click the update button, it redirects me to update.php where i can submit all changes. Everything works perfectly but it always shows the default option for selected, rather than showing the previously selected value.

Because this dropdown is grabbing data from my teams table to display the options, I'm having a hard time displaying the previously selected value without messing with the options.

This is what my code looks like currently for the home option:

   <select name="home" type="text" id="" value="">

<?php
$query = "SELECT * FROM team";
$result = $con->query($query);
if ($result->num_rows > 0) {
    while ($row = mysqli_fetch_assoc($result)) {

?> 
 <option value="<?php echo $row['id']; ?>"selected="selected"><?php echo $row['name']; ?></option>
<?php
    }
}

?>
   </select>
    <label>Home Team</label>

So as you can see, my query is fetching all the teams from 'team' table, to display as options. Which is great, but when I try to show the previously selected value using join, it no longer displays all the teams from 'team' and instead will only display two teams (whichever teams from most recent 'crud' record). Here is my code changing only the query for the home team option:

$query = "SELECT * FROM team
INNER JOIN crud ON crud.home=team.id";

I need it to fetch the previously selected data from table 'crud', but then keep the options to select from table 'team'. So I feel I am on the right track and join statement is most likely the way to go here.

Any help or direction is much appreciated. I have found lots of other resources for achieving this for manual inputs in option, but not for when the options are fetched from a different table in database.

CODE (same as above)

   <select name="home" type="text" id="" value="">

<?php
$query = "SELECT * FROM team";
;
$result = $con->query($query);
if ($result->num_rows > 0) {
    while ($row = mysqli_fetch_assoc($result)) {

?> 
 <option value="<?php echo $row['id']; ?>"selected="selected"><?php echo $row['name']; ?></option>
<?php
    }
}

?>
   </select>
    <label>Home Team</label>

$query = "SELECT * FROM team
INNER JOIN crud ON crud.home=team.id";

Answer 1

Assuming that your are editing the crud table record with id=1, then for the "home field" :

You should change the line

<option value="<?php echo $row['id']; ?>"selected="selected"><?php echo $row['name']; ?></option>

to something like the following

 <option value="<?php echo $row['id']; ?>"

<?php if ($row["id"]==$row0["home"]) { echo " selected "; } ?>
    ><?php echo $row['name']; ?></option>

So the code will be

<?php

$query0 = "SELECT * FROM crud where id=1";
$result0 = $con->query($query0);
$row0 = mysqli_fetch_assoc($result0)

<select name=home>

<?php
$query = "SELECT * FROM team";
$result = $con->query($query);
if ($result->num_rows > 0) {
    while ($row = mysqli_fetch_assoc($result)) {
?> 

 <option value="<?php echo $row['id']; ?>"
<?php if ($row["id"]==$row0["home"]) { echo " selected "; } ?>
    ><?php echo $row['name']; ?></option>

<?php
    }
}

?>
   </select>