Auto refresh content after changes in database – AJAX

There is new and improved version of this idea! If you interested go to

Volume II of ajax refresh with working example

If you need basic idea of Auto refresh see post: “Refresh content automatically after some period time – jQuery“.

In one of my application modules in my engineering thesis i had to ensure that end users always have current list of messages. It was very important because messages can be edited, hidden, published again, and a user does not have time to remember to refresh the page. Of course, it’s not a big deal. I set the time after which page must refresh and everything is working as intended.
Because of a specific character of the user, that time cannot be shorter than half a minute. The page always refreshing every half minute with:

HTTP request-> database query  ->  result formation -> result sent to the browser

It’s insane. And with hiding the content of about 10-20 messages, just after the beautifully formed list appears, we reload page once again.

The solution maybe a little crudy, but for my needs it worked well enough.

Idea

Create a counter stored in database and HTML code. With each change in the list of messages (regardless of the action – edit, add, hide) i increase counter in the database. Then, instead of getting entire list or refreshing a page, every half minute in the background im checking counter and compare with the one hidden in code. Page is reloaded only when these values are different.

And everyone is happy. Of course query path will not change. However the query time and the result formation will be significantly reduced. And most important, user will not want to shoot me ;o)

Tools

PHP, HTML, prototype

Preparation of HTML

In <head></head> section we are attaching prototype framework.

Somewhere in <body> section – ideally at the beginning – we are going to need two containers, with an assigned id, invisible for users.
For example:

old_count – will keep the state of the counter after page is loaded.
new_count – will keep the state of the counter after next request.

It doesn’t matter if it will be <div>, <p>, or anything else. It is important that it has an id.

And of course we need container for our message list.

<div id="msg_list" style="display:none"></div>

Because messages are stored in the database, when creating a table i’m automatically adding the row with id=1 where i store my counter. With every modification of entries like: edit, delete, add new, archive, etc – the value in dedicated column will be increased by one.

Since i’m working with classes, i will not go into details of the database connection.

Below I will present only the most important fragments.

– Function to increase the counter

function register_changes(){
  $this->db ->query("UPDATE table_name SET counting = counting + 1 WHERE id=1");
}

– the status counter check

function check_changes(){
  $result=$this->db->query("SELECT counting FROM table_name WHERE id=1");
  $result=$this->db->fetch_array($result);
  return $result[‘counting’];
}

The next step is to create a separate file created by AJAX. I will call it checker.php.

It looks like that:

require_once ('class_file.php’);
$refresh = new class_name();
$new_number = $news->check_changes();
echo $new_number;

Preparation of JS

At the begining we are going to create a function, that will execute hidden demand. AJAX is calling the script checker.php on the server. If the counter is increased, the list of messages is refreshing.

Input parameters for this function is:

url – the address/name of the called script

id_st – ID of the container that holds the old counter status

id_nw – ID of the container that holds the new state of the counter

function check(url,id_st,id_nw){
  req = new Ajax.Request(url, {
    contentType: 'text/html; charset=utf-8',
    method: 'post',
    onSuccess: function(response) {
      //containter update "new_count" with the current value of the counter
      id_nw.update(response.responseText);
      //assign the current counter to variable new
      new = id_nw.innerHTML;
      //assign the status of the counter hidden in the HTML code
      older = id_st.innerHTML; 
      //if the values are diffrent, it will refresh the list of messages by Ajax.Updater. Using that function we will refresh only the list, and not entire page
      if(older!=new){
        //There was a change, rather then refreshing the entire page to the next comparison, we need a new counter value
        id_st.update(new);
        //message list update.
        new Ajax.Updater('msg_list','msg.php');
        return false;
      }
    }
  });
}

We have our function, so now we just need to determine how often the counter must be checked and give him an initial value. The initial value is taken with the first query that is receiving the list of messages and in php it is assigned to variable called $start

//Every half minute call a function check
setInterval("check('checker.php',$('old_count'),$('new_count'));",30000); 
// When the page is loading we set the initial counter value
window.onload=function(){
  $('old_count').update('<?php echo $start; ?>');
}

Nothing more is needed, the list of messages (and only that) will refresh itself every half minute, if only something in its content will change.