0
255
ViewsCreate a 'pivot' table from array
I have an array that looks like this:
Array (
Array ( [date] => 04/2021 [name] => Fred [value] => 12.00 )
Array ( [date] => 04/2021 [name] => Tom [value] => 160.00 )
Array ( [date] => 04/2021 [name] => Mike [value] => 9.00 )
Array ( [date] => 07/2021 [name] => Tony [value] => 200.00 )
Array ( [date] => 07/2021 [name] => Fred [value] => 43.00 )
Array ( [date] => 07/2021 [name] => Tom [value] => 114.00 )
Array ( [date] => 07/2021 [name] => Mike [value] => 28.00 )
)
I am trying to get the data into a simple HTML table where the name goes on the x axis, the date on the y axis and the values where they intersect.
For example:
Fred Tom Mike Tony
04/2021 12.00 160.00 9.00 0
07/2021 43.00 114.00 28.00 200.00
I do not know if I am over thinking this but I cannot get this to work - I can get the x axis to work by pulling out the unique values for name but then when it comes to the y axis and the intersections I cannot get the data to display how I need.
I either end up with repetition of columns or a single column and so on. This is where I have gotten up to but still scratching my head how to do this:
<?php
$unique_names = array_unique(array_map(function($elem){
return $elem['name'];
}, $data));
?>
<thead>
<tr>
<th> </th>
<?php foreach ($unique_names as $i) { ?>
<th><?php print $i;?></th>
<?php } ?>
</tr>
</thead>
<tbody>
<?php
foreach ($data as $row) {
for ($i=0; $i<=count($unique_names);$i++) {
if ($i==0) {
print "<tr><td>".$row['date']."<td>";
} else {
print "<td>".$row['value']."<td>";
}
if ($i==count($unique_dates)) {
print "</tr>";
}
}
}
?>
</tbody>
2 answers
Answer question0
First, reorganize your data into an array that uses the date as key on the first level, and the name on the second. Collect the names at the same time:
$input = [
['date' => '04/2021', 'name' => 'Fred', 'value' => '12.00'],
['date' => '04/2021', 'name' => 'Tom', 'value' => '160.00'],
['date' => '04/2021', 'name' => 'Mike', 'value' => '9.00'],
['date' => '07/2021', 'name' => 'Tony', 'value' => '200.00'],
['date' => '07/2021', 'name' => 'Fred', 'value' => '43.00'],
['date' => '07/2021', 'name' => 'Tom', 'value' => '114.00'],
['date' => '07/2021', 'name' => 'Mike', 'value' => '28.00'],
];
$data = $names = [];
foreach($input as $item) {
$data[$item['date']][$item['name']] = $item['value'];
$names[] = $item['name'];
}
$names = array_unique($names);
Then create your table. Loop over the names to create the header row first. Then loop over the reorganized data array (to create one row for each date), and inside that loop over your names again - if you find an entry matching the name in the data array, then output the value, otherwise nothing.
<table>
<tr>
<th></th>
<?php foreach($names as $name): ?>
<th><?php echo $name; ?></th>
<?php endforeach; ?>
</tr>
<?php foreach($data as $date => $userData): ?>
<tr>
<td><?php echo $date; ?></td>
<?php foreach($names as $name): ?>
<td><?php echo $userData[$name]??'-'; ?></td>
<?php endforeach; ?>
</tr>
<?php endforeach; ?>
</table>
Result:
<table>
<tr>
<th></th>
<th>Fred</th>
<th>Tom</th>
<th>Mike</th>
<th>Tony</th>
</tr>
<tr>
<td>04/2021</td>
<td>12.00</td>
<td>160.00</td>
<td>9.00</td>
<td>-</td>
</tr>
<tr>
<td>07/2021</td>
<td>43.00</td>
<td>114.00</td>
<td>28.00</td>
<td>200.00</td>
</tr>
</table>0
Here is your answer, great exercise.
<html>
<head>
<style>
table, th, td {
border: 1px solid black;
}
</style>
</head>
<body>
<?php
$data = Array (
Array ( 'date' => '04/2021' ,'name' => 'Fred' ,'value' => '12.00' ),
Array ( 'date' => '04/2021' ,'name' => 'Tom' ,'value' => '160.00' ),
Array ( 'date' => '04/2021' ,'name' => 'Mike', 'value' => '9.00' ),
Array ( 'date' => '07/2021' ,'name' => 'Tony' ,'value' => '200.00' ),
Array ( 'date' => '07/2021' ,'name' => 'Fred' ,'value' => '43.00' ),
Array ( 'date' => '07/2021' ,'name' => 'Tom' ,'value' => '114.00' ),
Array ( 'date' => '07/2021' ,'name' => 'Mike' ,'value' => '28.00' )
);
foreach ($data as $key => $value) {
$tmp[$value['name']][$value['date']] = $value;
}
$unique_names = array_unique(array_map(function($elem){
return $elem['name'];
}, $data));
$unique_dates = array_unique(array_map(function($elem){
return $elem['date'];
}, $data));
?>
<table>
<thead>
<tr>
<th>Dates</th>
<?php foreach ($unique_names as $i) { ?>
<th><?php print $i;?></th>
<?php } ?>
</tr>
</thead>
<?php
foreach ($unique_dates as $date)
{
echo "<tr>";
echo "<td>".$date."</td>";
foreach ($unique_names as $name) {
if(array_key_exists($date,$tmp[$name]))
echo "<td>".$tmp[$name][$date]['value']."</td>";
else
echo "<td>0</td>";
}
echo "</tr>";
}
?>
<tbody>
</tbody>
</table>
</body>
</html>