输出ID不在foreach循环中的行

Question

我有一个如下的函数来填充select输入。

$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();
}

这很好，只是现在我想制作它，这样它就不会制作<option>，如果它有一个id，那就是在我的foreach循环中的一个对象中。

这就是我尝试过的

$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 1

您可以使用WHERE NOT IN (...)子句直接从sql查询中筛选不需要的id。

参数化WHERE NOT IN ()查询有点棘手，但是，基于这个出色的答案，可以这样做：

// 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 ----------------------------------------------------------------------------------v-----------------------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();
}