Detecting new permutations in multidimensional array

Question

In our DB we have one table that is the result of every permutation from three tables.

I'm trying to write a PHP script that will look at all of these tables as arrays and detect whether there is a missing permutation.

e.g.

$foo = array('one', 'two', NULL)
$bar = array('three', 'four', NULL)
$baz = array('five', 'six', NULL)

$permutations = array(
  array('one', 'three', 'five'),
  array('two', 'three', 'five'),
  array(NULL, 'three', 'five'),
  //etc
)

foreach $foo as $x
  foreach $bar as $y
    foreach $baz as $z
      $combo = array($x, $y, $z)
      if $combo is not in $permutations
         //generate sql to update db

How can I achieve this?

If you have X tables that each have Y elements (??), then you'll have X^Y permutations if this is a "permutation with repetition." So, a quick smoke test would be making sure that $permutations length is the same as X^Y (or whatever the range of sets should be)...you can also do something like making sure that $permutations has only unique elements before doing the length check. This may be more efficient than having to check every single possible permutation. — wilkesybear
– wilkesybear, Commented Feb 24, 2015 at 1:37

Adam · Accepted Answer · 2015-02-24 09:37:56Z

2

You have to retrieve each possible permutations with MySQL and using LEFT JOIN on the 4th table and testing if there is no match

If you have 3 tables t1, t2, t3 containing one column "value", and table t4 which is the "permutation" which contains three columns t1, t2 and t3, you can get the non existing "permutation" with the following query

SELECT t1.value v1, t2.value v2, t3.value v3
FROM t1, t2, t3
LEFT JOIN t4 ON t4.t1=v1 AND t4.t2=v2 AND t4.t3=v3
WHERE t4.t1 IS NULL;

You can adapt this query to match your database schema.

answered Feb 24, 2015 at 9:37

Adam

19k1 gold badge35 silver badges58 bronze badges

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

1 Comment

lnrdo Over a year ago

You should emulate a full outer join to get the answer in one query.

Balázs Édes · Accepted Answer · 2015-02-24 09:40:32Z

0

I dunno exactly what you try to do but i suggest :

$foo = array('one', 'two', NULL);
$bar = array('three', 'four', NULL);
$baz = array('five', 'six', NULL);

$permutations = array(
  array('one', 'three', 'five'),
  array('two', 'three', 'five'),
  array(NULL, 'three', 'five'),
  //etc
);

$foobarbaz = array_merge($foo, $bar, $baz);
foreach($foobarbaz as $k){ 
  if(!in_array($k, $permutations) {
     $sql_trigger_start();
  }
}

A example who get this problem. http://dannyherran.com/2011/06/finding-unique-array-combinations-with-php-permutations/

edited Feb 24, 2015 at 9:40

Balázs Édes

13.9k7 gold badges60 silver badges105 bronze badges

answered Feb 24, 2015 at 2:23

head

12 bronze badges

2 Comments

pjmil Over a year ago

array_merge won't work. Not like this anyway. The $permutations array currently contains every possible combination of the $foo, $bar, and $baz arrays. However sometimes $foo, $bar, $baz arrays have new items added to them, meaning that the $permutations array no longer contains every combination. I need a way of testing every possible combination against the $permutations array and detect when it does not exist so I can generate an SQL statement. Does that make sense?

head Over a year ago

yes thanks you can solve your problem going to this link dannyherran.com/2011/06/…

Collectives™ on Stack Overflow

Detecting new permutations in multidimensional array

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related