How do I execute a query once a day and output results to flat file using cron?

Question

Hello all.

I have a PHP/MySQL web site where I want to output the # of entries in a DB/table on the main page, but I don't want to make this query every time a user hits the home page. I want it to automatically run once a day and output the query results to a flat file which I can then just include. I figure that should reduce load some.

I've never done a cron job before, nor am I super familiar with the Unix system/commands. My site is with an ISP who has a Plesk control panel and I see a "crontab" section which lets me set up cron jobs.

I'm not quite sure what "command" to enter. I can formulate the query just fine, but not sure how to output the results to a flat file that I can include via PHP. Also, ideally, the flat file would appear in the web root directory (with the rest of the site) and overwrites itself every day, I don't want to end up with 365 flat files at the end of the year.

Answer 1

Well first off its called "cron" and its pretty easy to implement.. check out a little info about it: http://en.wikipedia.org/wiki/Cron and a getting started guide: http://www.unixgeeks.org/security/newbie/unix/cron-1.html.

What volume of traffic do you expect to see on this site? I don't see why firing off a query would be a big problem in most cases...

• Nicholas

Answer 2

This could be a little tricky to get right without further knowledge on your hosting system. If there is a mysql command line client available you could try something like this:

/usr/bin/mysql -u db_user --password=db_password -e "SELECT COUNT(*) INTO OUTFILE '/tmp/myoutfile.txt' FROM my_table;" my_database

Note that you will have to replace things like "db_user", "db_password", etc. in this call. Also note that /usr/bin just happens to be the directory that the client is located in on my system. It might differ on your hosting platform.

If the web front-end lets you execute shell commands directly, I would first try the command that way as this should give you better feedback if something is wrong or missing.

If you are feeling more comfortable coding PHP, another solution would be to write a small PHP script that does the job of creating/updating the file and have that script called in your crontab:

/path/to/php/bin/php /path/to/my/phpscript.php

Of course, this depends on the command line version of PHP being available on your system.

Answer 3

For the crontab part of you question, you could simply add something like this in your crontab (editing it with the command "crontab -e"):

0 0 * * * COMMAND TO EXECTUTE QUERY > THE_FILE

Which simply execute the query every day and pipes its result in THE_FILE, overwrithing its content.

The format of a crontab entry is:

minutes hours day_of_month month day_of_week COMMAND

Answer 4

You should write a standalone PHP script to do all the work, and just add it to your crontab like this:

0 2 * * * cd /path/to/script; /usr/bin/php daily.php 1> output.txt 2> errors.log

At every 2AM your daily.php script will run, the output is in output.txt and any errors generated (leave display_errors turned on) will end up in errors.log. This has a few advantages over putting the queyr straight into cron:

• You don't need to put your mysql connection details into cron
• You can include any of your site's PHP include files if you need them
• You can test the script standalone without waiting for cron to run it.

Probably the easiest and safest way to include the data in your PHP pages is to serialize() an array of results from the queries and then unserialize() it when you need it:

// daily.php
$data = array(
    'rows_in_table_foo' => 2343, // replace this with a query result
    'rows_in_table_bar' => 4321, // replace this with a query result
    );
echo serialize($data);
exit 0;

... and to read it ...

// index.php
$data = unserialize(file_get_contents('output.txt'));

Answer 5

lets summarize your question. You want to cache query results, how do you do it?

You're proposing a solution (store in a flat file) before you answer your question. So sure, you can do that. But if you are going to create a little bit of infrastructure, why not write a cache data class and cache all kinds of stuff. It can have 3 properties

-- cache item name -- cache item value -- cache item last update timestamp

add a couple functions

function loadCachedData($itemName);

//see if more time has passed than in the cache expiration window 
function isLoadedDataFresh($expirationWindowInSeconds);

function getLoadedItemValue();

function storeCacheValueForItem($itemName, $value);

You already have to write the query to select your item, so in your controller, just do this:

$cacheHandle = new Cache();

$cacheHandler->loadCachedData('whateverMyVarIs');

$displayValue = '';
if($cacheHandler->isLoadedDataFresh(60 * 60 * 24) ) //sec*min*hour = 1 day
{
    $displayValue = $cacheHandler->getLoadedItemValue();
}
else
{
    //update the display value here with your real query
    $displayValue = doMyStuff();
    $cacheHandler->storeCacheValueForItem('whateverMyVarIs', $displayValue);

}

of course, you will have to clean the hell out of that code, turn your expiry time windows into constants, and implement the class, but that shouldn't be tooo hard. Plus, you already have a db.. just back the cache class with that, and cache away!!! Or if you want to use an even better tool for fast caching go with memcached!! http://www.danga.com/memcached/

But finally, I just have to ask... are you prematurely optimizing? Has someone really complained about the performance on this page?