I have the trouble to find is any new record is inserted in table.
I am developing Penny auction site. In my site, I'm updating client side view every second using JQuery ajax.last bidder username, avater, price and many things were update.But when the bid is placed I want to apply flash effect for particular product live here. I tried the following Mysql query to find if any last bid is placed.
SELECT CASE WHEN COUNT(*)=0 THEN 'N' WHEN COUNT(*) > '0' THEN 'Y'
END AS is_bid FROM table_bid_history WHERE product_id = '$value' AND
( date_time BETWEEN DATE_SUB(NOW(), INTERVAL 1 SECOND) AND NOW() )
Basically, I'm storing last bidder details in table_bid_history table with time stamp column date_time. I'm sending ajax request every second to server side to get last update using recursive function. So my idea was minus 1 second checks correct time so I used ( date_time BETWEEN DATE_SUB(NOW(), INTERVAL 1 SECOND) AND NOW() ) and it Works. But in some case, delayed ajax polling makes this idea false, which means some time ajax request takes more than 1 second so I'm unable to get correct result from server. If I make INTERVAL 2 SECOND, then it gives Y for two times. So I'm confused what to do in this situation.
Please help me to get last activity in table. How they doing it. Any idea would be appreciated !
Thanks in advance
Instead of querying whether there was a bid in the last second, query if there was any new bid since the last known bid. So, in your jQuery code, store the date_time of the last bid that you know of. Then, though AJAX, see if there are any bids in your table_bid_history that have a date_time newer than the date_time stored in your jQuery script. If there are, flash for effect (and of course, store the newer date_time for more querying later on).
PS: You may want to look into long polling. That may be better on resources than frantically re-checking every second.