How make table data from vertical to horizontal with MYSQL & PHP

Question

Please help me to solve a problem with 2 tables:

1. tbl_data
2. tbl_shipped

tbl_data:

id_data	nama	category
110	Singkong	Asin
111	Ubi	Pedas
112	Pisang	Manis
113	Bawang	Asin
114	Tempe	Pedas Manis
115	Singkong	Pedas
116	Singkong	Manis
117	Singkong	Pedas Manis
118	Ubi	Pedas Manis
119	Ubi	Asin
120	Pisang	Pedas

tbl_shipped:

id	id_data	date	amount
1	110	2021-05-19	40
2	111	2021-05-21	31
3	112	2021-05-29	80
4	112	2021-06-19	50
5	112	2021-06-22	5
6	112	2021-07-30	23
7	113	2021-08-05	70
8	113	2021-08-13	28
9	114	2021-09-04	8
10	115	2021-11-10	67
11	115	2021-12-20	1
12	117	2021-05-19	12
13	117	2021-06-22	50
14	117	2021-08-05	21
15	120	2021-09-04	71
16	120	2021-11-10	53

Expected result:

nama	category	2021-05-19	2021-05-21	2021-05-29	2021-06-19	2021-06-22	2021-07-30	2021-08-05	2021-08-13	2021-09-04	2021-11-10	2021-12-20	Total
Singkong	Asin	40											40
Ubi	Pedas		31										31
Pisang	Manis			80	50	5	23						158
Bawang	Asin							70	28				98
Tempe	Pedas Manis									8			8
Singkong	Pedas										67	1	68
Singkong	Pedas Manis	12				50		21					83
Pisang	Pedas									71	53		60
	All Total :	52	31	80	50	55	23	91	28	15	120	1	546

If expected results are a pivot like on the table in the above picture, how can it be done?

This is my code (From request) which I have tried, but not optimal :

<table>
<thead>
    <?php include "conn.php";
        $result = $connect->query("SELECT * FROM tbl_shipped LEFT JOIN tbl_data ON tbl_data.id_data = tbl_shipped.id_data ORDER BY tbl_shipped.date ASC"); // query-in database
        $data = array();
        $dates = array();
        while ($row = $result->fetch_array()) { // fetching result
            if(!isset($data[$row['id_data']][$row['date']])) {
                $data[$row['id_data']][$row['date']] = array();
            }     
        
                if(!in_array($row['date'], $dates)) {
                    $dates[] = $row['date'];
                }
            
            $data[$row['id_data']][$row['date']] = $row['amount'];
        }
    ?>
    <tr>
        <th></th>
        <?php foreach($dates AS $date) : ?>
        <th><?=$date?></th>
        <?php endforeach; ?>
    </tr>                                            
</thead>
<tbody>
    <?php foreach($data AS $id => $entries) : ?> 
    <tr>     
        <td><?=$id?></th>                                                              
        <?php foreach($dates AS $date) : ?>
        <td><?=(isset($data[$id][$date])?$data[$id][$date]:'')?></td>
        <?php endforeach; ?>
    </tr>
    <?php endforeach; ?>
</tbody>
<tfoot>

</tfoot>
</table>

Based on sorce code on above, this is result:

	2021-05-19	2021-05-21	2021-05-29	2021-06-19	2021-06-22	2021-07-30	2021-08-05	2021-08-13	2021-09-04	2021-11-10	2021-12-20
110	40
111		31
112			80	50	5	23
113							70	28
114									8
115										67	1
117	12				50		21
120									7	53

Please help me. And how should i write to add code, i hope get result as expected. Thank you.

Answer 1

I can at least think of one way using prepared statement. Consider this option:

SET @sql := NULL;
SET @columns := NULL;

SET @columns := (SELECT GROUP_CONCAT(
                                 CONCAT("SUM(CASE WHEN `date`='",dates,"' 
                                                  THEN amount 
                                                   ELSE 0 END) AS '",dates,"'") SEPARATOR ',') 
                  FROM (SELECT DISTINCT `date` AS dates FROM tbl_shipped) dt);

SET @sql := CONCAT("SELECT td.id_data,
                           td.nama,
                            td.category,",@columns,"
                      FROM tbl_shipped AS ts 
                      JOIN tbl_data AS td ON ts.id_data=td.id_data
                  GROUP BY td.id_data, td.nama, td.category;");

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

I'm using @sql as a variable for the final query structure and @columns as a variable for the columns structures. It can be just one variable but I think this is easier to read and edit.

Demo fiddle