Displaying rows for this week only in PHP

Question

I have two dates stored in my database as columns dtp_s and dtp_e (start, end). These derive from a populated form which the user is made to select a start and end date.

I want to display records from Monday - Sunday of the current week, but my current solution is showing dates from 7 Days before - Today.

SELECT id
FROM _records
WHERE
    dtp_s > unix_timestamp(now() - interval 1 week)
    AND userid = ?
ORDER BY dtp_s DESC 
LIMIT 5

I have tried to change now() to be the value of strtotime( 'sunday' ) but this then shows no records when one does exist.

Any ideas on how I only show data based on ones that start the same week (Mon - Sun) ?

Answer 1

To get the Monday of the current week you could use:-

select date(curdate() - interval weekday(curdate()) day)

To add this into your code:-

SELECT id FROM _records 
WHERE dtp_s > date(curdate() - interval weekday(curdate()) day) AND userid = ? 
ORDER BY dtp_s DESC 
LIMIT 5

Answer 2

After looking at other questions from SO, this can be achieved in SQL rather than mixing PHP strtotime values that could be in different timezones if not configured correctly.

SELECT id FROM _records
WHERE dtp_s > unix_timestamp(date(now() + interval 6 - weekday(now()) DAY) - interval 1 week)
AND userid = ?
ORDER BY dtp_s DESC
LIMIT 5

I am getting only the records for this week displayed.