Time functions with mysql & php

[gerkintrigg]

hi!
I'm trying to work out how to format time using PHP and mysql.

I have a timestamp field in a database and I want to use PHP to display how many days are left before it needs to be removed. The limit is 30 days. So I'd like the php to display "X no of days remaining" where X no is the actual number.

I've tried using strtotime() and adding 30 days to the timestamp. I have NO IDEA how to start this.

Could anyone offer some suggestions please?

[onlyican]

for this, it is best to use mktime() and time()
http://uk2.php.net/function.mktime
http://uk2.php.net/manual/en/function.time.php

[gerkintrigg]

well: $no_of_days=(time($dp['expire'])-(time()))+30; seems to work for the moment, but we'll see what happens tomorrow...

Will this work?

[hitman6003]

Just use mysql:

[code]SELECT id, DATEDIFF(columnname, NOW()) AS time_in_db FROM tablename[/code]

Then loop through the results:

[code]
$result = mysql_query("SELECT id, DATEDIFF(columnname, NOW()) AS time_in_db FROM tablename");

while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
  if ($row['time_in_db']  > 30) {
    $ids_to_remove[] = $row['id'];
  }
}

$query = "DELETE FROM tablename WHERE id IN('" . implode("', '", $ids_to_remove) . "')";
mysql_query($query) or die(mysql_error());
[/code]

You may have to modify the DATEDIFF a little depending on if it's an actual timestamp column or not.  If not, just use DATE_FORMAT and other mysql functions.

http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html