How can PHP arrays be effectively utilized to replicate the functionality of SQL ROLLUP?

To replicate the functionality of SQL ROLLUP using PHP arrays, we can create a multi-dimensional array where each level represents a grouping level. We can then iterate over the data and populate the array accordingly, aggregating the values at each level. Finally, we can traverse the array to display the rolled-up data in the desired format. 

$data = [
    ['category' => 'A', 'sub_category' => 'X', 'value' => 10],
    ['category' => 'A', 'sub_category' => 'Y', 'value' => 20],
    ['category' => 'B', 'sub_category' => 'X', 'value' => 15],
    ['category' => 'B', 'sub_category' => 'Y', 'value' => 25],
];

$rollup = [];

foreach ($data as $row) {
    $category = $row['category'];
    $subCategory = $row['sub_category'];
    $value = $row['value'];

    if (!isset($rollup[$category])) {
        $rollup[$category] = ['total' => 0, 'sub_categories' => []];
    }

    if (!isset($rollup[$category]['sub_categories'][$subCategory])) {
        $rollup[$category]['sub_categories'][$subCategory] = 0;
    }

    $rollup[$category]['sub_categories'][$subCategory] += $value;
    $rollup[$category]['total'] += $value;
}

foreach ($rollup as $category => $data) {
    echo "Category: $category\n";
    foreach ($data['sub_categories'] as $subCategory => $total) {
        echo "Sub-Category: $subCategory, Total: $total\n";
    }
    echo "Total: {$data['total']}\n\n";
}

Keywords

PHP arrays SQL ROLLUP functionality utilization replication

Related Questions