CODEWITHSUNDEEP
mysqlsql
user2279205
user2279205

Reputation: 175

MySQL Current date

I would like to have user can see their today picture uploaded on profile page.

Is this correct? 

SELECT * FROM pictures 
WHERE userid = '$userid'
ORDER BY pictureuploaddate < DATE_ADD(NOW(), INTERVAL 1 DAY);

Still not working. Thanks for the help.

Upvotes: 3

Views: 1469

Answers (4)

Ross Smith II
Ross Smith II

Reputation: 12189

This will order by a boolean result of the comparison:

ORDER BY pictureuploaddate < DATE_ADD(NOW(), INTERVAL 1 DAY)

I'm guessing you want the comparison in the WHERE clause:

WHERE userid = '$userid'
AND pictureuploaddate BETWEEN CURDATE() AND CURDATE() + INTERVAL 86399 SECOND
ORDER BY pictureuploaddate DESC

Upvotes: 2

Cargo23
Cargo23

Reputation: 3189

I know you picked an answer already, but to avoid any confusion with multiple uploads on the same day, you could have also done:

SELECT * 
FROM pictures 
WHERE userid = '$userid'
ORDER BY pictureuploaddate DESC
LIMIT 1;

Upvotes: 2

Niels
Niels

Reputation: 49949

You would get something like:

SELECT * 
FROM pictures 
WHERE userid = '$userid' AND 
      DATE(pictureuploaddate) = CURDATE() # Match date without time
ORDER BY pictureuploaddate DESC

Why do you ORDER BY and use an = in it? It should be todays date, just add it to the WHERE. If you want to get the latest picture first you can ORDER BY pictureuploaddate DESC

Also it's better to compare dates instead of smaller than if you want current date. Because it is faster to match.

Upvotes: 2

Niels Keurentjes
Niels Keurentjes

Reputation: 41968

You are now trying to order the results by the output of a boolean test on the date. Adding that part to the WHERE-clause makes more sense.

Upvotes: 1

Related Questions