How can I refresh a page when a database is updated?

How can I detect the latest updates made to a database and silently refresh a page when a change occurs?

Let's say the database access looks like:

$host = "localhost";
$username = "root";
$password = "root";
$db = mysql_connect($host,$username,$password) or die(mysql_error());
mysql_select_db('ccr') or die(mysql_error());

Any ideas and samples would be appreciated. Thank you.

This is how I recently implemented a solution using jQuery.

PHP increments a field in the database every time a significant update occurs.

<?php

//  Call this function when data changes
function update_clients()
{
    mysql_query( "UPDATE pageGen SET id = id + 1 LIMIT 1" );
}

//  Call this function to get the ID to pass to JavaScript
function get_update()
{
    $result = mysql_query( "SELECT id FROM pageGen LIMIT 1" );
    $update = mysql_result( $result, 0, 'id' );
    return $update;
}

?>

When the page is initially loaded, populate a JavaScript variable with a number from the database:

<script type="text/javascript">
var pageGenID = 25218603  //  generated by PHP
var processUpdate = function( response ) 
{
    if ( pageGenID < response ) 
    {
        replace_current_data_with_new_via_ajax();
        pageGenID = response;
    }
}
//  Compare our Page Generate ID against that of the server
var checkUpdates = function()
{
    serverPoll = setInterval( function()
    {
        $.get('script_to_return_latest_pageGenID.php', 
          { lastupdate: 1 }, 
          processUpdate, 'html');
    }, 10000 )
};

//  Check for updates every 10 seconds
$( document ).ready( checkUpdates );

</script>

Here is what I did and used the answer you marked but I think there is a few things to change there. In the main page ( that you want to refresh if something changed in the database). I created a table in my database called setting, in this table i created a row that called rowCounter. the rowCounter is being updated when the numbers of rows in the table you check has been changed. so my code split in two blocks. one that giving me the number from the setting > rowCounter and the second is the script that give me the current number in the table. if they are not equal then I refresh the page.

so this is the first file you need
script_to_return_latest_pageGenID.php

// here you will make you connection query.

$result = mysql_query( "SELECT rowCounter FROM setting WHERE `id`='2'" );
$update = mysql_fetch_assoc($result);
        echo implode($update);

    $count=mysql_query("SELECT `id` FROM log_".$datestamp."")or die(mysql_error);
    $number =  mysql_num_rows($count);
//echo $number;

    $countFromSetting=mysql_query("SELECT `rowCounter` FROM setting  WHERE id='2'")or die(mysql_error);
    $numberFromSetting=implode(mysql_fetch_assoc($countFromSetting));

    if($number != $numberFromSetting)
        {
            mysql_query("UPDATE setting SET `rowCounter`='$number' WHERE `id`='2'")or die(mysql_error);

        }

In the main page you will write the two blocks of code I provided and you should put it before the script.

$countFromSetting=mysql_query("SELECT `rowCounter` FROM setting  WHERE id='2'")or die(mysql_error);  
         $numberFromSetting=implode(mysql_fetch_assoc($countFromSetting));

After this code you put the script that will query every few seconds the php script, check if the numbers are not equal it will refresh the page.

var pageGenID = "<?php echo $numberFromSetting; ?>"; 
var processUpdate = function( response ) {


var x=response;
//console.log(pageGenID); by removing the remarks you will see the compared numbers all the time.
//console.log(x);
if ( pageGenID != x ) 
{
    //replace_current_data_with_new_via_ajax();
    pageGenID = response;

    window.location.reload();       
   }
}

 var checkUpdates = function()
{
    serverPoll = setInterval( function()
  {

$.get('script_to_return_latest_pageGenID.php', 
      { lastupdate: 1 }, 
      processUpdate, 'html');
  }, 5000 ) };
    $( document ).ready( checkUpdates );

I believe you'd have to poll the database when you use PHP, PHP doesn't exist in a long running process as in the case of a Java container where the Java web application could create a persistent connection (in theory) you'll likely need to employ some sort of polling mechanism by which I mean setup a timer in Javascript or whatever your client code is to periodically make the request, in terms of increasing efficiency for this if need be you could create a flag on the users table and set the flag to indicate that the database has been invalidated since the user last polled, then your first check would be if invalidation has happened since that user last updated rather than sending everything to everyone all the time. Alternatively I think you'd need to abandon PHP for this task.

Ideea:

When you render a page with php , you can pass a js variable witch has keeps a "current database revision" ( a number ) . On the page you make an ajax call once every 30 seconds a witch recives a new current database revision so to speak . You test for those 2 and if the one you got from the ajax call is higher then it means you need to reload the page .

On the server side you need a table to store the current revision , everytime you make a query from php you set the current revision +1 ( in the revision database table ) . When you receive an ajax call from a client you read that number from the database and you pass it on to the cilent . You can setup a cronjob that will reset the counter every day .

Strictly speaking, this is not possible. MySQL does not fire triggers back to PHP if something is changed. It's just not possible. Also, MySQL operates using sessions, so even if it could report a change in the database, you'd have to use a persistent connection in order for you to be able to access that sort of trigger.

Now, if you want to execute select statements to detect a change in the database, that's different. Depending on how "new" you want your code to be (depending on how compatible you'd like it to be) you can use any one of the following to "poll" a PHP page to check for changes: Comet, AJAX, a forever frame, or HTML5's new WebSocket class.

While other say use the client to detect a change through variables like a database version stored in javascript, I'd advise that you leave that server sided simply because people who know how to inject javascript will be able to change that value, which may produce undesired or even malicious results (even, depending on how that value is used, MySQL injections).

How can I refresh a page when a database is updated?

Related

Recent Posts