PHP/MySQL timeout?

[Tombie]

wasnt sure what to name the title but i am creating an events booking system and want the seat to become unreserved aka delete the record in the databse if they do not complete payment within a certain amount of time, tried to find some tutorials somewhere, any pointers or opinions on how it could be done?

[Muddy_Funster]

As requested here is the code I came up with for you.  As usual it's not tested so you may want to change the DELETE FROM to a SELECT and echo"" for testing (and backup your DB of course).  I have chosen to get the the current time from the DB server, since you don't always get the web server being the DB server as well, and there could be clock sync issues.

 

here goes:

<?php
SESSION_START()
      include 'connectionFile.php';       //change as appropriate
$now = mysql_fetch_array(mysql_query("SELECT CURRENT_TIMESTAMP()"))  //get time from database
or die('UNABLE TO RETRIEVE CURRENT TIME FROM DATABASE -- '.mysql_error());  //show error on fail
$curTime = ereg_replace('[^0-9]+', '',$now['0']);     //get current time as number
$qryA = "SELECT idField, startTime FROM tableName";      //select files from database table for comparison
$resA = mysql_query($qryA) or die ('UNABLE TO RETRIEVE DATASET -- '.mysql_error());  //run query and show error on fail
  WHILE ($rowA = mysql_fetch_assoc($resA)){              //open loop to proccess each record in table
    $bTime = ereg_replace('[^0-9]+', '',$rowA['startTime']);    //get time from table as numbers
     if (($curTime - $bTime) > 1800){                               //set time cut off for deletion in seconds
       $qryD = "DELETE FROM tablename WHERE idField = ".$rowA['idfield'];         //delete outdated entrys
       mysql_query($qryD) or die ('ERROR DELETING EXPIRED RECORDS -- '.mysql_error())  // execute deletion and display error on failure
     } //close if
  } //close while
?>

[Tombie]

As requested here is the code I came up with for you.  As usual it's not tested so you may want to change the DELETE FROM to a SELECT and echo"" for testing (and backup your DB of course).  I have chosen to get the the current time from the DB server, since you don't always get the web server being the DB server as well, and there could be clock sync issues.

 

here goes:

<?php
SESSION_START()
      include 'connectionFile.php';       //change as appropriate
$now = mysql_fetch_array(mysql_query("SELECT CURRENT_TIMESTAMP()"))  //get time from database
or die('UNABLE TO RETRIEVE CURRENT TIME FROM DATABASE -- '.mysql_error());  //show error on fail
$curTime = ereg_replace('[^0-9]+', '',$now['0']);     //get current time as number
$qryA = "SELECT idField, startTime FROM tableName";      //select files from database table for comparison
$resA = mysql_query($qryA) or die ('UNABLE TO RETRIEVE DATASET -- '.mysql_error());  //run query and show error on fail
  WHILE ($rowA = mysql_fetch_assoc($resA)){              //open loop to proccess each record in table
    $bTime = ereg_replace('[^0-9]+', '',$rowA['startTime']);    //get time from table as numbers
     if (($curTime - $bTime) > 1800){                               //set time cut off for deletion in seconds
       $qryD = "DELETE FROM tablename WHERE idField = ".$rowA['idfield'];         //delete outdated entrys
       mysql_query($qryD) or die ('ERROR DELETING EXPIRED RECORDS -- '.mysql_error())  // execute deletion and display error on failure
     } //close if
  } //close while
?>

 

hey cheers working nicely and now pretty much complete thanks

[Muddy_Funster]

I just looked at that again and realised : time is not decimal.  The comparison will need some manipulation or posting at say 11:59 will show a time out after only 1 minute as rather than going to 11:60 it goes to 12:00.  There will be a better arithmetic function to use, but as for the SQL you should be set.

[Muddy_Funster]

I have included a crude fix for the affore mentioned problem:

<?php
  SESSION_START()
  include 'connectionFile.php';       //change as appropriate
  $now = mysql_fetch_array(mysql_query("SELECT CURRENT_TIMESTAMP()"))  //get time from database
  or die('UNABLE TO RETRIEVE CURRENT TIME FROM DATABASE -- '.mysql_error());  //show error on fail
  $curTime = ereg_replace('[^0-9]+', '',$now['0']);     //get current time as number
  $qryA = "SELECT idField, startTime FROM tableName";      //select files from database table for comparison
  $resA = mysql_query($qryA) or die ('UNABLE TO RETRIEVE DATASET -- '.mysql_error());  //run query and show error on fail
  WHILE ($rowA = mysql_fetch_assoc($resA)){              //open loop to proccess each record in table
  $bTime = ereg_replace('[^0-9]+', '',$rowA['startTime']);    //get time from table as numbers
  /*
    Time Correction
  */
$sam = substr($bTime, -4, 2);  //select minutes from table time
$sah = substr($bTime, -5, 1);  //select hours from table time
$sbh = substr($curTime, -5, 1); //select hours from current time
if ( $sam > 29 && ($sbh ==  $sah + 1)) //check for problem in conversion
{$bTime = $bTime + 4000; } //fix problem of going over the hour
$checkTime =  (($curTime - $bTime) * 0.6)  //make difference check output more accurate
  /*
    End of Time Correction
  */
  if ($checkTime > 1800){    //set time cut off for deletion in seconds -- changed from original due to above correction
  $qryD = "DELETE FROM tablename WHERE idField = ".$rowA['idfield'];         //delete outdated entrys
  mysql_query($qryD) or die ('ERROR DELETING EXPIRED RECORDS -- '.mysql_error())  // execute deletion and display error on failure
  } //close if
  } //close while
?>