5
\$\begingroup\$

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?

\$\endgroup\$
5
  • \$\begingroup\$ Welcome to Code Review! Is there any area in particular you'd like focused on in reviews or do you just want general feedback? \$\endgroup\$ Oct 14, 2015 at 13:22
  • 2
    \$\begingroup\$ @SuperBiasedMan Thanks! General feedback would be nice.. I'm a student so I would like to know the good and the bad about my solution :) \$\endgroup\$ Oct 14, 2015 at 13:25
  • \$\begingroup\$ How does the room_data get updated? Do you have control over that as well? \$\endgroup\$
    – hjpotter92
    Oct 14, 2015 at 16:00
  • \$\begingroup\$ @hjpotter92 yes, that table is updated local to the server. It is updated through a Python program which identifies bluetooth addresses from bluetooth devices discovered by the Pi. \$\endgroup\$ Oct 14, 2015 at 16:05
  • \$\begingroup\$ Why is there a doctype in your PHP pages, if you're injecting it inside a div? There's just no need \$\endgroup\$ Oct 16, 2015 at 10:27

1 Answer 1

3
\$\begingroup\$

Personally, I hate having a setInterval in JS code, especially to fetch live updates. I hate it even more when it is used for hitting the server for a database read/write operation, irrespective the size of project.

The code you have is quite good, considering that it was your first time. There are quite a few suggestions though. Read on:

  1. Instead of having 2 separate files to fetch data of attendance = 0 and attendance = 1, use a single file with a parameter passed via a GET or POST request.
  2. Instead of dumping the entire data as HTML, I'd suggest outputting the results as JSON so that it might be of use to other applications, without having to resort to HTML parsers. This helps if you think/plan on providing an API for other users to develop on.
  3. Since the data for room_data gets updated with an underlying python application, you can modify it to write the output to a static JSON file and hit this JSON content instead of executing a MySQL query every second. This will help as the browser will get a 304 response status from the server if the JSON was not updated since last fetch. Caching FTW ^_^
  4. Put the external script/stylesheets in head.
  5. Since all you need for the MySQL to return is concatenated name string, do so in MySQL itself:

    SELECT CONCAT(firstname, ' ', lastname) AS 'name'
    FROM room_Data
    WHERE attendance = :something
    
  6. Do not use h4 tags for list items.

If you follow (1) above, you won't need the (3). I strongly recommend using (3) though.

\$\endgroup\$
1
  • 1
    \$\begingroup\$ Thanks! Using JSON sounds like I can expand the project, and also get better efficiency out of what I already have. That should be a good goal for version 2.0! I have to admit my use of heading tags was very lazy of me haha, good catch. \$\endgroup\$ Oct 14, 2015 at 16:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.