one player table - two team dropdowns - how to get no double players (mysql,php,ajax)

Question

I've got one player table where all players are stored. When I'll will select the players for the home-team and the away-team. I've got two different sql queries on the player table.

 <? 
  $select_query = mysql_query("SELECT * FROM $player_table_2016 WHERE active = 1 ORDER by tname ASC"); 
   echo "<select data-placeholder='Spieler auswählen...' class='chosen-select' style='width:220px;' multiple tabindex='4' name='homespieler[]' required>";
      while ($row = mysql_fetch_array($select_query)) {
      echo "<option value='".$row['id']."'>" .$row['tname']."</option>";
     </option>";
    }
echo "</select>";      
?>

after that I select the away-team with another query:

$select_query = mysql_query("SELECT * FROM $player_table_2016 WHERE active = 1 ORDER by tname ASC"); 
   echo "<select data-placeholder='Spieler auswählen...' class='chosen-select' style='width:220px;' multiple tabindex='4' name='awayspieler[]' required>";
      while ($row = mysql_fetch_array($select_query)) {
      echo "<option value='".$row['id']."'>" .$row['tname']."</option>";
    </option>";
    }
echo "</select>"; 

The problem is, that I'm not able to check in the away-team selection, if a player is already selected for home-team. It would be great to choose in the away-team only those players, who aren't alreay in the home-team.

How can this be possible?

Answer 1

You can use ajax for such scenarios. Once user selects all the players for home team use jQuerys .blur event and pass all the id's to php. And than you can use query select * from table where id not in ($id) where $id is value received through POST or GET method