I tried to modified the long polling code to fit my situation. I would like to count the total number from mysql and if the number changes (increase or decrease), it will notify me.
The problem is the while loop keep looping. How can I stop it? I just need to have notification whenever the total sum change.
<?php
include("db.php");
$result = mysql_query("SELECT sum( r ) as totalsum FROM (SELECT colA, colB, COUNT( * ) AS r FROM product GROUP BY productid ) AS t");
while($row = mysql_fetch_array($result))
{
$old_totalsum = $row['totalsum'];
}
?>
<html>
<head>
<script src="http://code.jquery.com/jquery-latest.min.js" type="text/javascript"></script>
<script type="text/javascript" charset="utf-8">
var old_totalsum =<?php echo $old_totalsum; ?>;
function waitForMsg(){
$.ajax({
type: "GET",
url: "poll.php?old_totalsum=" + old_totalsum,
async: true,
cache: false,
success: function(data){
var json = "eval(+(" + data + ")+)";
if(json['msg'] != "") {
alert(data);
}
old_msg_id = json['old_msg_id'];
setTimeout('waitForMsg()',1000);
},
error: function(XMLHttpRequest, textStatus, errorThrown){
alert("error: " + textStatus + " (" + errorThrown + ")");
setTimeout('waitForMsg()',15000);
}
}); // end ajax
} //end waitformsg
$(document).ready(function(){
waitForMsg();
});
</script>
</head>
<body>
<div id="chat">
</div>
</body>
</html>
<----------------------------------------------------------->
poll.php
<----------------------------------------------------------->
<?php
include("db.php");
$old_totalsum = $_GET['old_totalsum'];
$result = mysql_query("SELECT sum( r ) as totalsum FROM (SELECT colA, colB, COUNT( * ) AS r FROM product GROUP BY productid ) AS t");
while($row = mysql_fetch_array($result))
{
$last_sum = $row['totalsum'];
}
while($last_sum < $old_totalsum || $last_sum > $old_totalsum)
while($last_sum <= $old_totalsum)
{
usleep(1000);
clearstatcache();
$result = mysql_query("SELECT sum( r ) as totalsum FROM (SELECT colA, colB, COUNT( * ) AS r FROM product GROUP BY productid ) AS t");
while($row = mysql_fetch_array($result))
{
$last_sum = $row['totalsum'];
//echo $last_sum;
//return;
}
}
$response = array();
$response['msg'] = 'new';
$response['old_msg_id'] = $last_sum;
echo json_encode($response);
?>
<------------------------------------------->
I expected logic like this:
$latest_sum= -1;
// Loop until sum from DB is different from what was passed in
while( $latest_sum == -1 || $latest_sum!= $old_sum ){
$result = mysql_query("SELECT sum( r ) as totalsum FROM (SELECT colA, colB, COUNT( * ) AS r FROM product GROUP BY productid ) AS t");
while($row = mysql_fetch_array($result))
{
$lastest_sum = $row['totalsum'];
}
}
$response = array();
$response['msg'] = 'new';
$response['old_msg_id'] = $latest_sum;
echo json_encode($response);
If it takes a long time to change, then Apache (or whatever is running PHP) will likely cancel the request. This is also going to run lots of queries on the db which is expensive.
So it would be better to have your loop in the javascript. It would call a function that would just return the latest sum from the database. The javascript should wait a second or more inbetween each request.
If performance is still too hard on the database, then the php should somehow cache the value every minute and return the cached value.