title: Server Side Events with PHP & MySQL
body: <p>I am building a (very basic) application using PHP and MySQL. The purpose of this application is to display 'real time' data transactions on a web page. These transactions come from a <code>transactions</code> table in a MySQL database.</p>
  
  <p>So far I can retrieve and display the data on the webpage. However I was expecting to see the data refresh <strong>only</strong> when new transactions were inserted into the <code>transactions</code> table? </p>
  
  <p>Currently the live feed displays the last record repeatedly until a new transaction is inserted, this loops.</p>
  
  <p>My code so far is;</p>
  
  <p><strong>transactions.html</strong></p>
  
  <pre><code>&lt;!DOCTYPE html&gt;
  &lt;html lang="en"&gt;
  &lt;head&gt;
      &lt;title&gt;HTML5 Server-Sent Events&lt;/title&gt;
      &lt;script type="text/javascript"&gt;
          window.onload = function(){
              var source = new EventSource("transactions.php");
              source.onmessage = function(event){
                  document.getElementById("result").innerHTML += "New transaction: " + event.data + "&lt;br&gt;";
              };
          };
      &lt;/script&gt;
  &lt;/head&gt;
  &lt;body&gt;
      &lt;div id="result"&gt;
          &lt;!--Server response will be inserted here--&gt;
      &lt;/div&gt;
  &lt;/body&gt;
  &lt;/html&gt;
  </code></pre>
  
  <p><strong>transactions.php</strong></p>
  
  <pre><code>&lt;?php 
  include 'conn.php'; // database connection
  
  header("Content-Type: text/event-stream");
  header("Cache-Control: no-cache");
  
  $query = "SELECT TimeStamp, CardNo FROM transactions ORDER BY TimeStamp DESC LIMIT 1";
  // `TimeStamp` is in the format YYYY-MM-DD H:i:s 
  if ($result = $conn-&gt;query($query)) {
      $row = $result-&gt;fetch_assoc();
      echo "data: " . $row['CardNo'] . "\n\n";
  }
  flush();
  ?&gt;
  </code></pre>
  
  <p>I have been following <a href="https://www.tutorialrepublic.com/html-tutorial/html5-server-sent-events.php" rel="nofollow noreferrer">this tutorial</a> if that makes any difference.</p>
  
  <p>My questions;</p>
  
  <ul>
  <li>how can I refresh the live feed <em>only when new transactions are inserted</em>?</li>
  <li>the live feed currently refreshes approximately every 3 seconds, where is this set?</li>
  </ul>
  
  <p>Any help is appreciated.</p>