I’d like to have a drop-down list that populates based on the table names (not rows) with a submit button that retrieves the row contents of that table and displays it.
There is a Python script that runs on a Raspberry Pi that reads temperature sensor at intervals, creates a table in a MySQL schema based on the device’s host name (if it’s not already there) and then stores the temperature in tempreading and the time and date in datetime rows.
A PHP script that connects to the database selects the id, tempreading and datatime from the table that is defined by its hostname.
The PHP script: getTemp.php
<?php
$host="localhost";
$username="root";
$password="mysql";
$database="tempmonitor";
$device = gethostname();
$conn = new mysqli($host, $username, $password, $database);
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
echo "Connected successfully";
echo "<br>";
$sql = "SELECT id, tempreading, datetime FROM ".$device."";
$result = $conn->query($sql);
if ($result->num_rows > 0) {
// output data of each row
while($row = $result->fetch_assoc()) {
echo "ID: " . $row["id"]. " - Temp: " . $row["tempreading"]. " " . $row["datetime"]. "<br>";
}
} else {
echo "0 results";
}
$conn->close();
?>
The HTML
<html>
<head>
<script>
function showTemp(str) {
if (str == "") {
document.getElementById("txtHint").innerHTML = "";
return;
} else {
if (window.XMLHttpRequest) {
// code for IE7+, Firefox, Chrome, Opera, Safari
xmlhttp = new XMLHttpRequest();
} else {
// code for IE6, IE5
xmlhttp = new ActiveXObject("Microsoft.XMLHTTP");
}
xmlhttp.onreadystatechange = function() {
if (this.readyState == 4 && this.status == 200) {
document.getElementById("Hint").innerHTML = this.responseText;
}
};
xmlhttp.open("GET","getTemp.php?q="+str,true);
xmlhttp.send();
}
}
</script>
</head>
<body>
<select name="locations">
<option value="0">Select a location:</option>
<option value="1">Table 1</option>
<option value="2">Table 2</option>
<option value="3">Table 3</option>
<option value="4">Table 4</option>
<br>
<br>
<input type="submit" value="View" onclick="showTemp()">
</select>
<br>
<br>
<div id="Hint"><b>Temperatures will be listed here...</b></div>
<br>
</body>
</html>
</body>
</html>
I can get the row data to display for each row if I 'Hard code' it so I think it's mostly there. It's the linking of the dropdown list with the database tables and the button that I am stuck with. This is my first attempt at any coding so it's built mostly from tutorials and many hours reading on w3schools.
04/08/2019 Ok, so an update. I can now list the tables and print them to the screen with the following:
$showtables= "SHOW TABLES FROM ".$database."";
$result = $conn->query($showtables);
while($table = mysqli_fetch_array($result)) {
echo($table[0] . "<br>");
}
How would I change:
echo($table[0] . "<br>");
to instead populate a drop down list? I've tried various things with the option value code, but had no success as yet.
