58

I would like to purge my SQL database from all entires older than 1 week, and I'd like to do it nightly. So, I'm going to set up a cron job. How do I query mySQL without having to enter my password manually every time?

The query in PHP is as follows:

mysql_query("DELETE FROM tbl_message WHERE DATEDIFF( NOW( ) ,  timestamp ) >=7");

Is there a way to run this as a shell script? If not, is there a way of making cron run a php file?

Paolo
  • 2,161
  • 5
  • 25
  • 32

4 Answers4

163

I personally find it easier use MySQL event scheduler than cron.

Enable it with

SET GLOBAL event_scheduler = ON;

and create an event like this:

CREATE EVENT name_of_event
ON SCHEDULE EVERY 1 DAY
STARTS '2014-01-18 00:00:00'
DO
DELETE FROM tbl_message WHERE DATEDIFF( NOW( ) ,  timestamp ) >=7;

and that's it.

Read more about the syntax here and here is more general information about it.

Musk
  • 1,477
  • 1
  • 15
  • 25
fancyPants
  • 50,732
  • 33
  • 89
  • 96
61

Try creating a shell script like the one below:

#!/bin/bash

mysql --user=[username] --password=[password] --database=[db name] --execute="DELETE FROM tbl_message WHERE DATEDIFF( NOW( ) ,  timestamp ) >=7"

You can then add this to the cron

Andy Day
  • 925
  • 9
  • 7
  • 2
    This answer is just as good as mine. It just depends on whether you want to do it through php or not. – miyasudokoro Jan 17 '14 at 22:04
  • 5
    It also depends on whether you want to spread your database credentials around in more places than necessary. The `event_scheduler` option is an improvement in this regard since it's both contained within the database and its installation can be entirely version-controlled as a migration script. – kungphu Mar 24 '16 at 01:57
  • so you are exposing your mysql password to other windows users? – tatskie Feb 21 '20 at 05:13
  • 2
    This is a bad answer. You should use the below answer to use the MySQL event scheduler. – Andy Day Jul 28 '20 at 01:53
  • I like fancyPants's answer. – Chris F Jul 29 '20 at 16:00
  • Hello, don't work for me, some one can help me please? – Mau España Sep 25 '22 at 22:21
9

It depends on what runs cron on your system, but all you have to do to run a php script from cron is to do call the location of the php installation followed by the script location. An example with crontab running every hour:

# crontab -e
00 * * * * /usr/local/bin/php /home/path/script.php

On my system, I don't even have to put the path to the php installation:

00 * * * * php /home/path/script.php

On another note, you should not be using mysql extension because it is deprecated, unless you are using an older installation of php. Read here for a comparison.

miyasudokoro
  • 1,705
  • 1
  • 15
  • 23
2

This was a very handy page as I have a requirement to DELETE records from a mySQL table where the expiry date is < Today.

I am on a shared host and CRON did not like the suggestion AndrewKDay. it also said (and I agree) that exposing the password in this way could be insecure.

I then tried turning Events ON in phpMyAdmin but again being on a shared host this was a no no. Sorry fancyPants.

So I turned to embedding the SQL script in a PHP file. I used the example [here][1]

[1]: https://www.w3schools.com/php/php_mysql_create_table.asp stored it in a sub folder somewhere safe and added an empty index.php for good measure. I was then able to test that this PHP file (and my SQL script) was working from the browser URL line.

All good so far. On to CRON. Following the above example almost worked. I ended up calling PHP before the path for my *.php file. Otherwise CRON didn't know what to do with the file.

my cron is set to run once per day and looks like this, modified for security.

00 * * * * php mywebsiteurl.com/wp-content/themes/ForteChildTheme/php/DeleteExpiredAssessment.php

For the final testing with CRON I initially set it to run each minute and had email alerts turned on. This quickly confirmed that it was running as planned and I changed it back to once per day.

Hope this helps.

John Anderson
  • 35
  • 8