Hi folks,
Firstly I'm not a programmer but can follow instructions quite well.
I have an ESP32 sending sensors data to Node-red on a Raspberry Pi, acting as an MQTT broker. The MQTT broker subscribes to a single topic that outputs the data as "a
parsed JSON object." The debug look like this:
{"controllertype":"mySQM+","firmware":127,"uptime":"00:09:04","date":"01/01/1900","time":"00:00:00","sqm":17.74,"nelm":3.75,"lux":0.0087,"ambient":23.67,"humidity":57.2,"dewpoint":14.71,"pressure":1014.22,"slpressure":1015.06,"bme280alt":7,"skyambient":20,"skyobject":20,"skystate":1,"cloudcover":100,"raining":0,"rvout":0,"rainprevhr":0,"raincurrhr":0,"raincurrday":0,"windspd":0,"windavg":0,"windgust":0,"windchill":100,"beaufort":0,"winddir":0,"gpsdate":"01/01/1900","gpstime":"00:00:00","gpslat":"-77 00 00 S","gpslon":"166 00 00 E","gpsalt":0,"gpssat":0,"gpsfix":0,"mac":"78:E3:6D:0A:24:14","makehay":12.52}
On the domain, the MySQL table will look something like the snippet below which should only contain "ambient", "humidity" and "pressure" data.
CREATE TABLE Sensor (
id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
value1 VARCHAR(10),
value2 VARCHAR(10),
value3 VARCHAR(10),
reading_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)
From Node-red I would like to send the data to the MySQL database using a PHP post script fround on the website which looks like this example:
$servername = "localhost";
// REPLACE with your Database name
$dbname = "REPLACE_WITH_YOUR_DATABASE_NAME";
// REPLACE with Database user
$username = "REPLACE_WITH_YOUR_USERNAME";
// REPLACE with Database user password
$password = "REPLACE_WITH_YOUR_PASSWORD";
// Keep this API Key value to be compatible with the ESP32 code provided in the project page. If you change this value, the ESP32 sketch needs to match
$api_key_value = "tPmAT5Ab3j7F9";
$api_key = $value1 = $value2 = $value3 = "";
if ($_SERVER["REQUEST_METHOD"] == "POST") {
$api_key = test_input($_POST["api_key"]);
if($api_key == $api_key_value) {
$value1 = test_input($_POST["value1"]);
$value2 = test_input($_POST["value2"]);
$value3 = test_input($_POST["value3"]);
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
$sql = "INSERT INTO Sensor (value1, value2, value3)
VALUES ('" . $value1 . "', '" . $value2 . "', '" . $value3 . "')";
if ($conn->query($sql) === TRUE) {
echo "New record created successfully";
}
else {
echo "Error: " . $sql . "<br>" . $conn->error;
}
$conn->close();
}
else {
echo "Wrong API Key provided.";
}
}
else {
echo "No data posted with HTTP POST.";
}
function test_input($data) {
$data = trim($data);
$data = stripslashes($data);
$data = htmlspecialchars($data);
return $data;
}
I'm not sure how to do this in Node-red, any guidance would be much appreciated.
Jairo
