Display the first and last day of a (calendar) week

user_15840906 0
Viewed 99

In my database I have a table with consecutive dates. I group all dates to calendar weeks and display them in a table in my CSS.

Now i want to display also the first and the last day of the calendar week in my table. Here is my code:

$statement = $pdo->prepare("SELECT WEEK(Date), Date FROM table GROUP BY WEEK(Date)");
    $result = $statement->execute();
    $count = 1;
    while($row = $statement->fetch()) {
        echo "<tr>";
        echo "<td>".$row['WEEK(Date)']."</td>";
        echo "<td>".$row['Date']."</td>";

Currently there is only shown the first day of the calendar week. I thought of duplicating $row['Date']and adding +6 (days) but the first dataset is not the first day of the week. So the first date range of the first calendar week would be wrong. Does anyone has an idea how to solve this problem?

date sql php
1 Answers

See a test of this here: https://www.tehplayground.com/c2dOYxxwIa9LDscW

while($row = $statement->fetch()) {
$thedate = $row['Date'];
$dow = date('w', strtotime($thedate)); // the day of the week of the date - number based - zero is sunday
$dowsun = date('Y-m-d', (strtotime($thedate) - ($dow * 60*60*24))); // first day of that week (sunday)
$dowsat = date('Y-m-d', strtotime($thedate) + ((6-$dow) * 60*60*24)); // last day of that week (saturday)

/* for example:
   if $row['WEEK(Date)'] = "2021-06-08" which is a Tuesday
   $dowsun = "2021-06-06" which is a Sunday
   $dowsat = "2021-06-12" which is a Saturday
   with this answer you get the full week that contains the target date
*/
   echo "<tr>";
   echo "<td>".$row['WEEK(Date)']."</td>";
   echo "<td>".$row['Date']."</td>";
user_17729330
Related