php - Output rows with Ids that arent in foreach loop

I have a function as follows to populate a select input.

$query1 = "SELECT id, address_line1, address_line2, city, state, country FROM addresses";
if($stmt = $connection->prepare($query1)){
  $stmt->execute();
  $stmt->bind_result($id, $addressLine1, $addressLine2, $city, $state, $country);
  while($stmt->fetch()){
    $addressOptions .= '<option value="'.$id.'">'.$addressLine1.", ".$addressLine2.", ".$city.", ".$state.", ".$country.'</option>';
  }
  $stmt->close();
}

This works fine except now I want to make it so it doesn't make an <option> if it has an id, that is in one of the objects in my foreach loop.

This is what i have tried

$query1 = "SELECT id, address_line1, address_line2, city, state, country FROM addresses";
if($stmt = $connection->prepare($query1)){
  $stmt->execute();
  $stmt->bind_result($id, $addressLine1, $addressLine2, $city, $state, $country);
  while($stmt->fetch()){
    foreach($takenAddresses as $taken) {
      if($id != $taken['id']) {
        $addressOptions .= '<option value="'.$id.'">'.$addressLine1.", ".$addressLine2.", ".$city.", ".$state.", ".$country.'</option>';
      }
    }
  }
  $stmt->close();
}
```

The issue I am facing is that it shows the correct options, but each of them multiple times. If I switch it to `==` then it shows the options i want to NOT be there. But `!=` shows the options I want to be there but duplicated

EDIT

```
$query1 = "SELECT id, address_line1, address_line2, city, state, country FROM addresses";
if($stmt = $connection->prepare($query1)){
  $stmt->execute();
  $stmt->bind_result($id, $addressLine1, $addressLine2, $city, $state, $country);
  while($stmt->fetch()){
    if(!in_array($id, $takenAddresses)) {
      $addressOptions .= '<option value="'.$id.'">'.$addressLine1.", ".$addressLine2.", ".$city.", ".$state.", ".$country.'</option>';

    }
  }
  $stmt->close();
}
```

Answer

Solution:

You can filter the ids you don't want directly from the sql query using a WHERE NOT IN (...) clause.

This is a bit tricky to parameterize a WHERE NOT IN () query, but, based on this excellent answer this can be done this way :

// You want a WHERE NOT IN (?, ?, ?...) with as much ? as there are elements in your array $takenAddresses

// extract the unwanted ids from the original array :
$unWantedIds = [];
foreach($takenAddresses as $taken) {
    $unWantedIds[] = taken['id'];
}

$params = implode(",", array_fill(0, count($unWantedIds), "?")); // This provide a string "?,?,?,?" if you have 4 elements in $unWantedIds

// TODO : Ensure that $params or $unWantedIds aren't empty

// prepare the query

Answer

--v

Answer

---v $query1 = "SELECT id, address_line1, address_line2, city, state, country FROM addresses FROM addresses WHERE id NOT IN ($params)"; if($stmt = $connection->prepare($query1)){ // bind the parameters (use i if id is INTEGER) $types = str_repeat("i", count($unWantedIds)); $args = array_merge(array($types), $unWantedIds); call_user_func_array(array($stmt, 'bind_param'), ref($args)); $stmt->execute(); $stmt->bind_result($id, $addressLine1, $addressLine2, $city, $state, $country); while($stmt->fetch()) { // you don't need your inner loop anymore, the results should now be filtered $addressOptions .= '<option value="'.$id.'">'.$addressLine1.", ".$addressLine2.", ".$city.", ".$state.", ".$country.'</option>'; } $stmt->close(); }

Source