ESP32 DHT22 Temperature Humidity to MySQL Database

 ESP32 DHT22 Temperature Humidity to MySQL Database

 I have decided to replace the Arduino Nano, DHT22 and HC-05 Module with something more simple, smaller, cheaper and with longer battery life. The main focus of this project is the code, for a simple power supply a power bank should last a week or more. The power usage will be 3mA for 99% of the time and only rising to ~140mA for a few seconds per reading.

The following were used :
ESP32 Devkit C
DHT22, temperature humidity sensor
MySQL database on local network eg. Raspberry Pi with MariaDB, phpMyAdmin is usefull as well.
USB Powerbank
Breadboard, wires etc.

To connect the DHT22, connect + to 3.3v, Out to pin 14, Gnd to Gnd. Simple :)

The complete code is below with extra comments shown in bold. Parts that you need to change are shown in red.

 The code can be downloaded from gitlab https://gitlab.com/leahC123/ESP32DHT22MYSQL/-/blob/master/ESP32DHT22.ino

---------------------------------------------------------------------------------------

/*    Based on examples by, Neil Kolban, Evandro Copercini, chegewara and various examples in the Ardruino IDE      
      assembled together by Leah C
*/

//Check these are installed with library manager
#include <DHT.h>
#include <WiFi.h>
#include <MySQL_Connection.h>
#include <MySQL_Cursor.h>

IPAddress server_addr(192,168,5,2);  // IP of the MySQL server
char userSQL[] = "Replace with your username";              // MySQL user login username
char passwordSQL[] = "Replace with your password"; // MySQL user login password

#define uS_TO_S_FACTOR 1000000ULL  // Conversion factor for micro seconds to seconds
#define TIME_TO_SLEEP  600        // Time ESP32 will go to sleep (in seconds) 600 = 10 minutes

#define DHTPIN 14     // Digital pin connected to the DHT sensor
#define DHTTYPE DHT22   // DHT 22  (AM2302), AM2321
DHT dht(DHTPIN, DHTTYPE);

const char* ssid     = "Replace with your SSID";       //WiFi SSID - Name of AP
const char* password = "Replace with your WiFi Password";   //Wifi Password
char query[128];
char query1[128];

//The following line defines the database,table and value / record names, you can find yours in phpMyadmin
char INSERT_DATA[] = "INSERT INTO monitor.readings (location, temperature) VALUES ('%s',%s)";

void setup() {
  dht.begin();  //Start temp sensor
  Serial.begin(115200);
  Serial.print("Connecting to ");
  Serial.println(ssid);
  //Connect to WiFi and print address
  WiFi.begin(ssid, password);
  while (WiFi.status() != WL_CONNECTED) {
      delay(500);
      Serial.print(".");
  }
  Serial.println("");
  Serial.println("WiFi connected");
  Serial.println("IP address: ");
  Serial.println(WiFi.localIP());
}

void loop() {
  //Read temperature and Humidity
  float h = dht.readHumidity();
  float t = dht.readTemperature();
  Serial.print("Temp = ");
  Serial.println(t);
  Serial.print("Humidity = ");
  Serial.println(h);
  //Check if reading worked, if not return to start of loop
  if (isnan(h) || isnan(t) ){
    Serial.println(F("Failed to read from DHT sensor!"));
    return;
  }
  WiFiClient client;
  MySQL_Connection conn((Client *)&client);
  if (conn.connect(server_addr, 3306, userSQL, passwordSQL)) {
    Serial.println("Database connected.");
  }
  else{
    Serial.println("Connection failed.");
    return;
  }
  //Set up the cursor
  MySQL_Cursor *cur_mem = new MySQL_Cursor(&conn);
  //Convert the float values to string, my query fails if using float and %d etc
  String tString = String(t,2);
  String hString = String(h,2);
  //Build the query
  sprintf(query, INSERT_DATA, "Small GH Temp", tString);
  sprintf(query1, INSERT_DATA, "Small GH Humidity", hString);
  //Print query to serial, good for troubleshooting
  Serial.println(query);
  Serial.println(query1);
  // Execute the query
  cur_mem->execute(query);
  cur_mem->execute(query1);
  // Deleting the cursor also frees up memory used
  delete cur_mem;
  Serial.println("closing connection\n");
  delay(10000); //Delay for 10 seconds, do not flood the database with values.
  esp_sleep_enable_timer_wakeup(TIME_TO_SLEEP * uS_TO_S_FACTOR);
  Serial.println("Sending ESP32 to sleep for " + String(TIME_TO_SLEEP) + " Seconds");
  Serial.println("Going to sleep now");
  esp_deep_sleep_start();
  //Nothing past this will ever run.
}

-----------------------------------------------------------------------------------------

If it all works you shoud have the readings in the database.... Mine is sat next to me in the living room, its not really that hot outside in South Wales at 2250 :)

The most difficult part was an issue building a query that would be accepted by the server. The value in the database is a float but when trying to send a float it would fail. This is where printing the query helped diagnose the problem. Converting the value to a string before sending works fine and the value in the database is correct.