Merging 2 arrays based on a relational array

Question

I have the following arrays:

$excel_arr = array(
         ["C1", "Title 3"],
         ["A1", "Title 1"],
         ["B1", "Title 2"],
         ["D1", "Title 4"]
);
$db_result = array(
         "title_2" => "Cell 2 Value",
         "title_1" => "Cell 1 Value",
         "title_3" => "Cell 3 Value",
         "title_5" => "Cell 5 Value"
);
$excel_db_relation = array(
         "title_1" => "Title 1",
         "title_2" => "Title 2",
         "title_3" => "Title 3",
         "title_4" => "Title 4",
         "title_5" => "Title 5"
);

usort($excel_arr, function ($a, $b) { return strnatcmp($a[0], $b[0]); });

$excel_arr is an array with the titles for each column in an excel file. The first cell defines the cell coordinate and the second the actual cell value.
$db_result is an array containing queried values from a database. The key is column name in the table.
$excel_db_relation is an array which defines the relation between the 2 former arrays. Which excel column is linked to which db table column. In this example they are very similar, but in practice there might be more than just an underscore that differs.

The cell coordinates in $excel_arr defines the order in which each value must be printed. To do this I sort the array with the usort() as seen above.

I need to somehow merge these arrays so that the resulting array becomes:

array("Title 1" => "Cell 1 Value", "Title 2" => "Cell 2 Value", "Title 3" => "Cell 3 Value")

The database array didn't return a value for cell 4 and the excel sheet doesn't define a E5 cell. So these must not be included in the resulting array.

I have tried array_merge($excel_db_relation, $db_result) and various combinations of array_merge() and array_flip() but no matter what I do I can't seem to merge the arrays with "Title X" being the key.

If $excel_arr defines the order, why sort it? That said, could you add an example of the final array? — Yoshi
– Yoshi, Commented Jan 24, 2017 at 9:58
@Yoshi, it's the first value in each array of $excel_arr that defines the order. It needs to be A1, B1, C1, D1. They are not sorted above. ~ Which final array do you mean? The final result is already in my post at the bottom. — Chris
– Chris, Commented Jan 24, 2017 at 10:01
Ah, I'm sorry, I misread that last array to be a intermediary step. — Yoshi
– Yoshi, Commented Jan 24, 2017 at 10:02
I didn't notice the sorting problem with Romans answer, so I thought it's better than mine, and wanted to reduce noise ;) — Yoshi
– Yoshi, Commented Jan 24, 2017 at 10:26

RomanPerekhrest · Accepted Answer · 2017-01-24 10:45:55Z

2

The solution using array_intersect_key, array_intersect and array_column functions:

$result = [];
 // getting concurrent 'titles'(by key)
$titles = array_intersect_key($excel_db_relation, $db_result); 

foreach (array_intersect($titles, array_column($excel_arr, 1)) as $k => $v) {
    $result[$v] = $db_result[$k];
}

print_r($result);

The output:

Array
(
    [Title 1] => Cell 1 Value
    [Title 2] => Cell 2 Value
    [Title 3] => Cell 3 Value
)

Update:
Alternative approach to hold the order in which each value must be printed.
Used functions: array_merge_recursive(to combine cell titles and values into separate groups) and array_column functions:

$result = [];
$bindings = array_column(array_merge_recursive($db_result, $excel_db_relation), 0, 1);
foreach (array_column($excel_arr, 1) as $title) {
    if (isset($bindings[$title])) $result[$title] = $bindings[$title];
}

print_r($result);

The output:

Array
(
    [Title 3] => Cell 3 Value
    [Title 1] => Cell 1 Value
    [Title 2] => Cell 2 Value
)

edited Jan 24, 2017 at 10:45

answered Jan 24, 2017 at 10:19

RomanPerekhrest

93.1k4 gold badges75 silver badges112 bronze badges

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

3 Comments

Chris Over a year ago

This is great, but it doesn't output in the sorted order. For example if "A1" => "Title 2", "B1" => "Title 1" then it needs to be Title 2, Title 1.

RomanPerekhrest Over a year ago

I've just taken your resulting array array("Title 1" => "Cell 1 Value", "Title 2" => "Cell 2 Value", "Title 3" => "Cell 3 Value") as equivalent. Ok, give me a minute

Chris Over a year ago

Yeah, after cells 4 and 5 are excluded, it just so happens that the remaining cells fall in order by chance. :) I should've made a better example. Thanks.

fafl · Accepted Answer · 2017-01-24 10:02:32Z

1

Try this:

$result = array_flip($excel_db_relation);
array_walk($result, function(&$value, $key) use ($db_result) {
    $value = $db_result[$value];
});
var_dump($result);

But make sure that all keys exist beforehand.

answered Jan 24, 2017 at 10:02

fafl

7,5074 gold badges32 silver badges54 bronze badges

Comments

Yoshi · Accepted Answer · 2017-01-24 10:25:06Z

1

This worked for me:

<?php
//...
usort($excel_arr, function ($a, $b) { return strnatcmp($a[0], $b[0]); });

$result = [];

// traverse the *title* column in the sorted $excel_arr
foreach (array_column($excel_arr, 1) as $a) {

    // could use array_flip to speed up this test, though
    // this can be problematic if the values aren't *good* array keys
    $k = array_search($a, $excel_db_relation);

    // if there is a key and it also exists in $db_result
    if (false !== $k && array_key_exists($k, $db_result)) {

        // assign it to the final result
        $result[$a] = $db_result[$k];
    }
}

print_r($result);

edited Jan 24, 2017 at 10:25

answered Jan 24, 2017 at 10:17

Yoshi

54.8k14 gold badges93 silver badges108 bronze badges

Collectives™ on Stack Overflow

Merging 2 arrays based on a relational array

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related