CODEWITHSUNDEEP
phpsqlmysqldate
Johnson
Johnson

Reputation: 818

PHP: making filter by today/yesterday

I have a filter here:

$today = time() - (3600*24);
$Yday = time() - (3600*48);


$getMsgsToday = mysql_query("SELECT * FROM users_msgs WHERE uID = '$USER' AND date > $today ORDER by date DESC LIMIT 10");


$getMsgsYday = mysql_query("SELECT * FROM users_msgs WHERE uID = '$USER' AND date BETWEEN $Yday AND $today ORDER by date DESC LIMIT 10");

Which does not work properly. If the time is 01:00, it will show you under "Today" dates from yesterday, e.g 23:00, 22:00, 21:00 and will first show you under "yesterday" after its over 01:00 the day after.

How can i fix this so it goes with the time right? So 00:01 is today and 23:59 is yesterday.. I think ive done wrong just doing time()-(3600*24).. how should i do?

Upvotes: 1

Views: 3469

Answers (6)

dz1984
dz1984

Reputation: 452

I think you want to this. Hope i can help you .....

//Date format(YYYY-MM-DD) change to timestamp 
function getTS($date){
    if (false ===preg_match('/\d{4}-\d{2}-\d{2}/i', $date))
        return 0;
    list($year,$month,$day) = explode('-',$date);
    return mktime(0,0,0,$month,$day,$year);
}
//Get Today and Yesterday Timestamp.
$today = getTS(date('Y-m-d'));
$Yday = getTS(date('Y-m-d',strtotime('yesterday')));

Apply sql script.

$getMsgsToday = mysql_query("SELECT * FROM users_msgs WHERE uID = '{$USER}' AND date > {$today} ORDER by date DESC LIMIT 10");

$getMsgsYday = mysql_query("SELECT * FROM users_msgs WHERE uID = '{$USER}' AND date BETWEEN {$Yday} AND {$today} ORDER by date DESC LIMIT 10");

Upvotes: 0

Gordon
Gordon

Reputation: 316939

You are probably getting the error because we had DST yesterday. With the code below, this is taken into account. You should never calculate timestamps yourself. It's error prone.

$now       = time();                 // gives timestamp of right now
$today     = strtotime('today')      // gives timestamp of today 00:00
$yesterday = strtotime('yesterday'); // gives timestamp for yesterday 00:00
$ts24hago  = strtotime('-24 hours'); // gives timestamp 24 hours ago

And I agree with El Yobo that it is easier to do that right from MySql.

Upvotes: 3

El Yobo
El Yobo

Reputation: 14946

There's no need to calculate this stuff in PHP, do it in the SQL itself.

-- Today's messages: round the "date" field to be only a date, not a timestamp, then compare
$getMsgsToday = mysql_query("SELECT * FROM users_msgs WHERE uID = '$USER' AND cast(`date` as date) = cast(now() as date) ORDER by date DESC LIMIT 10");

-- Yesterday's messages: round the "date" field to be only a date, then compare to today - 1 day
$getMsgsYday = mysql_query("SELECT * FROM users_msgs WHERE uID = '$USER' AND cast(`date` as date) = date_sub(cast(now() as date), interval 1 day) ORDER by date DESC LIMIT 10");

The date manipulation functions in most databases are easier to use than PHP anyway, so you don't have to make your life difficult :)

Upvotes: 3

Rob
Rob

Reputation: 8187

$today_start = strtotime(date('Y-m-d 00:00:00'));
$today_end = strtotime(date('Y-m-d 23:23:59'));
$yesterday_end = $today_start - 1;
$yesterday_start = $yesterday_end - 86399;

Upvotes: -1

KeatsKelleher
KeatsKelleher

Reputation: 10191

$today = date('Y-m-d',time());
$yesterday = date('Y-m-d',time() - 3600);

Upvotes: 0

localhost
localhost

Reputation: 367

time() will go based on the current to-the-second timestamp, where you need the beginning of the current/previous days. I would suggest using mktime instead, there are plenty of examples on the php site.

Upvotes: 0

Related Questions