javascript - Check for a database update using PHP and AJAX

View Original

I have a website where when the user clicks a canvas, he draws on it and it gets pushed into the database. And it draws for every user 10 times in one second.

        setInterval(function(){
        $.ajax({
            url: 'data/canvasSave.php',
            success: function(data) {
                $('.result').html(data);
                console.log(data);
                imageCanvas = data;
            }
        });
        let img = new Image();
        img.src = imageCanvas; 
        context.clearRect(0, 0, canvas.width, canvas.height);
        ctx.drawImage(img, 0 , 0, canvas.width, canvas.height);
    }, 100);

Now I notice that this isn't the best way. But is there a good way to check for Database updates? I tried a few if statements but none of them really worked.

Answer

Solution:

You can use PHP sockets for tracking MySQL Database changes with WebSockets.

Setup Frontend

File: index.php

<!DOCTYPE html>
<html lang="en">
<head>
  <meta charset="UTF-8">
  <meta http-equiv="X-UA-Compatible" content="IE=edge">
  <meta name="viewport" content="width=device-width, initial-scale=1.0">
  <title>MySQL Tracker</title>
 
  <link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/uikit@3.7.4/dist/css/uikit.min.css" />
 
</head>
<body>
  <div class="uk-container uk-padding">
    <h2>Users</h2>
    <p>Tracking users table with WebSockets</p>
    <table  class="uk-table">
      <thead>  
        <tr>
          <th>User ID</th>
          <th>User Name</th>
        </tr>
      </thead>  
    <tbody>  
        <?php
        $connection = mysqli_connect(
            'localhost',
            'root',
            PASSWORD,
            DB_NAME
        );
        $users = mysqli_query($connection, 'SELECT * FROM users');
        while ($user = mysqli_fetch_assoc($users)) {
            echo '<tr>';
            echo '<td>' . $user['id'] . '</td>';
            echo '<td>' . $user['name'] . '</td>';
            echo '</tr>';
        }
        ?>
    </tbody>  
    </table>
    </div>
</body>
</html>

Next, add the PieSocket-JS WebSocket library in your HTML file with the following code:

Now, we are ready to connect to the WebSocket channel and start receiving instantaneous updates from the server. Add the following code to your index.php file to make a websocket connection.

<script>
  var piesocket = new PieSocket({
    clusterId: 'CLUSTER_ID',
    apiKey: 'API_KEY'
  });
 
  // Connect to a WebSocket channel
  var channel = piesocket.subscribe("my-channel"); 
  channel.on("open", ()=>{
    console.log("PieSocket Channel Connected!");
  });
 
  //Handle updates from the server
  channel.on('message', function(msg){
    var data = JSON.parse(msg.data);
    if(data.event == "new_user"){
      alert(JSON.stringify(data.data));
    }
  });
</script>

Setup Backend

Let??�s create a file called admin.php which adds an entry to the user??�s table, every time it is visited.

<?php
$connection = mysqli_connect(
    'localhost',
    'root',
    PASSWORD,
    DB_NAME
);
mysqli_query($connection, "INSERT INTO users .....");
?>

The code snippet given above adds an entry in the users table every time its execute either via CLI or from a webserver.

Use the following PHP function to do that.

<?php
function publishUser($event){
  $curl = curl_init();
 
  $post_fields = [
    "key" => "PIESOCKET_API_KEY", 
    "secret" => "PIESOCKET_API_SECRET",
    "channelId" => "my-channel",
    "message" => $event
  ];
 
  curl_setopt_array($curl, array(
    CURLOPT_URL => "https://PIESOCKET_CLUTER_ID.piesocket.com/api/publish",
    CURLOPT_RETURNTRANSFER => true,
    CURLOPT_CUSTOMREQUEST => "POST",
    CURLOPT_POSTFIELDS => json_encode($post_fields),
    CURLOPT_HTTPHEADER => array(
      "Content-Type: application/json"
    ),
  ));
 
  $response = curl_exec($curl);
  print_r($response);
}
 
$connection = mysqli_connect(
    'localhost',
    'root',
    PASSWORD,
    DB_NAME
);
mysqli_query($connection, "INSERT INTO users .....");
$payload = json_encode([
  "event" => "new_user",
  "data" => [
  "id"=> 1, 
  "name"=>"Test user"]
]);
 
publishUser($payload);
?>

Track MySQL changes in real-time with Laravel

For example, in your User model under app/model/User.php add the following code block

public static function boot() {
    parent::boot();
 
    static::created(function($user) {
        publishUser(json_encode($user));
    });
}

Track MySQL changes in real-time with Django

import requests
import json
url = "https://CLUSTER_ID.piesocket.com/api/publish"
payload = json.dumps({
    "key": "API_KEY", 
    "secret": "API_SECRET",
    "channelId": 1,
    "message": { "text": "Hello world!" }
});
headers = {
  'Content-Type': 'application/json'
}
response = requests.request("POST", url, headers=headers, data = payload)
print(response.text.encode('utf8'))

Source