创建CRON作业以获取/发送数据库值

Question

Looking to do something that I think is fairly basic, but having never written a CRON job before, I'm not really sure how to go about it. Basically, I have a simple DB query that I've written:

SELECT SUM(total) as totalDownloads FROM wp_podpress_statcounts

As you'd expect, this displays a number. What I'd like to do, though, is create a CRON that automatically runs this query every day and sends me the results. I'm keeping track of day-to-day downloads of a podcast, and the podPress plugin I'm using leaves a lot to be desired in the metrics department. Ideally, I'd like to build my own stats system; however, my PHP isn't quite up to snuff.

Thanks in advance!

Answer 1

No need for PHP if you use a few built-in UNIX tools:

To execute from commandline, using the MySQL commandline:

mysql -e 'SELECT SUM(total) as totalDownloads FROM wp_podpress_statcounts';

Either put a -u -p for the username and password, or put a ~/.my.cnf in your homedir.

Mail it to yourself using UNIX mail(1):

mysql -e 'SELECT SUM(total) as totalDownloads FROM wp_podpress_statcounts' | mail [e-mail]

Now to crontab for each day.

Do a crontab -e and enter these two lines:

# at midnight, every day
0 0 * * *        mysql -e 'SELECT SUM(total) as totalDownloads FROM wp_podpress_statcounts' | mail [e-mail]