Using the MySQL DELETE clause

Question

I have a delete clause that deletes rows from my database where the 'viewed' column datetime value is older than 24 hours. I have that code working great, except the default value for the viewed column is 0000-00-00 00:00:00. In this case, every row of data with the default value gets deleted as well. How can I change my query to keep this from happening?

My Code:

$delete_expired_notifications_query= "DELETE FROM notifications WHERE user_id= '".$u_id."' 
AND viewed < NOW() - INTERVAL 24 HOUR";

Thanks

Answer 1

just add another condition, use =!

DELETE 
FROM notifications 
WHERE user_id= '".$u_id."' 
      AND viewed < NOW() - INTERVAL 24 HOUR
      AND viewed != '0000-00-00 00:00:00'

As a sidenote, the query is vulnerable with SQL Injection if the value(s) came from the outside. Please take a look at the article below to learn how to prevent from it. By using PreparedStatements you can get rid of using single quotes around values.

• How to prevent SQL injection in PHP?

Answer 2

Explicitly rule it out:

$delete_expired_notifications_query= "DELETE FROM notifications WHERE user_id= '".$u_id."' 
AND viewed < NOW() - INTERVAL 24 HOUR" AND viewed != '0000-00-00 00:00:00';