Eiolon Posted November 6, 2018 Share Posted November 6, 2018 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>'; Link to comment Share on other sites More sharing options...
requinix Posted November 6, 2018 Share Posted November 6, 2018 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. Link to comment Share on other sites More sharing options...
cyberRobot Posted November 6, 2018 Share Posted November 6, 2018 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. Link to comment Share on other sites More sharing options...
Barand Posted November 6, 2018 Share Posted November 6, 2018 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 Link to comment Share on other sites More sharing options...
Eiolon Posted November 6, 2018 Author Share Posted November 6, 2018 Thank you all so much for your advice! Link to comment Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.