How do I run a foreach against an array and include it in an SQL query?

Question

So I want to be able to get a value from an array and use it in an SQL query, and get information from those rows. My current code is like so:

$config['exclude_devices'] = array('1','5');

$exclude = $config['exclude_devices'];
foreach ($exclude as $excluded) {
    $deviceinformation = mysqli_query($db, "SELECT * FROM `devices` WHERE `device_id` NOT IN(" . $excluded . ")");
}
while ($row = mysqli_fetch_assoc($deviceinformation)) {
    // do stuff here
    // write to a variable for use in a table
}

However, this gives the output of devices 1,2,3,4.

If I move the foreach to include the while, it produces 2,3,4,5,1,2,3,4:

$exclude = $config['exclude_devices'];
foreach ($exclude as $excluded) {
    $deviceinformation = mysqli_query($db, "SELECT * FROM `devices` WHERE `device_id` NOT IN(" . $excluded . ")");
    while ($row = mysqli_fetch_assoc($deviceinformation)) {
        // do stuff here
    }
}

How can I modify this code to produce only the devices that are not listed in the config?

This may be helpful for what you want to do - http://stackoverflow.com/questions/920353/can-i-bind-an-array-to-an-in-condition — WillardSolutions, Mar 15 '16 at 15:59
Select records where device_id is not `1` - selects all including `5`. Select records where device_id is not `5` - selects all including `1`. Why? — u_mulder, Mar 15 '16 at 16:19

chris85 · Accepted Answer · 2016-03-15T16:19:08.487

In example 1:

You run:

SELECT * FROM `devices` WHERE `device_id` NOT IN(3)

then

SELECT * FROM `devices` WHERE `device_id` NOT IN(5)

You then only fetch the second queries results so you should get back devices with non 5 for their ids.

I would use an implode so you exclude all ids you don't want.

$excluded = implode(',', $config['exclude_devices']);
$deviceinformation = mysqli_query($db, "SELECT * FROM `devices` WHERE `device_id` NOT IN(" . $excluded . ")");
while ($row = mysqli_fetch_assoc($deviceinformation)) {
    // do stuff here
}

Demo: https://eval.in/536862
SQL Demo: http://sqlfiddle.com/#!9/e4ed47/2

I had tried explode, both with and without an array. ```implode``` works. Thanks a lot. — Spydar007, Mar 15 '16 at 16:25

How do I run a foreach against an array and include it in an SQL query?

1 Answers1