[SOLVED] Run a mySQL query as a cron job?

Issue

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?

Solution

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

Answered By - Andy Day