CODEWITHSUNDEEP
phpmysqldate
Ian
Ian

Reputation:

PHP- Getting yesterdays data from the database

I want to get yesterdays entries in the database. Any ideas on what query I should run? Its been blowing my mind for the last hour!

mysql_query("SELECT id , fullname , address1 , address2 , citytown , postcode , telno , email , property_value , on_market , agent , asking_price , reason , total_secured_debt , price_concession , FROM_UNIXTIME( enquiry_time ) , form_page FROM $table WHERE enquiry_time=??????????");

Thanks Ian

Upvotes: 2

Views: 407

Answers (2)

Pascal MARTIN
Pascal MARTIN

Reputation: 401002

What about using the function date_sub ?

For instance, to get yesterday (same time than now) :

select date_sub(now(), interval 1 day);

And to get yesterday, without specifying the time :

select date(date_sub(now(), interval 1 day));

So your condition could be like 

where enquiry_time >= date_sub(now(), interval 1 day) 
    and enquiry_time < date(date_sub(now(), interval 0 day))

(at least, if enquiry_time includes time ; if it only includes date, an = would be enough)

Upvotes: 3

Paul Dixon
Paul Dixon

Reputation: 300845

How about using TO_DAYS to turn the time into a day number, and check it against yesterdays day number:

WHERE TO_DAYS(FROM_UNIXTIME( enquiry_time )) = (TO_DAYS(NOW())-1)

Upvotes: 1

Related Questions