MySQL and PHP datetime not keeping active when data in database

Question

I am trying to code an alert system where it will display alerts from a system, and this will keep the alert active until the alert date is expired. Currently the alert will only stay active on the date inputted.

<?
$query_rsGetAlerts = "SELECT * FROM alerts WHERE DAY(alert_expiary) <= DAY(CURDATE()) AND MONTH(alert_expiary) <= MONTH(CURDATE()) AND  YEAR(alert_expiary) <= YEAR(CURDATE())"; 
$rsGetAlerts = mysql_query($query_rsGetAlerts) or die(mysql_error());
$row_rsGetAlerts = mysql_fetch_assoc($rsGetAlerts);
$totalRows_rsGetAlerts = mysql_num_rows($rsGetAlerts);


do {
echo "<div class=\"alert alert-" . $row_rsGetAlerts['alert_type'] . "\"                            role=\"alert\">". $row_rsGetAlerts['alert_message']  ."</div>";
} while($row_rsGetAlerts = mysql_fetch_assoc($rsGetAlerts));
?>

Table Data:

id  alert_type  alert_message   alert_expiary   
1   success Arron's Birthday is on the 16th October 2015-10-16
2   danger  The next training day is on 14th October    2015-10-1

Table Structure:

1   id  int(222)    AUTO_INCREMENT  
2   alert_type  varchar(222)    latin1_swedish_ci   
3   alert_message   varchar(222)    latin1_swedish_ci       
4   alert_expiary   date

Is your query returning a result? Is there data in your table? What is your table structure? — foxbeefly, Sep 18 '15 at 12:19
The alert when entered displays for the current day of input, however after that it disappears. I have added my table structure to the question. — Arron, Sep 18 '15 at 12:21
If you can, you should [stop using `mysql_*` functions](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php). They are [officially deprecated](https://wiki.php.net/rfc/mysql_deprecation). [These extensions](http://php.net/manual/en/migration70.removed-exts-sapis.php) have been removed in PHP 7. Learn about [prepared](http://en.wikipedia.org/wiki/Prepared_statement) [statements](http://php.net/manual/en/pdo.prepared-statements.php) instead, and consider using PDO, [it's really not hard](http://jayblanchard.net/demystifying_php_pdo.html). — Jay Blanchard, Sep 18 '15 at 12:54

score 1 · Accepted Answer · answered Sep 18 '15 at 12:37

So: if the date today is 2015-09-18, and you want an alert to show until the alert date is reached, you need to select records where the alert date is greater than the current date and display those...

You should get away with this:

SELECT *
FROM `alerts` 
WHERE `alert_expiary` > DATE(NOW());

MySQL and PHP datetime not keeping active when data in database

1 Answers1