php mysql today, yesterday and date from database

Question

I am trying to save date when the user log out so that he can find the date of the last login, I am using this code:

 mysql_query("UPDATE `table` SET `lastLog` = now() WHERE user_id = '".$_SESSION['userID']."'");

which will give the history and the time of the last login correctly but I want the code to show the word today if the last login was today and yesterday if the last login was yesterday. and after yesterday I want to show only the regular date for example if I logged out three days ago then the value should be like this: 8/7/2014. is there an easy way to do it.

Do it in PHP, there is plenty of ways, have a search, also, I hope you dont allow userids/usernames like `1' or '1'='1` — Lawrence Cherone, Jul 11 '14 at 07:39
So, if you store `today` in your database, what happens to that value 3 days later? — John Bupit, Jul 11 '14 at 07:43
Check out, [php date yesterday today](http://stackoverflow.com/questions/3454258/php-date-yesterday-today) — Lawrence Cherone, Jul 11 '14 at 07:44
@John Bupit its supposed to show the regular date for example if I logged out three days ago then the value should be like this: 8/7/2014 — user2521365, Jul 11 '14 at 07:47

rack_nilesh · Accepted Answer · 2014-07-11T08:29:35.653

3

    $result = mysql_query("select lastLog from `table` WHERE user_id = '".$_SESSION['userID']."'"); 
    while($row = mysql_fetch_array($result))
        {
         $last_login = $row['lastLog '];
        }

if($last_login  == date('d.m.Y'))
echo "its today";

if($last_login  == date('d.m.Y',strtotime("-1 days")))
echo "it was yesterday";

else
echo 'it was '.$last_login;

edited Jul 11 '14 at 08:29

answered Jul 11 '14 at 07:54

rack_nilesh

553
5
18

score 2 · Answer 2 · answered Jul 11 '14 at 07:46

Firstly, you are going to want to be using MYSQLI rather than mysql. Mysql is outdated and deprecated and MYSQLI is the newer, more stable child of mysql php syntax.

Second, this should point you in the right direction of how to achieve what you need.

$today = date('FORMAT OF DATE FROM QUERY');
$yesterday = date('FORMAT OF DATE FROM QUERY', strtotime("yesterday"));

if ($result === $today) {
    $result = "today";
}
elseif ($result === $yesterday) {
    $result = "yesterday";
}

score 1 · Answer 3 · answered Jul 11 '14 at 07:46

If you want to select the data and display 'Today/Yesterday' or 'Date' depending on the value you can do so using case-when in the query something as

select
col1,
col2,
.....
case 
when date(lastLog) = curdate() then 'Today' 
when date(lastLog) = curdate()-INTERVAL 1 day then 'Yesterday'
else lastLog
end as lastLog
from `table`

php mysql today, yesterday and date from database

3 Answers3

Linked