Create CRON job to get/send database value

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!

have you tried anything? did you have any specific issues while trying to implement it? — marcelog, Aug 16 '11 at 13:27

score 2 · Answer 1 · answered Aug 16 '11 at 13:31

2

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 your.addy@host.com

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 your.addy@host.com

answered Aug 16 '11 at 13:31

Konerak

39,272
12
98
118

Wow, good stuff, thanks! Only thing is that I'm not exactly sure where to put this. My server is running cPanel, which gives me a CRON tab and asks me for a script to run. It lets me pick a schedule, so I don't think I need the last portion of your code; the system seems like it'll take care of that for me. Also, if my localhost and DB name are different, how do I configure that? Based on the link you sent I'm assuming it's something like: mysql --user=user_name --password=my_password db_name How do I change host? – ndisdabest Aug 16 '11 at 13:38
On a shared host, it is not likely that you can create a cron job in this fashion. This answer is more applicable to those who have direct access to the host server. – Chris Baker Aug 16 '11 at 18:27
On the link, you'll also find the --host parameter. Note that this is indeed applicable if you have direct access to the host server. If you don't, maybe some of the tools (mysql/mail/crontab) won't be accessible to you, and you'll have to workaround. Luckily a PHP script can access MySQL, can send mails, and you can have it called using your cPanel CRON. – Konerak Aug 16 '11 at 18:47

score 0 · Answer 2 · answered Aug 16 '11 at 18:24

Just an FYI - there is one other option you could also look at - mysql events. Similar to what you would have in MS SQL Server for example. There are some limitations on what you can do so you would definitely want to check those out first.

Check out: http://dev.mysql.com/doc/refman/5.1/en/events.html for more info.

Personally, I often write a shell script and use cron as noted above.

Create CRON job to get/send database value

2 Answers2