MySQL select where two fields are not in array at the same time

Question

I have an e-shop. There are a lots of tables, but only two needed. Products and variants. Each product has multiple variants. Ex. Product: id | name | url | active

1337 | prod1 | prod-1 | 1

And variants: prod_id | color | size | price

1337 | xl | black | 10
1337 | sm | black | 11
1337 | xl | white | 9
1337 | sm | white | 8

etc.

And i have a huge xml of goods to import. I parse it into array and import products and variants that missing. Here everything is fine. What i have to do - is to remove options, that are missing in incoming array. I can do it step by step, but array of data is so huge, that server memory is not enough. So i have to make a SQL request, that picks up variants missing in incoming array.

I can make incoming array in any way, for example:

    array(2716) {
  ["00000614552"]=>
  array(1) {
    ["options"]=>
    array(44) {
      [0]=>
      array(2) {
        [0]=>
        string(11) "white"
        [1]=>
        string(3) "2/S"
      }
      [1]=>
      array(2) {
        [0]=>
        string(11) "white"
        [1]=>
        string(3) "3/M"
      }
      [2]=>
      array(2) {
        [0]=>
        string(11) "white"
        [1]=>
        string(3) "4/L"
      }
      [3]=>
      array(2) {
        [0]=>
        string(11) "white"
        [1]=>
        string(4) "5/XL"
      }
      [4]=>
      array(2) {
        [0]=>
        string(13) "black"
        [1]=>
        string(3) "2/S"
      }
...
}

Now is the question is - how to make query to select all variants, that are not in this array. Making sum array of sizes and colors and telling it to select rows NOT IN array not working, because missing option might be for example "black-xl", but there is "black-sm" and "white-xl", so black and xl are in that array.

Hope i made clear description ;)

Alex Kapustin · Accepted Answer · 2017-09-03 08:47:25Z

1

Actually you can run NOT IN / IN mysql condition.

You can gather all available variants from array and run delete with pair not in condition.

Let's say we have black-xl and white-sm variants, so query will be:

delete from variants where (color, size) not in (('black', 'xl'), ('white', 'sm'));

example on fiddle with select (to know what's going to be deleted): http://sqlfiddle.com/#!9/d6623c/5

answered Sep 3, 2017 at 8:47

Alex Kapustin

2,5291 gold badge15 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

ikebastuz Over a year ago

Worked like a charm. Thank you, sir!

Collectives™ on Stack Overflow

MySQL select where two fields are not in array at the same time

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related