Alternative filemtime for MySQL

Question

I play a little with push notifications and want to refresh the page whenever there is a change in the database.

I have this from http://www.screenr.com/SNH :

<?php $filename = dirname(__FILE__).'/data.php'; $lastmodif = isset($_GET['timestamp']) ? $_GET['timestamp'] : 0; $currentmodif = filemtime($filename); while ($currentmodif <= $lastmodif) { usleep(10000); clearstatcache(); $currentmodif = filemtime($filename); } $response = array(); $response['msg'] = file_get_contents($filename); $response['timestamp'] = $currentmodif; echo json_encode($response); ?> 

My data.php is a script to get data from a JSON file:

 <script>function itnews_overview() { $.getJSON('/ajax.php?type=itnews_overview', function(data) { $.each(data.data, function(option, type) { $('.bjqs').append('<li><span class="date">'+ type.submitted +'<br />'+ type.time +'</span><h2>' + type.title + '</h2><p>' + type.content + '</p></li>'); }); }); } </script> <script> itnews_overview(); </script> <div id="news"> <ul class="bjqs"></ul> </div> 

UPDATE: Code from index.php:

 <script type="text/javascript"> var timestamp = null; function waitForMsg() { $.ajax({ type: "GET", url: "getData.php?timestamp=" + timestamp, async: true, cache: false, success: function(data) { var json = eval('(' + data + ')'); if(json['msg'] != "") { $(".news").html(json['msg']); } timestamp = json['timestamp']; setTimeout('waitForMsg()',1000); }, error: function(XMLHttpRequest, textStatus, errorThrown){ setTimeout('waitForMsg()',15000); } }); } $(document).ready(function(){ waitForMsg(); }); </script> 

Since this file is not saved when I add something to the database, filemtime will not work - is there another way to check if new rows are added to the table?

UPDATE: Attempting to resolve this issue using SSE. I have two files: index.php and send_sse.php (inspiration from http://www.developerdrive.com/2012/03/pushing-updates-to-the-web-page-with-html5-server-sent- events / )

index.php:

 <div id="serverData">Content</div> <script type="text/javascript"> //check for browser support if(typeof(EventSource)!=="undefined") { //create an object, passing it the name and location of the server side script var eSource = new EventSource("send_sse.php"); //detect message receipt eSource.onmessage = function(event) { //write the received data to the page document.getElementById("serverData").innerHTML = event.data; }; } else { document.getElementById("serverData").innerHTML="Whoops! Your browser doesn't receive server-sent events."; } </script> 

send_sse.php:

 <?php header('Content-Type: text/event-stream'); header('Cache-Control: no-cache'); $url = "content.json"; $str = file_get_contents($url); $data = json_decode($str, TRUE); //generate random number for demonstration //echo the new number echo "data: " . json_encode($data); ob_flush(); ?> 

This, however, does not seem to work, which is probably due to the fact that SSE needs plain text data. I just can't figure out how to do this, and then wrap this content in a couple of HTML tags.

UPDATE: Well, now it seems to work with SSE, thanks to VDP. I have the following:

 $sql= "SELECT title, content, submitted FROM `flex_itnews` where valid = 1 order by submitted desc"; $query= mysql_query($sql); setlocale(LC_ALL, 'da_DK'); while($result = mysql_fetch_array($query)){ echo "data: <li><span class='date'>". strftime('%e. %B', strtotime($result['submitted'])) ."<br />kl. ". strftime('%H.%M', strtotime($result['submitted'])) ."</span><h2>" . $result['title']. "</h2><p>" . $result['content'] ."</p></li>\n"; } 

However, when I add something new, it just echoes data: data: data . If I refresh the page, it displays correctly.

UPDATE: Using the livequery plugin:

  <script> var source = new EventSource('data2.php'); source.onmessage = function (event) { $('.bjqs').html(event.data); }; $('#news').livequery(function(){ $(this).bjqs({ 'animation' : 'slide', 'showMarkers' : false, 'showControls' : false, 'rotationSpeed': 100, 'width' : 1800, 'height' : 160 }); }); </script> 

UPDATE: Trying to use delegate ()

  <script> $("body").delegate(".news", "click", function(){ $("#news").bjqs({ 'animation' : 'slide', 'showMarkers' : false, 'showControls' : false, 'rotationSpeed': 100, 'width' : 1800, 'height' : 160 }); var source = new EventSource('data2.php'); source.onmessage = function (event) { $('.bjqs').append(event.data); }; }); </script> 

Answer 1

Yes! There are several ways (better):

• websocket (the best solution, but not supported on old or mobile browsers)
• Events Sent by the Server (SSE) (poll type, but optimized only for the task you are requesting)
• Long poll (how do you do)
• Flash sockets
• other plugin based files.
• ajax survey

I sent another answer with examples before this

I have listed several transport methods. websockets are ideal (as this is the only two way communication between the server and the client), SSE is my second choice. You do not need the $.getJSON method. The general idea will be the same.

On the server side (php in your case) you request your database for changes. You are returning the data as JSON ( json_encode(data) can do this). On the client side, you are decoding JSON ( JSON.parse(data) can do this). With the data you received, you refresh your page.

Just polling like what it does causes additional overhead because you are doing a lot of server requests.

SSE is more "I want to subscribe to the stream" and "I want to stop listening." => less overhead

Websockets more: "I have established a connection. I say that the server is listening. The server says that the client is listening" Full duplex connection. => least service information

SSE Code Example

The page the client is going to (for example, index.html or index.php)

This is just a regular html page containing this javascript:

 <html> <head> <script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.8/jquery.min.js"></script> <script> //javascript: var source = new EventSource('data.php'); source.onmessage = function (event) { //here you do the stuff with the received messages. //like log it to the console console.log(event.data); //or append it to div $('#response').append(event.data); }; </script> </head> <body> <div id="response"></div> </body> </html> 

Page 'data.php':

 <?php /* set the header first, don't echo/print anything before this header is set! Else the default headers will be set first then this line tries to set the headers and results in an error because the header is already set. */ header("Content-Type: text/event-stream\n\n"); //query the database $sql= "SELECT COUNT(*) FROM `messages`"; $query= mysql_query($sql); $result = mysql_fetch_array($query); $count = $result[0]; //return the data echo "data: " . $count. "\n"; ?> 

So you only need these 2 pages.

UPDATE:

I just saw your comments are not updates .. sorry;)

if you use .delegate() , you should not use the body, but try a selector as high as possible on the tree ( .bjqs in your case).

In your case, you don’t even need to live, delegate, anyway! Just apply bjq again and the content is updated.

  var source = new EventSource('data2.php'); source.onmessage = function (event) { $('.bjqs').html(event.data); $("#news").bjqs({ 'animation' : 'slide', 'showMarkers' : false, 'showControls' : false, 'rotationSpeed': 100, 'width' : 1800, 'height' : 160 }); }; 

It will also give you problems because you constantly reinitialize bjq and do not write to dynamically update the content. What you can do is send only data (with php) if there is new data. Check if the call returns if not updated:

  var source = new EventSource('data2.php'); source.onmessage = function (event) { if(event.data !=""){ $('.bjqs').html(event.data); $("#news").bjqs({ 'animation' : 'slide', 'showMarkers' : false, 'showControls' : false, 'rotationSpeed': 100, 'width' : 1800, 'height' : 160 }); } }; 

Answer 2

You can count the number of rows in a table and check if the number of rows has changed.

Answer 3

Without going into the code too much, answering the question in the title:

add the last modified column to your table, it has a built-in mysql trigger that is updated whenever a row is added or changed:

 ALTER TABLE `yourTable` ADD COLUMN `last_modified` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ADD INDEX (`last_modified`); 

and then request it,

 SELECT * FROM yourTable where last_modified > ? 

("?" is the pdo placeholder that you replace with the last timestamp requested)