Real time loop issue

I am having problem archiving real time loop. While it going to next iteration, it should check the status first for an update (status= 0) before going to next iteration.

status field might be updated somewhere else...

For example:

<?php
$SQL = "SELECT * FROM data WHERE status = 0";
$query = $db->prepare($SQL);
$query->execute();

while ($row = $query->fetch(PDO::FETCH_ASSOC)) {
    print_r($row);
    print("\n\n");
    sleep(5);
}
?>

While php script is running/lo开发者_StackOverflow社区oping, in mysql console, I quickly did:

UPDATE data SET status = 1

It is still showing all the records even I have updated the status. How can that problem be fixed?

The result set is only computed once. If you want to see intermediate updates, you need to issue another SELECT statement.

When you enter the while loop you've long gone fron the DB, you're simply displaying now what was sent back.

You must requery the database each time you want to get freash data. Otherwise, you will only re-iterate over the old fetched data.

<?php
$SQL = "SELECT * FROM data WHERE status = 0";

while (true) { // set the condition you need here (eg: time)

    // I don't know PDO enough to know if prepare can be executed out of the loop
    $query = $db->prepare($SQL);

    // re-query the database to get the new statuses
    $query->execute();

    // process the data
    while ($row = $query->fetch(PDO::FETCH_ASSOC)) {
        print_r($row);
        print("\n\n");
    }

    // sleep when all the row have been processed
    sleep(5);
}
?>