PHP Table auto update client side

Question

I am trying to get a website to auto update the table on client side every (x) seconds with the newest data, this data is stored in a database and receives constant updates. Currently the only way to see the newest data is to do a Ctrl+F5 refresh on the website.

Any assistance will be greatly appreciated, code is as follows:

    include ('./vam_index_header.php');
    include ('./helpers/conversions.php');
    header('Cache-Control: no-cache');
    header('Pragma: no-cache');
    if (!isset($_GET["page"]) || trim($_GET["page"]) == "") {
        ?>
        <?php
            $sql = 'select callsign, arrival, departure, flight_status, name, surname, pending_nm, plane_type from vam_live_flights vf, gvausers gu where gu.gvauser_id = vf.gvauser_id ';
            if (!$result = $db->query($sql)) {
                die('There was an error running the query [' . $db->error . ']');
            }
            $row_cnt = $result->num_rows;
            $sql = "SELECT flight_id FROM `vam_live_flights` WHERE UNIX_TIMESTAMP (now())-UNIX_TIMESTAMP (last_update)>180";
            if (!$result = $db->query($sql)) {
                die('There was an error running the query [' . $db->error . ']');
            }
            while ($row = $result->fetch_assoc())
            {
                $sql_inner = "delete from vam_live_acars where flight_id='".$row["flight_id"]."'";
                if (!$result_acars = $db->query($sql_inner))
                {
                die('There was an error running the query [' . $db->error . ']');
                }
                $sql_inner = "delete from vam_live_flights where flight_id='".$row["flight_id"]."'";
                if (!$result_acars = $db->query($sql_inner))
                {
                die('There was an error running the query [' . $db->error . ']');
                }
            }
            if ($row_cnt>0){
        ?>
        <div class="row" id="live_flights">
            <div class="col-md-12">
                <div class="panel panel-default">
                    <div class="panel-heading">
                        <h3 class="panel-title"><IMG src="images/icons/ic_flight_takeoff_white_18dp_1x.png">&nbsp;<?php echo "LIVE FIGHTS" ?></h3>
                    </div>
                    <div class="panel-body">
                        <div class="table-responsive">
                            <table class="table table-hover" id="live_flights_table">
                            <?php
                                    echo "<tr><th>" . LF_CALLSIG . "</th><th>" . LF_PILOT . "</th><th>" . LF_DEPARTURE . "</th><th>" . LF_ARRIVAL . "</th><th>" . FLIGHT_STAGE . "</th><th>". BOOK_ROUTE_ARICRAFT_TYPE . "</th><th>" . PERC_DONE ."</th><th>" . PENDING_NM . "</th></tr>";
                            ?>
                            </table>
                        <?php include ('./vam_live_flights_map.php') ?>
                    </div>
                </div>
            </div>
        </div>```

You can check for a failed db connection with `if (!$db) {`. You should be using prepared statements for security/stability so that user-supplied data is safely bound to your queries. Lenght should be spelled Length. Echoing `$sql` will only show you the final insert performed. It is unclear to me what you need. Do you want to know how to schedule a page reload? Did you research this before posting your question? Are you seeking a cronjob or what? Do you want this: https://stackoverflow.com/questions/9619362/running-a-cron-every-30-seconds — mickmackusa, Feb 17 '20 at 07:45
The table does displaying current flights in progress does not automatically update on client side. Standard page refresh does not update it either it is only once a hard refresh (Cntr+F5) is done that the table updates to the latest data. I am trying to get the table to do an auto update without having to do the refresh. Apologies I have posted the incorrect code calling the table to the page. — Belantro Gaming, Feb 17 '20 at 08:19

score 0 · Answer 1 · answered Feb 17 '20 at 08:31

The best in your situation could be to use the EventSource. It's well described at https://developer.mozilla.org/en-US/docs/Web/API/EventSource with some examples.

An EventSource instance opens a persistent connection to an HTTP server, which sends events in text/event-stream format.

There is another example at https://www.w3schools.com/html/html5_serversentevents.asp

I have used there in a scenario where the backend code run in a loop that sleeps for a few seconds per loop. And each iteration of the loop can detect if new data is available and it will send some corresponding event messages that can be collected by the front-end.

Another good example is available at https://developer.mozilla.org/en-US/docs/Web/API/Server-sent_events/Using_server-sent_events

I have one word of caution. When using the while(true){} style loop, you have to create a way to break the loop if the client-side connection is closed. That when you can do something like the following ...

<?php
while(true) {
    //... some code

    // A way to break out of the loop.
    if (connection_aborted()) {
        break;
    }
}

PHP Table auto update client side

1 Answers1