...

View Full Version : time in database to return certian value



Dan13071992
02-19-2012, 06:21 PM
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
02-20-2012, 11:44 AM
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
02-20-2012, 07:00 PM
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
02-22-2012, 12:27 PM
can anyone help me with this please?

Dormilich
02-22-2012, 01:53 PM
what do you need help with? the MySQL date/time functions are here (http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html).

Dan13071992
02-22-2012, 05:37 PM
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
02-22-2012, 06:50 PM
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
02-22-2012, 07:04 PM
on that note, is this a reasonable approach?



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

$days= $utime-86400;

Dormilich
02-22-2012, 07:05 PM
I would do the date difference calculation in SQL. it has that many date functions for a reason.

Dan13071992
02-22-2012, 07:08 PM
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
02-22-2012, 07:19 PM
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
02-22-2012, 07:23 PM
is this right?



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

Dormilich
02-22-2012, 07:25 PM
does the result meet your expectation?

Dan13071992
02-22-2012, 07:46 PM
well ive tried this:



$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
02-22-2012, 07:48 PM
yes, twice.
- there is no lastseen property in your fetched object
- property calls do not work inside strings

hint: look 3 functions up

Dan13071992
02-22-2012, 07:49 PM
are you refering to the "TIMESTAMP()" one?

Dormilich
02-22-2012, 07:51 PM
nope, but almost.

Dan13071992
02-22-2012, 07:53 PM
TIMEDIFF() ?

so if i was to write it like this:

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


i still get the following error:

Warning: mysql_fetch_object(): supplied argument is not a valid MySQL result resource in /home/crimewav/domains/crime-wave.co.uk/public_html/profile.php on line 44

Dormilich
02-22-2012, 07:57 PM
obviously, look at what the function call is supposed to be.

TIMEDIFF() is not bad, but there is a better function

Dan13071992
02-22-2012, 08:03 PM
is it something to do with putting the NOW(); in the query?

Dormilich
02-22-2012, 08:06 PM
that’s quite a good idea.

Dan13071992
02-22-2012, 08:09 PM
going by what your saying, i tried this:



$querytestnew = mysql_query("SELECT TIMESTAMPDIFF('lastseen') FROM users WHERE username='DOS1392' AND DATE_ADD(lastseen < NOW();");


is this how i would write it?

Dormilich
02-22-2012, 08:10 PM
what does the manual say how to write it?

Dan13071992
02-22-2012, 08:14 PM
ok, sorry about the confusion on the last one, i think ive got it now, after reading how to write it, ive got this:



$date = gmdate('Y-m-d h:i:s');
$querytestnew = mysql_query("SELECT TIMESTAMPDIFF(DAY,'lastseen','$date') FROM users WHERE username='DOS1392'");

Dormilich
02-22-2012, 08:16 PM
now the only problem is that 'lastseen' is not a date(string) but an ordinary string. besides that you can replace '$date' by NOW().

Dan13071992
02-22-2012, 08:19 PM
is it possible to write a function, like the TIMESTAMPDIFF() inside a previous one?

Dormilich
02-22-2012, 08:44 PM
as long as the return value matches the expected input—yes.

Dan13071992
02-22-2012, 08:45 PM
so would this be right:



$querytestnew = mysql_query("SELECT TIMESTAMPDIFF(DAY,'DATE('lastseen')','$date') FROM users WHERE username='DOS1392'");

Dormilich
02-22-2012, 08:47 PM
you’re turning in circles. TIMESTAMPDIFF() expects a date, not a string.

Dan13071992
02-22-2012, 08:49 PM
ok, i think ive got it now, as i said, im still quite new to working with just sql, but here goes, $fetch2->lastseen is from a previous call, so i replaced lastseen with that:



$querytestnew = mysql_query("SELECT TIMESTAMPDIFF(DAY,'$fetch2->lastseen','$date') FROM users WHERE username='DOS1392'");

Dormilich
02-22-2012, 08:49 PM
I said DATE, not string.

Dan13071992
02-22-2012, 08:53 PM
so i would change lastseen from something like this:

2012-02-22 07:49:23

to this:

2012-02-22

is that what you mean?

Dormilich
02-22-2012, 08:54 PM
nope, you can just pass in the lastseen column in.

Dan13071992
02-22-2012, 08:55 PM
im sorry, i dont follow what you mean

Dormilich
02-22-2012, 08:56 PM
what about TIMESTAMPDIFF(DAY, lastseen, NOW()) ?

Dan13071992
02-22-2012, 09:03 PM
ok so ive got that, am i still right in calling it using a fetch? if so, how would i use the operator -> eg whats after the ->

Dormilich
02-22-2012, 09:40 PM
originally the "column", resp. the expression, you place after the select. but fear not my friend, you can use an Alias in SQL.


SELECT something AS something_else FROM ...

PS. or you use numeral fetching

Dan13071992
02-22-2012, 09:43 PM
ok, so i tried this:



$querytestnew = mysql_query("SELECT TIMESTAMPDIFF(DAY, lastseen, NOW()) AS date FROM users WHERE username='DOS1392'");
$fetchnewnew = mysql_fetch_object($querytestnew);
echo "$date";


and the result i got was:


2012-02-22 08:53:19

Dormilich
02-22-2012, 09:48 PM
what is the relation between $fetchnewnew = mysql_fetch_object($querytestnew); and echo "$date";? is there a relation at all?

Dan13071992
02-22-2012, 09:50 PM
i believed it was this part:

TIMESTAMPDIFF(DAY, lastseen, NOW()) AS date

was i wrong?



sorry i realised it should have been:

$fetchnewnew->date



so with this should i use if statements, eg:

if ($fetchnewnew->date == "0") {
$days = "Today";
}elseif ($fetchnewnew->date == "1") {
$days = "Yesterday";
}else{
$days = "$fetchnewnew->date Days Ago";
}

Dormilich
02-22-2012, 09:54 PM
and if we could get around to dropping the outdated mysql extension for the more modern MySQLi (http://php.net/mysqli) or PDO (http://php.net/pdo) extensions, that would be bliss (and even safe against SQL Injections).

Dan13071992
02-22-2012, 09:56 PM
i edited my last post, just a quick question, what does you mean by this:



and if we could get around of dropping the outdated mysql extension for the more modern MySQLi or PDO extensions, that would be bliss (and even safe against SQL Injections).


as this is just to display some results, it wont be used for anything else, and i dont understand the PDO extensions as i dont know what they are.

Dormilich
02-22-2012, 09:59 PM
directly quoted from the Manual:

Note:

If you are using MySQL versions 4.1.3 or later it is strongly recommended that you use the mysqli extension instead.

(note: current version of MySQL is something around 5.5)

that means, only MySQLi and PDO (and not mysql) are able to make Prepared Satements, which protect against SQL Injections.

mysqli and PDO are database extensions just like mysql—only better

Dan13071992
02-22-2012, 10:01 PM
o right, this is going to sound really stupid if im wrong, but i was always led to believe that, mysql could not be used alongside mysqli and what does PDO stand for

Dormilich
02-22-2012, 10:04 PM
mysql is outdated, so there is no need to use it alongside mysqli. PDO stands for PHP Data Objects and is a data-access abstraction layer (i.e. it is able to use other databases (like PostgreSQL or SQLite) as well). you’ll find more in the above link.

Dan13071992
02-22-2012, 10:06 PM
so in reality, i should stop using mysql, and move onto learning PDO and mysqli?

Dormilich
02-22-2012, 10:13 PM
not necessarily both, but one. both have their pros and cons so you would need to weight your decision.

MySQLi:
+ can get the most out of MySQL
– no Iterators, no Exceptions
– error handling must be done manually
– handling of Prepared Statements is somewhat clunky (IMO)

PDO:
+ uses Iterators and Exceptions
+ errors can be handled by PDO
– used as a "common denominator" for many databases
– does not support the full MySQL functionality



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum