Refresh div every time a MySQL table refreshes

Question

I have a website which is a messaging application.

Currently, it refreshes every 500ms by reading the outputs of the refresh.php file.

I would want to know if it is possible to call the refresh function everytime the 'messages' table updates.

Most of the answers include setTimeout or setInterval, but I want to refresh it only when the table updates, because last time my website got suspended for overloading the host servers.

chatbox (index.php):



            <div id="chatbox">
                <?php


                if ($result = $mysqli->query("SELECT * FROM messages")) {
                    while ($row = $result->fetch_assoc()) {

                        echo formatMessageHTML($row["text"], $row["authorid"], $row["timestamp"], $row["hidden"], $mysqli);
                    }
                    $result->free();
                }
                ?>
            </div>

refresh.php


<?php

include_once "dbconnect.php";
$mysqli = new mysqli("localhost", $username, $password, $database);

if ($result = $mysqli->query("SELECT * FROM messages LIMIT 50")) {
    while ($row = $result->fetch_assoc()) {
            echo formatMessageHTML($row["text"], $row["edited"], $row["authorid"], $row["timestamp"], $row["hidden"], $mysqli);
        }
        
    }
    $result->free();

function formatMessageHTML($text, $edited, $authorid, $timestamp, $hidden, $mysqli)
{

    $html_message = "";
    if ($hidden == 1) return;
    if (!isset($text) || !isset($authorid) || !isset($timestamp)) return "<div class='msgln'><span class='chat-time'>" . date("g:i A") . "</span> <a class='taglink' href='javascript:insert_mention(\"Unknown\");'><b class='user-name'>Unknown</b></a> Unknown<br></div>";
    if ($result = $mysqli->query("SELECT username, namecolor_fg, namecolor_bg FROM users WHERE id=" . $authorid)) {
        while ($row = $result->fetch_assoc()) {
            $tms = explode(":", $timestamp);
            unset($tms[2]);
            if($edited == 0) {
                $html_message = "<div class='msgln'><span class='chat-time'>" . join(":", $tms) . "</span> <a class='taglink' href='javascript:insert_mention(\"" . $row["username"] . "\");'><b class='user-name' style='color: " . $row["namecolor_fg"] . "; background-color: " . $row["namecolor_bg"] . "'>" . $row["username"] . "</b></a> " . $text . "<br></div>";
            } else {
                $html_message = "<div class='msgln'><span class='chat-time'>" . join(":", $tms) . "</span> <a class='taglink' href='javascript:insert_mention(\"" . $row["username"] . "\");'><b class='user-name' style='color: " . $row["namecolor_fg"] . "; background-color: " . $row["namecolor_bg"] . "'>" . $row["username"] . "</b></a> " . $text . " <span class='edited'>(edited)</span><br></div>";
            }
            
        }
    }

    

    return $html_message;
}

?>

Javascript (index.php):


<script src="http://ajax.googleapis.com/ajax/libs/jquery/1.3.2/jquery.min.js"></script>

<script>
                function loadLog() {
                    console.log("[DEBUG] Refreshed chat");
                    var oldscrollHeight = $("#chatbox")[0].scrollHeight - 20; //Scroll height before the request

                    $.ajax({
                        url: "refresh.php",
                        cache: false,
                        success: function(html) {
                            $("#chatbox").html(html); //Insert chat log into the #chatbox div

                            //Auto-scroll           
                            var newscrollHeight = $("#chatbox")[0].scrollHeight - 20; //Scroll height after the request
                            if (newscrollHeight > oldscrollHeight) {
                                $("#chatbox").animate({
                                    scrollTop: newscrollHeight
                                }, 'normal'); //Autoscroll to bottom of div
                            }
                        }
                    });
                }

                setInterval(loadLog, 500);
</script>

Does this answer your question? [How can I tell when a MySQL table was last updated?](https://stackoverflow.com/questions/307438/how-can-i-tell-when-a-mysql-table-was-last-updated) — Timothy Alexis Vass, Jul 25 '21 at 18:34
What you want is server push, but mysql is not suitable for that purpose as it has no notification functionality. If this is what you want, you need to seriously rework your backend using a message queue or event bridge solution. — Shadow, Jul 25 '21 at 20:32

score 1 · Accepted Answer · answered Jul 25 '21 at 19:04

1

You're using pull-based data model, called AJAX polling. To avoid unnecessary requests you might use long polling as described here: https://stackoverflow.com/a/30259130/3980811

If the server still overloads resources, you can consider using "push-based" data model. If you want to build a bidirectional real-time communication like chat - you should use WebSockets. But it requires additional configuration on the server.

For unidirectional use cases like "stock exchange" or "real-time message logging" you can use Server-Sent Events. It operates over HTTP protocol and doesn't require additional configuration.

Here you can find the comparison between them: WebSockets vs. Server-Sent events/EventSource

answered Jul 25 '21 at 19:04

Dawid Janiga

68
5

None of this is supported by mysql, so your answer is incomplete. – Shadow Jul 25 '21 at 20:25
@Shadow Both of them are supported by JavaScript and PHP, so your comment is useless. – Dawid Janiga Jul 25 '21 at 21:55
The question was: how to update a web page when data in mysql is refreshed, therefore the fact that mysql does not work with these techniques is relevant. – Shadow Jul 25 '21 at 22:46
I'm open minded, but you need to frame your answer from the question's point of view. I'm **sure** the OP does not know what technology to use for real-time messaging, this is why you need to spell out that mysql is not really great for this. With mysql you can use polling only. – Shadow Jul 26 '21 at 11:05
Thanks for your time, but we won't find the common language. – Dawid Janiga Jul 26 '21 at 12:24
Hey, I decided to wait 2 versions for some reason, but then implemented WebSockets using NodeJS! Thanks! – neksodebe Nov 06 '21 at 10:55
1

The socket.io version is now going really well! Moving to socket and express from php was such a good idea. – neksodebe Feb 17 '22 at 19:16

Refresh div every time a MySQL table refreshes

1 Answers1