CODEWITHSUNDEEP
phpmysql
Keith Rattray
Keith Rattray

Reputation: 61

mysql delete from row only if another field is empty or null

I have a php / mysql event lister and I am coming unstuck where I only want to auto delete a record if it's startdate (field_1) has passed and there is no future enddate set (field_5), so field_5 would need to be empty before it's deleted. Both start and end date fields are set to null on mysql dbase. I can get the code to delete a lapsed event using my code below but I have no idea how to apply mysql logic / conditional code required to check if the enddate (field_5) is set or empty, thereby preventing a deletion if that field is set with a string date. Can anyone advise please ?

$eventstart ="STR_TO_DATE(field_1, '%m/%d/%Y')";
$eventend ="STR_TO_DATE(field_5, '%m/%d/%Y')";
mysql_query("DELETE FROM events3 WHERE $eventstart < $date ") or  die(mysql_error());

Upvotes: 0

Views: 819

Answers (2)

alexander.polomodov
alexander.polomodov

Reputation: 5534

Try to add checking if field_5 IS NULL or equal empty string with this code:

$eventstart ="STR_TO_DATE(field_1, '%m/%d/%Y')";
$eventend ="STR_TO_DATE(field_5, '%m/%d/%Y')";
mysql_query("DELETE FROM events3 
    WHERE $eventstart < $date AND 
    (field_5 IS NULL OR field_5 ='')"
) or  die(mysql_error());

If we need to delete rows with a lapsed set of start and end dates (so both field_1 & field_5 were past current date and warranted deletion) we can use this code:

$eventstart ="STR_TO_DATE(field_1, '%m/%d/%Y')";
$eventend ="STR_TO_DATE(field_5, '%m/%d/%Y')";
mysql_query("DELETE FROM events3 
    WHERE $eventstart < $date AND 
    (field_5 IS NULL OR field_5 ='' OR $eventend < $date)"
) or  die(mysql_error());

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1271171

I think the logic would be coded as:

DELETE e FROM events3 e
    WHERE field_1 <= NOW() AND field_5 is null;

I don't see any need for variables in this query.

If you have mistakenly stored the date values as strings, then you would need str_to_date():

DELETE e FROM events3 e
    WHERE STR_TO_DATE(field_1, '%m/%d/%Y') <= NOW() AND field_5 is null;

Storing dates as strings is a bad practice.

Upvotes: 3

Related Questions