How do I automatically Delete Rows from MySQL Database on specific date?

Question

I know there has to be a way to automatically delete a row in a MySQL table on a specified date. I am just brain dead today : /

Let's just say I have CMS that I input posts and want one of the fields in the table to be "delete_date" and if I put a date in that field it would delete that row on the date specified. If I do not enter a date it would not auto delete.

What would be the best way to do this using PHP/MySQL. Tips or examples would help.

dev.mysql.com/doc/refman/5.1/en/events-overview.html

hakre
– hakre

2012-10-21 12:29:00 +00:00
Commented Oct 21, 2012 at 12:29 — hakre
– hakre, Commented Oct 21, 2012 at 12:29

tylerl · Accepted Answer · 2011-02-18 19:10:52Z

2

Run a scheduled task (cron job) which deletes all the rows where the delete_date field is greater than or equal to the current date.

answered Feb 18, 2011 at 19:10

tylerl

31k14 gold badges85 silver badges115 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

horatio · Accepted Answer · 2011-02-18 19:14:30Z

0

A basic query:

DELETE * FROM cms_table WHERE delete_date='$the_date_to_delete'

( see: http://dev.mysql.com/doc/refman/5.0/en/delete.html )

answered Feb 18, 2011 at 19:14

horatio

1,4368 silver badges7 bronze badges

1 Comment

Funk Forty Niner Over a year ago

There's a syntax error here. The asterisk as posted DELETE * is invalid. The manual does show the usage of one, but that isn't it. The manual shows .* in this example pulled from it tbl_name[.*] [, tbl_name[.*]] ... - The answer should be edited in order to reflect that change.

IAbstract · Accepted Answer · 2011-02-18 19:18:03Z

0

I don't know PHP, but this is really a language agnostic question unless you don't know how to execute SQL in the language.

You can run a scheduled task, as @tylerl suggests. Easy to do in a .Net language, not sure about PHP. You also have the option of running a service if you want old records to be continually deleted throughout the day. Again, easy enough in .Net.

If, however, you have an application (PHP, .Net, etc) that you want to physically check for records to delete:

delete * from myTable where recordDateField > 'cutOffDate' // I think you pass parameters with a `$` - not sure

answered Feb 18, 2011 at 19:18

IAbstract

19.9k17 gold badges104 silver badges153 bronze badges

2 Comments

Funk Forty Niner Over a year ago

They're using MySQL here. The asterisk as posted delete * from is invalid. The manual does show the usage of one, but that isn't it. The manual shows .* in this example pulled from it tbl_name[.*] [, tbl_name[.*]] ... - The answer needs to be fixed.

IAbstract Over a year ago

@FunkFortyNiner : if you know better, have updated information, etc. go ahead and edit the answer. It may be valid (correct) from more than 7 years ago.

Collectives™ on Stack Overflow

How do I automatically Delete Rows from MySQL Database on specific date?

3 Answers 3

Comments

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related