Link to home
Create AccountLog in
Avatar of Dada44
Dada44Flag for Spain

asked on

MySQL: getting rows for a given date

Hi all,

I have the following query:
$yesterday=date("Y-m-d",mktime(0,0,0,date("m"),(date("d")-1),date("Y")));
$sql = "SELECT messages FROM user WHERE sent_date='".$yesterday."'";

Open in new window


With it I select all the messages sent to the users yesterday.

In my database sent_date looks like: 2010-12-28 11:55:30

But with $yesterday I get: 2010-12-28

So I get 0 results.

How can I modify my query so I can select all the messages from yesterday no matter the hour?

Thanks a ton
Avatar of Ray Paseur
Ray Paseur
Flag of United States of America image

$yesterday=date("c", strtotime('Yesterday'));

Then try the query.
This article should give you the background information you need.
https://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/A_201-Handling-date-and-time-in-PHP-and-MySQL.html

After you have a chance to read that, please post back with any specific questions.

best regards, ~Ray
Avatar of Dada44

ASKER

Thanks Ray, but  I think I did not explain myself properly:

If $yesterday is 2010-12-28, it does not match 2010-12-28 11:55:30 in the database so I get 0 results when there was a message sent yesterday.

Using strtotime I get 2010-12-28 00:00:00 which does not equals 11:55:30 so I get 0 results anyway.

How can I get just the date and not the hour from the database?

Thanks a lot for the article I'll have it around for the future.

Thanks again

ASKER CERTIFIED SOLUTION
Avatar of rduke15
rduke15
Flag of Switzerland image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Then I might try $yesterday = strtotime("Now - 1 day");  It really just depends on what your logic requires.  If yesterday was December 28 and it is 4:00pm now, do you want to show everything from December 28?  Or do you want to see only those things that happened in the last 24 hours?

But you probably do not want to use an equality test in the query.  Time is advancing.  Something more like this might be what you want.
$sql = "SELECT messages FROM user WHERE sent_date > '$yesterday' ORDER BY... etc..."
Avatar of Dada44

ASKER

Thanks a lot!