How to use while loop inside a concatenation?

[Eiolon]

I am working on a calendar but am having difficulty finding a way to loop the events through the concatenation.  Having it loop on the outside of the string brings only the first event for the day, not all of them.  If I loop outside of the string of $week outputs all the events but it creates a new "day" square for each event.  Thanks for your advice.

for ($day = 1; $day <= $count_days; $day++, $str++) {
     
    $date = $year_month . '-' . $day;

	$sth_events = $dbh_mysql->prepare("
		SELECT 
			id, name, date_time
		FROM
  			events
 		WHERE
			date_time LIKE '$date%'
	");
	$sth_events->execute();	
	
	while ($row_events = $sth_events->fetch(PDO::FETCH_ASSOC)) { 
		$name = $row_events['name'];
	}

    if ($today == $date) {
        $week .= '<td>' . '<div class="today">' . $day . '</div>' . $name;
    } else {
		$week .= '<td>' . '<div class="day">' . $day . '</div>' . $name;		
    }
    $week .= '</td>';

 

[requinix]

There's a smarter solution to this. One that doesn't involve queries in a loop.

Get all the events for the month. Before the loop.

SELECT id, name, date_time, DAYOFMONTH(date_time) AS day
FROM events
WHERE date_time BETWEEN '2018-11-01 00:00:00' AND '2018-11-31 23:59:59'
ORDER BY date_time

The date range is $year_month-01 midnight through $year_month-31 (doesn't matter if the month has <31 days) just before midnight. And the list is sorted by the date.

Go through the results and stick it all into a 2D array according to the "day" value. Then your main loop can check the array to see if it has any events.

[cyberRobot]

You could assign the event names to an array, for example:

$name = array();
while ($row_events = $sth_events->fetch(PDO::FETCH_ASSOC)) { 
    $name[] = $row_events['name'];
}

Then you could use implode() to output the array. Note that I streamlined the following code a bit:

$week .= '<td><div class="';
$week .= ($today == $date) ? 'today' : 'day';
$week .= '">' . $day . '</div>' . implode('<br>', $name) . '</td>';

With that said, it would be better to rewrite the script, as requinix is suggesting, so that you're not running queries inside of loops. 

[Barand]

I have to agree - do a single query to get the events for the month. I would

• set up a calendar array of the days in the month (structure: $calendar[wk][wkday][events] )
• Loop through the query results and drop the events into their respective week/day slots
• Loop throught the array to output the calendar

DATA

TABLE: event
+----+----------+---------------------+
| id | name     | date_time           |
+----+----------+---------------------+
|  1 | Event 1  | 2018-11-01 15:00:00 |
|  2 | Event 2  | 2018-11-02 12:00:00 |
|  3 | Event 3  | 2018-11-11 14:00:00 |
|  4 | Event 4  | 2018-11-14 11:00:00 |
|  5 | Event 5  | 2018-11-15 14:00:00 |
|  6 | Event 6  | 2018-11-16 15:00:00 |
|  7 | Event 7  | 2018-11-19 15:00:00 |
|  8 | Event 8  | 2018-11-20 16:00:00 |
|  9 | Event 9  | 2018-11-23 14:00:00 |
| 10 | Event 10 | 2018-11-30 10:00:00 |
| 11 | Event 3A | 2018-11-11 16:00:00 |
+----+----------+---------------------+

CODE

<?php
include('db_inc.php');
$db = pdoConnect("test");   // connect to "test" database

$curmonth = date('F Y');

//
// set up the date range required
//
$dt1 = new DateTime("first day of this month");
$dt2 = clone $dt1;
$dt2->add(new DateInterval('P1M'));

$dint = new DateInterval('P1D');
$dper = new DatePeriod($dt1, $dint, $dt2);

//
// create an array calendar[wk][wkday][events] to store events then output
//
$calendar = [];
foreach ($dper as $d) {
    $wk = $d->format("W");
    $calendar[$wk] = array_fill_keys(range(0,6), []);
}

//
// get the event data for current month
//
$stmt = $db->query("SELECT id
                           , DATE_FORMAT(date_time, '%D') as day
                           , WEEK(date_time, 1) as wkno
                           , WEEKDAY(date_time) as wkday
                           , DATE_FORMAT(date_time, '%k:%i') as time
                           , name
                      FROM event
                      WHERE YEAR(date_time) = YEAR(CURDATE())
                        AND MONTH(date_time) = MONTH(CURDATE())
                      ORDER BY wkno, wkday, time 
                      ");

//
// loop through results and drop events into the array
//
foreach ($stmt as $ev) {
    $calendar[$ev['wkno']][$ev['wkday']][] = [ 'day' => $ev['day'],
                                               'time' => $ev['time'],
                                               'name' => $ev['name']
                                             ];
}

//
// output the array
//
$tdata = '';
foreach ($calendar as $wk => $wdata) {
    $tdata .= '<tr>';
    foreach ($wdata as $dno => $events) {
        $cls = $dno > 4 ? "class='we'":"";
        $tdata .= "<td $cls>";
        foreach ($events as $ev) {
            $tdata .= "{$ev['day']} {$ev['time']} {$ev['name']}<br>";
        }
        $tdata .= "</td>\n";
    }
    $tdata .= "</tr>\n";
}
?>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<meta name="generator" content="PhpED 18.0 (Build 18044, 64bit)">
<meta name="creation-date" content="11/06/2018">
<title>Sample Calendar</title>
<style>
    table {
        border-collapse: collapse;
        font-family: verdana, sans-serif;
        font-size: 10pt;
    }
    th {
        background-color: #369;
        color: white;
        padding: 5px;
        width: 14%;
    }
    th.we {
        background-color: #358;
    }
    td {
        background-color: #FFE;
        padding: 5px;
    }
    td.we {
        background-color: #FFC;
    }
</style>
</head>
<body>
<h3>Calendar <?=$curmonth?></h3>
<table border='1'>
    <thead>
        <tr><th>Mon</th><th>Tue</th><th>Wed</th><th>Thu</th><th>Fri</th><th class='we'>Sat</th><th class='we'>Sun</th></tr>
    </thead>
    <tbody>
        <?=$tdata?>
    </tbody>
</table>
</body>
</html>

OUTPUT

 

[Eiolon]

Thank you all so much for your advice!