0

Disclaimer: I'm familiar with PHP, MySQL, jQuery, AJAX, but am by no means an expert in any of them.

I'm working on an web application that checks updates to a MySQL database every two seconds. Currently, there are 5 tables and for the sake of discussion we can assume each has less than 50 rows. The design I inherited was to refresh 5 iframes every two seconds (roughly each iframe corresponds to a table).

I've since attempted to improve upon this by replacing the iframes with divs. Checking the UPDATE_TIME in INFORMATION_SCHEMA and only updating the divs in which the content has changed since the save previous UPDATE_TIME. To do this, I use a jQuery AJAX call to get the new data from a PHP script. The problem with this strategy is that an external program is updating the database asynchronously so it is possible that it could make multiple updates within a second.

This question is very similar to other questions with the exception that the whole second resolution provided by the UPDATE_TIME is not enough in my case if I'm to base my updates solely .
Query to find tables modified in the last hour

Any solutions would be greatly appreciated!

Community
  • 1
  • 1
user800133
  • 63
  • 1
  • 9
  • Could you clarify your concern. What exactly is you 'exception'? – Shad Oct 12 '11 at 03:04
  • Why does it matter if multiple updates are made in a second? Can't you just retrieve them all at the same time? – mellamokb Oct 12 '11 at 03:46
  • Let's say for example one update comes in at time 1.0 seconds and I update the page. Then another comes in 300 milliseconds later at time 1.3 seconds the user will miss this update because the UPDATE_TIME will still indicate that the update occurred at time 1.0. – user800133 Oct 12 '11 at 14:36

2 Answers2

0

I had a similar implementation. The tables which I needed to fetch from had a column which says CREATED_TIME. This column could be inserted with CURRENT_TIMESTAMP using the column default value or from the application.

Initially once you load the contents to the div, keep a javascript variable corresponding to each div which stores the CLIENT_MAX(CREATED_TIME). Each time when you need to update the div the latest rows, follow this step:

  1. Request to server with the CheckIfTable1Updated?maxTime=CLIENT_MAX(CREATED_TIME) value using ajax.
  2. The server should fetch the SERVER_MAX(CREATED_TIME) value from Table1 and compare with the value send by the client.
  3. If the max value in the table is greater than the value send by client, the response should be send with the SERVER_MAX(CREATED_TIME), otherwise send 0.
  4. If client receives a 0, do nothing.
  5. If client receives a value greater than zero, i.e. the SERVER_MAX(CREATED_TIME), call the server with ajax- 'RetrieveTable1Updates?fromTime=CLIENT_MAX(CREATED_TIME)&toTime=SERVER_MAX_TIME_JUST_RECEIVED'
  6. Server handles this, fetches the rows with the constraint BETWEEN CLIENT_MAX_TIME AND SERVER_MAX_TIME_ACCORDING_TO_CLIENT.
  7. Server sends the html elements.
  8. Client receives the data, appends to the corresponding div.
  9. Makes the CLIENT_MAX(CREATED_TIME) as SERVER_MAX(CREATED_TIME), received in the first ajax call.

Note: This can be handles with the row_id too which would be much easier than timestamp handling as BETWEEN would need CLIENT_MAX_TIME + 1 to handle duplication.

James Jithin
  • 10,183
  • 5
  • 36
  • 51
  • You can merge the two requests into one - check the last update id/time and if it's greater than the currently displayed to the user - return the changes and update the page. You can save the overhead of sending two requests when one could do the job with (almost) no other performance impact. – Nikoloff Oct 12 '11 at 07:23
  • Yup. If you can handle the ajax request in such a way, can do that. This can go little complex, better for advanced users. – James Jithin Oct 12 '11 at 07:40
  • Many thanks! I was hoping to find a way of doing it without changing the database/external application, but this may be the way I have to go. – user800133 Oct 12 '11 at 16:31
0

Use HTML 5 sockets along with jquery to get maximum benefit

Imdad
  • 5,942
  • 4
  • 33
  • 53