[SOLVED] working with dates

[chinclub]

I am writing a new auction script for my website and I have everything working except closing the auctions.

I have the database set up to record the start time like this:

 

Field  Type          Default

time  datetime      0000-00-00 00:00:00

 

I want to set up a cron job that will close all auctions that are 10 days old.  My only problem is I don't know how to write the code the find the 10 day cutoff.  I know I need to somehow take today's day and subtract 10 days from it but I can't seem to find out how.

 

My plan is once I find that code I will then take that variable and compare it to the time in the database with >.

 

 

can anyone help?

 

[ataria]

$query = mysql_query("SELECT *  FROM `table` WHERE `id`>0");
// change ID to whatever you use as the index. the id above, ^

while($row=mysql_fetch_array($query)){

$timestamp = strtotime($row[time]);

$cutoff = time()-$timestamp;

$limit = 864000;

if($limit > $cutoff){
$query = mysql_query("UPDATE `table` SET `close`=1 WHERE `id`=$row[id]");
// Of course, the "`close`=1" part would be replaced by whatever you have that represents it being closed.
// Again, the ID would change to whatever your index is. 
}
else{
// nothing. 
}
}

 

*** Spaced out so it's easier on the eyes.

 

I believe that should work, not 100% sure. I'd prefer if someone revises it for me.

but, yeah, try it if you want.

[chinclub]

that didn't seem to work.  ???

[Psycho]

There's no need to "close" the auctions discretely. PHP/MySQL is a dynamic platform.

 

In your pages that access the auctions you can determine if the auction is open or closed based upon the current date/time and the date/time the auction was opened.

 

Or, an easier method IMHO, would be to set the opening and the closing date/time when the auction is created. Then you can determin if an auction is closed if it's closing date/time is before the current date/time. That way there are less computations to do.

[chrisuk]

I used the following code in an I.T. helpdesk system I developed to calculate the number of days since a call was logged:

 

<?php
//time difference 
$today = strtotime("now");
$logged_day = strtotime("$system_date"); //this is the date the call was logged
$timediff_sec = $today - $logged_day; //time difference in seconds
$timediff = $timediff_sec/86400; //86400 seconds in a day therefore dividing by this give no of days
$timediff = floor($timediff); //round down otherwise will appear one day older than actually is
?>

[dough boy]

  UPDATE table SET expired = 1 WHERE NOW() > time + INTERVAL 10 DAY;

 

As someone else mentioned though, you are better off setting another field in the database and call it end time so that you do not have to call a function every second to run a query to close all old items.  This is especially important as the more items you have the longer the query is going to take to run.