PHP/AJAX: Automated MySQL query to manage button class

Question

I am very new to PHP and working on a user interface. This is for a personal circuit breaker project where wattage and breaker status (ON/OFF) is sent to a database.

What I'm attempting now is to make a live status button for the interface. Essentially, I want the button class/background color to change according to the status row in the database. (Status = 0 --> Red status button, Status = 1 --> Green status button).

What I need help with is the automatic query of the database for the status value every 2-3 seconds. I know how to query for the value I want to read using mysql/php and how to toggle the button class/background color using a Javascript function.

Should I be using AJAX for is automation process?

Here is the module I am playing with: JSFIDDLE

Here is the most recent source:

<!DOCTYPE html>
<html>
<head>
<script src= "https://ajax.googleapis.com/ajax/libs/jquery/3.1.1/jquery.min.js">
</script>
<script>
$(document).ready(function(){
  $("button").click(function(){
    $(this).toggleClass("off");
  });
});
</script>
<style>
div{
  float: left;
  color:#fff;
  font-size:40px;
}

h2{
  text-align: center;
}

.wrapper{
  position: absolute;
  top: 25%;
  left: 10%;
  margin-right: -50%;
  transform: translate(-50%, -50%);
}

.one{
  width: 125px;
  height:100px;
}

.two{
  width: 120px;
  height:75px;
  background:darkblue;
}

.three{
  width:120px;
  height:75px;
  background:blue;
}

.button{
  background-color: #4CAF50; /* Green */
  border: 1px solid green;
  color: white;
  text-align: center;
  text-decoration: none;
  display: inline-block;
  font-size: 16px;
  cursor: pointer;
  float: left;
  position: relative;
  left: 15%;
}

.button2{
  background-color: #4CAF50; /* Green */
  border: 1px solid green;
  color: white;
  text-align: center;
  text-decoration: none;
  display: inline-block;
  font-size: 16px;
  cursor: pointer;
  float: left;
  position: relative;
  left: 15%;
}

.off {
  background: red;
}

.button:hover {
  background-color: #3e8e41;
}

.button2:hover {
  background-color: #3e8e41;
}

</head>
</style>

<?php
$query ="SELECT status FROM ICBP.radio3 ORDER BY time DESC LIMIT 1;";
$result = mysql_query($query);
$status = mysql_fetch_row($result);
if($status = 0){
  echo "off";
}
else if ($status = 1) {
  echo "on";
}

?>

<body>
<h2>ICBP Dashboard</h2>
<div class="wrapper">
<div class="one">
  <div class="two">
  <a href="index3.php"><button class="button">Breaker 1</button></a>
  <button class="button2">Status</button>
  </div>
  <div class="three">
  <a href="index3.php"><button class="button">Breaker 2</button></a>
  <button class="button2">Status</button>
  </div>
</div>

<div class="one">
  <div class="two">
  <a href="index3.php"><button class="button">Breaker 3</button></a>
  <button class="<?php echo $status ?>">Status</button>
  </div>
  <div class="three">
  <a href="index3.php"><button class="button">Breaker 4</button></a>
  </div>
</div>
</div>
</body>
</html>

Any guidance regarding my approach is very appreciated.

Thank you!

score 0 · Accepted Answer · edited May 23 '17 at 10:30

Yes, I would fire an AJAX request to a PHP script that queries the database and returns the current status (with contents similar to what you've got there between your PHP tags).

The request would need to be triggered from setInterval function: https://developer.mozilla.org/en-US/docs/Web/API/WindowTimers/setInterval

EDIT

To expand on the answer... I would divide the code into separate scripts like so:

HTML

<!DOCTYPE html>
<html>
    <head>
        <meta charset="UTF-8">

        <link rel="stylesheet" href="styles.css" type="text/css">
    </head>
    <body>
        <button class="btn-grey">&nbsp;</button>

        <!-- loading jQuery here as I'm using it within script.js -->
        <script
            src="https://code.jquery.com/jquery-2.2.4.min.js"
            integrity="sha256-BbhdlvQf/xTY9gja0Dq3HiwQF8LaCRTXxZKRutelT44="
        crossorigin="anonymous"></script>
        <script type="text/javascript" src="script.js"></script>
    </body>
</html>

styles.css

.btn-green {
    background-color: green;
}

.btn-red {
    background-color: red;
}

.btn-grey {
    background-color: grey;
}

status.php

<?php

// perform the query
// echo out the status depending on the result of the query:
echo 1;

script.js (modified to your needs version of the code here: https://stackoverflow.com/a/5052661/1075071)

// this function is going to be called on page load and then in 5s intervals
(function worker() {
    // fire AJAX request to status.php 
    $.ajax({
        url: 'status.php',
        success: function (data) {
            // process the data received from the PHP script
            if (data === '1') {
                $('button').removeClass();
                $('button').addClass('btn-green');
            } else if (data === '0') {
                $('button').removeClass();
                $('button').addClass('btn-red');
            } else {
                $('button').removeClass();
                $('button').addClass('btn-grey');
            }
        },
        error: function () {
            $('button').removeClass();
            $('button').addClass('btn-grey');
        },
        complete: function () {
            // Schedule the next request when the current one's complete
            setTimeout(worker, 5000);
        }
    });
})();

So the whole process would be something like: ajax request -> php query -> output string based on condition. Would you clarify if I am on the right track in calling the php script in the HTML body? Thank you. — giri, Nov 14 '16 at 23:16
@giri please check my edited answer - this is a working skeleton of the whole app. What you need to do now is stick required logic into the PHP script and you should be done :) — ad_amin, Dec 04 '16 at 18:34

PHP/AJAX: Automated MySQL query to manage button class

1 Answers1