CODEWITHSUNDEEP
phpmysqlsql
Matt
Matt

Reputation: 163

Using the MySQL DELETE clause

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

Upvotes: 0

Views: 81

Answers (2)

John Woo
John Woo

Reputation: 263713

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.

Upvotes: 6

John Conde
John Conde

Reputation: 219804

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';

Upvotes: 2

Related Questions