如何将sql表数据与不同的列日期一起“回显”？-解网

问：

SQL 表tbl_meals

+-----+---------+---------+---------+---------+----------+
| id  | receiver|  bfast  |  lunch  |  dinner |   date   |
+-----+---------+---------+---------+---------+----------+
|  1  | smith   | served  |         | served  | 04-18-23 |
+-----+---------+---------+---------+---------+----------+
|  2  | philip  |         |  served | served  | 04-18-23 |
+-----+---------+---------+---------+---------+----------+
|  3  | mercede | served  |         | served  | 04-19-23 |
+-----+---------+---------+---------+---------+----------+
|  4  | annie   |         |  served | served  | 04-20-23 |
+-----+---------+---------+---------+---------+----------+

假设 a to 是成功的。我需要在 HTML 中显示它。该表应如下所示...connectiondatabaseweek_mealtable

+---------+----------+----------+----------+
|   Date  | April 18 | April 19 | April 20 | and so on, until the last tbl_meals of each week
+---------+----------+----------+----------+
|Breakfast|   1      |    1     |  None    |
+---------+----------+----------+----------+
|  Lunch  |   1      |  None    |   1      |
+---------+----------+----------+----------+
|  Dinner |   2      |    1     |   1      |
+---------+----------+----------+----------+

我知道这对我这个新手来说相当复杂，phpmysqli

我只是简单地从列中显示值。它仅适用于以下日期$queryDISTINCTtbl_mealsdate

$sql = "SELECT DISTINCT date 
        FROM tbl_meals 
        ORDER BY date ASC";
$res = mysqli_query($connection, $sql);

但不是它应该显示的方式。

+---------+----------+----------+----------+
|   Date  | April 18 | April 19 | April 20 | and so on, until the last tbl_meals of each week
+---------+----------+----------+----------+
|Breakfast|   1      |    1     |  None    |
+---------+----------+----------+----------+
|  Lunch  |   1      |  None    |   1      |
+---------+----------+----------+----------+
|  Dinner |   2      |    1     |   1      |
+---------+----------+----------+----------+

PHP 数据库 mysqli html-table

// adapt to your needs
$startDate = new DateTime('2023-04-18');
$finishDate = new DateTime('2023-04-25');

// we need to convert a string (mm-dd-yy) into a MySQL DATE (yyyy-mm-dd)
$datesWhere = ' WHERE CAST(CONCAT(' .
    // year (plus separator)
    '"20", SUBSTR(`date`, 7, 2), "-", ' .
    // month (plus separator)
    'SUBSTR(`date`, 1, 2), "-", ' .
    // day
    'SUBSTR(`date`, 4, 2)' .
    ') AS DATE)' .
    sprintf(' BETWEEN "%s" AND "%s"', $startDate->format('Y-m-d'), $finishDate->format('Y-m-d'));

// first we retrieve the distinct dates (within the date range)
$sql = 'SELECT DISTINCT `date`' . 
    ' FROM `tbl_meals`' .
    $datesWhere .
    ' ORDER BY `date` ASC';

$res = mysqli_query($connection, $sql); 

// use the distinct dates as table column headings
// and also use them to contruct the columns of our next SQL statement
$select = [];
echo '<table>';
echo '<thead><tr>';
echo '<th>Meal</th>';
while ($row = mysqli_fetch_assoc($res)) {
    echo '<th>' . htmlspecialchars($row['date']) . '</th>';
    // we will count the number of non-NULL columns for this date
    // NB leaving '%s' in the string for later sprintf() substitution according to which meal
    $select[] = sprintf('COUNT(IF(`date` = "%s", `%%s`, NULL)) AS "%s"', $row['date'], $row['date']);
}
echo '</tr></thead>';

// aggregate SQL statement - one column for meal name, then our date columns
$sql = 'SELECT "%s" as "Meal", ' . implode(',', $select) .
    ' FROM `tbl_meals`' .
    $datesWhere .
    ' GROUP BY `date`';

// make three versions for the three meal types
$bfastSQL = sprintf($sql, 'Breakfast', ...array_fill(0, count($select), 'bfast'));
$lunchSQL = sprintf($sql, 'Lunch', ...array_fill(0, count($select), 'lunch'));
$dinnerSQL = sprintf($sql, 'Dinner', ...array_fill(0, count($select), 'dinner'));

// join them together so that we get three rows in our result set
$mealsSQL = $bfastSQL . ' UNION ' . $lunchSQL . ' UNION ' . $dinnerSQL;

$res = mysqli_query($connection, $mealsSQL); 
echo '<tbody>';
while ($row = mysqli_fetch_assoc($res)) {
    echo '<tr>';
    // the first item is the meal name
    echo '<th>' . htmlspecialchars(array_shift($row)) . '</th>';
    // the remaining items are the values by date
    echo '<td>' . implode('</td><td>', array_map('htmlspecialchars', $row)) . '</td>';
    echo '</tr>';
}
echo '</tbody></table>';

如何将sql表数据与不同的列日期一起“回显”？

How to `echo` sql table data together with distinct column date?

评论

评论