selecting where a date value is > 20 minutes ago

[dkeats]

Hello folks,

BACKGROUND TO PROBLEM
I am in the process of moving to PHP from ASP, and I will be bringing a team of open source developers with me. In order to learn PHP, I decided to develop an application to manage (physical) conferences based on one we did in ASP (http://idlelo.uwc.ac.za). Now I am up against something that was easy in ASP/M$SQL, but seems to be very difficult in PHP.

THE PROBLEM TO SOLVE
I have a table, tbl_loggedinusers that enables users to communicate with one another and maintain state. Any activity on the site updates the table with the time that any given page was loaded. I need to purge the table regularly by checking if the timelastactive in any records is more than 20 minutes ago, and then deleting the record. Note: I use PEAR DB for database abstraction, mySQL as the database.

Conceptually, what I want to do is:

$sql="DELETE FROM tbl_loggedinusers WHERE DATEDIFF(whenlastactive, '".$myDate."')<1200";

THE QUESTION
Is there a way to achieve this easily? I have searched on this and other boards, documentation, etc and all 11 books that I bought and I am totally stuck. It just seems so simple.

Thanks for your time and patience,
Derek

[bastien]

try this:

From the mysql manual (date and time functions)

ADDTIME(expr,expr2)
SUBTIME(expr,expr2)
expr is a date or datetime expression, and expr2 is a time expression. ADDTIME() adds expr2 to expr and returns the result. SUBTIME() subtracts expr2 from expr and returns the result.
mysql> SELECT ADDTIME("1997-12-31 23:59:59.999999", "1 1:1:1.000002");
-> '1998-01-02 01:01:01.000001'
mysql> SELECT SUBTIME("1997-12-31 23:59:59.999999", "1 1:1:1.000002");
-> '1997-12-30 22:58:58.999997'
mysql> SELECT ADDTIME("01:00:00.999999", "02:00:00.999998");
-> '03:00:01.999997'
mysql> SELECT SUBTIME("01:00:00.999999", "02:00:00.999998");
-> '-00:59:59.999999'

$sql="delete * from table where timesub('".date()."','0:20:0')>daetfield";

[dkeats]

Thanks for the suggestion, but pear db returns:

Warning: Illegal offset type in C:\PHP\pear\DB.php on line 434
unknown error
Query was: DELETE * FROM tbl_loggedinusers WHERE timesub('2003-11-03 21:11:33','0:20:0')>whenlastactive

Talk about your useful error messages! Oh well...i will leave this for professionals..I think I am too old for it, hehe...

Regards
Derek

[dkeats]

This is how I eventually got it to work, but its ugly. It should be possible to do it with only one SQL statement.

  

    /*Method to clear inactive users*/
    function clear_inactive_users() {
        require_once ("DB.php");    # PEAR database abstratction
        global $dbconstr;           # make the connection string available
        global $db;
        //Connect to database using PEAR DB and return recordset
        $db = DB::connect($dbconstr) or die("Could not connect to database server");
        global $system_timeout;
        $curtime = strtotime(date('Y-m-d H:m:s'));
        $sql="SELECT id, whenlastactive FROM tbl_loggedinusers";
        if ( DB::isError( $rs=$db->query( $sql ) ) ) {        #trap errors
            die(DB::errorMessage($rs).'<br>Query was: '.$sql);
        }
        while($line = $rs->fetchRow()) {
            $id=$line[0];
            $lastactive=strtotime($line[1]);
            if ($curtime-$lastactive > $system_timeout) {
                $sql="DELETE FROM tbl_loggedinusers WHERE id='".$id."'";
                $db->query( $sql );
            }
        }
        $db->disconnect();
    }

/

[Drakla]

delete from table where NOW() - INTERVAL 20 MINUTE > datefield;

Ta-daaaaa