Welcome to WebmasterWorld Guest from 54.226.189.112

Forum Moderators: coopster & jatar k

Message Too Old, No Replies

PHP - Time - MySql

Displaying how long ago, rather then what time

     

nfs2

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

10+ Year Member



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

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

10+ Year Member



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

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

10+ Year Member



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

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

10+ Year Member



None that I know of unfortunately.

inveni0

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

5+ Year Member



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

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

10+ Year Member



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

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

5+ Year Member



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

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

10+ Year Member



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

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

10+ Year Member



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

nfs2

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

10+ Year Member



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

SeanW

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

10+ Year Member



@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

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

10+ Year Member



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

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

10+ Year Member



"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

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

10+ Year Member



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

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

10+ Year Member



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

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

WebmasterWorld Administrator jatar_k is a WebmasterWorld Top Contributor of All Time 10+ Year Member



what is going wrong?

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

nfs2

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

10+ Year Member



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

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

5+ Year Member



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

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

10+ Year Member



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

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

10+ Year Member



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.

 

Featured Threads

Hot Threads This Week

Hot Threads This Month