How can I alert the user if a new line is inserted in the database

Question

I have a desktop application inserting values into a mysql table with fields id, keyword and timestamp

I have a php page which is kept open in my browser and lists all entries from that database.

What I want to do is to have this page automatically check if there's a new entry in the database and warn me. This warning must be a sound.

I consider an entry to be new if it's timestamp is less than 60 seconds the current timestamp.

So, I have the mysql query which does check for new entries, but my main question is, how to perform that query every 1 second without manually refreshing the page? And how to play a sound alarm when a new entry is found?

Thanks in advance.

Volkan Ulukut · Answer 1 · 2014-02-04T09:22:00.930

2

try using ajax get function with setTimeout to run indefinitly:

function checkDB()
{
    $.get("checkDB.php","",function(data) {
        if(data=="1") //if new entry is present
        {
            //use sound manager to play a sound file
        }
    });
    setTimeout("checkDB()",1000); // this will ensure this function to run every 1000 milliseconds
}

sound manager link

edited Feb 04 '14 at 09:22

answered Feb 03 '14 at 13:03

Volkan Ulukut

4,230
1
20
38

I get "Call to undefined function setTimeout()". – Marc.2377 Feb 04 '14 at 09:10

score 1 · Accepted Answer · answered Feb 04 '14 at 11:24

This is how I managed to do it:

Within the page's <head> node, the following code:

<script src="https://ajax.googleapis.com/ajax/libs/jquery/1.7.2/jquery.min.js"></script>

<script type="text/javascript">// <![CDATA[
$(document).ready(function() {
$.ajaxSetup({ cache: false }); // This part addresses an IE bug. without it, IE will only load the first number and will never refresh
setInterval(function() {
$('#divToRefresh').load('dbcheck.php');
}, 500); // refers to the time to refresh the div. it is in milliseconds.
});
// ]]></script>

Then, somewhere in the page:

<div id="divToRefresh">Running...</div>

And naturally, I've created a new php file called dbcheck.php containing which connects to the database and contains the following:

$sql3 = "SELECT keyword, timestamp FROM keywords ORDER BY id DESC LIMIT 1;";
$query3 = $pdo->prepare($sql3);
$query3->execute();
$result3 = $query3->fetch();

$timestampCheck = time() - 120;
if ($result3['timestamp'] >= $timestampCheck)
{
echo "<div>NEW KEYWORD FOUND!</div>\n\n";
echo "<div>", $result3['keyword'], "</div>";

echo "<audio autoplay=\"autoplay\">
<source src=\"alarm.wav\" type=\"audio/wav\">
Your browser does not support the audio element.
</audio>";

}
else
{
echo "Listening...";
}

score 0 · Answer 3 · edited May 23 '17 at 11:58

0

You might want to look into WebSockets or Long Polling. This is a good post that goes into some detail about the options you have. Here is a post that might be able to help with Long Polling.

edited May 23 '17 at 11:58

Community

1
1

answered Feb 03 '14 at 13:04

SeanWM

16,789
7
51
83

score 0 · Answer 4 · answered Feb 03 '14 at 13:05

0

U can use ajax for this and persistent connection to be faster as it leave the connection open

answered Feb 03 '14 at 13:05

Robert

2,342
2
24
41

How can I alert the user if a new line is inserted in the database

4 Answers4