Output rows with Ids that arent in foreach loop

Question

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

Well of course this happens, when you make a loop inside of a loop, then the results “multiply” … _this_ kind of logic is not suitable for what you want to begin with. Remove the foreach loop. Get the ID values you want to exclude into an array, and then use `in_array` to check if the current record id is in there. — CBroe, Aug 12 '20 at 10:43
@CBroe I've edited my question to try this, still doesn't work. Shows all options as normal — TiernO, Aug 12 '20 at 10:48
If `$takenAddresses` is still the same thing you had before, then of course this does not work. You need an array that contains _only_ the ID values, not whatever complex data structure you had in there before. — CBroe, Aug 12 '20 at 10:52
Thats what `$takenAddresses` is. It is the following structure. `Array ( [0] => Array ( [id] => 13 ) [1] => Array ( [id] => 16 ) [2] => Array ( [id] => 20 ) [3] => Array ( [id] => 23 ) ) ` — TiernO, Aug 12 '20 at 10:55
That is an array of arrays, you need an array of just integers. (`array_column` can help you extract those.) — CBroe, Aug 12 '20 at 10:59

Cid · Answer 1 · 2020-08-12T10:56:57.527

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

Output rows with Ids that arent in foreach loop

1 Answers1