time in database to return certian value

[Dan13071992]

sorry if the title isnt very clear, i just didnt know how to word it, anyways, the point of this topics is, that im wondering how to pull a quesry from the database and have it display either "Today", "Yesterday", "1 Day Ago", "4 days ago" ect ect, 4 in that last one being X ammount of days, i am trying to get it from the value of this feild in the database:

lastseen

which produces a value in the format of date and time like so:

2012-02-19 05:14:31

so my question is, how exactly would i do this, as im not very good with the time functions and quesrys in php.

Cheers if you can help, also if you need more explanations please just ask

Dan

[Dormilich]

there is certainly a function in SQL that lets you get the difference in days. then you can either use a SQL procedure to convert 0 into today and 1 into yesterday or you do that part in PHP.

PS. I deem "yesterday" and "1 day ago" synonymous

[Dan13071992]

Originally Posted by Dormilich

PS. I deem "yesterday" and "1 day ago" synonymous

that is true, i was using it as an example but you are completely correct.

Dan

[Dan13071992]

can anyone help me with this please?

[Dormilich]

what do you need help with? the MySQL date/time functions are here.

[Dan13071992]

well what i need help on is whats in my OP, basically converting the time stamp, from what it is, to something like "Yesterday", "2 Days Ago", "3 Days Ago" so on, so forth.

[Dormilich]

the first step would be calculating the date difference. for that I have given you a link. once you have that it’s just a matter of if()s to make that into a human understandable string.

[Dan13071992]

on that note, is this a reasonable approach?

PHP Code:

$utime=strtotime($fetch2->lastseen);
$eachday = 86400;

$days= $utime-86400; 


[Dormilich]

I would do the date difference calculation in SQL. it has that many date functions for a reason.

[Dan13071992]

im sorry, i dont really understand, ive only recently started learning different methods of using sql, such as table joins, so could you possibly, if you dont mind, show me some sort of example?

[Dormilich]

let’s use a slightly off-topic date function to demonstrate

-- show all dates in european format that are not older than one week
SELECT DATE_FORMAT(mydate, GET_FORMAT(DATE, 'EUR')) FROM mytable WHERE DATE_ADD(mydate, INTERVAL 1 WEEK) > NOW();

PS. there is a function that does exactly what you want.

[Dan13071992]

is this right?

PHP Code:

SELECT TO_DAYS(lastseen) FROM users WHERE username='$viewuser' 


[Dormilich]

does the result meet your expectation?

[Dan13071992]

well ive tried this:

PHP Code:

$querytestnew = mysql_query("SELECT TO_DAYS(lastseen) FROM users WHERE username='DOS1392'");
$fetchnewnew = mysql_fetch_object($querytestnew);
echo "$fetchnewnew->lastseen"; 


however its not displaying anything at all, have i gone wrong somewhere?

[Dormilich]

yes, twice.
- there is no lastseen property in your fetched object
- property calls do not work inside strings

hint: look 3 functions up