Merge and diff arrays of arrays to find common values

Question

I am trying to loop through several database table structures and determine the common structure (ie what columns are identical). The final structure should only show common columns, so if any table has a unique column, it should not be in the final array.

This is an example of what three table structures may look like.

$arr1 = [
    ["name"=>"col1", "type"=>"varchar"],
    ["name"=>"col2", "type"=>"int"]    
];
$arr2 = [
    ["name"=>"col1", "type"=>"varchar"],
    ["name"=>"col2", "type"=>"int"]    ,
    ["name"=>"col3", "type"=>"date"]    
];
$arr3 = [
    ["name"=>"col1", "type"=>"varchar"],
    ["name"=>"col3", "type"=>"int"]    
];

$arrays = [$arr1, $arr2, $arr3];

Using array_merge, array_diff, array_intersect, or a loop, is it possible to determine which of these columns are common to all tables?

The end value should be [["name"=>"col1", "type"=>"varchar"]]

What have your efforts been so far? Can you include the code. — Nigel Ren
– Nigel Ren, Commented Jul 3, 2018 at 14:36
@NigelRen Essentially lots of crumpled up pieces of paper. Loops in loops in loops, counters for each column, pruning, merges, intersects, diffs.. Lots of tries and I've given up. — billy_bones_3
– billy_bones_3, Commented Jul 3, 2018 at 14:38

Philipp · Accepted Answer · 2018-07-03 15:10:02Z

5

You could use array_uintersect, which does the intersection with your own function. But you should keep in mind, that the compare function does not simply returns true or false - instead you have to return -1, 0 or 1.

In PHP7 you could use the spaceship operator <=> for the comparison.

$intersection = array_uintersect($arr1, $arr2, $arr3, function($a, $b) {
    $ret = $a['name'] <=> $b['name'];
    return $ret ? $ret : $a['type'] <=> $b['type'];
});
print_r($intersection);

If you want to put all arrays inside the intersection, you could do this:

$arrays = [$arr1, $arr2, $arr3];
$arrays[] = function($a, $b) {
    $ret = $a['name'] <=> $b['name'];
    return $ret ? $ret : $a['type'] <=> $b['type'];
};

$intersection = array_uintersect(...$arrays);

In older versions, you should instead use strcasecmp.

edited Jul 3, 2018 at 15:10

answered Jul 3, 2018 at 14:56

Philipp

15.7k4 gold badges38 silver badges54 bronze badges

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

1 Comment

Timmetje Over a year ago

very nice, also love the fact you help him use the variadic argument.

Timmetje · Accepted Answer · 2018-07-03 15:09:51Z

3

You can use a custom compare method with array_uintersect():

$arr1 = [
    ["name" => "col1", "type" => "varchar"],
    ["name" => "col2", "type" => "int"]
];
$arr2 = [
    ["name" => "col1", "type" => "varchar"],
    ["name" => "col2", "type" => "int"],
    ["name" => "col3", "type" => "date"]
];
$arr3 = [
    ["name" => "col1", "type" => "varchar"],
    ["name" => "col3", "type" => "int"]
];

$common_columns = array_uintersect($arr1, $arr2, $arr3, 'compareDeepValue');

print_r($common_columns);

function compareDeepValue($val1, $val2)
{
    return (strcasecmp(serialize($val1), serialize($val2))) ;
}

Will output:

Array
(
    [0] => Array
        (
            [name] => col1
            [type] => varchar
        )

)

Note:

@Abracadaver made a good point this method will only work correctly when you have the array conventions in the same order.

Than you can for example use this:

function compareDeepValue($val1, $val2)
{
    return ($val1['name'] === $val2['name'] && $val1['type'] === $val2['type']) ? 0 : -1;
}

edited Jul 3, 2018 at 15:09

answered Jul 3, 2018 at 14:52

Timmetje

7,70521 silver badges36 bronze badges

4 Comments

AbraCadaver Over a year ago

Works only if they are always in the same order, try ["type"=>"varchar", "name"=>"col1"] or more values ["name"=>"col1", "type"=>"varchar", "default"=>"NULL"].

Timmetje Over a year ago

Good point, but the goal is that OP can use his custom method, which of course he can change to whatever he needs. I'll add the note, thanks.

billy_bones_3 Over a year ago

This is working so far. Now I am just not understanding why I cannot use spread operator for the parameters.. ie array_uintersect($arr1, ...$arrays .. assuming I took $arr1 out of $arrays

Timmetje Over a year ago

array_u* functions are not variadic because of the last positional argument. The problem is that they all take a final callback parameter (or two) after the varargs list, and that doesn't fit the rules for how variadics work.

AbraCadaver · Accepted Answer · 2018-07-03 15:03:32Z

1

You can extract the arrays and index by the name key and compute the intersection using the keys:

$result = array_intersect_key(array_column($arr1, null, 'name'),
                              array_column($arr2, null, 'name'),
                              array_column($arr3, null, 'name'));

Yields:

Array
(
    [col1] => Array
        (
            [name] => col1
            [type] => varchar
        )
)

If needed, use array_values to get back to numeric indexes.

edited Jul 3, 2018 at 15:03

answered Jul 3, 2018 at 14:56

AbraCadaver

79.2k7 gold badges75 silver badges91 bronze badges

2 Comments

billyonecan Over a year ago

It's worth mentioning that this does not compare the values of type at all

AbraCadaver Over a year ago

@billyonecan Correct to determine which of these columns are common to all tables

Valentin P · Accepted Answer · 2018-07-03 14:53:58Z

0

Maybe ?

$arrays = [$arr1, $arr2, $arr3];
$arrays_extended = [];

foreach($arrays as $row => $innerArray){
  foreach($innerArray as $innerRow => $value){
    array_push($arrays_extended, $value);
  }
}

var_dump(array_unique($arrays_extended));

Outputs [["name"=>"col1", "type"=>"varchar"]]

answered Jul 3, 2018 at 14:53

Valentin P

3882 silver badges10 bronze badges

3 Comments

xate Over a year ago

I didn't downvote you but this throws a ton of notices because array_unique isn't used properly. And yeah.. this code is overall kinda broken: 3v4l.org/1okcm

Valentin P Over a year ago

I'm not trying it on Online PHP editor and it didn't throws anything for me. But thanks for explanation.

xate Over a year ago

Thats probably because your error_reporting / display_errors is not correctly configured. See how to configure error reporting for development

Sahith Vibudhi · Accepted Answer · 2018-07-03 14:58:14Z

Approach:

1.convert elements to strings as follows:

 array(2) {
      [0] =>
      string(32) "{"name":"col1","type":"varchar"}"
      [1] =>
      string(28) "{"name":"col2","type":"int"}"
    }
    array(3) {
      [0] =>
      string(32) "{"name":"col1","type":"varchar"}"
      [1] =>
      string(28) "{"name":"col2","type":"int"}"
      [2] =>
      string(29) "{"name":"col3","type":"date"}"
    }
    array(2) {
      [0] =>
      string(32) "{"name":"col1","type":"varchar"}"
      [1] =>
      string(28) "{"name":"col3","type":"int"}"
    }

2.Use array intersect to find common elements

3.convert back to arrays.

$arr1 = [
    ["name"=>"col1", "type"=>"varchar"],
    ["name"=>"col2", "type"=>"int"]    
];
$arr2 = [
    ["name"=>"col1", "type"=>"varchar"],
    ["name"=>"col2", "type"=>"int"]    ,
    ["name"=>"col3", "type"=>"date"]    
];
$arr3 = [
    ["name"=>"col1", "type"=>"varchar"],
    ["name"=>"col3", "type"=>"int"]    
];

list($darr1, $darr2, $darr3) = convertArrToStr($arr1, $arr2, $arr3);
/* output:
array(2) {
  [0] =>
  string(32) "{"name":"col1","type":"varchar"}"
  [1] =>
  string(28) "{"name":"col2","type":"int"}"
}
array(3) {
  [0] =>
  string(32) "{"name":"col1","type":"varchar"}"
  [1] =>
  string(28) "{"name":"col2","type":"int"}"
  [2] =>
  string(29) "{"name":"col3","type":"date"}"
}
array(2) {
  [0] =>
  string(32) "{"name":"col1","type":"varchar"}"
  [1] =>
  string(28) "{"name":"col3","type":"int"}"
}
 */
var_dump(duplicates($darr1, $darr2, $darr3));
/* output:
array(1) {
  [0] =>
  array(2) {
    'name' =>
    string(4) "col1"
    'type' =>
    string(7) "varchar"
  }
}
*/    

function convertArrToStr() {
    $args = func_get_args();
    foreach($args as &$arg){
          foreach($arg as $k => $arr) {
                      $arg[$k] = json_encode($arr, true);
          }
    }
    return $args;
}

function duplicates($darr1, $darr2, $darr3) {
    $intersects = array_intersect($darr1, $darr2, $darr3);
    $r = [];
    foreach($intersects as $v) {
          $r[] = json_decode($v, true);    
    }
    return $r;
}

Hope this helps you write a more elegant solution.

Collectives™ on Stack Overflow

Merge and diff arrays of arrays to find common values

5 Answers 5

1 Comment

4 Comments

2 Comments

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

1 Comment

4 Comments

2 Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related