有没有一种方法来'听'一个数据库事件，并实时更新的网页？ [英] Is there a way to 'listen' for a database event and update a page in real time?
问题描述
我在寻找一种方式来创建一个简单的HTML表，可以实时在数据库中更改事件进行更新;特别是一个新的记录添加。
在换句话说，想起来像一个执行仪表板。如果销售是由一个新的产品线可以在数据库（MySQL的在我的情况），然后加入网页应刷新表与新行。
我看到的新的使用 EVENT GATEWAY ，但所有的例子都使用ColdFusion作为的推进器，而不是消费者的一些信息。我想有两个的Coldfusion更新/推送事件到网关，也消耗了回应。
如果这可以使用AJAX和CF的组合来实现，请让我知道！
我真的只是希望了解从哪里开始使用实时更新。
感谢你在前进！
选择答案修改/说明：
我结束了@ bpeterson76的答案，因为目前它是最容易实现的小规模去。我真的很喜欢他的数据表的建议，而我使用的近实时更新什么的。
由于我的网站变大，但（希望），我不知道这是否是一个可扩展的解决方案，因为每个用户都会被击中监听器页面，随后查询我的数据库。我的查询是比较简单的，但我仍然担心，在未来的表现。
但在我看来，作为HTML5开始成为网络标准，网络套接字方法，通过@iKnowKungFoo建议很可能是最好的办法。彗星与长轮询也是一个伟大的想法，但它是一个有点麻烦实行/似乎也有一定比例的问题。
所以，让我们希望互联网用户开始采取支持HTML5更现代的浏览器，因为网络套接字是一个相对容易的，可扩展的方式获得接近实时。
如果你觉得我做出了错误的决定，请发表评论。
最后，这里是一些源$ C $下这一切：
JavaScript的：
注意，这是一个非常简单的实现。它只是想看看是否记录在当前数据表的数量发生了变化，如果是更新表，并引发警报。生产code是更长的时间，更多地参与。这只是显示出越来越紧密实时更新的简单方法。
＆LT;脚本SRC =http://ajax.googleapis.com/ajax/libs/jquery/1.6.1/jquery.js＆GT;＆LT; / SCRIPT＆GT;
＆LT;脚本类型=文/ JavaScript的字符集=utf-8＆GT;
变种originalNumberOfRecsInDatatable = 0;
VAR oTable;
VAR setChecker = setInterval的（checkIfNewRecordHasBeenAdded，5000）; //每隔5秒
传播checkIfNewRecordHasBeenAdded（）{
// JSON对象张贴到CFM页面
VAR POSTDATA = {
numberOfRecords：originalNumberOfRecsInDatatable
};
VAR ajaxResponse = $阿贾克斯（{
类型：后，
网址：./tabs/checkIfNewItemIsAvailable.cfm
的contentType：应用/ JSON
数据：JSON.stringify（POSTDATA）
}）
//当响应回来，如果有可用更新
//然后重新绘制数据表，并引发警报的用户
ajaxResponse.then（
功能（apiResponse）{
VAR OBJ = jQuery.parseJSON（apiResponse）;
如果（obj.isUpdateAvail ==是）
{
。oTable = $（'＃MY_DATATABLE_ID）的dataTable（）;
oTable.fnDraw（假）;
originalNumberOfRecsInDatatable = obj.recordcount;
警报（新行已添加！）;
}
}
）;
}
＆LT; / SCRIPT＆GT;
ColdFusion的：
＆LT; CFSET requestBody =的toString（getHtt prequestData（）的内容。）/＆GT;
＆LT;！---仔细检查，以确保它是一个JSON值。 ---＆GT;
＆LT; CFIF isJSON（requestBody）＆GT;
＆LT; CFSET deserializedResult = deserializeJSON（requestBody）＆GT;
＆LT; CFSET numberOFRecords =＃deserializedResult.originalNumberOfRecsInDatatable＃＆GT;
＆LT; CFQUERY NAME =qCount数据源=＃Application.DBdsn＃用户名=＃Application.DBusername＃密码=＃Application.DBpw＃＆GT;
SELECT COUNT（ID）的总
FROM mytable的
＆LT; / CFQUERY＆GT;
＆LT; CFIF＃qCount.total＃NEQ＃variables.originalNumberOfRecsInDatatable＃＆GT;
{isUpdateAvail：是的，的RecordCount：其中，CFOUTPUT＆GT;＃qCount.total＃＆LT; / CFOUTPUT＆GT;}
＆LT; CFELSE＆GT;
{isUpdateAvail：否}
＆LT; / CFIF＆GT;
＆LT; / CFIF＆GT;
这是不是太困难。最简单的方法是通过.append补充：
$（'#table＆GT; TBODY：最后'）追加（'＆LT; TR ID =ID＆GT;＆LT; TD＆GT;东西＆LT; / TD＆GT;＆LT; / TR＆GT;'）;
添加元素的实时不是完全可能的。你必须运行一个Ajax查询更新一个循环来抓的转变。所以，不能完全实时的，但非常，非常接近。您的用户真的不会注意到其中的差别，虽然你的服务器的负载可能。
但是，如果你要获得更多的参与，我建议在看数据表。它为您提供了不少新的功能，包括排序，分页，过滤，限制，搜索和AJAX加载。从那里，你既可以通过AJAX添加元素并刷新表视图，或者干脆通过其API追加上。我一直在使用我的应用程序一段时间的数据表，现在，他们已经一直被评论为数字1的功能，使数据的巨大量可用。
- 编辑 -
由于不是很明显，更新您的数据表打电话给一个变量，你调用设置的数据表：
VAR oTable = $（'＃选择'）的dataTable（）;
然后运行该做的更新：
oTable.fnDraw（假）;
I'm looking for a way to create a simple HTML table that can be updated in real-time upon a database change event; specifically a new record added.
In other words, think of it like an executive dashboard. If a sale is made and a new line is added in a database (MySQL in my case) then the web page should "refresh" the table with the new line.
I have seen some information on the new using EVENT GATEWAY but all of the examples use Coldfusion as the "pusher" and not the "consumer". I would like to have Coldfusion both update / push an event to the gateway and also consume the response.
If this can be done using a combination of AJAX and CF please let me know!
I'm really just looking to understand where to get started with real-time updating.
Thank you in advance!!
EDIT / Explanation of selected answer:
I ended up going with @bpeterson76's answer because at the moment it was easiest to implement on a small scale. I really like his Datatables suggestion, and that's what I am using to update in close to real time.
As my site gets larger though (hopefully), I'm not sure if this will be a scalable solution as every user will be hitting a "listener" page and then subsequently querying my DB. My query is relatively simple, but I'm still worried about performance in the future.
In my opinion though, as HTML5 starts to become the web standard, the Web Sockets method suggested by @iKnowKungFoo is most likely the best approach. Comet with long polling is also a great idea, but it's a little cumbersome to implement / also seems to have some scaling issues.
So, let's hope web users start to adopt more modern browsers that support HTML5, because Web Sockets is a relatively easy and scalable way to get close to real time.
If you feel that I made the wrong decision please leave a comment.
Finally, here is some source code for it all:
Javascript:
note, this is a very simple implementation. It's only looking to see if the number of records in the current datatable has changed and if so update the table and throw an alert. The production code is much longer and more involved. This is just showing a simple way of getting a close to real-time update.
<script src="http://ajax.googleapis.com/ajax/libs/jquery/1.6.1/jquery.js"></script>
<script type="text/javascript" charset="utf-8">
var originalNumberOfRecsInDatatable = 0;
var oTable;
var setChecker = setInterval(checkIfNewRecordHasBeenAdded,5000); //5 second intervals
function checkIfNewRecordHasBeenAdded() {
//json object to post to CFM page
var postData = {
numberOfRecords: originalNumberOfRecsInDatatable
};
var ajaxResponse = $.ajax({
type: "post",
url: "./tabs/checkIfNewItemIsAvailable.cfm",
contentType: "application/json",
data: JSON.stringify( postData )
})
// When the response comes back, if update is available
//then re-draw the datatable and throw an alert to the user
ajaxResponse.then(
function( apiResponse ){
var obj = jQuery.parseJSON(apiResponse);
if (obj.isUpdateAvail == "Yes")
{
oTable = $('#MY_DATATABLE_ID').dataTable();
oTable.fnDraw(false);
originalNumberOfRecsInDatatable = obj.recordcount;
alert('A new line has been added!');
}
}
);
}
</script>
Coldfusion:
<cfset requestBody = toString( getHttpRequestData().content ) />
<!--- Double-check to make sure it's a JSON value. --->
<cfif isJSON( requestBody )>
<cfset deserializedResult = deserializeJSON( requestBody )>
<cfset numberOFRecords = #deserializedResult.originalNumberOfRecsInDatatable#>
<cfquery name="qCount" datasource="#Application.DBdsn#" username="#Application.DBusername#" password="#Application.DBpw#">
SELECT COUNT(ID) as total
FROM myTable
</cfquery>
<cfif #qCount.total# neq #variables.originalNumberOfRecsInDatatable#>
{"isUpdateAvail": "Yes", "recordcount": <cfoutput>#qCount.total#</cfoutput>}
<cfelse>
{"isUpdateAvail": "No"}
</cfif>
</cfif>
This isn't too difficult. The simple way would be to add via .append:
$( '#table > tbody:last').append('<tr id="id"><td>stuff</td></tr>');
Adding elements real-time isn't entirely possible. You'd have to run an Ajax query that updates in a loop to "catch" the change. So, not totally real-time, but very, very close to it. Your user really wouldn't notice the difference, though your server's load might.
But if you're going to get more involved, I'd suggest looking at DataTables. It gives you quite a few new features, including sorting, paging, filtering, limiting, searching, and ajax loading. From there, you could either add an element via ajax and refresh the table view, or simply append on via its API. I've been using DataTables in my app for some time now and they've been consistently cited as the number 1 feature that makes the immense amount of data usable.
--Edit --
Because it isn't obvious, to update the DataTable you call set your Datatables call to a variable:
var oTable = $('#selector').dataTable();
Then run this to do the update:
oTable.fnDraw(false);
这篇关于有没有一种方法来'听'一个数据库事件，并实时更新的网页？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
