Andy php sql coding problem
So. I'm no coder as will become clear. Teaching myself I thought it's time to ask for some advice.
Previous to this
Script sshs to machines, df -h and gets it into SQL. We start from there
In SQL
Few things to note here:
- machine will be unique
- Multiple machines will have the same 'disk' entry
- There will be multiple entries for each machine/ disk combo based on date so I'm pulling the latest one for this part but want to keep that so I can look at trends later
- It's probably much better to just use an existing tool/ script to do ALL of this. This is more an exercise for me to learn sql/ php maybe javascript/ python as I build it out. Can all be deleted at one point
Current Output
https://rml.richmondmakerlabs.uk/phptest.php
I appreciate this isn't very helpful formatting as it's a work in progress. Some notes:
- ignore the 'is ok' etc. I was just playing with conditionally colouring the output e.g. over 90% is red
- Similarly formatting etc. Just playing there with css
Output wanted/ problem
So I've obviously managed a few things 1) Filter on the sql query on date 2) Form a basic loop over the rows 3) Output and sort by machine
BUT
It would be nice to have
Machine 1 Disk 1 Disk 2 etc Machine 2 Disk 1 Disk 2 etc
Maybe even sorting the actual machines by which ones have any disks above X% and I should probably consider that as part of this
Be interested in how you'd go about that as suspect I'm missing a trick here.
I'm starting to think more about efficiency rather than nesting tons of loops - not that it really matters as have horse power to throw at it but as a matter of being a better coder.
For another day I'll ask about getting the data into SQL more efficiently - there's a lot of ssh going on and that script really is spaghetti code but that's partly down to when machines are on and which ones have permisisons to access other ones.
Perhaps injecting that data into the webpage may be a task for another day
php code
<!DOCTYPE html>
<html>
<header>
<link rel="stylesheet" href="class.css" media="screen">
<meta name="color-scheme" content="dark light">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
</header>
<body>
<h5>Disks</h5>
<?php
# auto refresh
$url1=$_SERVER['REQUEST_URI'];
header("Refresh: 5; URL=$url1");
#comments
// also this
/*
and a
multiline comment
*/
// $var="a variable";
// echo "well here we are testing refresh<br>";
// echo "using $var <br>";
?>
<?php
##Connection variables for mysql deleted ##
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
$ok="IF(usedpc>80, 'nok', 'ok')";
echo $var;
$sql = "SELECT machine,disk,usedpc,$ok FROM disk_usage GROUP BY machine,DISK ORDER BY machine,disk,usedpc DESC";
$result = $conn->query($sql);
if ($result->num_rows > 0) {
// output data of each row
while($row = $result->fetch_assoc()) {
echo "<div class=$row[$ok]>".'<b class="machine">Machine</b>:'. $row["machine" ]. "<b>disk</b>:" . $row["disk"]. " has used ". $row["usedpc"]." is $row[$ok]". "</div><br> ";
}
} else {
echo "0 results";
}
$conn->close();
?>
</body>
</html>