使用 PHP 和 AJAX 检查数据库更新

Check for a database update using PHP and AJAX

提问人:JUNSKI 提问时间:9/18/2022 更新时间:9/18/2022 访问量:372

问:

我有一个网站,当用户点击画布时,他会在上面画画,然后它被推送到数据库中。它在一秒钟内为每个用户绘制 10 次。

        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);
    

现在我注意到这不是最好的方法。但是有没有一种好方法可以检查数据库更新? 我尝试了一些 if 语句,但没有一个真正有效。

javascript php jquery mysqli

评论


答:

0赞 Mahesh Thorat 9/18/2022 #1

您可以使用 WebSockets 跟踪 MySQL 数据库更改。PHP sockets

设置前端

文件: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/[email protected]/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>

接下来,使用以下代码在 HTML 文件中添加 PieSocket-JS WebSocket 库:

现在,我们已准备好连接到 WebSocket 通道并开始接收来自服务器的即时更新。将以下代码添加到 index.php 文件中以建立 websocket 连接。

<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>

设置后端

让我们创建一个名为 admin.php 的文件,每次访问用户表时,它都会向用户的表添加一个条目。

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

上面给出的代码片段每次通过 CLI 或从 Web 服务器执行时都会在 users 表中添加一个条目。

使用以下 PHP 函数来执行此操作。

<?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);
?>

使用 Laravel 实时跟踪 MySQL 更改

例如,在 app/model/User.php 下的 User 模型中,添加以下代码块

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

使用 Django 实时跟踪 MySQL 更改

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'))