Merge multidimensional array data by one column and ensure all rows contain all columns

I have an array which contains data from two or more database queries.

The merging needs to be done by a specified column (date in this case) and the resultant rows must contain all associative keys from the original array.

$array = [
    [
        ['date' => '2011-01-17', 'col-1' => 58, 'col-2' => 54],
        ['date' => '2011-01-19', 'col-1' => 50, 'col-2' => 61],
        ['date' => '2011-01-20', 'col-1' => 44, 'col-2' => 22],
        ['date' => null, 'col-1' => 448, 'col-2' => 196],
    ],
    [
        ['date' => '2011-01-17', 'col-3' => 1489],
        ['date' => '2011-01-18', 'col-3' => 1534],
        ['date' => null, 'col-3' => 1534],
    ]
];

I'd like to merge data that is related by the date value, make sure that every row contains all possible columns, and is sorted by date with the empty-dated row occurring last.

Desired output:

array (
  0 => 
  array (
    'date' => '2011-01-17',
    'col-1' => 58,
    'col-2' => 54,
    'col-3' => 1489,
  ),
  1 => 
  array (
    'date' => '2011-01-18',
    'col-1' => NULL,
    'col-2' => NULL,
    'col-3' => 1534,
  ),
  2 => 
  array (
    'date' => '2011-01-19',
    'col-1' => 50,
    'col-2' => 61,
    'col-3' => NULL,
  ),
  3 => 
  array (
    'date' => '2011-01-20',
    'col-1' => 44,
    'col-2' => 22,
    'col-3' => NULL,
  ),
  4 => 
  array (
    'date' => NULL,
    'col-1' => 448,
    'col-2' => 196,
  开发者_开发知识库  'col-3' => 1534,
  ),
)

Additional notes:

• The input array may have a count greater than 2.
• Subarray may have differring counts and are not relatable by their index.

First, I'm going to assume your data is sorted by date, since you can do it in SQL and it's sorted in your example. You need to walk through all of your sets at the same time, merging as you go.

$merged = array();
$i_first = 0;
$i_second = 0;

$count_first = count($data[0]);
$cound_second = count($data[1]);

while ($i_first < $count_first || $i_second < $count_second)
{
    // this comparison depends on what your merge_by is
    $diff = strtotime($data[$i_first]['date']) - strtotime($data[$i_second]['date']);

    if ($diff == 0)
    {
        $merged[] = array_merge($data[$i_first], $data[$i_second]);
        $i_first++;
        $i_second++;
    }
    elseif ($diff < 0) // first date is earlier
    {
        $i_first++;
    }
    else  // second date earlier
    {
        $i_second++;
    }
}

Now your $merged array should have what you want. Note that this solution assumes you don't have duplicate date rows in one chunk, or it would overwrite the existing results. Also, you could expand to have more than two data sets if you want.

1. Populate an array of default values to ensure that rows will contain null values for all expected columns by using two rounds of a flattening technique then mapping all keys to null values for later use.

2. Use nested loops to access and push rows of data into the result array. Use temporary first level keys to identify unique date groups. To aid in the sorting of rows later, use the Elvis operator (?:) to fallback to the literal string 'last' when defining the first level key -- all other date values will be alphabetically sorted before this key.

3. While iterating, if the current row contains a date which has not been encountered before, overwrite the default values with its values and push that data into that group's row. If the current row contains a date which has been encountered before, overwrite the cached group data with the new data.

4. After looping, sort the array by its first level keys.

5. To remove the temporary first level keys, call array_values().

Code: (Demo)

$defaults = array_map(
    fn() => null,
    array_merge(...array_merge(...$array))
);

$result = [];
foreach ($array as $set) {
    foreach ($set as $row) {
        $key = $row['date'] ?: 'last';
        $result[$key] = array_replace($result[$key] ?? $defaults, $row);
    }
}
ksort($result);
var_export(array_values($result));