1
\$\begingroup\$

I am writing a script that is supposed to retrieve some data from a MYSQL database but I need to make it happen live and after lots of research I learned that this thing can be done using something called "Web Sockets" but since it seemed too complicated and I haven't even learned javascript that well, I chose to keep practicing javascript and use the only other method that I've found during the research and that method is by using a setInterval function.

However, almost every example that I found using this method had a side note saying "This method is not recommended since if you have many users constantly sending requests to retrieve data from your server, it may cause the server to slow down or malfunction", which makes a lot of sense, but still I thought, can we optimize this to a minimal level that it doesn't even bother the server that much?

Now, I've done some thinking and I believe that I found a possible solution that can help the client request the smallest piece of data on setInterval and only retrieve the big data when there is a change in the database... here is how I did it...

connect.php

<?php $mysqli = new mysqli('localhost', 'root', '', 'learningajax'); ?>

index.html

<html>
<head>
    <title>
        Learning AJAX
    </title>
    <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.4.1/jquery.min.js"></script>
    <script type="text/javascript" src="script.js"></script>
</head>
<body>
    <div id="data"></div>
</body>
</html>

observer.php

<?php
include 'connect.php';
$count = mysqli_query($mysqli, "SELECT * FROM users");
$result= mysqli_num_rows($count);
echo json_encode($result);
?>

display.php

<?php
include 'connect.php';
$data = mysqli_query($mysqli, "SELECT * FROM users");
echo "<table><tr>
    <th>ID</th>
    <th>Username</th>
    <th>Password</th>
    </tr>";
while($row = $data->fetch_assoc()){
    $user_id = $row['id'];
    $username = $row['username'];
    $password = $row['password'];
    echo "<tr>
    <td>$user_id</td>
    <td>$username</td>
    <td>$password</td>";
}
echo "</tr></table>";
?>

script.js

$(document).ready(function load(){
    $.ajax({
        type: "GET",
        url: "display.php",
        dataType: "html",
        success: function(data){
            $("#data").html(data);            
        }
    });

    $.ajax({
        type: "GET",
        url: "observer.php",
        dataType: "html",
        success: function (primary){
            console.log(primary);
            let check = setInterval(function (){
                $.ajax({
                    type: "GET",
                    url: "observer.php",
                    dataType: "html",
                    success: function(trigger){
                        console.log(trigger);
                        if(primary != trigger){
                            clearInterval(check);
                            load();
                        }
                    }
                });
            }, 1000);     
        }
    });
});

insert.php

<?php
include 'connect.php';

$username = $_POST['username'];
$password = $_POST['password'];

mysqli_query($mysqli, "INSERT INTO users (username,password) VALUES ('$username','$password')");
?>
<form method="POST">
    <input type="text" name="username" />
    <input type="password" name="password" />
    <input type="submit" />
</form>

As you can see there is a script (observer.php) that only retrieves the number of records in the table and I used that script to define one variable and store the retrieved value on page load and then I made a function that will run the same script each second storing the value that the script returns in another variable (updating the value of the variable each second) and then told the code to load the entire code again only if the script retrieved a different value than the one that was stored on page load, I also clear the interval before calling the function because if I don't then it keeps loading the function over and over again after at least one change happened.

Now, the point is that the script that runs each second over and over again is so minimal that it takes so little from the server and the big data is only pulled when the script spots a change in the number of records in the table.

Now, I have few questions...

  • Is this a good alternative of WebSockets for a small UI platform that don't have much users?
  • Will the server still slow down or overwhelm by the requests no matter how small the data you retrieve is if you have several copies (per user) of this script continuously running for 16 hours a day?
  • Everybody is saying that if you have too many users constantly sending requests the server may slow down or overwhelm but how many users are too many? (assuming that we use some standard package of some of the popular hosting providers among the web, you give the example)

install.php

<?php
include 'connect.php';

mysqli_query($mysqli, "CREATE TABLE IF NOT EXISTS users(
    id INT(11) AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(255) NOT NULL,
    password VARCHAR(255) NOT NULL
)");

mysqli_query($mysqli, "INSERT INTO users (
    username,
    password
    ) VALUES (
        'default',
        'password'
        )");
?>
\$\endgroup\$
1
  • \$\begingroup\$ The answers to your questions are likely No, Yes (although your example doesn't make much sense) and They're right but you won't easily find statistics on that. \$\endgroup\$
    – Mast
    Apr 6 '20 at 20:12
3
\$\begingroup\$

I would recommend some modifications.

First, don't use setInterval. Use setTimeout instead. The setInterval function won't wait for the server to answer before sending another request. It will keep sending request to the server even if the server takes more than one second to answer your request. If you do something like:

function displayData(numUsers) {
  $.ajax({
    type: "GET",
    url: "display.php",
    dataType: "html",
    success: function(data){
      $("#data").html(data);
      askObjserver(numUsers);
    }
  });
}

function askObserver(primary) {
  $.ajax({
    type: "GET",
    url: "observer.php",
    dataType: "html",
    success: function(trigger){
      console.log(trigger);
      if(primary != trigger){
        displayData(trigger);
      } else {
        setTimeout(askObserver, 1000, trigger);
      }
    }
  });
}

$(document).ready(askObserver);

The fist time, the script will retrieve the number of elements. As the input primary is undefined, primary !== trigger. This forces the first display of elements and reactivates the observer cycle. While the response is the same than before, the function keeps enqueuing more calls to the observer method. Every time the result differs, the information is updated.

The second point to modify would be the query being used in the observer method. I would suggest using a query like SELECT COUNT(id) FROM users. That will make the query more efficient. The database manager will use the primary key index to make the query more efficient. You only need to read the returned value in the query instead of counting the number of rows returned in the resulting cursor.

For your questions:

  • Websockets allow your server to be the one notifying to the clients when a change happens avoiding unnecessary HTTP calls. This alternative isn't better. Take into account that you are executing a query to the database for every connection established against your PHP methods. The Websocket alternative would only need one server procedure executing the query internally every second. Whenever a change is detected, the server broadcast to all the clients the information.
  • The limitations you will face are the number of connections that can be opened at the same time against the database. That will mark the number of concurrent users you can have using the application.
\$\endgroup\$
1
  • 1
    \$\begingroup\$ ...and user input should be valudated and sanitized and the INSERT should be a prepared statement with bound parameters. \$\endgroup\$ Apr 6 '20 at 8:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.