Andy php sql coding problem

From richmondmakerlabs.uk
Jump to navigation Jump to search

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:

  1. machine will be unique
  2. Multiple machines will have the same 'disk' entry
  3. 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
  4. 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:

  1. ignore the 'is ok' etc. I was just playing with conditionally colouring the output e.g. over 90% is red
  2. 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>
Retrieved from "https://wiki.richmondmakerlabs.uk/index.php?title=Andy_php_sql_coding_problem&oldid=15611"