Server Side Events with MySQL and PHP

Server Side Events with MySQL and PHP

​I wanted to build an application that would display a 'real time' feed of data on my web page.


I had access to a MySQL database that contained a 'transactions' table, this table received numerous events every second. I needed to display the transactions in real time (or close enough). 


Not only did I need to display the live data, but I also wanted to incorporate pause and resume buttons. This seemed like an easy enough task (2 weeks later...). I struggled with so many tutorials and never found a solution so I thought i'd write my own, hopefully this saves someone some time.

That should be easy...

- famous last words

​You will need two files;


data.php - this will contain the queries to retrieve data from the database and echo results

index.html - this will display the real time data


The data.php file might look something like this;

	// set these headers
	header("Content-Type: text/event-stream");
    header("Cache-Control: no-cache");
    header("Access-Control-Allow-Origin: *");
	
	// prepare and run query
    $query = "SELECT * FROM transactions WHERE ...";
	$stmt = $conn->prepare($query);
	$stmt->execute();
	$result = $stmt->get_result();
	// get results
	if($row = $result->fetch_assoc()) {
	    //send request every x seconds
		echo "retry: 100\n\n"; 
        // this sends (in my case) a unique id ($row["SerialNo"]) 
		echo "id: " .$row["SerialNo"]. "\n\n";
		// send data to client
		echo "data: ".$row['SerialNo']. ' ' .$row['TimeStamp']. "\n\n";
		// flush
		flush();
	} 

This is simplified code but really all you're doing is running a query and returning data in a certain format. 


Notice how the data needs to be in the format `echo id:` and `echo data:`. The id and data are keywords that will be recognised in our html file. Above my id is a unique field from my database (important!).


Also remember to set the headers above or your code may not work.


Next, your index.html file could look like this;

<!DOCTYPE html>
<html lang="en">
<head>
	<script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/2.2.4/jquery.js"></script>
	<title>HTML5 Server-Sent Events</title>
	<script type="text/javascript">
	          $("document").ready(function(){
	              // set the default stopped flag to false
	              var is_stopped = false;
	              var source = new EventSource("data.php");
	              // persistent variable
	              var offline;

	              source.onmessage = function(event) {

	                  if (is_stopped) {
	                      // get data sent from data.php
	                      offline = event.data;
	                      // Put into storage
	                      localStorage.setItem(event.lastEventId, offline);
	                      // Retrieve from storage
	                      offline = localStorage.getItem("offline");
	                  }
	                  
	                  if (!is_stopped) {
	                      // log live transactions
	                      document.getElementById("result").innerHTML += "New transaction: " + event.data + "<br>";
	                  }
	              };
	              
	              $("document").ready(function(){ 
	                  $("#start").click(function(){
	                      // set stopped flag to false
	                      is_stopped = false;
	                      // console.log('started');
	                      // loop through the localstorage 
	                      for (var i = 0; i < localStorage.length; i++){
	                          // do something with localStorage.getItem(localStorage.key(i));
	                          // add the localstorage values to the live feed and append with an asterix 
	                          document.getElementById("result").innerHTML += "New transaction: " + localStorage.getItem(localStorage.key(i)) + " *<br>";
	                      }
	                  // clear the local storage
	                  localStorage.clear();
	              });

	                  $("#stop").click(function(){
	                      // set the stopped flag to true
	                      is_stopped = true;
	                      // console.log('stopped');
	                  });
	          });//end dom ready
	          });

	</script>
</head>
<body>
	<div id="result">
		<!--Server response will be inserted here-->
	</div>
	<button id="stop">stop</button> 
	<button id="start">start</button>
</body>
</html> 

I've added comments to the code above to try explain what's going on. 


The most difficult part was trying to implement the start/stop buttons. In order to achieve this I had to declare a `is_stopped` flag in my code. Then I implemented html5 localstorage to log the 'paused' transactions. When the start button was clicked the paused transactions would be displayed on screen and the localstorage would be deleted.


I'm sure the code above can be improved greatly, but it works so i'm happy.


Please ask any questions and i'll try help.

Rate this blog entry:
Phoca Download Empty Categories
Ubuntu 14 Install and Configure SSH

Comments

 
No comments yet