DateTime Comparison

[damo87]

Im my football tipping application, I would like for users to be able to update their tips during the weekend's round of play, even if some games have already been played (the usual scenario).

 

So when users submit their tips, I need my SQL statement to delete their tips only for the games that havnt already started. (I insert their new tips for those games in the next block of code, but that is not the issue here).

 

The problem I am having is getting the SQL to ignore the games that have started. I have tried this:

DELETE FROM selected WHERE selected.round=$round and user=$user and EXISTS (select distinct game.home_team from game where selected.team=game.home_team and game.round=$round and datetime >NOW())

 

This seems to work in all situations except where the datetime (which is an actual datetime field) and now() times have the same morning hour, eg:

 

now time: 2010-03-25 04:39:46

game time: 2010-03-25 04:37:00

 

The game started a few minutes ago, so it shouldnt delete it - but it does!

 

I have also tried:

$datetime = date('Y-m-d h:m:s');
$datetime = strtotime($datetime);
DELETE FROM selected WHERE selected.round=$round and user=$user and EXISTS (select distinct game.home_team from game where selected.team=game.home_team and game.round=$round and UNIX_TIMESTAMP(datetime) >= '$datetime')"

 

...but this has the same outcome. Any Ideas greatly appreciated.

 

Versions:

MySQL - 5.0.90

PHP Version 5.2.13

 

[damo87]

Got it working like this:

// check the server time
$mysqltime = date ("Y-m-d H:i:s");
$query = "DELETE FROM selected WHERE selected.round=$round and user=$user and EXISTS (select distinct game.home_team from game where selected.team=game.home_team and game.round=$round and datetime >'$mysqltime')";