Updating HTML table from SQL server repeadetly

Question

I have an HTML page with a table in it which I want to update it's values every 2 seconds with the values stored in the MySQL database.

setInterval(updateSensorsTable, 2000);
setInterval(updatePower, 2000);

function showValue(value) {
  document.getElementById("range").innerHTML = value;
}

function TurnLedOn() {
  //TODO: need to set the led state and update the sql server
  document.getElementById("ledStatus").src = "/LedOn.png";
}

function TurnLedOff() {
  //TODO: need to set the led state and update the sql server
  document.getElementById("ledStatus").src = "/LedOff.png";
}

function AjaxCaller() {
  var xmlhttp = false;
  try {
    xmlhttp = new ActiveXObject("Msxml2.XMLHTTP");
  } catch (e) {
    try {
      xmlhttp = new ActiveXObject("Microsoft.XMLHTTP");
    } catch (E) {
      xmlhttp = false;
    }
  }

  if (!xmlhttp && typeof XMLHttpRequest != 'undefined') {
    xmlhttp = new XMLHttpRequest();
  }
  return xmlhttp;
}

function callPage(url, div) {
  ajax = AjaxCaller();
  ajax.open("GET", url, true);
  ajax.onreadystatechange = function() {
    if (ajax.readyState == 4) {
      if (ajax.status == 200) {
        div.innerHTML = ajax.responseText;
      }
    }
  }
  ajax.send(null);
}

function updateSensorsTable() {
  for (i = 0; i <= 7; i++)
    callPage('/getVarFromDB.php?offset=' + i, document.getElementById(i));
}

function updatePower() {
  document.getElementById("powerValue").innerHTML = '200';
}

<!DOCTYPE html>
<html lang="en">

<head>
  <meta charset="utf-8" />
  <meta name="viewport" content="width=device-width,height=device-height,initial-scale=1.0" />
  <title>SmartLight</title>
</head>

<body bgcolor="#E6E6FA" onload='updateSensorsTable()'>
  <br></br>
  <table class="sensorsTable" align="center">
    <thead>
      <tr>
        <th>Sensor</th>
        <th>Value</th>
      </tr>
    </thead>
    <tbody>
      <tr>
        <td align="left">GPS</td>
        <td align="center" id="0">0</td>
      </tr>
      <tr>
        <td align="left">Temperature</td>
        <td align="center" id="1">0</td>
      </tr>
      <tr>
        <td align="left">Pressure</td>
        <td align="center" id="2">0</td>
      </tr>
      <tr>
        <td align="left">Light</td>
        <td align="center" id="3">0</td>
      </tr>
      <tr>
        <td align="left">Altitude</td>
        <td align="center" id="4">0</td>
      </tr>
      <tr>
        <td align="left">Accelerometer</td>
        <td align="center" id="5">0</td>
      </tr>
      <tr>
        <td align="left">Humidity</td>
        <td align="center" id="6">0</td>
      </tr>
      <tr>
        <td align="left">Camera</td>
        <td align="center" id="7">0</td>
      </tr>
    </tbody>
  </table>

  <br></br>

  <table class="ledTable" align="center">
    <tr>
      <td align="center">
        <input type="image" src="/TurnOn.png" id="turnOn" width="60" height="60" onclick='TurnLedOn()'>
      </td>
      <td align="center">
        <input type="image" src="/TurnOff.png" id="turnOff" width="60" height="60" onclick='TurnLedOff()'>
      </td>
      <td align="center">
        <img src="/LedOn.png" style="width:60px;height:60px" id="ledStatus">
      </td>
    </tr>
    <tr>
      <td align="center" id="ledOnButton">LED On</td>
      <td align="center" id="ledOffButton">LED Off</td>
      <td align="center">LED Status</td>
    </tr>
  </table>

  <div align="center">
    Brightness:
    <input type="range" name="brightness" min="0" max="100" value="0" step="1" onchange="showValue(this.value)" />
    <span id="range">0</span>
    <table align="center" class="power">
      <tr>
        <td align="center" id="powerValue">0</td>
        <td align="left">mW</td>
      </tr>
    </table>
    <div align="center">LED Power Meter</div>
  </div>
</body>
</html>

Here is the php code:

<?php
include("connect_to_mysql.php");

$result = mysql_query("SELECT value FROM sens" );

echo mysql_result($result,$offset);

Please help me to do this with the correct way. This code doesn't work when I use the for loop. Using this code with a direct assigment e.g callPage('/getVarFromDB.php?offset=' + 1, document.getElementById(1)); is working

I dont wanna be that guy, but dont use `mysql` use `mysqli` instead. No one can actually answer this question **right** without using mysqli — Loko, Feb 10 '15 at 10:21
You effectively fire 7 xhr requests after each other, but store each of them in a global variable called `ajax`. Change the global to a local: `var ajax = AjaxCaller()` and this should work. However, why use seven separate calls, Send all the offsets as JSON and return one call. — Mouser, Feb 10 '15 at 10:29
Mouser: you got it right.. did as you suggested and it worked. The reason for calling 7 requests is very simple - I'm newbie and have no idea what I'm doing.. I'm in C codding for microChips orientation so this task is extremely hard for me :) — bardalas, Feb 10 '15 at 11:21
@Loko they might use the more widely preffered PDO and get it right ;--) — Mawg says reinstate Monica, Feb 10 '15 at 13:17
@Mawg Yeah true that. Mysqli just looks more like mysql. That's why I only said mysqli but of course PDO is right as well. — Loko, Feb 10 '15 at 13:21

score 0 · Answer 1 · answered Feb 10 '15 at 13:23

0

"I have an HTML page with a table in it which I want to update it's values every 2 seconds with the values stored in the MySQL database"

Am I missing something? Where does JavaScript enter into this (other than Das Blinkenlights)?

Is there any compelling reason not to just use an HTML page and auto-refresh it every 2 seconds with <meta http-equiv="refresh" content="2000">?

That's the KISS solution.

answered Feb 10 '15 at 13:23

Mawg says reinstate Monica

38,334
103
306
551

Hi Mawg. The reason for not using auto-refresh is because it will refresh the whole page and reload all the images etc'. You can see in my code that I've added setInterval(updateSensorsTable, 2000); This operates the function updateSensorTable every 2 seconds. But be aware that updateSensorTable is not very efficient since it inquirers the server 8 times every time it is being call and pulls one value each time. The proper way for updating the values in the table should be by inquiring 1 time on each function calling and to get an array. Then using JSON command pass it to javascript. – bardalas Feb 10 '15 at 15:01
I see. Sorry for the answer, then. Maybe you could clarify that in the question. Good luck! – Mawg says reinstate Monica Feb 10 '15 at 15:14
still struggling on how to pass an array from php to js – bardalas Feb 10 '15 at 15:25
surely you just echo() it? – Mawg says reinstate Monica Feb 10 '15 at 15:33
it's more then just echo it [take a look](http://stackoverflow.com/questions/28435382/pass-an-array-from-php-to-javascript?noredirect=1#comment45201296_28435382) – bardalas Feb 10 '15 at 15:52
"still struggling on how to pass an array from php to js" - see http://stackoverflow.com/questions/7064391/php-returning-json-to-jquery-ajax-call – Mawg says reinstate Monica Feb 11 '15 at 14:35

score 0 · Accepted Answer · answered Feb 10 '15 at 15:04

0

Change the global to a local: var ajax = AjaxCaller() and this should work.

answered Feb 10 '15 at 15:04

bardalas

81
1
1
10

Updating HTML table from SQL server repeadetly

2 Answers2