How to poll database based on table change php + mysql

Question

I want to update my generated table in my php file only when there are changes in the database table but i don't know how to do this. After reading all the many similar questions i still haven't come close to understanding what it is that I am supposed to do.

Im displaying my table within this div <div id="table_holder" class="table-responsive" style="height: 300px;"></div> within my main.php

and this is the JS that I'm using to refresh the table every 5 seconds.

<script type="text/javascript">
    $(document).ready(function(){
        refreshTable();
    });

    function refreshTable(){

        $('#table_holder').load('live_table_data.php', function(){
            setTimeout(refreshTable, 5000);
        });
    }
</script>

This is how im displaying my table in live_data_table.php

$result = $conn->query("select date,student_id, s.s_name as Name, s.s_lname as Last, s.s_email as Email, time from attendance_record A 
                            inner join student s on A.student_id = s.s_id
                            inner join session b on A.session_id = b.session_id
                        where b.module_id = 7 and A.date = '2017-03-20' and b.start_time = '16:00'");

?>
<table class="table-responsive" id="live_table">
    <thead>
    <tr >
        <th>Student ID</th>
        <th>Name</th>
        <th>Last Name</th>
        <th>Email</th>
        <th>Arrival</th>
        <th>Date</th>
    </tr>
    </thead>
    <tbody style="overflow: auto">
    <?php
    while ($row = $result->fetch_assoc()) {
        unset($student_id, $Name, $Last, $Email, $time, $date);
        $student_id = $row['student_id'];
        $Name = $row['Name'];
        $Last = $row['Last'];
        $Email = $row['Email'];
        $time = $row['time'];
        $date = $row['date'];
        echo "<tr>";
        echo "<td>".$student_id."</td>";
        echo "<td>".$Name."</td>";
        echo "<td>".$Last."</td>";
        echo "<td>".$Email."</td>";
        echo "<td>".$time."</td>";
        echo "<td>".$date."</td>";

        echo "</tr>";
    }
    ?>
    </tbody>
</table>

My problem is that I've no idea how to dynamically update the table based on if and only if there is a change within the database

You would have to compare what you have displayed with what is in the database. If there is a change you update, if not, you don't. — Jay Blanchard
– Jay Blanchard, Commented Mar 21, 2017 at 11:39

Crouching Kitten · Accepted Answer · 2017-03-21 12:14:35Z

Usually people don't do what you are trying to, because making a query which would tell if anything has changed would require the same amount of resources as doing the original query. So the best solution might be to just repeat the query in a fixed interval, like in every minute.

But in case you have a very heavy query, for which you want to reduce the number of times it is done, you can do the following:

Since traditional relational databases have no event-driven aspects, the only way to do this, is to make polls for the changes in fixed intervals. So this as to be solved on the application level, namely: every time you modify the data you update something in the database with the current datetime. There are many possibilities:

You could add a last_modified field to the student table, and each time the code adds or modifies a record it would set there the current time. Then you can either query only the modified records, or check the latest modification time by:
```
select
    max(`last_modified`)
from
    `student`
```

You can create a separate table, which has a record for each tables you want to track. Every time your code adds or modifies a student, then it would set the current time for the student table.

insert into
    `track_updates`
    (`table_name`, `time`)
values
    ("student", "2017-03-21 12:59:00")
on duplicate key update
    `time` = "2017-03-21 12:59:00"

And then query by the table:

select
    `time`
from
    `track_updates`
where
    `table_name` = "student"

You could even have a table with always a single record in it, containing a single field for the last update time, for all relevant tables.

You can make these kinds of polling more efficient, with techniques like: https://en.wikipedia.org/wiki/Comet_%28programming%29

zenko · Accepted Answer · 2017-03-21 12:04:03Z

0

To know if there has been modification on your table, I can think of :

Check and save the last update time in information_schema
Create a "versionningTable" that is triggered every update of your table(s) data update, insert or delete
Calculate and save a hash of the selected resultset

There might be some other ways to do this.

answered Mar 21, 2017 at 12:04

zenko

1471 silver badge8 bronze badges

Comments

zen · Accepted Answer · 2017-03-21 12:05:36Z

You can't update content in browser without sending request from frontend. But you can minimize this process. Just create php script, which will output status and result in json. To make php script understand, when table updated and when not, use hash summ.

When you update any data in database, change this hash.
When you render table, add this hash summ as data-hash="<some hash>" to your table tag.
And when you will send ajax request to php script, add this hash as send data.
Then just compate actual hash and script sended hash. If this not equal, set json status as updated and output actual data.
Then render new data in table with jquery.

However, maybe it is not worth it and you better to leave it as is.

oviliukaslt · Accepted Answer · 2017-03-21 12:38:40Z

0

As per the recommendations of many, I decided to go with a different route where by eliminating the refresh of the table every 5 seconds and instead have a button designed to refresh the amount of rows within the table.

<button type="button" onClick="refreshTable()">Refresh</button>
<div id="table_holder" class="table-responsive" style="height: 300px;"></div>


<script type="text/javascript">
$(document).ready(function(){
    refreshTable();
});

function refreshTable(){
    $('#table_holder').load('live_table_data.php')};

answered Mar 21, 2017 at 12:38

oviliukaslt

515 bronze badges

Collectives™ on Stack Overflow

How to poll database based on table change php + mysql

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related