如何编写程序以每隔x分钟自动修改现有的SQL数据库？

Question

I am a PHP/SQL novice user....Finishing off my first PHP website. The question is similar to the initial question found at:

http://forums.phpfreaks.com/topic/266235-modifying-database-after-a-set-time-limit/

but I did not completely understand the answer. Similar to that user, I have an entire column (called status) of a database (called challenge) that can take 3 values for status - 'inactive', 'pending', or 'active'. In the normal flow of website operations, User A will typically click a button (event 1) which creates a new row (with a unique *challenge_id*) in the database and triggers a status of 'pending' in that row. User B can change the status with other clicked buttons, which can set the status to 'inactive' or 'active'.

One undesirable scenario is where USER B does nothing (i.e., no event trigger). In this case, User A is unfortunately stuck, waiting for the status to change from 'pending' to either 'active' or 'inactive' before he/she can click and trigger the next event 1. This situation could occur for example if User B gets tired of the site and does not use it anymore, leaving 'pending' requests unanswered.

Clearly, I can manually alter the SQL, changing any 'pending' status to 'inactive' after a certain time limit. This would be fine at the beginning, but if the site ever became popular, this would take more time. Is there any way to write a non-PHP program to account for this 'no event trigger' scenario where all 'pending' status SQL entries are automatically altered after a certain time limit? Or can PHP do this? I tried writing a php script that would sweep the database every time any user logged in (note: *challenge_id* is created by an event triggered on a different PHP page):

<?php session_start();

if ((($_SESSION['role']) != SHA1('user')) && (($_SESSION['status']) != SHA1('active')))     
{
header( 'Location: index.php' ) ;
session_destroy();
} else 


include 'connect.php';

$_SESSION['login_id'];

$universaltime = time();

$sqlt = mysql_fetch_assoc(mysql_query("SELECT challenge.challengetime, 
challenge.status FROM challenge"); //Selects an array of all values for challengetime 
//and status for all users I presume


while ((($universaltime - $sqlt['challengetime']) > 1000) && 
($sqlt['status'] == 'pending'))   
{

$sqlt1 = mysqli_query("UPDATE challenge SET $sqlt['status'] ='inactive'"); 
//Also tried with if instead of while

}



?>

I'm sure my code can be improved...any help would be appreciated greatly! Or do I need to use something other than PHP?

Answer 1

First of all your UPDATE statement is wrong. Assuming that challengetime is of int data type holding unix time values your UPDATE statements should look something like this

UPDATE challenge 
   SET status = 'inactive'
 WHERE status = 'pending' 
   AND 1000 < UNIX_TIMESTAMP() - challengetime;

It can be and should be run on its own. You don't need to select anything prior to calling it. Therefore you can change this part

$universaltime = time();
$sqlt = mysql_fetch_assoc(mysql_query("SELECT challenge.challengetime, 
challenge.status FROM challenge"); //Selects an array of all values for challengetime 
//and status for all users I presume
while ((($universaltime - $sqlt['challengetime']) > 1000) && 
($sqlt['status'] == 'pending'))   
{
$sqlt1 = mysqli_query("UPDATE challenge SET $sqlt['status'] ='inactive'"); 
//Also tried with if instead of while
}

with just

$sql = "UPDATE challenge SET status = 'inactive' WHERE status = 'pending' AND 1000 < UNIX_TIMESTAMP() - challengetime";
$result = mysql_query($sql);
if (!$result) {
    die('Invalid query: ' . mysql_error()); //TODO better error handling
}

---

Now to make it execute periodically on it own you don't necessarily need php. You can:

First option Use MySQL event. To execute this statement every day at 11pm

CREATE EVENT change_status
ON SCHEDULE EVERY 1 DAY 
   STARTS CURDATE() + INTERVAL 23 HOUR 
DO 
  UPDATE challenge 
     SET status = 'inactive'
   WHERE status = 'pending' 
     AND 1000 < UNIX_TIMESTAMP() - challengetime;

Use SHOW PROCESSLIST to check if event scheduler is enabled. If it's ON you should see a process "Daemon" by user "event_scheduler". Use SET GLOBAL event_scheduler = ON;to enable the scheduler if it's currently not enabled. More on configuring event scheduler here

Second option Use crontab to invoke CLI mysql

/usr/local/mysql/bin/mysql -uuser -ppassword -e " UPDATE challenge SET status = 'inactive' WHERE status = 'pending' AND 1000 < UNIX_TIMESTAMP() - challengetime"