date to mon - fri

[Destramic]

hey guys i have a little problem and not sure of the best way to tackle it, if you could help please.

i have 2 url which could be like so
 

tasks/ricky-powell/week/14/08/2018
tasls/ricky-powell/day/14/08/2018

now if /day/14/08/2018 then thats a simple query.

SELECT task FROM tasks WHERE scheduled_date = '2018-08-14'

 

but...if users selects to view tasks by week (mon-fri) then it becomes a little bit complicated.

14/08/2018 is today which is a tuesday, and i would need to get the range from date mon - fri.

would it be easier to:

 

1. ensure weekly url is a monday?

2.work out the date (mon - fri) view mysql/php

3. something else.

 

thank you.

 

 

 

[Barand]

try

$date = '2018-08-14';
$dt = new DateTime($date);
if ($dt->format('D') != 'Mon') {
    $dt->modify('last monday');
}
echo $dt->format('Y-m-d');      //--> 2018-08-13

 

[Destramic]

thank you for your quick reply. i would also need friday's date.

ive been testing via sql, and think ive almost made a breakthrough, but i get an error.

SELECT 
DATE_ADD(DATE('2018-08-14'), INTERVAL - WEEKDAY(DATE('2018-08-14')) DAY) AS 'week_start',
DATE_ADD(week_start, INTERVAL 5 DAY) AS 'week_end'

 

Quote

Error Code: 1054. Unknown column 'week_start' in 'field list'

 

[Barand]

Column aliases are applied on query output so week_start doesn't exist. Also the alias shouldn't be in quotes.

Continuing from my previous code
 

$monday = $dt->format('Y-m-d');
$friday = $dt->add(new DateInterval('P4D'))->format('Y-m-d');

or

SELECT ... WHERE scheduled_date BETWEEN '$monday' AND '$monday' + INTERVAL 4 DAY

 

[Destramic]

thats brilliant...thank you for your help Barand ?

much appreciated.