PHP&服务器端事件MySQL的 [英] Server Side Events with PHP & MySQL
问题描述
我正在使用PHP和MySQL构建一个（非常基础的）应用程序。此应用程序的目的是在网页上显示实时数据事务。这些事务来自MySQL数据库中的事务表。
I am building a (very basic) application using PHP and MySQL. The purpose of this application is to display 'real time' data transactions on a web page. These transactions come from a transactions table in a MySQL database.
到目前为止，我可以检索和显示数据网页。但是，当新的交易被插入交易表时，我期待看到数据刷新仅？
So far I can retrieve and display the data on the webpage. However I was expecting to see the data refresh only when new transactions were inserted into the transactions table?
目前，实时Feed会重复显示最后一条记录，直到插入新交易为止。此循环。
Currently the live feed displays the last record repeatedly until a new transaction is inserted, this loops.
My到目前为止的代码是;
My code so far is;
transactions.html
<!DOCTYPE html>
<html lang="en">
<head>
<title>HTML5 Server-Sent Events</title>
<script type="text/javascript">
window.onload = function(){
var source = new EventSource("transactions.php");
source.onmessage = function(event){
document.getElementById("result").innerHTML += "New transaction: " + event.data + "<br>";
};
};
</script>
</head>
<body>
<div id="result">
<!--Server response will be inserted here-->
</div>
</body>
</html>
transactions.php
<?php
include 'conn.php'; // database connection
header("Content-Type: text/event-stream");
header("Cache-Control: no-cache");
$query = "SELECT TimeStamp, CardNo FROM transactions ORDER BY TimeStamp DESC LIMIT 1";
// `TimeStamp` is in the format YYYY-MM-DD H:i:s
if ($result = $conn->query($query)) {
$row = $result->fetch_assoc();
echo "data: " . $row['CardNo'] . "\n\n";
}
flush();
?>
我一直关注本教程如果有任何不同。
I have been following this tutorial if that makes any difference.
我的问题;
- 如何在插入新交易时刷新实时订阅源？
- 现在直播大约每3秒刷新一次，这是设置在哪里？
感谢任何帮助。
推荐答案
你在服务器端缺少一些东西来完成这项工作。
You are missing a couple of things on the server side to make this work.
首先，正如@RiggsFilly指出的那样，你需要在语句中使用WHERE子句。条件应该是查找比上次发送的更新的交易。
First, as @RiggsFilly pointed out, you need to use a WHERE clause in the statement. The condition should be to look for transactions that are newer than the last sent.
为此，您需要跟踪上次发送的消息的时间戳。
For that, you need to keep track of the timestamp of the last sent message.
服务器应该如果现在有条件的查询返回结果，则仅发送消息。
The server should only send a message if the query, now with the condition, returns a result.
最后，检查新事务并发送消息（如果找到）的整个例程必须是保持循环。
Finally, the entire routine to check for new transactions and send a message if found must be kept in a loop.
<?php
include 'conn.php'; // database connection
header("Content-Type: text/event-stream");
header("Cache-Control: no-cache");
$query = "SELECT TimeStamp, CardNo FROM transactions WHERE TimeStamp > ?";
$stmt = $conn->prepare($query);
$ts = time();
while(true)
{
if ($result = $stmt->execute([$ts])) {
$row = $result->fetch_assoc();
echo "data: " . $row['CardNo'] . "\n\n";
$ts = $row['TimeStamp'];
flush();
}
sleep(2);
}
这篇关于PHP&服务器端事件MySQL的的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
