如何每天执行一次查询并使用cron将结果输出到平面文件？

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.

我有一个PHP / MySQL网站,我想在主页上输出DB /表中的条目数,但我不希望每次用户点击主页时都进行此查询。我希望它每天自动运行一次并将查询结果输出到一个平面文件,然后我可以将其包含在内。我认为应该减少一些负荷。

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.

我以前从未做过cron工作,也不熟悉Unix系统/命令。我的网站是一个拥有Plesk控制面板的ISP,我看到一个“crontab”部分,它允许我设置cron作业。

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.

我不太确定要进入什么“命令”。我可以很好地制定查询,但不知道如何将结果输出到我可以通过PHP包含的平面文件。此外,理想情况下,平面文件将出现在Web根目录(与站点的其余部分)并且每天都会覆盖自己,我不想在年底使用365个平面文件。

5 个解决方案

#1

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

您应该编写一个独立的PHP脚本来完成所有工作,然后将它添加到您的crontab中,如下所示:

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 query straight into cron:

每隔2点,您的daily.php脚本将运行,输出在output.txt中,并且生成的任何错误(保持display_errors打开)将最终出现在errors.log中。与将查询直接放入cron相比,这有一些优点:

You don't need to put your mysql connection details into cron

您不需要将您的mysql连接详细信息放入cron

You can include any of your site's PHP include files if you need them

如果需要,您可以包含任何站点的PHP包含文件

You can test the script standalone without waiting for cron to run it.

您可以独立测试脚本,而无需等待cron运行它。

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:

在PHP页面中包含数据的最简单和最安全的方法可能是从查询中序列化()一组结果,然后在需要时反序列化()它:

// 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'));

#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:

如果没有进一步了解您的托管系统,这可能有点棘手。如果有一个mysql命令行客户端可用,您可以尝试这样的事情:

/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.

请注意,您必须在此调用中替换“db_user”,“db_password”等内容。另请注意,/ usr / bin恰好是客户端在我的系统上的目录。它可能在您的托管平台上有所不同。

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.

如果Web前端允许您直接执行shell命令,我会首先尝试这种方式,因为如果出现错误或缺失,这应该会给您更好的反馈。

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:

如果您对编写PHP感觉更舒服,另一种解决方案是编写一个小PHP脚本来完成创建/更新文件的工作,并在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.

当然,这取决于系统上可用的PHP命令行版本。

#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"):

对于你问题的crontab部分,你可以在你的crontab中添加这样的东西(使用命令“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_FILE,并将其内容覆盖。

The format of a crontab entry is:

crontab条目的格式为:

minutes hours day_of_month month day_of_week COMMAND

#4

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.

首先,它被称为“cron”,它很容易实现..查看一些关于它的信息:http://en.wikipedia.org/wiki/Cron和入门指南:http://www.unixgeeks .ORG /安全/新手/ 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

#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

在回答问题之前,您提出了一个解决方案(存储在平面文件中)。当然,你可以做到这一点。但是,如果要创建一些基础结构,为什么不编写缓存数据类并缓存各种内容。它可以有3个属性

-- 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/

当然,你将不得不清除那些代码的地狱,将你的到期时间窗口变成常量,并实现类,但这不应该太难。另外,你已经有了一个数据库..只需用它来缓存缓存类,然后缓存掉!或者如果你想使用更好的工具进行快速缓存,请使用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?

但最后,我只是要问......你是否过早地进行了优化?有人真的抱怨过这个页面上的表现吗?

#1