Play a sound file when new data has been added to mysql table

Question

I want the browser to play a sound file when a new table row has been added to database.

This is my php file for the table. I'm using ajax for refresh.

I'm thinking that Javascript is going to be my solution. Do you have any ideas how to accomplish this?

show_table.php

<script src="ajax2.js"></script>
<script type="text/javascript"><!--
refreshdiv();
// --></script>
<div id="timediv"></br>

ajax2.js

var seconds = 1;
var divid = "timediv";
var url = "print_table.php";

function refreshdiv(){


var xmlHttp;
try{
xmlHttp=new XMLHttpRequest(); // Firefox, Opera 8.0+, Safari
}
catch (e){
try{
xmlHttp=new ActiveXObject("Msxml2.XMLHTTP"); // Internet Explorer
}
catch (e){
try{
xmlHttp=new ActiveXObject("Microsoft.XMLHTTP");
}
catch (e){
alert("Your browser does not support AJAX.");
return false;
}
}
}



fetch_unix_timestamp = function()
{
return parseInt(new Date().getTime().toString().substring(0, 10))
}

var timestamp = fetch_unix_timestamp();
var nocacheurl = url+"?t="+timestamp;



xmlHttp.onreadystatechange=function(){
if(xmlHttp.readyState==4){
document.getElementById(divid).innerHTML=xmlHttp.responseText;
setTimeout('refreshdiv()',seconds*1000);
}
}
xmlHttp.open("GET",nocacheurl,true);
xmlHttp.send(null);
}



var seconds;
window.onload = function startrefresh(){
setTimeout('refreshdiv()',seconds*1000);
}

print_table.php

$query = "SELECT * FROM $tablename ORDER BY time DESC LIMIT 50";
$result = mysql_query($query);


echo "<table class='gridtable'>
<tr>
<th>Time</th>
<th>Department</th>
<th>Priority</th>
<th>Destination</th>
<th>Comment</th>
<th>Status</th>
<th>Staff</th>
<th>Confirm</th>
</tr>";

while($row = mysql_fetch_array($result, MYSQL_ASSOC))
{

echo "<tr>";
echo "<td>" . $row['time'] . "</td>";
echo "<td>" . $row['department'] . "</td>";
echo "<td>" . $row['priority'] . "</td>";
echo "<td>" . $row['destination'] . "</td>";
echo "<td>" . $row['comment'] . "</td>";
echo "<td>" . $row['status'] . "</td>";
echo "<td>" . $row['staff'] . "</td>";
echo "<td><a href=\"edit3.php?id=".$row['id']."&status=app\">Confirm</a></td>";
echo "</tr>";
}
echo "</table>";
?>

Answer 1

The Easiest Way

(Read-on below, I break this down)

HTML:

<!-- Include this in the HTML, if we wanted to play a flute sound -->
<audio id="audiotag1" src="audio/flute_c_long_01.wav" preload="auto"></audio>  

Javascript:

// Replace: document.getElementById(divid).innerHTML=xmlHttp.responseText; with:
if(document.getElementById(divid).innerHTML != xmlHttp.responseText)
{
    // Moving this line inside since there is no reason to update if the content is the same.
    document.getElementById(divid).innerHTML=xmlHttp.responseText;

    // Play sound
    document.getElementById('audiotag1').play();
}

How to see if the table has changed:

If you just wanted to compare the HTML of the current to what is being returned, you could simply check the current contents of the table against the new table response. As an example, you could use something like this before the recursive timed-call to refreshdiv().

// If the table within divid ("timediv") is different, play an alert sound
if(document.getElementById(divid).innerHTML != xmlHttp.responseText)
{
    // Play the sound here, the tables are different
}

However, this may not actually answer your question since you specifically asked for adding rows. The above would cover this, but would also register updates to existing rows as well.

You have the lines:

parseInt(new Date().getTime().toString().substring(0, 10))

and

var nocacheurl = url+"?t="+timestamp;

The first line you provided is outputting the current time in seconds since the epoch, using the timezone and date from the client. You are already passing this value per the query string which you can GET in the PHP with $_GET['t'] (second line). You could, in theory, rewrite quite a bit of your PHP code to return only new rows put into the database since that time and the last time polled. By returning only new rows, you could simply check to see if new tags are being sent, and incorporate them accordingly.

Two points of note if you do this:

• Make sure to escape or parameterize your query if you pass the query-string through your MySQL query.
• Be cautious of dates you are passing, since they are calculated based on the client's date-time. Consider relying on the server side time if possible.

And as for playing the sound

There is some detail on how to play sounds here: Playing audio with Javascript?

To play the audio, the easiest way is to use the audio tag (HTML5). The example given on http://www.storiesinflight.com/html5/audio.html is:

<audio id="audiotag1" src="audio/flute_c_long_01.wav" preload="auto"></audio>

Then you will need to call this audio by getting audio element and calling the play() method on it. So:

document.getElementById('audiotag1').play();