将当前日期/时间与数据库中的日期/时间进行比较

Question

I need to create something to display an image based on the current date and time. I have an SQL table set up that looks like this:

eventid | startdate  | enddate    | imgfile    | status
6       | 2013-04-29 | 2013-05-03 | finals.jpg | active

What I need to do is compare the start date and end date to the current date, and if the current date falls within the startdate and enddate, then display the imgfile.

I'm not sure how to go about this, but this is what I tried.

//get current date and time
$currentdatetime = strtotime(now);
$formatteddatetime = date("y-m-d", $currentdatetime);

while($row=mysql_fetch_array($getimage))
{
    //get variable for startdate
    $formattedstartdate = date("y-m-d", $row[startdate]);
    $formattedenddate = date("y-m-d", $row[enddate]);
    if($formatteddatetime >= $formattedstartdate AND $formatteddatetime <= $formattedenddate AND $row[status] == 'active')
    {
        echo $row[imgfile];
    }
}
?>

Right now, it doesn't show anything inside of the entire while statement. Am I at least on the right track?

Answer 1

You should do it in your SQL query instead of PHP. For example:

SELECT * FROM table WHERE CURRENT_DATE BETWEEN startdate AND enddate;

CURRENT_DATE() is a MySQL function that returns the current date, as the name suggests. It is one of a few special functions that have an alias without the parentheses.