MySQL: getting rows for a given date

I have the following que开发者_如何学运维ry:

$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."'";

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

---

You should use MySQL Date functions for that (assuming the column is a datetime).

SELECT messages FROM user WHERE sent_date = DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY);

or always from 00:00

SELECT messages FROM user WHERE sent_date = DATE_SUB(CONCAT(CURDATE(), ' 00:00:00'), INTERVAL 1 DAY);

If this is not giving any results, please edit your question with more info. You exact schema, how do you insert data and ensure that there are valid rows inside the table.

---

Perhaps by using

$sql = "SELECT messages FROM user WHERE date(sent_date)='".$yesterday."'";

That way you don't use the time in the datetime for comparison.

---

You could do a BETWEEN and put in a starting and ending time concatenated on the $yesterday...

Actually... what the next post suggests is better if you want the full date.