CODEWITHSUNDEEP
phpmysqlarraysselect
ikebastuz
ikebastuz

Reputation: 389

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

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

And variants: prod_id | color | size | price

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

Upvotes: 1

Views: 483

Answers (1)

Alex Kapustin
Alex Kapustin

Reputation: 2449

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

Upvotes: 1

Related Questions