Page 1 of 1
Execute only during DATETIME range?
Posted: Thu Dec 25, 2008 5:58 pm
by JAB Creations
Using PHP and MySQL's standard DATETIME format how would I approach executing...say a function...only if the current time is within the range of the start and end DATETIME?
Or if there is not a magical PHP function I'm not aware of for this I'm open to suggestions on how to initially approach this goal. Thoughts please?
Re: Execute only during DATETIME range?
Posted: Thu Dec 25, 2008 6:15 pm
by JAB Creations
Well this was an interesting way of achieving my goal...
Code: Select all
<?php
$date_0 = '2008-12-24 12:00:00';
$date_1 = '2009-03-24 12:00:00';
$date = date('Y-m-d H:i:s');
if ($date < $date_0) {echo '$date < $date_0';}
else if ($date > $date_0) {echo '$date > $date_0';}
echo '<br />';
if ($date < $date_1) {echo '$date < $date_1';}
else if ($date > $date_1) {echo '$date > $date_1';}
echo '<br />';
if ($date > $date_0 && $date < $date_1) {echo 'within range!';}
else if ($date < $date_0) {echo 'too early for poll!';}
else if ($date > $date_1) {echo 'poll has closed, too late!';}
?>
Re: Execute only during DATETIME range?
Posted: Thu Dec 25, 2008 6:38 pm
by JAB Creations
I thought for a bonus for archive readers I'd post how to select the highest or latest ID in a MySQL table for a row in which is within the DATETIME range. I also have a column named
public (for non-registered users) so this query can be used to get the latest public poll record from a MySQL table. It's interesting, it works, and I did this entirely on my own merits.
Code: Select all
SELECT MAX(id) FROM poll WHERE date_0 < NOW() AND date_1 > NOW() AND public='1'
Re: Execute only during DATETIME range?
Posted: Thu Dec 25, 2008 6:43 pm
by requinix
I assume these DATETIMES are coming from a table via a query? If all you care about is whether it's between two dates then
Code: Select all
SELECT date_0, date_1, NOW() BETWEEN date_0 AND date_1 AS exec FROM poll
Code: Select all
list($date_0, $date_1, $exec) = mysql_fetch_array($query);
if ($exec) {
// ?
}
If you want to know if it's too early or too late then it's pretty much the last few lines of the code you have now.
Another way of doing that "latest ID" thing would be
Code: Select all
SELECT id FROM poll WHERE NOW() BETWEEN date_0 AND date_1 AND public='1' ORDER BY id DESC LIMIT 1
Note that BETWEEN...AND is
inclusive, while the < and > you have now are
exclusive.
Re: Execute only during DATETIME range?
Posted: Thu Dec 25, 2008 6:59 pm
by JAB Creations
Pulling just the ID and no other column though would be pointless in most situations so here is a little revision. By using LIMIT in conjunction with
ORDER BY id DESC we can effectively choose the latest row...unless of course editing of polls (or whatever the feature may be) is allowed. In which case I'm not sure how to combine MAX(id) with GROUP BY and getting
only the last row to return without using LIMIT. This should work in the majority of circumstances though I think.
Code: Select all
SELECT id, question FROM poll WHERE date_0 < NOW() AND date_1 > NOW() AND public='1' ORDER BY id DESC LIMIT 0, 1
*edit* @ tasairis Didn't see your post, will try it out right now and post again soon.

Re: Execute only during DATETIME range?
Posted: Thu Dec 25, 2008 7:08 pm
by JAB Creations
Thanks tasairis, this works beautifully!
Code: Select all
SELECT id, question FROM poll WHERE NOW() BETWEEN date_0 AND date_1 AND public='1' ORDER BY id DESC LIMIT 1
I think I sort of follow you on inclusive versus exclusive...BETWEEN being inclusive between the following AND statement and the operators being exclusive...not sure how to explain it but I think I have the idea in my head in a vague ineffable sense.