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"]]

Answer 1

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.

Answer 2

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

Answer 3

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.

Answer 4

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"]]

Answer 5

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.