24小时后更改值 [英] change a value after 24 hours

问题描述

24小时后如何更改mysql数据库中的值? 用户是否需要访问网站才能运行查询? 例如，我有一个名为"user-rank"的列，其值为"10"，我希望它在24小时后为"20" ...

How can I change a value in mysql database after 24 hours? Does a user need to visit the website for the query to run? For example I have a column named "user-rank" and it's value is "10" ,I want it to be "20" after 24 hours ...

推荐答案

Crontab aka CRON是最灵活的解决方案，但是您需要在shell或特定控制面板中使用它的特权，因为许多托管服务提供商不愿意将特权共享给出于安全原因.

Crontab aka CRON is the most flexible solution but you need to have privileges using it in shell or specific control panel as many hosting providers do not like to share privileges to it due security reason.

首先创建PHP脚本和更新代码，如下所示:

First create PHP script whit update code, something like this:

<?php

//connect to database
//.... 

//prepare MySQL query
$sql = "UPDATE myTable SET `user-rank` = `user-rank` + 10";

//execute query and handle errors
//...

//optinal reporting on e-mail
//...

//end
?>

接下来，您需要通过在外壳程序中调用"crontab -e"来向crontab添加条目，像这样粘贴您的代码并保存更改:

Next you need to add entry to crontab by calling "crontab -e" in shell, paste your code like this and save changes:

0    0     *     *     *     /usr/bin/php -f /path/to/my_update_script.php > /dev/null

该条目将定义每天午夜(服务器时间)对脚本的调用，而忽略脚本的任何输出.

This entry will define call to script every day at midnight (server time) and ignore any output from script.

注意:您需要为php二进制文件指定确切的路径，该路径对于不同的OS或用户定义的配置文件可能会有所不同.使用"哪个php " shell命令进行检查.

NOTE: You need to specify exact path for php binary which can be different for various OS or user defined profile. Check it with "which php" shell command.

---------------更新---------------

--------------- UPDATE ---------------

不使用crontab的解决方案.

创建一个新的数据库表 php_cron 并使用以下MySQL代码插入初始记录(仅冷杉，并且只需要一个):

Create one new database table php_cron and insert initial record (firs and only one required) with following MySQL code:

CREATE TABLE `php_cron` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `last_ts` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
);

INSERT INTO `php_cron` (`id`, `last_ts`) VALUES (1,'2012-08-10 00:00:00');

然后创建新的PHP脚本，该脚本将保存该任务的所有必需逻辑，并将其包含在您的主index.php文件或每次用户访问您的网站时都可以加载该文件的其他位置，例如，在某些配置或类似内容中脚本.我想您每天至少会有一个访客.

Then create new PHP script which will hold all required logic for such task and include it in your master index.php file or other places where it can be loaded every time when user visit your site, for example in some config or similar script. I suppose that you will have at least one visitors per day.

这个新脚本需要完成两个任务，首先查询那些php_cron表并计算时间差，如果时间差大于24h(86400秒)，然后执行代码以更新用户等级并将新的时间戳保存到数据库中.

This new script need to do two task, first to query those php_cron table and calculate time difference and if difference is larger than 24h (86400 seconds) then execute code for updating user-rank and save new timestamp into database.

该脚本可能如下所示:

<?php


    //connect to database
    //.... 

    //get time difference in seconds from last execution
    $sql1 = "SELECT TIME_TO_SEC(TIMEDIFF(NOW(), last_ts)) AS tdif FROM php_cron WHERE id=1";
    $res1 = mysql_query($sql1) or die("[1] MySQL ERROR: ".mysql_error());
    $dif = mysql_fetch_assoc($res1['tdif']);


    if ($dif >= 86400) { //24h

        //following code will run once every 24h

        //update user's page rank
        $sql2 = "UPDATE myTable SET `user-rank` = `user-rank` + 10"; 
        mysql_query($sql2) or die("[2] MySQL ERROR: ".mysql_error());

        //update last execution time
        $sql3 = "UPDATE php_cron SET last_ts = NOW() WHERE id=1";
        mysql_query($sql3) or die("[3] MySQL ERROR: ".mysql_error());

    }

?>

此脚本不像CRON那样时间精确，但是可以确定每天运行一次.可以使用更多的php/mysql逻辑来纠正此问题，但我认为它可以完成工作.

This script is not time accurate as CRON but for sure will be run once per day. That can be corrected with some more php/mysql logic but I thing it will do the job.

这篇关于24小时后更改值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！