Append column values from a 2d array to the rows of another 2d array based on shared column values

Question

Let's say I have following arrays:

$first = [
    ['id' => 5, 'name' => 'Education'],
    ['id' => 4, 'name' => 'Computers'],
    ['id' => 7, 'name' => 'Science'],
    ['id' => 1, 'name' => 'Sports'],
];

$second = [
    ['id' => 1, 'title' => 'Sport'],
    ['id' => 7, 'title' => 'Sci'],
    ['id' => 4, 'title' => 'Comp'],
    ['id' => 5, 'title' => 'Edu'],
];

And desired output is:

[
    ['id' => 5, 'name' => 'Education', 'title' => 'Edu'],
    ['id' => 4, 'name' => 'Computers', 'title' => 'Comp'],
    ['id' => 7, 'name' => 'Science', 'title' => 'Sci'],
    ['id' => 1, 'name' => 'Sports', 'title' => 'Sport'],
]

I have managed to merge these arrays with simply:

foreach ($first as $key => $value) {
    $result[$key] = array_merge($first[$key], $second[$key]);
}

But the output is combined by first level index instead of their id value:

Array
    (
        [0] => Array
            (
                [id] => 5
                [name] => Education
                [title] => Sport
            )

        [1] => Array
            (
                [id] => 4
                [name] => Computers
                [title] => Sci
            )

        [3] => Array
            (
                [id] => 7
                [name] => Science
                [title] => Comp

        [4] => Array
            (
                [id] => 1
                [name] => Sports
                [title] => Edu
            )
    )

I would like to append the title values from the second array to the first array where there is a matching id value and maintain the sorting of the first array.

How can I achieve this?

Related page where rows are indexed instead of associative and the two arrays do not have the same length/ids: Merge two indexed arrays of indexed arrays based on first column value — mickmackusa
– mickmackusa ♦, Commented Aug 21, 2023 at 22:16

WebChemist · Accepted Answer · 2012-12-02 13:28:07Z

11

You can just do a nested loop and check if the id values match, then add title to $first (or name to $second)

foreach($first as $key => $value){
    foreach($second as $value2){
        if($value['id'] === $value2['id']){
            $first[$key]['title'] = $value2['title'];
        }               
    }
}

answered Dec 2, 2012 at 13:28

WebChemist

4,4116 gold badges30 silver badges37 bronze badges

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

2 Comments

valoricDe Over a year ago

What is the time complexity of this? O(n^2)? It can be definitely done better.

mickmackusa Over a year ago

The time complexity won't be awesome because there is no conditional break in the inner loop.

Marcin Orlowski · Accepted Answer · 2012-12-02 13:38:53Z

2

You code works fine. Your expectations are simply incorrect. For example in one array 4th element id holds 1 but in another array, 4th element id is 5, so your "merge these arrays on the same id" makes no sense as by merging 4th elements into one you also merge their children, and since id is used in both arrays, once value HAVE TO be gone as there cannot be two equal keys in array.

EDIT

you have to merge manually as PHP functions merge based on keys while you want to merge based on content:

$result = array();
foreach( $arrayA as $keyA => $valA ) {
  foreach( $arrayB as $keyB => $valB ) {
     if( $valA['id'] == $valB['id'] ) {
       $result[$keyA] = $valA + $valB;

       // or if you do not care output keys, just
       // $result[] = $valA + $valB;
     }
  }
}

edited Dec 2, 2012 at 13:38

answered Dec 2, 2012 at 13:14

Marcin Orlowski

75.7k11 gold badges128 silver badges152 bronze badges

3 Comments

user1292810 Over a year ago

I have corrected some mistakes in outputs, sorry for that. What I want is to take e.g $first[0]['id'] (equals 5) find same id in the second array, here $second[4]['id'] and merge them into new array like so: array('id'=>5, 'name'=>'Education', 'title'=>'Edu'). Isn't that possible?

user1292810 Over a year ago

Works like a charm, thanks :) And sorry for misleading title.

mickmackusa Over a year ago

No early breaking when a match is found?

Will B. · Accepted Answer · 2018-11-03 18:36:16Z

2

To provide an alternative approach available in PHP 5.5+.

Since the ordering of the two arrays is not identical, first use array_column to index the arrays by the id.

This will allow you to use array_replace_recusrive on the id indexed arrays.

array_replace_recursive will merge the values in the arrays matching the index association of both array sets.

Optionally use array_values to reindex the array, removing the id index association.

Example https://3v4l.org/ndv2j

$first = array_column($first, null, 'id');
$second = array_column($second, null, 'id');
$result = array_values(array_replace_recursive($first, $second));

Result

Array
(
    [0] => Array
        (
            [id] => 5
            [name] => Education
            [title] => Edu
        )

    [1] => Array
        (
            [id] => 4
            [name] => Computers
            [title] => Comp
        )

    [2] => Array
        (
            [id] => 7
            [name] => Science
            [title] => Sci
        )

    [3] => Array
        (
            [id] => 1
            [name] => Sports
            [title] => Sport
        )

)

answered Nov 3, 2018 at 18:36

Will B.

18.5k5 gold badges72 silver badges73 bronze badges

1 Comment

mickmackusa Over a year ago

This snippet makes 4 functional loops.

Jasim Juwel · Accepted Answer · 2019-11-15 05:28:59Z

1

Do not use foreach in foreach,that might be too slow when the array so big.

$idArray = array_column($secondArray,'title','id');
foreach($firstArray as $key => $val){
  $firstArray[$key]['title'] = (isset($idArray[$val['id']])) ? $idArray[$val['id']] : 'some title';
}

edited Nov 15, 2019 at 5:28

Jasim Juwel

7661 gold badge9 silver badges19 bronze badges

answered Nov 24, 2017 at 8:45

monkey king

91 bronze badge

Comments

Nick · Accepted Answer · 2020-10-12 22:55:17Z

1

This can be easily done using the 3-parameter form of array_column to re-index your second array by the id value and then looping over the first array, merging the contents of the matching second value (where it exists):

$second = array_column($second, null, 'id');
foreach ($first as &$subject) {
    $subject = array_merge($subject, $second[$subject['id']] ?? []);
}

Output:

Array
(
    [0] => Array
        (
            [id] => 5
            [name] => Education
            [title] => Edu
        )
    [1] => Array
        (
            [id] => 4
            [name] => Computers
            [title] => Comp
        )
    [3] => Array
        (
            [id] => 7
            [name] => Science
            [title] => Sci
        )
    [4] => Array
        (
            [id] => 1
            [name] => Sports
            [title] => Sport
        )
)

Demo on 3v4l.org

This has the advantage over Will B.'s answer of not re-indexing the first array. It's also slightly faster (my tests show ~10%) due to only having one call to array_column and none to array_values.

Update

This code can actually be sped up even more by using the array union operator (+) (thanks @mickmackusa):

$second = array_column($second, null, 'id');
foreach ($first as &$subject) {
    $subject += $second[$subject['id']] ?? [];
}

The output of this code is the same as above, but it runs about 10% faster again.

Demo on 3v4l.org

edited Oct 12, 2020 at 22:55

answered Oct 12, 2020 at 6:16

Nick

147k23 gold badges67 silver badges106 bronze badges

2 Comments

mickmackusa Over a year ago

This answer is probably as optimized for speed as it can be, but bear in mind that the call of array_column() will lose data IF there are duplicate id column values in the $second array. (This is just a fringe consideration for readers, because the sample data in the question does not have duplicated id column values in the $second array.)

mickmackusa Over a year ago

Another fringe scenario would be if the $first array did not contain an id value that corresponds to the $second array, the data from the $second would be omitted from the result. This may or may not be a desired behavior. In the posted question all available id values are represented in both arrays.

Pol Dellaiera · Accepted Answer · 2018-01-15 13:10:42Z

0

Make sure that the items are in the same order then:

$items = array_map(function($itemFirstArray, $itemSecondArray) {
  return array_merge($itemFirstArray, $itemSecondArray);
}, $firstArray, $secondArray);

answered Jan 15, 2018 at 13:10

Pol Dellaiera

1271 silver badge8 bronze badges

2 Comments

mickmackusa Over a year ago

It probably makes sense to not yatta-yatta over the bit of code that ensures that the two arrays are sorted correctly. To be clear, this technique requires that both arrays contain exactly and only the same id-related data.

mickmackusa Over a year ago

This answer requires that both arrays are the same length and have exactly the same mapping column values.

jmlnik · Accepted Answer · 2021-09-30 02:52:22Z

0

Sort both arrays by your 'id' field, then let php do the merge with array_replace_recursive.

function cmp($a, $b) {
  return ((int) $a['id'] < (int) $b['id']) ? -1 : 1;
}

usort($array1, 'cmp');
usort($array2, 'cmp');

$result = array_replace_recursive($array1, $array2);

edited Sep 30, 2021 at 2:52

answered Dec 2, 2012 at 14:07

jmlnik

2,8972 gold badges19 silver badges20 bronze badges

3 Comments

mickmackusa Over a year ago

This untested snippet is clearly incorrect in its output.

jmlnik Over a year ago

Hey @mickmackusa, well spotted. This code works fine with array_replace_recursive but not with array_merge, obviously!

mickmackusa Over a year ago

I still don't recommend this technique because usort() will require greater computation complexity versus other answers on this page. I'm biased because I cooperated with Nick already, but -- I'd use Nick's answer. This assumes that both arrays contain the same volume and data structure. It also requires both arrays to share the same ids. This answer is not reliable for general use.

Collectives™ on Stack Overflow

Append column values from a 2d array to the rows of another 2d array based on shared column values

7 Answers 7

2 Comments

3 Comments

Example https://3v4l.org/ndv2j

1 Comment

Comments

2 Comments

2 Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

7 Answers 7

2 Comments

3 Comments

Example https://3v4l.org/ndv2j

1 Comment

Comments

2 Comments

2 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related