我有一个如下的函数来填充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();
}
```
您可以使用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();
}