Question 46206 in PHP Question posted in
Official manual can be found here.

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

SQL Table 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 |
+-----+---------+---------+---------+---------+----------+

Supposing that a connection to the database week_meal is successful.
I need to display this in the HTML table.
The table should look like…

+---------+----------+----------+----------+
|   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      |
+---------+----------+----------+----------+

I know it is quite complicated for me as a newbie in php, mysqli

I did only simple $query to display DISTINCT value from tbl_meals date column. It works fine only for the date which is

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

But not the way it should be displayed.

+---------+----------+----------+----------+
|   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      |
+---------+----------+----------+----------+
Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    I have solved it myself. I will be sharing it here. Thank you for the interest in helping me solve this. I appreciate your efforts.


  2. 0 
    // 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>';
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search