The Problem
We are a textiles manufacturing plant with many PLCs on an automated line. We would like to create a display accessible over the Internet which will display key performance indicators in an easily digestible format.
I am a novice level web developer/junior level SQL DBA/junior level systems administrator acting as the primary net/sysadmin and DBA for our US site.
I am trying to figure out how to solve the problem preferably without having to learn a completely new language. I am experienced in PHP, so I have been trying to use that.
Basically, I have managed to get the system working, but I want to understand if this is a poor solution and/or if there is a better solution that I should work towards.
Please take a minute to browse through the following code and let me know your thoughts.
The Solution
The general concept is that individual queries will be stored in individual includes files and will be reloaded periodically using javascript.
index.php
<!DOCTYPE HTML>
<html>
<head>
<title>Performance Display</title>
<link href="https://fonts.googleapis.com/css?family=Noto+Sans" rel="stylesheet">
<link rel="stylesheet" type="text/css" href="styles/normalize.css" />
<link rel="stylesheet" type="text/css" href="styles/main.css" />
</head>
<body onload="updateClock(); setInterval('updateClock()', 1000);
loadData(); setInterval('loadData()', 30000);">
<div id="container">
<header>
<div id="product"> </div>
<div id="data"> </div>
<div id="clock"> </div>
</header>
<table id="product_table">
<thead>
<tr>
<th> </th>
<th>ACTUAL</th>
<th>STANDARD</th>
</tr>
</thead>
<tbody>
<tr>
<td id="output_actual"> </td>
<td id="output_standard"> </td>
</tr>
<tr>
<td id="speed_actual"> </td>
<td id="speed_standard"> </td>
</tr>
</tbody>
</table>
</div>
</body>
<script src="https://code.jquery.com/jquery-1.10.2.js"></script>
<script type="text/javascript" src="scripts/functions.js"></script>
</html>
functions.js
function updateClock() {
// This is a standard clock function removed to avoid unnecessary clutter.
};
function loadData() {
$("#date").load("includes/date.php");
$("#product").load("includes/product.php");
$("#output_actual").load("includes/output_actual.php");
$("#speed_actual").load("includes/speed_actual.php");
$("#output_standard").load("includes/output_standard.php");
$("#speed_standard").load("includes/speed_standard.php");
};
product.php
<?php
include_once("connections.php");
$sql = "SELECT TOP 1 * FROM Tbl_Perf_Data_Help ORDER BY Date_Time DESC";
$stmt = sqlsrv_query($productionUSA->connect(), $sql);
while($obj = sqlsrv_fetch_object($stmt)) {
echo "PRODUCT ".$obj->Current_Product;
}
connections.php
<?php
class connection {
private $serverName;
private $connInfo;
public $conn;
public function __construct($serverName, $db, $usr, $pwd) {
$this->serverName = $serverName;
$this->connInfo = array("Database"=>$db, "UID"=>$usr, "PWD"=>$pwd);
}
public function connect() {
$this->conn = sqlsrv_connect($this->serverName, $this->connInfo);
if (($this->conn === false) or (sqlsrv_begin_transaction($this->conn) === false)) {
die(print_r(sqlsrv_errors(), true));
}
return $this->conn;
sqlsrv_close($this->conn);
}
}
$productionUSA = new connection("dbserv", "productionDB", "admin", "CorrectHorseBatteryStaple");
Final Thoughts
As you can see, each piece of dynamic data is being reloaded as a php page every 30 seconds. The reason I think this might be inelegant is that the complete code for the performance display has about 58 fields. That's 58 seperate php files with potentially 58 different connections to our database, if I understand that correctly.
Any guidance or suggestions would be greatly appreciated!
