Leaderboard
Popular Content
Showing content with the highest reputation on 08/22/2021 in all areas
-
input +----+---------------+----------+-------+---------------------+ | id | license_plate | car_bike | price | create_date | +----+---------------+----------+-------+---------------------+ | 1 | BA12ECD | 1 | 5.50 | 2021-08-21 08:09:51 | | 3 | DA12ECD | 1 | 8.50 | 2021-08-21 08:09:51 | | 6 | GA12ECD | 1 | 5.50 | 2021-08-21 08:09:51 | | 7 | HA12ECD | 1 | 8.50 | 2021-08-21 08:09:51 | | 2 | | 2 | 1.50 | 2021-08-21 08:09:51 | | 4 | | 2 | 1.50 | 2021-08-21 08:09:51 | | 5 | | 2 | 1.50 | 2021-08-21 08:09:51 | | 8 | BB12ECD | 1 | 5.50 | 2021-08-22 08:09:51 | | 10 | DB12ECD | 1 | 5.50 | 2021-08-22 08:09:51 | | 13 | GB12ECD | 1 | 9.50 | 2021-08-22 08:09:51 | | 14 | HB12ECD | 1 | 5.50 | 2021-08-22 08:09:51 | | 9 | | 2 | 1.50 | 2021-08-22 08:09:51 | +----+---------------+----------+-------+---------------------+ query SELECT date(create_date) as parking_date , car_bike , count(*) as num , sum(price) as tot FROM tb_parking_group GROUP BY parking_date, car_bike WITH ROLLUP; output +--------------+----------+-----+-------+ | parking_date | car_bike | num | tot | +--------------+----------+-----+-------+ | 2021-08-21 | 1 | 4 | 28.00 | | 2021-08-21 | 2 | 3 | 4.50 | | 2021-08-21 | NULL | 7 | 32.50 | <-- day subtotal | 2021-08-22 | 1 | 4 | 26.00 | | 2021-08-22 | 2 | 1 | 1.50 | | 2021-08-22 | NULL | 5 | 27.50 | <-- day subtotal | NULL | NULL | 12 | 60.00 | <-- total +--------------+----------+-----+-------+1 point
-
Do you mean something like this? <?php // get the "name" headings that you need for the columns // and also use them as keys in a "template" array // $res = $db->query("SELECT DISTINCT name FROM dataset ORDER BY name "); $names = $res->fetchAll(); $heads = array_column($names, 'name'); $temp = array_fill_keys($heads, ''); $table_header = "<tr><td></td><td class='thead'>Result</td><td class='thead'>" . join("</td><td class='thead'>", $heads) . "</td></tr>\n"; // now get the data // store in an array by "id" // witd subarrays for each name $res = $db->query("SELECT id , edate , result , name , nos FROM maintab m JOIN dataset d ON m.id = d.mid ORDER BY id "); $data = []; foreach ($res as $r) { if (!isset($data[$r['id']])) { $data[$r['id']] = [ 'edate' => $r['edate'], 'result' => $r['result'], 'names' => $temp // the template array from earlier ]; } $data[$r['id']]['names'][$r['name']] = $r['nos']; // put value in tempate array } // now we simply output data array into html table rows $tdata = ''; foreach ($data as $row) { $tdata .= "<tr><td>{$row['edate']}</td><td>{$row['result']}</td><td>" . join('</td><td>', $row['names']) . "</td></tr>\n"; } ?> <html> <head> <title>Example</title> <style type='text/css'> td { padding: 4px 10px; } .thead { font-weight: 600; border-top: 1px solid gray; border-bottom: 1px solid gray; } </style> </head> <body> <table> <?= $table_header ?> <?= $tdata ?> </table> </body> </html> OUTPUT [edit] PS Sorry about the data typo. That's what happens when people post pictures instead of copyable text.
1 point
This leaderboard is set to New York/GMT-05:00