Mysql, PHP: Use "WHERE NOT IN" for associative array

Question

I have sent a stringified associative array from the client side, for example:

[{placeId: 1, stateId: 2}, {placeId: 1, stateId: 3}, {placeId: 2, stateId: 4}, ...]

In the server side, I received it by using json_decode.

Now what I want is, delete everything from a table where rows does not exists in the array request.

For a plain array ([1,2,3,4]) and single column lookup(say, placeId), I can query it like this:

DELETE FROM table where placeId NOT IN [1,2,3,4]

But how should I do it when looking up 2 columns(placeId, stateId) and comparing it to an associative array like above?

Answer 1

Sure. Look at here.

$json = [{placeId: 1, stateId: 2}, {placeId: 1, stateId: 3}, {placeId: 2, stateId: 4}, ...];
$array = json_decode($json, true);
$firstColumn = array_column($array, 'placeId');  
$secondColumn = array_column($array, 'stateId');

And use follow query.

DELETE FROM table where placeId NOT IN $firstColumn AND stateId NOT IN $secondColumn

Of course, the request is written incorrectly, but you must understand how you will execute it in PHP, I'm just showing you that it really works