CODEWITHSUNDEEP
mysqlsqldate
Jaquarh
Jaquarh

Reputation: 6673

Displaying rows for this week only in PHP

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) ?

Upvotes: 3

Views: 60

Answers (2)

Chris
Chris

Reputation: 1027

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

Upvotes: 2

Jaquarh
Jaquarh

Reputation: 6673

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.

Upvotes: 1

Related Questions