Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 9 of 9
  1. #1
    Regular Coder
    Join Date
    Jan 2010
    Location
    Washington
    Posts
    223
    Thanks
    34
    Thanked 0 Times in 0 Posts

    Question mysql time display

    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 Code:
    <?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++;
                }
    Last edited by cosmicsea; 11-08-2010 at 11:21 PM.

  • #2
    Senior Coder
    Join Date
    Jun 2008
    Location
    New Jersey
    Posts
    2,536
    Thanks
    45
    Thanked 259 Times in 256 Posts

  • Users who have thanked Keleth for this post:

    cosmicsea (11-08-2010)

  • #3
    Regular Coder
    Join Date
    Jan 2010
    Location
    Washington
    Posts
    223
    Thanks
    34
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Keleth View Post
    Thanks, That just might help to resolve my problem. I will work on it for a bit and see if it does.

  • #4
    Regular Coder
    Join Date
    Jan 2010
    Location
    Washington
    Posts
    223
    Thanks
    34
    Thanked 0 Times in 0 Posts
    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.

  • #5
    Senior Coder
    Join Date
    Jun 2008
    Location
    New Jersey
    Posts
    2,536
    Thanks
    45
    Thanked 259 Times in 256 Posts
    Well, we'd need to see what you're trying to do to help.

  • #6
    Regular Coder
    Join Date
    Jan 2010
    Location
    Washington
    Posts
    223
    Thanks
    34
    Thanked 0 Times in 0 Posts
    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.

  • #7
    Senior Coder
    Join Date
    Jun 2008
    Location
    New Jersey
    Posts
    2,536
    Thanks
    45
    Thanked 259 Times in 256 Posts
    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?

  • #8
    Regular Coder
    Join Date
    Jan 2010
    Location
    Washington
    Posts
    223
    Thanks
    34
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Keleth View Post
    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.

  • #9
    Senior Coder
    Join Date
    Jun 2008
    Location
    New Jersey
    Posts
    2,536
    Thanks
    45
    Thanked 259 Times in 256 Posts
    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.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •