...

View Full Version : mysql time display



cosmicsea
11-08-2010, 11:17 PM
Hi, For my tag cloud I show my last 5 searches and how many results were shown for that search and the query time. My question is how can I get my query time to show "1 second ago, 1 minute 32 seconds ago, or 1 hour ago etc"? Instead of being displayed like this.
2010-11-08 17:10:56

I know this is possible. I am just not sure if I do it with mysql or php or both.
Can anyone help with this?
Here is my code that I am working on.



<?php

$class = "evrow";
echo "
<table width='60%' align='center'>

<td class='tblhead'>Query</td>
<td class='tblhead'>Results</td>
<td class='tblhead'>Queried at:</td>
</tr>
";
$num = '1';
$result=mysql_query("select query, date_format(time, '%Y-%m-%d %H:%i:%s'), results from ".$mysql_table_prefix."query_log order by time desc");
if ($debug > '0') echo mysql_error();
while (($row=mysql_fetch_row($result)) && ($num <= '5')) {
if ($class =="evrow")
$word = $row[0];
$time = $row[1];
$results = $row[2];
echo "<tr class='$class'>
<td><a href='../search.php?query=$word&amp;search=1' target='rel' title='View search results in new window'>".($word)."</a></td>
<td> ".$results."</td>
<td> ".$time."</td>
</tr>
";
$num++;
}

Keleth
11-08-2010, 11:39 PM
This JUST came up today :p

http://codingforums.com/showthread.php?t=208797

cosmicsea
11-08-2010, 11:48 PM
This JUST came up today :p

http://codingforums.com/showthread.php?t=208797

Thanks, That just might help to resolve my problem. I will work on it for a bit and see if it does.

cosmicsea
11-09-2010, 12:15 AM
I cant seem to get that code to work with mine. I am trying to edit my existing code to work with it and cannot get it to work. I will keep trying but it doesn't seem like it will work right. If anyone wants to help me try to integrate this code I would appreciate it.

Keleth
11-09-2010, 12:37 AM
Well, we'd need to see what you're trying to do to help.

cosmicsea
11-09-2010, 12:54 AM
I am trying to make the original code I posted above show the last 5 searched keyword dates show as like "1 second ago, 5 seconds ago, 1 minute 25 seconds ago, and so on". All I know how to do with the date is display dates from the database.

Keleth
11-09-2010, 01:03 AM
If your `time` column is as a unix timestamp, plugging it directly into the code in the previously linked thread should work perfectly, and if it is in a datetime format, extract it as is, and then feed it through strtotime then through the function previously created.

What what about that code didn't work?

cosmicsea
11-09-2010, 01:09 AM
If your `time` column is as a unix timestamp, plugging it directly into the code in the previously linked thread should work perfectly, and if it is in a datetime format, extract it as is, and then feed it through strtotime then through the function previously created.

What what about that code didn't work?

Ok I will try that. My `time` column is a unix timestamp. I will try again. I am just having trouble because I am still learning php. I figured there would be a simple query change to achieve this but after going through the list of them I didn't see what I was looking for. I will retry to do this and post back soon.

Keleth
11-09-2010, 01:20 AM
As far as I know (and was mentioned in that thread), there is no native function for it. Now note, I didn't test the code provided there, just gave it a cursory look, so it might not work, but it looks good.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum