How to hide selected value in dropdown?

Question

I always got the wrong result every time I choose a value in the select dropdown. I want the process to be like this, after selecting a busnum (bus number), the next dropdown will show the available seat numbers (base on busnum, sched date, and sched time) except for those numbers that are already taken. But every time I run my code, it shows the available seats but it only hides one seat number (taken) when that busnum has multiple taken seats. For example, I select busnum 30, then busnum 30 has already 3 taken seats (1, 3, 9) so if I clicked the next dropdown (for seat numbers), it is supposed to hide 1, 3, 9 out of 15. But it only hides 1 and the 3 and 9 still show. This is my code from that part so far:

 $sel = "SELECT * FROM ticket";
  $rslt = mysqli_query($conn, $sel);
  if($rslt->num_rows>0)
  {
      while($rows = $rslt->fetch_assoc())
      {

    $date = $rows['Tk_Sch_Date'];
    $time = $rows['Tk_Sch_Time'];

    $sel2 = "SELECT * FROM ticket WHERE Bus_Plate_Number='".$_GET['busnum']."' AND Tk_Sch_Date = '".$date."' AND Tk_Sch_Time='".$time."' AND Tk_Stat = 'PAID'";
    $rslt = mysqli_query($conn, $sel2);
    if($rslt->num_rows>0)
    {
      while($data = $rslt->fetch_assoc())
      {
        $chsnseat = $data['Tk_Chsn_Seat'];

        $sel3 = "SELECT * FROM seat WHERE NOT Seat_Num ='".$chsnseat."'";
        $result = mysqli_query($conn, $sel3);
        if($result->num_rows>0)
        {
          while($rows=$result->fetch_assoc())
          {
            ?>
            <option value="<?=$rows['Seat_Num']?>"><?=$rows['Seat_Num']?></option>
            <?php
          }
        }
      }
    }
  }


}
  else
  {
          $sel = "SELECT * FROM seat";
          $result = $conn->query($sel);
          if($result->num_rows>0)
          { 
            while ($rows = $result->fetch_assoc())
            {
              ?>
              <option value="<?php echo $rows['Seat_Num']; ?>"><?php echo $rows['Seat_Num']; ?>
              </option>
              <?php
            }
          }
  }
}

**Warning:** You are wide open to [SQL Injections](https://php.net/manual/en/security.database.sql-injection.php) and should use parameterized **prepared statements** instead of manually building your queries. They are provided by [PDO](https://php.net/manual/pdo.prepared-statements.php) or by [MySQLi](https://php.net/manual/mysqli.quickstart.prepared-statements.php). Never trust any kind of input! Even when your queries are executed only by trusted users, [you are still in risk of corrupting your data](http://bobby-tables.com/). [Escaping is not enough!](https://stackoverflow.com/q/5741187) — Dharman, Nov 03 '21 at 09:34

score 1 · Accepted Answer · answered Nov 03 '21 at 03:47

You're only filtering out one seat at a time. So on the first iteration you filter out seat 1, but show seats 3 and 9. On the second iteration you filter out 3, but show 1 and 9. And so on.

You need to join the tables so you filter out all the taken seats at once.

$sel2 = "
    SELECT s.*
    FROM seat AS s
    LEFT JOIN ticket AS t ON s.seat_num = t.Tk_Chsn_Seat
        AND Bus_Plate_Number=? AND Tk_Sch_Date = ? AND Tk_Sch_Time= ? AND Tk_Stat = 'PAID'
    WHERE t.Tk_Chsn_Seat IS NULL";
$stmt = $conn->prepare($sel2);
$stmt->bind_param("sss", $_GET['busnum'], $date, $time);
$stmt->execute();
$result = $stmt->get_result();

You also should use prepared statements instead of substituting variables into the SQL, to protect against SQL injection.

Your query is correct but I just realized that $date and $time should not be taken from the database but from the stored data in $_SESSION. Do you have any idea how to get those stored data and place it in one of the parameters? — gellooo, Nov 03 '21 at 05:43

How to hide selected value in dropdown?

1 Answers1