How to format html table to show unique months and item id and sum the cost? (PHP)-CodePudding

I have been stuck for days on this 'little' problem. I have one array which contains specific data:

$data = array(
0 => array('id' => 8, 'month' => 1, 'cost' => 12500),
1 => array('id' => 8, 'month' => 2, 'cost' => 14200),
2 => array('id' => 9, 'month' => 1, 'cost' => 23000),
3 => array('id' => 9, 'month' => 2, 'cost' => 18000),
);

And this is the html table results i need to get:

Id     Jan      Feb      Mar     Apr        May
 8    12,500  14,200  10,200   10,300     11,000
 9    23,000  18,000  21,320   10,642     14,636

How i can sort array to display this data in html table on view.ctp ? I have tried using foreach loops but i really don't know how to put unique months and unique id like it is displayed above. I'm using CakePHP 2.x technology.

I appreciate every help. Thank you

CodePudding user response：

Loop over your array and group into a new array based on the ID. Assign month=cost key-value pairs, using IDs as the grouping keys of your multidimensional array.

$by_id = [];

foreach($data as $x) {
    $by_id[$x['id']][$x['month']] = $x['cost'];
    // e.g. $by_id[8][2] = 14200;
}

This will turn your sample data into the following array:

array(2) {
    [8] · array(2) {
        [1] · int(12500)
        [2] · int(14200)
    }
    [9] · array(2) {
        [1] · int(23000)
        [2] · int(18000)
    }
}

That should be much easier to turn into a HTML table. For example like this:

$html = [];
$html[] = '<table>';
$html[] = '<tr><th>ID</th><th>Jan</th><th>Feb</th></tr>';

foreach($by_id as $id => $months) {
    $html[] = '<tr>';
    $html[] = "<td>{$id}</td>";

    foreach($months as $month => $cost) {
        $html[] = "<td>{$cost}</td>";
    }
    
    $html[] = '</tr>';
}

$html[] = '</table>';

echo implode("\n", $html);

I trust the code is clear enough without elaborate comments. Add months to the table header as necessary. Use e.g. number_format for $cost to format your monies. Be aware that the sample code above doesn't account for possible missing months for a given ID, it assumes symmetric data. Add checks and remedies if necessary. See demo at https://3v4l.org/7CFRf.

CodePudding user response：

You should use a 2D array to display that kind of table.

Then the $data array might be like this:

$data = array(
    0 => array('id' => 8, 'costData' => array(
        0 => array('month' => 1, 'cost' => 12500),
        1 => array('monnt' => 2, 'cost' => 14200)
    ),
    1 => array('id' => 9, 'costData' => array(
        0 => array('month' => 1, 'cost' => 23000),
        1 => array('month' => 2, 'cost' => 18000)
    )
);

To revert the $data array like this, the code below will be needed.

<?php
$newData = array();
for($i = 0; $i < count($data); $i  )
{
    $newItem = array('id' => $data[$i]['id'], 'costData' => array(
        'month' => $data[$i]['month'],
        'cost' => $data[$i]['cost']
    ));
    for($j = $i   1; $j < count($data); $j  )
    {
        if($data[$j]['id'] == $data[$i]['id']) {
            $newItem['costData'][] = array('month' => $data[$j]['month'], 'cost' => $data[$j]['cost']);
            array_splice($data, $j, 1);
            $j--;
        }
    }
    $newData[] = $newItem;
}

Using new data Array, you can make your html page like this.

$html = '';
for($i = 0; $i < count($newData); $i  )
{
    $html .= '<tr>';
    $html .= '<td>'.($newData[$i]['id']).'</td>';
    for($j = 0; $j < count($newData[$i]['costData']); $j  ) {
        $html .= '<td>'.($newData[$i]['costData'][$j]).'</td>';
        // you can do some operations if there are missing months to leave blank td tags..
    }
    // fill the missing months to fill the td tags.
}