在没有POST的情况下将数据从DB加载到下拉列表中

Question

I have 2 dropdown select in a HTML modal, one is for teams and the other is for players.

Teams dropdown select data are extracted from a DB. Each player is assigned to a team.

What I'm trying to do is when I change the teams dropdown select, the players dropdown select change according to the selected team which means it will contain only the players of the selected team.

==> I want to do that without CLOSING the modal and without REFRESHING the page.

<select id="teams" name="teams">
 <?php
 $sql = $bdd->query('SELECT * FROM teams;');
 while($fetch = $sql->fetch(PDO::FETCH_ASSOC)){ ?>                      
   <option id="opt-<?php echo $fetch['id_team']; ?>"><?php echo $fetch['name']; ?></option>
 <?php } ?>
</select>

<select id="players" name="players">
 <?php
 $sql = $bdd->query('SELECT * FROM players where id_team=...;');
 while($fetch = $sql->fetch(PDO::FETCH_ASSOC)){ ?>                      
   <option id="opt-<?php echo $fetch['id_player']; ?>"><?php echo $fetch['name']; ?></option>
 <?php } ?>
</select>

How can this be done ?

Answer 1

I hope this will help. You will need to use Ajax and Jquery library.

main.php

<script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>
<select id="teams" name="teams">            
 <?php
 $sql = $bdd->query('SELECT id_team, name FROM teams;');
 while($fetch = $sql->fetch(PDO::FETCH_ASSOC)){ ?>                      
   <option id="opt-<?php echo $fetch['id_team']; ?>"><?php echo $fetch['name']; ?></option>
 <?php } ?>
</select>

<select id="players" name="players"></select>

<script>
    $( "#teams" ).change(function() {
        var id = $(this).children(":selected").attr("id");
        var id_split = id.split("-");
        $.ajax({
            url: "getPlayers.php",
            type: "post",
            data: {id: id_split[1]},
            success: function (response) {
                document.getElementById("players").innerHTML = response;
            },
            error: function(jqXHR, textStatus, errorThrown) {
                console.log(textStatus, errorThrown);
            }
        });
    });
</script>

getPlayers.php

<?php
if(isset($_POST['id'])){
  $id = $_POST['id']; 
    //sql connection
  $sql = $bdd->query("SELECT id_player, name FROM players where id_team = $id");
  while($fetch = $sql->fetch(PDO::FETCH_ASSOC)){ ?>                      
    <option id="opt-<?php echo $fetch['id_player']; ?>"><?php echo $fetch['name']; ?></option>
 <?php }
}
?>