homepage Welcome to WebmasterWorld Guest from 50.19.206.49
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member
Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
Forum Library, Charter, Moderators: coopster & jatar k

PHP Server Side Scripting Forum

    
PHP - Time - MySql
Displaying how long ago, rather then what time
nfs2




msg:1256560
 7:01 pm on Mar 10, 2006 (gmt 0)

Is there a way in php to say how long ago a mysql table was updated?

In the table has the time inserted into it when it is updated, but instead of displaying it as "Friday, March 10, 2006" or something, i'd like it to display "2 hours ago" or "3 days ago" etc..

Can php do that?

 

Grinler




msg:1256561
 9:16 pm on Mar 10, 2006 (gmt 0)

You can use the show table status to see when a table was last udpated:

[dev.mysql.com...]

You can compute it by comparing the current timestamp minus the amount of seconds for the time difference. For example 7200 is two hours. So if you get a number the current time - 2 hours, and the update_time is greater, then you are within the two hour window.


if($row['Update_time'] > (time() - 7200))
echo "Less than 2 hours ago.";

nfs2




msg:1256562
 7:19 am on Mar 11, 2006 (gmt 0)

if($row['Update_time'] > (time() - 7200)) {
echo "Less than 2 hours ago.";
}

Doesnt seem to work, but it seems limited anyways. Id like to reflect exactly how long ago the table was updated. If that code worked, id have to do an "if" for every minute/hour possible

Is there another way?

Grinler




msg:1256563
 9:09 am on Mar 11, 2006 (gmt 0)

None that I know of unfortunately.

inveni0




msg:1256564
 12:36 pm on Mar 11, 2006 (gmt 0)

If you split both time values into Days, Hours, Minutes, Seconds, you can compare each value. Just subtract each from its match and you have the difference for each item. String 'em together (using an 'if' statement to rid any zero differences) and you win!

nfs2




msg:1256565
 1:04 pm on Mar 11, 2006 (gmt 0)

Well if i could do that i woulndt need to split it up! The problem is comparing and subtracting to get the time difference.

For example, someone makes a blog post, and that goes into the mysql table with the time of the post.

So id need to find the difference between that time and the current time. I dont see how splitting it up makes it any easier, when i cant find the difference of the whole time to begin with

inveni0




msg:1256566
 1:14 pm on Mar 11, 2006 (gmt 0)

I'll make it easier:

Format your time like, WeekDay:Month:Hour:Minute:Second:Year

Make sure each value is NUMERICAL. Name this $OldTime

split(":", $OldTime);

Now you have an array. Do this same thing with $TimeNow and subtract each array entry from its pair....then piece the results back together, converting back to a non-numerical format....see?

SeanW




msg:1256567
 1:29 am on Mar 12, 2006 (gmt 0)

Since you have the timestamp in the table, you can pull out the latest entry (ie with the table foo, column lastupdated)

SELECT UNIX_TIMESTAMP(NOW()) - UNIX_TIMESTAMP(MAX(lastupdated)) FROM foo;

...
$last = fetch...

if ($last > 24*3600) {
$string = floor($last/(24*3600)) . " days ago";
} else if ($last > 3600) {
$string = floor($last/3600) . " hours ago";
) else...

Make sense?

nfs2




msg:1256568
 6:17 am on Mar 12, 2006 (gmt 0)

The date and time is put into the database with date("F j, Y, g:i a");

nfs2




msg:1256569
 6:19 am on Mar 12, 2006 (gmt 0)

...but yea i think i get that, i'll change my database to update with a unix timestamp.

SeanW




msg:1256570
 3:03 pm on Mar 12, 2006 (gmt 0)

@nfs2: Make sure to index that timestamp column, mysql should then be able to get the MAX() value from the index rather than requiring a table scan.

Sean

nfs2




msg:1256571
 10:54 pm on Apr 2, 2006 (gmt 0)

On second thought i have no idea how to use that query.

I changed my time column to update with a timestamp, but i didnt see any mention of unix

[edited by: nfs2 at 11:07 pm (utc) on April 2, 2006]

nfs2




msg:1256572
 10:56 pm on Apr 2, 2006 (gmt 0)

"Since you have the timestamp in the table, you can pull out the latest entry"

...and since i need this for the 5 most recent entries, will it still work, or is it just for the latest entry?

Also, how do i work it into my existing query, since i need to use it there anyways?

argh this just seems impossible.. Rational thought would dictate that something so basic as to say "this so-and-so was updates x long ago" would be easy.. This has to be the most infuriatingly fustrating experiance i've ever had with php.. I just can't do it :(

</whining>

nfs2




msg:1256573
 11:11 pm on Apr 2, 2006 (gmt 0)

Here's the query im using to get the last 5 updated blogs

$result7=mysql_query("SELECT DISTINCT blog_id FROM blog_entries ORDER BY entry_id DESC LIMIT 5");

Where would i fit you query into that?

nfs2




msg:1256574
 11:39 pm on Apr 2, 2006 (gmt 0)

Heres my whole code that im working on now

$result7=mysql_query("SELECT DISTINCT blog_id FROM blog_entries ORDER BY entry_id DESC LIMIT 5");
$journal = array();
while ($i = mysql_fetch_array($result7)) {

$last = mysql_query("SELECT UNIX_TIMESTAMP(NOW()) - UNIX_TIMESTAMP(MAX(entry_time)) FROM blog_entries") or die(mysql_error());

if ($last > 24*3600) {
$string = floor($last/(24*3600)) . " days ago";
} else if ($last > 3600) {
$string = floor($last/3600) . " hours ago";
}
$journal[] = '<td style="padding: 10px; border: 1px solid #0056a1; vertical-align: top; background: url(\'bluegrad.gif\') repeat-x;"><center><b><a href="http://www.example.com/profiles/'.$i['blog_id'].'">'.$i['blog_id'].'</a></b><br/><br/><a href="http://www.example.com/users/'.$i['blog_id'].'">'.$mainpic.'</a><div style="color: red; font-size: 9px;">'.$string.'</div></center></td><td width="5px"></td>';
}
echo $journal[0];
echo $journal[1];
echo $journal[2];
echo $journal[3];
echo $journal[4];

jatar_k




msg:1256575
 4:52 pm on Apr 3, 2006 (gmt 0)

what is going wrong?

is it repeating the same last updated time for each entry?

nfs2




msg:1256576
 5:58 pm on Apr 3, 2006 (gmt 0)

Well yes, but at also displays nothing right now. What i mean is with the following code

if ($last > 24*3600) {
$string = floor($last/(24*3600)) . " days ago";
} else if ($last > 3600) {
$string = floor($last/3600) . " hours ago";
}

It will display nothing for any of the last 5 entries, even though most of them were made many hours ago. But if i add an "else" at the end to test it like this

if ($last > 24*3600) {
$string = floor($last/(24*3600)) . " days ago";
} else if ($last > 3600) {
$string = floor($last/3600) . " hours ago";
} else {
$sring = "none of the above is true";
}

Or something, it will say that for each entry.. The other problem is i dont know how to get it down to minutes and seconds.

barns101




msg:1256577
 7:34 pm on Apr 3, 2006 (gmt 0)

If I understand what you are trying to do, one way would be like this:

1) Subtract the timestamp (time of post) from the current time to give you the number of seconds that have elapsed since the post was made. Call this $elapsed_time

2) Divide $elapsed_time by the number of seconds in a month / week / day and if the floor() result is more than 1, display a message:


// Lets say that the post is 2h 5m old
$elapsed_time = 7500;
// There are 604800 seconds in a week
$1_week = 604800;

$weeks = floor($elapsed_time / $1_week);

if($weeks ==1)
{
echo 'One week';
}
elseif ($weeks > 1)
{
echo "$weeks weeks";
}
// If $week = 0 then nothing is printed.

Then do the same for days and minutes.

I hope that is what you were trying to achieve and I have not completely misinterpreted ;)

nfs2




msg:1256578
 7:56 pm on Apr 3, 2006 (gmt 0)

Thank you! I now have the following working code

$now = time();
$then = $i['entry_time'];
$elapsed_time = $now - $then;

if ($elapsed_time > 604799) {

$one_week = '604800';
$weeks = floor($elapsed_time / $one_week);
if($weeks == 1)
{
$time = 'One week';
}
elseif ($weeks > 1)
{
$time = "$weeks weeks";
}

} else if ($elapsed_time > 86399){

$one_day = '86400';
$days = floor($elapsed_time / $one_day);
if($days == 1)
{
$time = 'One day';
}
elseif ($days > 1)
{
$time = "$days days";
}

} else if ($elapsed_time > 3599) {

$one_hour = '3600';
$hours = floor($elapsed_time / $one_hour);
if($hours == 1)
{
$time = 'One hour';
}
elseif ($hours > 1)
{
$time = "$hours hours";
}

} else if ($elapsed_time > 60){

$one_minute = '60';
$minutes = floor($elapsed_time / $one_minute);
if($minutes == 1)
{
$time = 'One minute';
}
elseif ($minutes > 1)
{
$time = "$minutes minutes";
}

} else {

$one_second = '1';
$seconds = floor($elapsed_time / $one_second);
if($seconds == 1)
{
$time = 'Right now';
}
elseif ($seconds > 1)
{
$time = "$seconds seconds";
}

Thanks again to everyone who helped me with this

slade7




msg:1256579
 8:57 pm on Apr 3, 2006 (gmt 0)

or, once you get the elapsed time, you could do this:

function spitback_timestring($elapsed_time)
{
$num_weeks = floor($elapsed_time/604800);
$time_left = $elapsed_time - ($num_weeks*604800);

$num_days = floor($time_left/86400);
$time_left = $time_left - ($num_days*86400);

$num_hours = floor($time_left/3600);
$time_left = $time_left - ($num_hours*3600);

$num_minutes = floor($time_left/60);
$time_left = $time_left - ($num_minutes*60);

$num_seconds = $time_left;

// and then to print...

echo "$num_weeks weeks<br>
$num_days days<br>
$num_hours hours<br>
$num_minutes minutes<br>
$num_seconds seconds<br>";

}// end function

You could use the ifs to print or not print - depending - I suppose. I tried this briefly and it seems to work. I used something similar a while back.

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved