I have a code that works like this: it takes the information live from the database and displays it in a table, every change made in the database is displayed instantly in the table, now I want that when every new record which is added to the database, after it is displayed in the table, a pop-up with sound will be displayed to notify that a new record has been added to the database.
fetch.php
<?php
error_reporting(0);
// make is suitable for SSE
header("Cache-Control: no-store");
header("Content-Type: text/event-stream");
// make connection with database
include("db_connection.php");
// lets continue to check data in database with loop
$p = '';
while(true){
// now fetch data from database
$result = $con->query("SELECT * FROM data");
$r = array();
if($result->num_rows > 0){
while($row = $result-> fetch_assoc()){
// get all data in json from
$r[] = $row;
}
}
$n = json_encode($r);
if(strcmp($p, $n) !== 0){
// here data will shown on change
echo "data:" . $n . "\n\n";
$p = $n;
}
// here data is shown each time
// but we need data when change
// mean when data add, update or delete then show only
// this will show data even the loading is not completed
ob_end_flush();
flush();
// sleep process for 1 sec
sleep(1);
// but still data will not show
}
?>
index.html
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta http-equiv="X-UA-Compatible" content="IE=edge">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>Realtime Fetching From Normal Database</title>
<style>
.container{
max-width: 900px;
font-family: sans-serif;
margin: auto;
}
h1{
text-align: center;
margin-bottom: 30px;
}
table{
width: 100%;
border-spacing: 0px;
}
table th,
table td{
padding: 15px 0px;
border-bottom: 1px solid #cacaca;
text-align: left;
}
td:last-child{
width: 600px;
}
</style>
</head>
<body>
<!-- create table to show data -->
<div class="container">
<h1>Realtime fetching From Normal Database</h1>
<table>
<thead>
<tr>
<th>ID</th>
<th>Name</th>
<th>Message</th>
</tr>
</thead>
<tbody>
</tbody>
</table>
</div>
<!-- We will use SSE in JS to get event from server -->
<!-- for best performance in realtime fetching -->
<script>
var source = new EventSource("fetch.php");
source.onmessage = function (event) {
var arrayData = JSON.parse(event.data);
var dataContainer = document.querySelector('tbody')
dataContainer.innerHTML = ''
arrayData.forEach(e => {
dataContainer.innerHTML += `
<tr>
<td>${e.id}</td>
<td>${e.name}</td>
<td>${e.message}</td>
</tr>
`;
});
}
</script>
</body>
</html>
db_connection.php
<?php
$server = "localhost";
$user = "root";
$pass = "";
// create database first
$database = "yt_test";
$con = mysqli_connect($server, $user, $pass);
if(!$con){
echo 'Server not connected';
}
$db = mysqli_select_db($con, $database);
if(!$db){
echo 'Database not connected';
}
?>
To pop-up a message together with playing a sound (e.g. mp3 file), you can use JS to trigger the popup by SweetAlert2 (autoclose in 2 seconds) and use HTML play() (e.g. audio.play()) of a mp3 file. (remember to place the mp3 file in the same directory of your scripts)
As an example, I will put the above into one function as playsoundwithpopupautoclose()
So, the code will be:
<script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.2.1/jquery.min.js"></script>
<!-- SweetAlert2 -->
<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/limonte-sweetalert2/7.2.0/sweetalert2.min.css">
<script src="https://cdnjs.cloudflare.com/ajax/libs/limonte-sweetalert2/7.2.0/sweetalert2.all.min.js"></script>
<script>
var audio = new Audio('sound1.mp3');
</script>
<script>
function playsoundwithpopupautoclose() {
audio.play();
swal({
title: "Record Added!",
text: "A New Record has been added !",
type: "",
showConfirmButton: false, //hide OK button
allowOutsideClick: false,
timer: 2000
});
}
</script>
To trigger the above, you can place the playsoundwithpopupautoclose() function say at the end of source.onmessage block, so change
<script>
var source = new EventSource("fetch.php");
source.onmessage = function (event) {
var arrayData = JSON.parse(event.data);
var dataContainer = document.querySelector('tbody')
dataContainer.innerHTML = ''
arrayData.forEach(e => {
dataContainer.innerHTML += `
<tr>
<td>${e.id}</td>
<td>${e.name}</td>
<td>${e.message}</td>
</tr>
`;
});
}
</script>
to
<script>
var source = new EventSource("fetch.php");
source.onmessage = function (event) {
var arrayData = JSON.parse(event.data);
var dataContainer = document.querySelector('tbody')
dataContainer.innerHTML = ''
arrayData.forEach(e => {
dataContainer.innerHTML += `
<tr>
<td>${e.id}</td>
<td>${e.name}</td>
<td>${e.message}</td>
</tr>
`;
});
playsoundwithpopupautoclose();
}
</script>
Last but not least, please make sure that you have turned on the speaker on the client machine. Enjoy.