If mysql table updated since page load

[slj90]

I am creating a stream page that updates automatically without the page reloading by reloading a div on the page using a timer. However, I only want it to run the update and fade in/fade out when there is a new status in the table. I tried doing it through PHP getting the number of rows in the table on the page itself and then checking the number of rows on an action page, however, the number of rows didnt update when the div was reloaded. I then tried doing it using jquery/javscript but had the same problem.

How can I acheive this?

 

Thanks in advance

[kicken]

Use javascript to check the server in the background and see if there are any updates, then only update the UI if there actually are updates. So, something like:

$.get('/script.php').done(function(html){
   if (html){
      $('#thediv').html(html);
   }
});

script.php would query the database for any updates since the last check. If there are updates then generate the HTML to be rendered as the response. If there were no updates, do not output anything.

[slj90]

Use javascript to check the server in the background and see if there are any updates, then only update the UI if there actually are updates. So, something like:

$.get('/script.php').done(function(html){
   if (html){
      $('#thediv').html(html);
   }
});

script.php would query the database for any updates since the last check. If there are updates then generate the HTML to be rendered as the response. If there were no updates, do not output anything.

 

Thanks for your reply. This is the kind of thing I was doing but couldn't get it to work. What should the PHP file be doing? Before I was sending the ID of the last status loaded on the page and comparing that to the number of rows in the table. What would you suggest?

 

Thanks,

[slj90]

I understand that the response needs to be the updated HTML but how would it check if there any updates? Thanks

[Alex_]

The logical answer is that you'll need a way to determine when a user is active, or in "streaming terms": "Went live". You mentioned a Mysql Table where you currently retain currently Live streams. I'd say throw in a new column there that says when the Stream started. Doing so, you will be able to something similar to this:

 

(Let's call the new column "UpdateTime", for example)

 

Frontend

 

var startTime; //Gets filled in startTimer()
var streamsSelector = ''; //Selector for where you want the updated response
 
function handleUpdatedStreamsResponse(response) {
//Do whatever you want to do with the response here.
//Preferably you should keep to using JSON responses from backend, but let's assume plain html for now..
$(streamsSelector).append(response);
 
//The reason it's not completely overwriting the current html is because you'll only be returning the "Updated"/"New" streams from the backend.
//This can of course be changed should you wish to handle it differently.
 
//Also update the startTime
startTime = moment().format('YYYY-MM-DD HH:mm:ss');
}
 
function getUpdatedStreams() {
$.get('streamUpdates.php?startTime=' + startTime, handleUpdatedStreamsResponse);
}
 
function startTimer() {
startTime = moment().format('YYYY-MM-DD HH:mm:ss'); //Small tip: Always use momentjs. Lovely library!
setInterval(getUpdatedStreams, 10000); //Fetch updated streams every 10 seconds ?
}
 
$(document).ready(startTimer);

 

Backend

 

<?php
 
$db = new MyDbClass(...);
 
$startTime = $_GET['starttime'];
$rows = $db->query('SELECT * FROM mytable WHERE UpdateTime >= $startTime');
$response = '';
 
foreach($rows as $row) {
$response .= buildHtmlBlock($row); //Some function that builds the html block for the new record
}
 
echo $response;

 

 

This is very minimalistic and there's plenty of room for improvement, but I wrote it as simple as I could so that you could get the idea behind it.

[mac_gyver]

you shouldn't try to use time for this. multiple pieces of data can be inserted in a single second and during the same second that you are trying to retrieve data.. you can have data with the exact same time stamp with rows that were inserted before you run a query to retrieve data, that the query will find, and with rows that were inserted after the retrieval query was ran that you won't find. this is a bug that has plagued things like Vbulletin's forum software for a time (no pun intended) now. also, for the code Alex_ posted above, that's trying to compare the client's time with what's been stored on the server, this isn't reliable since it assumes that the clients time zone is related to (all) the data stored on the server and that the client's exact time (seconds) is synchronized precisely with whatever time source was used when the data was stored.

 

instead, take a lesson from the SMF forum software, where the id (auto-increment database field) of the last retrieved/displayed information is carried in the url's and is used to find any rows inserted since that information was retrieved and displayed (in the case of SMF software, they are doing this to display 'new posts where made since you viewed this thread' and they are also using the thread id to qualify the results to the current thread being viewed in the browser.)

 

this seems similar to what the op stated in the first post, but you must query for rows that have an id greater than the 'last displayed id'. you cannot check for a quantity of rows matching the id value since id's may not be contiguous if rows have been deleted.