如何在PHP中不断刷新mysql查询? [英] How to make a mysql query constantly refresh in PHP?
问题描述
我正在制作一个基本的聊天室.我的代码:
I'm making a basic chat room. My code:
$conn = ("127.0.0.1","root","","mymessages");
$stmt = "SELECT * FROM posts ORDER BY timestamp LIMIT 100";
$result = mysqli_query($conn, $stmt);
if(!$result) {
echo("Query failed" . mysqli_error());
}
while ($row = mysqli_fetch_assoc($result)) {
$messagefname = $row['fname'];
$messagelname = $row['lname'];
$subject = $row['subject'];
$content = $row['content'];
$day = $row['day'];
$month = $row['month'];
$year = $row['year'];
$hour = $row['hour'];
$min = $row['minute'];
echo("<font size='1.5' face='Arial'>");
echo("Sent by " . $messagefname . " " . $messagelname . " at " . $hour . ":" . $min. " on " . $day . "/" . $month . "/" . $year . "<br>");
echo("</font><br><font size='4' face='Arial'><b>");
echo($subject);
echo("</font><br><font size='3' face='Arial'></b>");
echo($content);
echo("</font><br><br><hr><br>");
}
每次刷新页面时，都会更新，并且自上次刷新以来添加的任何记录都会添加到列表中.在聊天室中，这并不理想，那么有没有一种方法可以在尽可能少地干扰用户的情况下不断检查新记录?谢谢！
Every time you refresh the page, this updates, and any records that have been added since the last refresh are added to the list. In a chat room, this isn't ideal, so is there a way to have this constantly checking for new records with as little disruption to the user as possible? Thanks!
推荐答案
您一直在使用当前的技术堆栈进行轮询.MySQL 没有任何好方法向您推送通知，告诉您有新的东西要看.
You're stuck with polling with your current tech stack. MySQL doesn't have any good way to push a notification to you that there's something new to see.
(您可以考虑添加消息队列子系统，例如 RabbitMQ，但这需要对应用程序的结构进行重大更改).
(You could consider adding a message queuing subsystem, like RabbitMQ for example, but that would require a big change to the structure of your application).
以尽可能低的成本处理轮询的一些指南.
Some guidelines for handling polling at the least possible cost.
将用户期望设置为消息延迟几秒而不是毫秒.
Set user expectations at for a few seconds' latency on the messages rather than milliseconds.
尽可能频繁地查询以满足延迟预期.
Query as often as necessary to meet the expectations for latency.
避免每次查询都查询大量数据.你怎么能这样做?
Avoid querying lots of data with each query. How can you do that?
一个.每次运行查询时保存最近的时间戳
a. Save the most recent timestamp whenever you run a query
B.不要使用 SELECT *.而是给出您实际需要的列的名称.这让 MySQL 的优化器有助于降低查询成本.
b. Don't use SELECT *. Instead give the names of the columns you actually need. This lets MySQL's optimizer help cut the cost of your queries.
c.让你的查询做 SELECT 任何 WHERE 时间戳 >saved_timestamp ORDER BY timestamp 所以你只能按顺序从你的表中获取新项目.如果您的系统不是很忙，这些 SELECTs 通常不会返回任何行.那很好.
c. Make your queries do SELECT whatever WHERE timestamp > saved_timestamp ORDER BY timestamp so you only get new items from your table, in order. If your system is not very busy, these SELECTs will often return no rows. That's good.
d.确保在时间戳和 SELECT 语句中的其他列上有一个多列索引.这称为覆盖索引.
d. Make sure you have a multicolumn index on timestamp and the other columns in your SELECT statement. This is called a covering index.
e.在打开与 MySQL 的连接后，立即发出此 SQL 语句.它让 MySQL 可以在与其他 MySQL 客户端向表中插入行的争用更少的情况下获取您的数据.
e. Right after you open your connection to MySQL, issue this SQL statement. It lets MySQL fetch your data with less contention with other MySQL clients inserting rows to to the table.
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
如果您在用户的浏览器中使用 AJAX 执行此操作，您希望在 AJAX 请求中包含最新的时间戳，以便您可以向每个用户的浏览器提供所需的新项目.
If you're doing this with AJAX from your users' browsers, you want to include the latest timestamp in the AJAX requests so you can deliver new the needed new items to each user's browser.
这篇关于如何在PHP中不断刷新mysql查询?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
