title: PHP/Ajax auto refresh after adding value to sql database
body: <p>Sorry for my english.
  I try to modify this script which uses AJAX to look for changes in a database table, and if a change is detected, return information from that table : <a href="http://blog.codebusters.pl/en/ajax-auto-refresh-volume-ii/" rel="nofollow noreferrer">http://blog.codebusters.pl/en/ajax-auto-refresh-volume-ii/</a></p>
  
  <p>Instead of using html form to add value to database (from add.php), I try to send value to the db when the page is loaded (goal is to be alerted in real time that a user is on that page).</p>
  
  <p>I think the file which need to be modified is add.php and especially this :</p>
  
  <pre><code>"&lt;?php require('common.php');
  if($_POST &amp;&amp; !empty($_POST['title'])){
  $result = $db-&gt;add_news(strip_tags($_POST['title']));
  }?&gt;"
  </code></pre>
  
  <p>I remplaced by :</p>
  
  <pre><code>&lt;?php
  
  $link = mysqli_connect("localhost", "user", "mypass", "db");
  
  if($link === false){
  
      die("ERROR: Could not connect. " . mysqli_connect_error());
  
  }
  
  $sql = "INSERT INTO news (title) VALUES ('This page has been loaded !')";
  
  if(mysqli_query($link, $sql)){
  
      echo "Records inserted successfully.";
  
  } else{
  
      echo "ERROR: Could not able to execute $sql. " . mysqli_error($link);
  
  }
  mysqli_close($link);
  ?&gt;
  </code></pre>
  
  <p>Problem is auto refresh isn't working. I think this is because this line is missing from add.php :</p>
  
  <pre><code>"$result = $db-&gt;add_news(strip_tags($_POST['title']));"
  </code></pre>
  
  <p>How can I ask for auto refresh after adding value in sql table ?
  I think I have to use function register_changes from db.php but I don't know how and where.. I'm beginner and lost!</p>
  
  <p>Thank you in advance for your help.</p>
  
  <p>Here are the original files :</p>
  
  <p>add.php :</p>
  
  <pre><code>&lt;?php require('common.php');
  if($_POST &amp;&amp; !empty($_POST['title'])){
      $result = $db-&gt;add_news(strip_tags($_POST['title']));
  }?&gt;
  &lt;!DOCTYPE html&gt;
  &lt;html&gt;
  &lt;head&gt;
      &lt;meta charset="utf-8"&gt;
      &lt;title&gt;Add news - Demo for Ajax Autorefresh&lt;/title&gt;
  &lt;/head&gt;
  &lt;body&gt;
      &lt;h1&gt;This is a demo for post &lt;a href="http://blog.codebusters.pl/en/entry/ajax-auto-refresh-volume-ii"&gt;Ajax Auto Refresh - Volume II&lt;/a&gt;&lt;/h1&gt;
      &lt;p&gt;Open &lt;a href="index.php"&gt;list of messages&lt;/a&gt; in new window, add new message with this form and look at that list. Don't refresh it manually. It should refresh automatically after 20 seconds.&lt;p&gt;
      &lt;?php if(isset($result)){
          if($result==TRUE){
              echo '&lt;p&gt;Success&lt;/p&gt;';
          }else{
              echo '&lt;p&gt;Error&lt;/p&gt;';
          }
      }else{?&gt;
          &lt;form method="post" action="#"&gt;
              &lt;input type="text" name="title" size="50" /&gt;
              &lt;input type="submit" value="Add message" /&gt;
          &lt;/form&gt;
      &lt;?php }?&gt;
  &lt;/body&gt;
  &lt;/html&gt;
  </code></pre>
  
  <p>common.php :</p>
  
  <pre><code>&lt;?php
  require_once ('db.php'); //get our database class
  $db = new db();
  /* end of file common.php */
  </code></pre>
  
  <p>db.php :</p>
  
  <pre><code>&lt;?php
  /**
   * Class db for Ajax Auto Refresh - Volume II - demo
   *
   * @author Eliza Witkowska &lt;kokers@codebusters.pl&gt;
   * @link http://blog.codebusters.pl/en/entry/ajax-auto-refresh-volume-ii
   */
  class db{
  
      /**
       * db
       *
       * @var $   public $db;
       */
      public $db;
  
  
      /**
       * __construct
       *
       * @return void
       */
      function __construct(){
          $this-&gt;db_connect('localhost','root','alamakota','test');
      }
  
  
      /**
       * db_connect
       *
       * Connect with database
       *
       * @param mixed $host
       * @param mixed $user
       * @param mixed $pass
       * @param mixed $database
       * @return void
       */
      function db_connect($host,$user,$pass,$database){
          $this-&gt;db = new mysqli($host, $user, $pass, $database);
  
          if($this-&gt;db-&gt;connect_errno &gt; 0){
              die('Unable to connect to database [' . $this-&gt;db-&gt;connect_error . ']');
          }
      }
  
  
      /**
       * check_changes
       *
       * Get counter value from database
       *
       * @return void
       */
      function check_changes(){
          $result = $this-&gt;db-&gt;query('SELECT counting FROM news WHERE id=1');
          if($result = $result-&gt;fetch_object()){
              return $result-&gt;counting;
          }
          return 0;
      }
  
  
      /**
       * register_changes
       *
       * Increase value of counter in database. Should be called everytime when
       * something change (add,edit or delete)
       *
       * @return void
       */
      function register_changes(){
          $this-&gt;db-&gt;query('UPDATE news SET counting = counting + 1 WHERE id=1');
      }
  
  
      /**
       * get_news
       *
       * Get list of news
       *
       * @return void
       */
      function get_news(){
          if($result = $this-&gt;db-&gt;query('SELECT * FROM news WHERE id&lt;&gt;1 ORDER BY add_date DESC LIMIT 50')){
              $return = '';
              while($r = $result-&gt;fetch_object()){
                  $return .= '&lt;p&gt;id: '.$r-&gt;id.' | '.htmlspecialchars($r-&gt;title).'&lt;/p&gt;';
                  $return .= '&lt;hr/&gt;';
              }
              return $return;
          }
      }
  
  
      /**
       * add_news
       *
       * Add new message
       *
       * @param mixed $title
       * @return void
       */
      function add_news($title){
          $title = $this-&gt;db-&gt;real_escape_string($title);
          if($this-&gt;db-&gt;query('INSERT into news (title) VALUES ("'.$title.'")')){
              $this-&gt;register_changes();
              return TRUE;
          }
          return FALSE;
      }
  }
  /* End of file db.php */
  </code></pre>
  
  <p>index.php :</p>
  
  <pre><code>&lt;?php require('common.php'); ?&gt;
  &lt;!DOCTYPE html&gt;
  &lt;html&gt;
  &lt;head&gt;
      &lt;meta charset="utf-8"&gt;
      &lt;title&gt;Demo for Ajax Auto Refresh&lt;/title&gt;
      &lt;script src="jquery-1.10.2.min.js"&gt;&lt;/script&gt;
      &lt;script&gt;
          /* AJAX request to checker */
          function check(){
              $.ajax({
                  type: 'POST',
                  url: 'checker.php',
                  dataType: 'json',
                  data: {
                      counter:$('#message-list').data('counter')
                  }
              }).done(function( response ) {
                  /* update counter */
                  $('#message-list').data('counter',response.current);
                  /* check if with response we got a new update */
                  if(response.update==true){
                      $('#message-list').html(response.news);
                  }
              });
          }
          //Every 20 sec check if there is new update
          setInterval(check,20000);
      &lt;/script&gt;
  &lt;/head&gt;
  &lt;body&gt;
      &lt;h1&gt;This is a demo for post &lt;a href="http://blog.codebusters.pl/en/entry/ajax-auto-refresh-volume-ii"&gt;Ajax Auto Refresh - Volume II&lt;/a&gt;&lt;/h1&gt;
      &lt;?php /* Our message container. data-counter should contain initial value of couner from database */ ?&gt;
      &lt;div id="message-list" data-counter="&lt;?php echo (int)$db-&gt;check_changes();?&gt;"&gt;
          &lt;?php echo $db-&gt;get_news();?&gt;
      &lt;/div&gt;
      &lt;p&gt;&lt;a href="add.php"&gt;Add new message&lt;/a&gt;&lt;/p&gt;
  &lt;/body&gt;
  &lt;/html&gt;
  </code></pre>
  
  <p>checker.php :</p>
  
  <pre><code>&lt;?php require('common.php');
  //get current counter
  $data['current'] = (int)$db-&gt;check_changes();
  //set initial value of update to false
  $data['update'] = false;
  //check if it's ajax call with POST containing current (for user) counter;
  //and check if that counter is diffrent from the one in database
  if(isset($_POST) &amp;&amp; !empty($_POST['counter']) &amp;&amp; (int)$_POST['counter']!=$data['current']){
      //the counters are diffrent so get new message list
      $data['news'] = '&lt;h1&gt;OMG! It\'s alive!!! NEW UPDATE !!!&lt;/h1&gt;';
      $data['news'] .= $db-&gt;get_news();
      $data['update'] = true;
  }
  //just echo as JSON
  echo json_encode($data);
  /* End of file checker.php */
  </code></pre>