如何检查mysql上是否有新数据

Question

I need to write a application that checks database from external server every 10 seconds to see if there is new data. Currently I have a javascript that checks if data has changed server by comparing two JSON (the old JSON and the new fetched from server) and if it has alerts user. But that is not what I need in this application. User should be alerted only when data is new, not when it has changed.

I was thinking that maybe I could do this with a PHP code that queries MYSQL and if query num_results is 0 loop until num_results is more than 0 when user gets notified. In this application it doesn't matter whether the new data is available for user in 0,1 second or 10 seconds, just as long as user gets it. This is how I tried to do the MYSQL check, but it isn't working:

<?php
include 'config.php';

if(isset($_GET['ID'])) {
    $maxLoop = 20;    
    while($maxLoop--) {
        $dbh = new PDO("mysql:host=$dbhost;dbname=$dbname", $dbuser, $dbpass); 
        $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        try {
            $sth = $dbh->prepare('select * from testit where id = :id');
            $sth->bindParam(':id',$_GET['ID'],PDO::PARAM_INT);
            $sth->execute();
            if($sth->rowCount()>0) {
                $results = $sth->fetchAll(PDO::FETCH_OBJ);
                echo '{"key":'. json_encode($results) .'}';
                exit; // Found new data, end loop and script
            }
        } catch(PDOException $e) {
            break;   
        }
        sleep(3);    
    } // end while
} // end if

So how can I alter this code to make it work, or should I just try to write some javascript that would do this? And if so, how can I check whether data is new or not, instead of just checking whether it has changed or not?

Answer 1

How do you record 'new' data? is there a timestamp? an auto_increment primary key field?

The easiest method for polling is to simply keep track of the last known id/timestamp and see if there's anything with a higher id/newer timestamp.

As is, your method is quite inefficient. You select ALL records in the table, forcing mysql/pdo to start fetching that data from disk, etc... then simply throw it away after getting the row count. A more efficient method is do to a select count(*) ... and checking that value. This keeps mysql from having to start fetching actual row data from disk. And on some table types (myisam in particular), a count(*) operation is VERY quick.