WHERE (a, b) NOT IN (array)

Question

I am trying to get all the data from a table on which i have 2 specific columns ( width and height) + other data, and i also need to filter them using an array of data that i have inside the app. My inside array looks like this, but with multiple entries :

$presetSize = Array (

    [0] => Array
        (
            [width] => 336
            [height] => 280
        )

    [1] => Array
        (
            [width] => 300
            [height] => 250
        )
.
.
.

)

The thing I am trying to do is to get all the data from the DB where the width and height are not found inside the array, which in my case represent custom sizes.

In mysql “Where not in using two columns” using two columns I can read how to accomplish that using the form select when you extract data from another table, but here i have an array with arrays that sends an error when i try it.

I tried:

SELECT * FROM items WHERE type = 1 AND (width, height) NOT IN (" . $presetSize . ");

and it returns

MySQL Error: Unknown column 'Array' in 'where clause'

Hope the question is clear enough.

Why do you think that such syntax exists? Use `WHERE a NOT IN () AND b NOT IN ()` — u_mulder, Jun 26 '18 at 09:13
@u_mulder (i) such syntax exists in MySQL (ii) `WHERE width NOT IN (336, 300) AND height NOT IN (280, 250)` fails for `(336, 999)`; it should be selected but it is not (iii) `WHERE width NOT IN (336, 300) OR height NOT IN (280, 250)` fails for `(336, 250)`; it should be selected but it is not. — Salman A, Jun 26 '18 at 10:31
exactly, i need the pairs to be verified, not individual height and individual width... your solution only works separately mulder, where i need the exact match — Nicu-Cristian Vlad, Jun 26 '18 at 12:53

HamzaNig · Answer 1 · 2018-06-26T10:57:39.423

0

Here A solution you can use foreach width and height then use them in query :

$presetSize = Array (
[0] => Array
    (
        [width] => 336
        [height] => 280
    )

[1] => Array
    (
        [width] => 300
        [height] => 250
    )

    )

    $width=array();
    $height=array();

    foreach($presetSize  as $values)
    {
        $width[]=$values['width'];
        $height[]=$values['height'];



    }



 $query="SELECT * FROM items WHERE type = 1 AND (width, height) NOT IN ((" . implode(',',$width) . "), (".implode(',',$height) ."))";

edited Jun 26 '18 at 10:57

answered Jun 26 '18 at 09:22

HamzaNig

1,019
1
10
33

@NicoHaase see my comment on OP. Checking pairs of values is always tricky. – Salman A Jun 26 '18 at 10:32
I think your right brother @SalmanA i update my solution Thanks for advice – HamzaNig Jun 26 '18 at 10:58

Salman A · Accepted Answer · 2018-06-26T09:53:50.100

You can just shove a variable in a query and hope it will work. You need to generate width-height pairs manually:

$sizelist = array();
foreach ($presetSize as $size) {
    $sizelist[] = sprintf("(%d, %d)", $size["width"], $size["height"]);
}
$query = "SELECT * FROM items WHERE type = 1 AND (width, height) NOT IN (" . implode(", ", $sizelist) . ")";

This will produce the following (MySQL specific) query:

SELECT * FROM items WHERE type = 1 AND (width, height) NOT IN ((336, 280), (300, 250))

SQL Fiddle

WHERE (a, b) NOT IN (array)

2 Answers2