Sensorwerte in eine Datenbank schreiben und auslesen

Das ist ein Projekt mit einem WeMos mini. Das Programm dient zur Übermittlung der BME280 Sensordaten Temperatur, Luftfeuchte und Luftdruck in eine Datenbank. Die Datenbank dient als Datenlogger und kann ausgelesen werden.

Hardware

Die Hardware für dieses Projekt:

• 1 x WeMos mini
• 1 x BME280

Software

Wir benötigen ein C++ Programm für den WeMos und 2 PHP-Programme im Webspace.

• C++ Programm zum auslesen der Temperatur, Luftfeuchte und Lutdruck aus dem BME280.
• PHP Programm zum schreiben in die Datenbank.
• PHP Programm zum auslesen der Datenbank.

Installation

Für die Installation ist ein eigener Webspace von Vorteil, kann aber auch lokal mit XAMPP getestet werden.

• Webspace Installation
• Mit MySQL eine Datenbank anlegen.
• Eine Tabelle mit den Spalten ID, Sensor,Location,Temperatur,Luftfeuchte, Luftdruck und Timestamp anlegen.
• Die PHP-Programme mit den eigenen Zugangsdaten ergänzen.
• Die PHP-Programme esp-data.php und post-esp-data.php in den Webspace kopieren.
• Das C++ Programm mit den eigenen Zugangsdaten ergänzen.
• Das C++ Programm in den WeMos Mini laden.
• Der BME280 wird am I2C-Bus des WeMos Mini angeschlossen. SCL => D1, SDA => D2.

Die Programme testen.

Die Tabelle esp-data.php muss gegebenfalls im Browser mit Strg+F5 aktualisiert werden.

Programm

C++ Wemos Code:

/*
 SQL-Post frankyhub.net
 Board WeMos D1 R1

*/

#ifdef ESP32
  #include <WiFi.h>
  #include <HTTPClient.h>
#else
  #include <ESP8266WiFi.h>
  #include <ESP8266HTTPClient.h>
  #include <WiFiClient.h>
#endif

#include <Wire.h>
#include <Adafruit_Sensor.h>
#include <Adafruit_BME280.h>

// Replace with your network credentials
const char* ssid     = "xxx";
const char* password = "xxx";

// REPLACE with your Domain name and URL path or IP address with path
const char* serverName = "http://frankyhub.net/post-esp-data.php";

// Keep this API Key value to be compatible with the PHP code provided in the project page.
// If you change the apiKeyValue value, the PHP file /post-esp-data.php also needs to have the same key
String apiKeyValue = "tPmAT5Ab3j7F9";

String sensorName = "BME280";
String sensorLocation = "Office";

/*#include <SPI.h>
#define BME_SCK 18
#define BME_MISO 19
#define BME_MOSI 23
#define BME_CS 5*/

#define SEALEVELPRESSURE_HPA (1013.25)

Adafruit_BME280 bme;  // I2C
//Adafruit_BME280 bme(BME_CS);  // hardware SPI
//Adafruit_BME280 bme(BME_CS, BME_MOSI, BME_MISO, BME_SCK);  // software SPI

void setup() {
  Serial.begin(115200);

  WiFi.begin(ssid, password);
  Serial.println("Connecting");
  while(WiFi.status() != WL_CONNECTED) {
    delay(500);
    Serial.print(".");
  }
  Serial.println("");
  Serial.print("Connected to WiFi network with IP Address: ");
  Serial.println(WiFi.localIP());

  // (you can also pass in a Wire library object like &Wire2)
  bool status = bme.begin(0x76);
  if (!status) {
    Serial.println("Could not find a valid BME280 sensor, check wiring or change I2C address!");
    while (1);
  }
}

void loop() {
  //Check WiFi connection status
  if(WiFi.status()== WL_CONNECTED){
    HTTPClient http;

    // Your Domain name with URL path or IP address with path
    http.begin(serverName);

    // Specify content-type header
    http.addHeader("Content-Type", "application/x-www-form-urlencoded");

    // Prepare your HTTP POST request data
    String httpRequestData = "api_key=" + apiKeyValue + "&sensor=" + sensorName
                          + "&location=" + sensorLocation + "&value1=" + String(bme.readTemperature())
                          + "&value2=" + String(bme.readHumidity()) + "&value3=" + String(bme.readPressure()/100.0F) + "";
    Serial.print("httpRequestData: ");
    Serial.println(httpRequestData);

    // You can comment the httpRequestData variable above
    // then, use the httpRequestData variable below (for testing purposes without the BME280 sensor)
    //String httpRequestData = "api_key=tPmAT5Ab3j7F9&sensor=BME280&location=Office&value1=24.75&value2=49.54&value3=1005.14";

    // Send HTTP POST request
    int httpResponseCode = http.POST(httpRequestData);

    // If you need an HTTP request with a content type: text/plain
    //http.addHeader("Content-Type", "text/plain");
    //int httpResponseCode = http.POST("Hello, World!");

    // If you need an HTTP request with a content type: application/json, use the following:
    //http.addHeader("Content-Type", "application/json");
    //int httpResponseCode = http.POST("{\"value1\":\"19\",\"value2\":\"67\",\"value3\":\"78\"}");

    if (httpResponseCode>0) {
      Serial.print("HTTP Response code: ");
      Serial.println(httpResponseCode);
    }
    else {
      Serial.print("Error code: ");
      Serial.println(httpResponseCode);
    }
    // Free resources
    http.end();
  }
  else {
    Serial.println("WiFi Disconnected");
  }
  //Send an HTTP POST request every 30 seconds
  delay(30000);
}

RAW code

PHP Daten schreiben (post-esp-data.php):

<?php

/*
KHF Barometer-Daten in die Datenbank frankyhu_esp_data schreiben
ID        Sensor        Location        Temperatur        Luftfeuchte        Luftdruck        Timestamp
V23.01.2021
*/

$servername = "localhost";

// REPLACE with your Database name
$dbname = "frankyhu_esp_data";
// REPLACE with Database user
$username = "frankyxxx";
// REPLACE with Database user password
$password = "xxx";

// 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= $sensor = $location = $value1 = $value2 = $value3 = "";

if ($_SERVER["REQUEST_METHOD"] == "POST") {
    $api_key = test_input($_POST["api_key"]);
    if($api_key == $api_key_value) {
        $sensor = test_input($_POST["sensor"]);
        $location = test_input($_POST["location"]);
        $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 SensorData (sensor, location, value1, value2, value3)
        VALUES ('" . $sensor . "', '" . $location . "', '" . $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;
}

RAW code

PHP Daten lesen (esp-data.php):

<!DOCTYPE html>
<html><body>
<?php
/*
KHF Barometer-Daten aus der Datenbank frankyhu_esp_data auslesen
ID        Sensor        Location        Temperatur        Luftfeuchte        Luftdruck        Timestamp
V23.01.2021
*/

$servername = "localhost";


// REPLACE with your Database name
$dbname = "frankyhu_esp_data";
// REPLACE with Database user
$username = "frankyxxx";
// REPLACE with Database user password
$password = "xxx";


// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}


$sql = "SELECT id, sensor, location, value1, value2, value3, reading_time FROM SensorData ORDER BY id DESC";

echo '<table cellspacing="5" cellpadding="5">
      <tr>
        <td>ID</td>
        <td>Sensor</td>
        <td>Location</td>
        <td>Temperatur</td>
        <td>Luftfeuchte</td>
        <td>Luftdruck</td>
        <td>Timestamp</td>
      </tr>';

if ($result = $conn->query($sql)) {
    while ($row = $result->fetch_assoc()) {
        $row_id = $row["id"];
        $row_sensor = $row["sensor"];
        $row_location = $row["location"];
        $row_value1 = $row["value1"];
        $row_value2 = $row["value2"];
        $row_value3 = $row["value3"];
        $row_reading_time = $row["reading_time"];
        // Uncomment to set timezone to - 1 hour (you can change 1 to any number)
        //$row_reading_time = date("Y-m-d H:i:s", strtotime("$row_reading_time - 1 hours"));

        // Uncomment to set timezone to + 4 hours (you can change 4 to any number)
        //$row_reading_time = date("Y-m-d H:i:s", strtotime("$row_reading_time + 4 hours"));

        echo '<tr>
                <td>' . $row_id . '</td>
                <td>' . $row_sensor . '</td>
                <td>' . $row_location . '</td>
                <td>' . $row_value1 . '</td>
                <td>' . $row_value2 . '</td>
                <td>' . $row_value3 . '</td>
                <td>' . $row_reading_time . '</td>
              </tr>';
    }
    $result->free();
}

$conn->close();

?>
</table>
</body>
</html>

RAW code

nach oben