[SOLVED] Working out when a user joined

[graham23s]

Hi Guys,

 

on my site when a user joins it's stored in mysql as:

 

2007-05-14 19:11:43

 

i was wondering how i would go about diusplaying next to it the time in weeks i.e:

 

2007-05-14 19:11:43 (4 weeks ago)

 

any help would be appreciated

 

cheers

 

Graham

[Wildbug]

Eh, do you only want weeks?  Or would you want things like "1 hour ago" or "three days ago," too?

 

If you just want weeks, you can use some of MySQL's functions to render that.

 

mysql> SELECT CONCAT( FORMAT( DATEDIFF( NOW(),"2007-05-14 19:11:43" )/7 ,0)," weeks ago");
+-----------------------------------------------------------------------------+
| CONCAT( FORMAT( DATEDIFF( NOW(),"2007-05-14 19:11:43" )/7 ,0)," weeks ago") |
+-----------------------------------------------------------------------------+
| 3 weeks ago                                                                 |
+-----------------------------------------------------------------------------+
1 row in set (0.00 sec)


mysql> SELECT CONCAT( FORMAT( DATEDIFF( NOW(),NOW() + INTERVAL 2 DAY)/7 ,0)," weeks ago");
+-----------------------------------------------------------------------------+
| CONCAT( FORMAT( DATEDIFF( NOW(),NOW() + INTERVAL 2 DAY)/7 ,0)," weeks ago") |
+-----------------------------------------------------------------------------+
| 0 weeks ago                                                                 |
+-----------------------------------------------------------------------------+
1 row in set (0.01 sec)

 

If you want days, weeks, months, etc, I'd recommend writing a function in PHP and doing the calculations there.

[graham23s]

Hi Mate,

 

i have this at the top:

 

     // Get the users details from mysql...//////////////////////////////////////////////
     $query_1 = "SELECT * FROM `membership` WHERE `id`='$id'";
     $result_1 = mysql_query($query_1) or die (mysql_error());
     $row = mysql_fetch_array($result_1);
     
     $users_id = $row['id'];
     $username = $row['username'];
     $join_date = $row['join_date'];

 

would i need to do another query (using the method you posted above) using the "$join_date" variable?

 

i was just originally wanting weeks to start with then maybe in future go from there lol

 

thanks Wildbug

 

Graham

[trq]

$query_1 = "SELECT id, username,  FORMAT( DATEDIFF( NOW(),join_date)/7 ,0) AS joined FROM `membership` WHERE `id`='$id'";

[graham23s]

Thanks guys.

 

Graham