I have 2 fields, date_added which is the date the record was added using the now() function, the other num_days which is the number of days before it gets deleted, values can be 30 or 180 days.
To get the days left before deletion i've got -
$query = qq!
SELECT UNIX_TIMESTAMP(DATE_ADD(date_added,INTERVAL num_days +1 DAY)) FROM table WHERE id=$user
!;
$sth = $dbh->prepare($query) or &cgierr("Unable to query. Reason: $DBI::errstr.");
$sth->execute or &cgierr("Unable to query database. Reason: $DBI::errstr. Query: $query");;
while (($u_date) = $sth->fetchrow_array) {
$diff=int((($u_date - time())/86400));
print "$diff";}
$sth->finish;
$dbh->disconnect;

works fine, but can any one think of a way to do it all within the select statement. I've tried everything I can think of, maybe there isn't a way.

Bob
http://totallyfreeads.com

You could run:

SELECT TO_DAYS(NOW()) - TO_DAYS(num_days)
FROM table
WHERE id = $user AND

to get the number of days old it is.

Cheers,

Alex
--
Gossamer Threads Inc.

Alex thanks for the reply.
That won't work the way I currently have things setup as num_days is a numeric field.
What I'll to do is change the num_days field to a date field and add values using date_add(). This will give me a future deletion date, and then I can use to_days() to calculate the remaining days.
I hate working with dates, they are my pet hate.

thanks

Bob
http://totallyfreeads.com