I've recently finished a prototype for a little Raspberry Pi website. The main page of the site displays current users found in the room (through bluetooth). I wanted this list updated regularly from data in a MySQL table, so no refresh is needed. When someone walks into or out of the room, the webpage shows almost instantly.
This is the solution I created:
index.html
<html>
<head>
<!-- This page uses jQuery to insert PHP files into HTML divs -->
</head>
<body>
<div class="list-group">
<a href="#" class="list-group-item active">
<h4 class="list-group-item-heading"><u>Present:</u></h4>
<div class="list-group" id="list1">
<!-- php will be injected here, and it will create html -->
</div>
</a>
</div>
<div class="list-group">
<a href="#" class="list-group-item active">
<h4 class="list-group-item-heading"><u>Absent:</u></h4>
<div class="list-group" id="list2">
<!-- php will be injected here, and it will create html -->
</div>
</a>
</div>
</body>
<script>
<!-- references :) -->
<script src="https://ajax.googleapis.com/ajax/libs/jquery/2.1.3/jquery.min.js"></script>
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.5/css/bootstrap.min.css">
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.5/js/bootstrap.min.js"></script>
var timer = setInterval(listLoad, 1000);
<!-- Every 1s this function is called... -->
function listLoad(){
$(function(){
$("#list1").load("herelist.php");
$("#list2").load("notherelist.php");
});
}
</script>
</html>
herelist.php
<!DOCTYPE html>
<html>
<body>
<?php
$servername = "localhost";
$username = "xxxx";
$password = "xxxx";
$dbname = "xxxx";
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
$sql = "SELECT firstname, lastname FROM room_Data WHERE attendance = 1";
$result = $conn->query($sql);
if ($result->num_rows > 0) {
// output data of each row
echo("<div id=\"list1\">")
while($row = $result->fetch_assoc()) {
echo ("<li><h4 class=\"list-group-item-heading\">". $row["firstname"]. " " . $row["lastname"] . "</h4></li>");
}
echo("</div>");
} else {
echo ("");
}
$conn->close();
?>
</body>
</html>
notherelist.php
Same as herelist.php, just the where
clause is = 0
instead of = 1
This project isn't for some major production scale, which is why I don't really mind hitting my MySQL server every second requesting a read. I actually like this solution a lot since it was my first time ever injecting PHP through jQuery and I thought it was a neat idea.
Is this a good solution for a personal project/school project? Or, is there something I should look into to improve this?
room_data
get updated? Do you have control over that as well? \$\endgroup\$