SitePoint Sponsor

User Tag List

Results 1 to 11 of 11

Hybrid View

  1. #1
    SitePoint Guru deepM's Avatar
    Join Date
    Dec 2007
    Location
    India
    Posts
    705
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    2 min ago and timestamps??

    Hello,

    I have column in database named as recente_visited, and it has type as timestamps. basically i am checking the user recent most time for a perticular page on my website.

    so my recent visited time is stored something like this

    visitor _id recent_visited
    4 2009-05-13 10:37:55

    now i want to show on page that this visitor visited 2 min ago, 30 min ago something like that. and i have to show 5 visitors on that page with there time. how can i achive that?

    i have logic in mind

    $visit = NOW() -recent_visited

    but then again how can i apply it in my selection query?

    Code:
    "SELECT  a.username,a.avatar,r.visitor_id,r.visiting_count,r.recent_visited FROM recent_visitor as r ,author as a WHERE (r.visitor_id = a.id) AND r.profile_owner = '$uid' ORDER BY r.recent_visited DESC  LIMIT $start, $limit_value"
    can anyone help me?

    thanks in advance.

  2. #2
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,578
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Select the timestamp as is, then format it in PHP:

    PHP Code:
    $seconds time() - strtotime($row['recent_visited']);
    $minutes round($seconds 60);
    echo 
    $minutes " ago"

  3. #3
    SitePoint Guru deepM's Avatar
    Join Date
    Dec 2007
    Location
    India
    Posts
    705
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    thannks for your reply Dan_Grossman,

    Getting output something like this:-

    -330 ago

    Could you please tell me is this logic ll consider the same thing in case of

    1 hour 20 min ago or 2 days ago

    one more question, where to put this $minutes in my while loop?

    PHP Code:
    <?
    $sqlpage    
    $op->runsql("SELECT  a.username,a.avatar,r.visitor_id,r.visiting_count,r.recent_visited FROM recent_visitor as r ,author as a WHERE (r.visitor_id = a.id) AND r.profile_owner = '$uid' ORDER BY r.recent_visited DESC  LIMIT $start$limit_value");
     if(
    mysql_num_rows($sqlpage) > 0){
    ?>
              <p class="msgText">Total <strong><?=$total_records;?></strong><?if($total_records >1){
    ?> Recent Visitor's<?}else{?> Recent Visitor<?}}?></p>
    <?
                          
    if(mysql_num_rows($sqlpage) > 0){

           while(
    $row1 $op->select($sqlpage)){
           
    $imagearr explode(",",$row1['avatar']);
            
    $imagearr[0]= preg_replace("/\/avatar\//","/small/"$imagearr[0]);

         
    $seconds time() - strtotime($row1['recent_visited']);
         
    $minutes round($seconds 60);
         echo 
    $minutes " ago";

          
    ?>
          <a href='/profile/<?=$blog->spacereplace($row1['username']);?>/' title="<?=$row1['username']?>"><img src="<?=$retpath;?>/<?=$imagearr[0];?>" width="40" height="40" alt="<?=$row1['username']?>"/></a>(<?=$row1['visiting_count']?>
          visit)<? echo"\t";
             
    $i++; }
             }

    ?>

  4. #4
    SitePoint Guru deepM's Avatar
    Join Date
    Dec 2007
    Location
    India
    Posts
    705
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    PHP Code:
    function time_since($original) {
        
    // array of time period chunks
        
    $chunks = array(
            array(
    60 60 24 365 'year'),
            array(
    60 60 24 30 'month'),
            array(
    60 60 24 7'week'),
            array(
    60 60 24 'day'),
            array(
    60 60 'hour'),
            array(
    60 'minute'),
        );

        
    $today time(); /* Current unix time  */
        
    $since $today $original;

        if(
    $since 604800) {
            
    $print date("M jS"$original);

            if(
    $since 31536000) {
                    
    $print .= ", " date("Y"$original);
                }

            return 
    $print;

        }

        
    // $j saves performing the count function each time around the loop
        
    for ($i 0$j count($chunks); $i $j$i++) {

            
    $seconds $chunks[$i][0];
            
    $name $chunks[$i][1];

            
    // finding the biggest chunk (if the chunk fits, break)
            
    if (($count floor($since $seconds)) != 0) {
                
    // DEBUG print "<!-- It's $name -->\n";
                
    break;
            }
        }

        
    $print = ($count == 1) ? '1 '.$name "$count {$name}s";

        return 
    $print " ago";


    I got this function. but it showing me output like this

    Jan 1st, 197039 years ago

    could you please help me?

  5. #5
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,578
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    It's negative?

    Is the time zone on the web server and the database server the same? You shouldn't get a negative unless the time in the database was AFTER the current time.

  6. #6
    SitePoint Guru deepM's Avatar
    Join Date
    Dec 2007
    Location
    India
    Posts
    705
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Dan Grossman View Post
    It's negative?

    Is the time zone on the web server and the database server the same? You shouldn't get a negative unless the time in the database was AFTER the current time.
    record which i have inserted today showing this negative value like

    -330 ago

    and for the others which i had inserted yesterday are showing like this:

    850 ago sidharth(15 visit) 865 ago narendra(5 visit)

    any suggestion?

  7. #7
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,578
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    I suggest you compare the current time at the database to the current time in your PHP code so that you can answer my question

  8. #8
    SitePoint Guru deepM's Avatar
    Join Date
    Dec 2007
    Location
    India
    Posts
    705
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Is the time zone on the web server and the database server the same
    how to check that?

    2009-05-13 10:37:55

    but one thing i can tell you here, 10.37 am is an Indian time. so now how can i check my web server time? (confused?) because still i am working on locally.i haven't tried this on my server yet.

    so could you please tell me ?

  9. #9
    SitePoint Addict
    Join Date
    Oct 2006
    Posts
    210
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    PHP Code:
    echo date('r'); 

    For the database:

    Code:
    select NOW()

    mikem
    --------

  10. #10
    SitePoint Guru deepM's Avatar
    Join Date
    Dec 2007
    Location
    India
    Posts
    705
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks mikem,

    my server time is - Wed, 13 May 2009 06:50:31

    my database time is - 2009-05-13 12:20:16

    so now how can i change my server time with database time?

  11. #11
    SitePoint Guru deepM's Avatar
    Join Date
    Dec 2007
    Location
    India
    Posts
    705
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hello,

    i have solved my problem with the help of other forum. so just dumping my logic here, may it will be helpful for someone like me

    first changed my query
    Code MySQL:
    "SELECT  a.username,a.avatar,r.visitor_id,r.visiting_count,TIMESTAMPDIFF(MINUTE, r.recent_visited, NOW()) AS MinsSinceLastVisit FROM recent_visitor as r ,author as a WHERE (r.visitor_id = a.id) AND r.profile_owner = '$uid' ORDER BY r.recent_visited DESC  LIMIT $start, $limit_value"

    and then my php code is

    Code PHP:
    <? $minutes_dif = $row1['MinsSinceLastVisit'];
     
    	   $Ymin = 60 * 24 * 365;
    	   $Mmin = 60 * 24 * 30;
    	   $Wmin = 60 * 24 * 7;
    	   $Dmin = 60 * 24;
    	   $Hmin = 60;
     
           $Smin =60
    	   $Y = (int)($minutes_dif / $Ymin);
    	   $minutes_dif = $minutes_dif % $Ymin;
     
    	   $MON = (int)($minutes_dif / $Mmin);
    	   $minutes_dif = $minutes_dif % $Mmin;
     
    	   $W = (int)($minutes_dif / $Wmin);
    	   $minutes_dif = $minutes_dif % $Wmin;
     
    	   $D = (int)($minutes_dif / $Dmin);
    	   $minutes_dif = $minutes_dif % $Dmin;
     
    	   $H = (int)($minutes_dif / $Hmin);
    	   $minutes_dif = $minutes_dif % $Hmin;
     
    	   if($Y > 0 ){
    	   echo "$Y year(s) ";
     
    	   }else if($MON > 0){
    	   echo "$MON month(s) ";
    	   }
    	   else if($W > 0){
    	   echo "$W week(s) ";
    	   }
    	   else if($D > 0){
    	   echo "$D day(s) ";
    	   }
    	   else if($H > 0){
     
    	   echo "$H hour(s) ";
    	   }
    	   else if($minutes_dif > 0){
     
    	   echo "$minutes_dif minute(s) ";
    	   }
          echo "ago";  ?>



Bookmarks

Posting Permissions

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