I can't get this to work, I'm missing the last step...
1) query the database:
SELECT * FROM (
SELECT id_fattura, ROUND(valore_linea * quantita_linea, 2) AS quota, data_inizio, WEEK(data_inizio, 3) AS settimana, id_cliente, cod_servizio
FROM tbl_linee_fattura_el
) tbl_fe
LEFT JOIN (
SELECT id_cliente, CONCAT(cognome_cliente,' ', nome_cliente) AS nome
FROM tbl_clienti
) tbl_cl
ON tbl_fe.id_cliente = tbl_cl.id_cliente
WHERE cod_servizio = '01200'
AND data_inizio BETWEEN '2020-06-01' AND '2020-09-30'
GROUP BY settimana, nome
ORDER BY settimana, nome ASC
2) PHP code:
$stmt = $db->prepare($query);
$stmt->execute();
$all_result = $stmt->fetchAll();
//print_r($all_result);
$total_rows = $stmt->rowCount();
I get this:
Array
(
[0] => Array
(
[quota] => 65.00
[settimana] => 25
[nome] => John Wayne
)
[1] => Array
(
[quota] => 65.00
[settimana] => 25
[nome] => Clint Eastwood
)
[2] => Array
(
[quota] => 65.00
[settimana] => 25
[nome] => Charls Bronson
)
/*lot of other arrays, I left here only the relevant keys and values for the example.
*'quota' is a payment,
*'settimana' is week of the year (corresponding to date)
*'nome' is a customer */
)
the next step: i found this function on stackoverflow (unfortunally I did not bookmark the page, i will update here If Icatch that page again)
<?php
function group_by($key, $data) {
$result = array();
foreach($data as $val) {
if(array_key_exists($key, $val)){
$result[$val[$key]][] = $val;
}else{
$result[""][] = $val;
}
}
return $result;
}
$all_result = group_by('nome', $all_result);
?>
and I get this:
Array
(
[John Wayne] => Array
(
[0] => Array
(
[settimana] => 25
[quota] => 10.00
)
[1] => Array
(
[settimana] => 26
[quota] => 21.00
)
)
[Clint Eastwood] => Array
(
[0] => Array
(
[settimana] => 26
[quota] => 22.00
)
[1] => Array
(
[settimana] => 27
[quota] => 23.00
)
[2] => Array
(
[settimana] => 28
[quota] => 24.00
)
)
[Charls Bronson] => Array
(
[0] => Array
(
[settimana] => 26
[quota] => 22.00
)
[1] => Array
(
[settimana] => 28
[quota] => 23.00
)
)
)
other step: build html table
<table>
<thead>
<tr>
<th>Nome</th>
<th>Week 25</th>
<th>Week 26</th>
<th>Week 27</th>
<th>Week 28</th>
</tr>
</thead>
<tbody>
<?php
$keys = array_keys($all_result);
for($i = 0; $i < count($keys); $i++) {
echo "<tr>";
echo "<td>".$keys[$i]."</td>";
$k = array(
'settimana' => '',
'quota' => ''
);
// need array_pad to avoid empty cells in the tables and php warnings
$all_pad = array_pad($all_result[$keys[$i]], 4, $k);
foreach($all_pad as $row) {
echo "<td>" . $row['quota'] . "</td>";
}
echo "</tr>";
}
?>
</tbody>
</table>
the final result:
As you can see results are displayed in the wrong columns:
John Wayne it's ok
Clint Eastwood values should be displayed in columns week 26, week 27 and week 28
Charles Bronson first value it's ok, second value shoul be in column week 28
(I put the right position inside the red baloons, W25, W26 and so on...).
How can I display the values at the right place?
A better MySQL query()?
A better array_pad() function?
A better foreach() loop?
UPDATE I found this thread: Insert new item in array on any position in PHP
so, this is the code:
$keys = array_keys($all_result);
// this is the same function used to group by name, now group by weeks to get all the weeks numbers
$week = group_by('week', $all_result);
$weeks = array_keys($week);
for($i = 0; $i < count($keys); $i++) {
echo "<tr>";
echo "<td>".$keys[$i]."</td>";
foreach ($weeks as $k) {
if ( find_key_value($all_result[$keys[$i]], 'week', $k) !== true ) {
$pad = array(
'week' => $k,
'quota' => '',
);
// the code to insert arrays
array_splice( $all_result[$keys[$i]], $i - 1, 0, array($pad) );
// unfortunally (don't know why...) the first two arrays doesn't go in the right place, so I had to use this other code
array_multisort(array_column($all_result[$keys[$i]], 'week'), SORT_ASC, $all_result[$keys[$i]]);
}
}
foreach($all_result[$keys[$i]] as $row) {
echo "<td>" . $row['quota'] . "</td>";
}
echo "</tr>";
}
the final result:
