php - Date Range Query MySQL

Question

I need a query to select data between two dates with today's date as a reference.

The database has a datetime field for "start" and a datetime field for "end".

$todays_date = date("Y-m-d H:i:s");

$q = "SELECT * FROM news WHERE `end` >= '" .  $todays_date . "' AND `start` >= '" .  $todays_date . "' ORDER BY id DESC";

The problem is the query is still pulling results where the start date is greater than today. So then i modified my query to look like this:

 $q = "SELECT * FROM news WHERE `end` >= '" .  $todays_date . "' AND `start` >= '" .  $todays_date . "' AND `start` <='" . $todays_date . "' ORDER BY id DESC";

Is this the correct way of selection data between two datetime fields that uses todays date as a limiter?

Thanks

Answer 1

You can use the now timedate function in MySQL and the BETWEEN operator.

Raw SQL:

SELECT * FROM news
WHERE NOW() BETWEEN start AND end;

Note: Be mindful of the default timezone, which affects the NOW() function, used by the server providing your MySQL resource.