CODEWITHSUNDEEP
mysql
user2292887
user2292887

Reputation: 13

MYSQL Select By This Week

I would like to have user can see their uploaded images by this week.

Is this correct?

"SELECT * 
FROM images 
WHERE userid = '$userid' 
  AND uploadeddate >= DATEPART(week, uploadeddate) = DATEPART(week, GETDATE()) 
ORDER BY uploadeddate DESC";

I am getting error. Thanks for the help.

Upvotes: 0

Views: 98

Answers (2)

Quassnoi
Quassnoi

Reputation: 425291

SELECT  *
FROM    images
WHERE   userid = $userid
        AND uploadeddate >= CURDATE() - INTERVAL WEEKDAY(day) DAY
        AND uploadeddate < CURDATE() - INTERVAL WEEKDAY(day) DAY + INTERVAL 7 DAY

Create an index on (userid, uploadeddate) for this to work fast.

Upvotes: 1

Kermit
Kermit

Reputation: 34055

The below queries will return records for the current week. It's also best practice to specify a column list in your SELECT.

If you're using SQL Server:

SELECT ... WHERE DATEPART(week, uploadeddate) = DATEPART(week, GETDATE())

If you're using MySQL:

SELECT ... WHERE WEEK(uploadeddate) = WEEK(CURDATE())

Upvotes: 0

Related Questions