Updates to a web page based on MySQL Database

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 databa开发者_运维问答se 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!

---

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.

---

Use HTML 5 sockets along with jquery to get maximum benefit