Send data to mysql through esp8266 and uno

Hi all
I have a uno which runs that attached code. I have a esp8266 connected to my tx and rx of uno, can anyone help me modify my program so that arduino can directly insert the temperature to mysql database using mysql connector?

Esp8266 is connected to my WiFi router and my pc is also connected to the same WiFi router

My database setup is like this

I have an odbc with connected to mysql database, connection name is TEMPLOG

Mysql is installed in my pc and it's ip is 192.168.1.50

Mysql is in my pc at address localhost

User name root
Pass: @temp@12

Database has 3 column
I'd ( auto incriment)
Date (system date)
Temperature (where we need to insert data)

Is it possible to insert data directly?

This is my sketch
.

//
// First we include the libraries
#include <OneWire.h>
#include <DallasTemperature.h>
/
/
// Data wire is plugged into pin 2 on the Arduino
#define ONE_WIRE_BUS 2
//
// Setup a oneWire instance to communicate with any OneWire devices
// (not just Maxim/Dallas temperature ICs)
OneWire oneWire(ONE_WIRE_BUS);
/
/
// Pass our oneWire reference to Dallas Temperature.
DallasTemperature sensors(&oneWire);
//
void setup(void)
{
// start serial port
Serial.begin(9600);
Serial.println("Dallas Temperature IC Control Library Demo");
// Start up the library
sensors.begin();
}
void loop(void)
{
// call sensors.requestTemperatures() to issue a global temperature
// request to all devices on the bus
/
/
Serial.print(" Requesting temperatures...");
sensors.requestTemperatures(); // Send the command to get temperature readings
Serial.println("DONE");
/********************************************************************/
Serial.print("Temperature is: ");
Serial.print(sensors.getTempCByIndex(0)); // Why "byIndex"?
// You can have more than one DS18B20 on the same bus.
// 0 refers to the first IC on the wire
delay(1000);
}

F5XAS9CIFMTNEBR.ino (1.01 KB)

I have the same question. The MySQL database is up and running. How to put data into a MySQL database. (I have a Mega 2650. The application is similar to yours)

There are several ways. The most common is to create a web page on a server somewhere. Have your arduino act as web client using a GET request to access the web page. Whatever data you want to pass to the web server is sent as parameters in the GET. Parse the returned web page if desired to get results. Often, this step is ommitted as the only point was to pass IoT data to the database. Then the web server stores the data in the database.

You can do it directly too, see here.

Have you attend to send data ??? Can you post the code please ??!!

rahulkp1986:
Hi all

I have a uno which runs that attached code. I have a esp8266 connected to my tx and rx of uno

No level shifters? And the ESP8266, a 3.3V device, still continues to work with the 5.0V device? I use ESP32's and they do not take much more than 5.1V before they act like fuses.

who got the code for the above question i have the same problem , am using an arduino mega with esp8266 01 connected through serial1

I get data to a MYSQL dB by having the ESP32 publish its data to a MQTT Broker on a local network; a RPi. The RPi has a Python script that subscribes to the MQTT broker and sends the subscribed data to a MYSQL dB on my web site.

And this post might be of interest Sending Sensor data to MySQL DB via Uno and Ethernet Shield Rev2 - Science and Measurement - Arduino Forum

Hello, I want to send data to mysql database (php.myadmin) but I don't get it. Can you explain me how I get the connection?

The code should retrieve the live values and display them on the monitor. The ESP should include a web server that publishes the live values. Furthermore the data should be inserted into a MQSL(php.myadmin) database. Furthermore the sensor values should be filtered.
I've done some work, but not quite yet. I'm asking for your help.

Thank you!

my setup:

  • ESP8266
  • DHT22 sensor
  • SDS011 sensor

CODE:

// Datenbanken initialisieren
#include <ESP8266WiFi.h>
#include <ESP8266WebServer.h>
#include "DHT.h"
#include "SDS011.h"
// Typ des Sensors definieren, hier DHT22
#define DHTTYPE DHT22   // DHT 22  (AM2302), AM2321

// Zugangsdaten zum WLAN:
const char* ssid = "xxxxx";
const char* password = "xxxxx";

ESP8266WebServer server(80);

// DHT Sensor
// PIN welcher mit dem "Data" des DHT22 verbunden ist
uint8_t DHTPin = 13; 
              
// DHT Sensor initialisieren.
DHT dht(DHTPin, DHTTYPE);   
            
SDS011 my_sds;

float Temperature;
int Humidity;
float p10,p25;
int error;

void setup() {
 Serial.begin(115200);
 delay(100);
 
 pinMode(LED_BUILTIN,OUTPUT); // LED als Output definieren
 digitalWrite(LED_BUILTIN,HIGH); // LED Ausschalten
 
 my_sds.begin(05,04);
 pinMode(DHTPin, INPUT);   
         
// Verbindungsaufbau zu einem WiFi-Netzwerk
 Serial.println("Verbinden mit ");
 Serial.println(ssid);
 WiFi.begin(ssid, password);

 //überprüfung ob Wlan mit dem Router verbunden ist
 while (WiFi.status() != WL_CONNECTED) {
 delay(1000);
 Serial.print(".");
 }
 Serial.println("");
 Serial.println("WiFi verbunden..!");
 Serial.print("IP vom ESP8266: ");  
 Serial.println(WiFi.localIP());
 
// Sensoren werden aktiviert
 dht.begin();   
 
//Server wird gestartet
 server.on("/", handle_OnConnect);
 server.onNotFound(handle_NotFound);

 server.begin();
 Serial.println("HTTP server gestartet");

}
void loop() {
 
 digitalWrite(LED_BUILTIN,LOW); // LED wird eingeschaltet
 delay(500);
 
 Serial.print("Temperatur:"); // Ausgabe der Temperatur in seriellen Monitor
 Serial.println(Temperature);
 Temperature = dht.readTemperature(); // misst die Temperatur aus
 
 Serial.print("Luftfeuchtigkeit:"); // Ausgabe der Luftfeuchtigkeit in seriellen Monitor
 Serial.println(Humidity);
 Humidity = dht.readHumidity(); // misst die Luftfeuchtigkeit aus 
 
 error = my_sds.read(&p25,&p10); // misst die Luftqualität für 2.5 und 10 mikrogramm/m^3 aus
 Serial.print("Partikelgroesse 2.5:");
 Serial.println(p25);

 Serial.print("Partikelgroesse 10.0:");
 Serial.println(p10);
   
 digitalWrite(LED_BUILTIN,HIGH); //LED wird ausgeschaltet
 delay(3000);
 
 server.handleClient(); //sucht nach ausstehenden Anfragen, gleicht die angeforderte Adresse ab und führt den Code aus, der im Setup registriert wurde.
 
}

void handle_OnConnect() {
 
 Temperature = dht.readTemperature(); // misst die Temperatur aus
 Humidity = dht.readHumidity(); // misst die Luftfeuchtigkeit aus 
 error = my_sds.read(&p25,&p10); // misst die Luftqualität für 2.5 und 10 mikrogramm/m^3 aus
 server.send(200, "text/html", SendHTML(Temperature,Humidity,p25,p10));

}

void handle_NotFound(){
 server.send(404, "text/plain", "Not found");
}

String SendHTML(float Temperaturestat,int Humiditystat,float p25,float p10){
 String ptr = "<!DOCTYPE html> <html>\n";
 ptr +="<head><meta name=\"viewport\" content=\"width=device-width, initial-scale=1.0, user-scalable=no\">\n";
 ptr +="<title>ESP8266 Wetterbericht</title>\n";
 ptr +="<meta http-equiv=\"refresh\" content=\"2\" >";
 ptr +="<style>html { font-family: Helvetica; display: inline-block; margin: 0px auto; text-align: center;}\n";
 ptr +="body{margin-top: 50px;} h1 {color: #444444;margin: 50px auto 30px;}\n";
 ptr +="p {font-size: 24px;color: #444444;margin-bottom: 10px;}\n";
 ptr +="</style>\n";
 ptr +="</head>\n";
 ptr +="<body>\n";
 ptr +="<div id=\"webpage\">\n";
 ptr +="<h1>ESP8266 NodeMCU Wetterdaten</h1>\n";
 
 ptr +="<p>Temperatur: ";
 ptr +=(int)Temperaturestat;
 ptr +=" C</p>";
 
 ptr +="<p>Luftfeuchtigkeit: ";
 ptr +=(int)Humiditystat;
 ptr +=" %</p>";

 ptr +="<p>Partikelgroesse 2.5: ";
 ptr +=(float)p25;
 ptr +=" mikrogramm pro qm</p>";

 ptr +="<p>Partikelgroesse 10.0: ";
 ptr +=(float)p10;
 ptr +=" mikrogramm pro qm</p>";
 
 ptr +="</div>\n";
 ptr +="</body>\n";
 ptr +="</html>\n";
 return ptr;
}

Take the ESP data and send it to a computer running a MQTT Broker; the ESP does not have a library to write to a MySQL dB. On the MQTT Broker, use Node Red or write a Python Script or write a C++ thingy to extract the data from the MQTT Broker. Once the data is extracted from the MQTT Broker, package it up and send the data to the MySQL dB.

You will need to allow remote access to the MySQL dB.

Okay, I understand the drill, thank you. But I don't know what that code looks like. Do you have a sample code?

bilderberg2020:
Okay, I understand the drill, thank you. But I don't know what that code looks like. Do you have a sample code?

Sure thing:

#include "time.h"
#include <WiFi.h>
#include <WiFiClientSecure.h>
#include <PubSubClient.h>
#include "certs.h"
#include "sdkconfig.h"
#include "esp_system.h" //This inclusion configures the peripherals in the ESP system.
#include "freertos/FreeRTOS.h"
#include "freertos/task.h"
#include "freertos/timers.h"
#include "freertos/event_groups.h"
#include "esp_sleep.h"
#include <SPI.h>
#include <Adafruit_Sensor.h>
#include "Adafruit_BME680.h"
////
EventGroupHandle_t eg;
#define evtDoBME ( 1 << 2 ) //100
#define evtSetupBME_Complete ( 1 << 3 )
SemaphoreHandle_t sema_ReadBME680;
////
WiFiClientSecure secureClient = WiFiClientSecure();
WiFiClient wifiClient;
PubSubClient client(mqtt_server, 1883, wifiClient); // 1883 is the listener port for the Broker
////
Adafruit_BME680 bme( GPIO_NUM_5); // hardware SPI
///
////
void setup()
{
  eg = xEventGroupCreate();
  SPI.begin();
  sema_ReadBME680 = xSemaphoreCreateBinary();
  xTaskCreatePinnedToCore( fDoBME, "fDoBME", 20000, NULL, 3, NULL, 1 ); // assigned to core
  //start this task last
  xTaskCreatePinnedToCore( fDoTheThing, "fDoTheThing", 40000, NULL, 5, NULL, 1 ); // assigned to core
} // end setup()
////
void mqttCallBack( char* topic, byte* payload, unsigned int length )
{
  if ( client.connected() )
  {
    String s;
    for ( int i = 0; i < length; i++ )
    {
      s += (char)payload[i];
    }
    log_i( "incomming... topic %s payload %s", topic, s);
  }
}
////
void connectToMQTT()
{
  client.setCallback( mqttCallBack );
  if ( client.connect(clientID, mqtt_username, mqtt_password) )
  {
    log_i("Connected to MQTT Broker!");
  } else {
    log_i("Connection to MQTT Broker failed...");
  }
  log_i("MQTT Connected");
}
//
void connectToWiFi()
{
  struct tm timeinfo;
  const char* ntpServer = "pool.ntp.org";
  const long  gmtOffset_sec = -25200;
  const int   daylightOffset_sec = 3600;
  log_i( "Connecting to WiFi " );
  WiFi.begin( SSID, PWD );
  vTaskDelay( 750);
  while ( WiFi.status() != WL_CONNECTED )
  {
    log_i(".");
    vTaskDelay( 800 );
  }
  log_i( "WiFi Connected - ");
  configTime(gmtOffset_sec, daylightOffset_sec, ntpServer);
}
////
void fDoTheThing( void * pvParameters )
{
  xEventGroupWaitBits (eg, evtSetupBME_Complete, pdTRUE, pdTRUE, portMAX_DELAY ); //
  connectToWiFi();
  connectToMQTT();
  while (1)
  {
    xEventGroupSetBits( eg, evtDoBME ); // trigger tasks
    xSemaphoreTake( sema_ReadBME680, portMAX_DELAY ); // wait for task to be done
    log_i( "entering deep sleep" );
    client.disconnect();
    WiFi.disconnect(true);
    WiFi.mode(WIFI_OFF);
    esp_sleep_enable_timer_wakeup( (60000000 * 6) ); // set timer to wake up every 60000000uS (1 minute) * 6
    //esp_sleep_enable_timer_wakeup( (60000000) ); // set timer to wake up every 60000000uS (1 minute)
    esp_deep_sleep_start();
  } //while(1)
  vTaskDelete ( NULL );
} // void fDoTheThing( void * pvParameters )
////
void fDoBME ( void *pvParameters )
{
  float hum_score, gas_score;
  while (!bme.begin())
  {
    log_i("Could not find BME680 sensor!");
    vTaskDelay( 10 );
  }
  log_i("Found BME680 sensor!");
  bme.setTemperatureOversampling(BME680_OS_8X);
  bme.setHumidityOversampling(BME680_OS_2X);
  bme.setPressureOversampling(BME680_OS_4X);
  bme.setIIRFilterSize(BME680_FILTER_SIZE_3);
  bme.setGasHeater(320, 150); // 320*C for 150 ms
  float Temperature = 0.0f;
  float Pressure = 0.0f;
  float Humidity = 0.0f;
  float gas_reference = 0.0;
  float hum_reference = 40.0f;
  int   getgasreference_count = 0;
  float gas_lower_limit = 5000.0f;   // Bad air quality limit
  float gas_upper_limit = 50000.0f;  // Good air quality limit 
  float air_quality_score = 0.0f;
  xEventGroupSetBits( eg, evtSetupBME_Complete ); // trigger task to begin
  for ( ;; )
  {
    xEventGroupWaitBits (eg, evtDoBME, pdTRUE, pdTRUE, portMAX_DELAY ); //
    Temperature = (bme.readTemperature() * 1.8f) + 32.0f; // (Celsius x 1.8) + 32
    Pressure = bme.readPressure() / 133.3223684f; //converts to mmHg
    Humidity = bme.readHumidity();
    //Calculate humidity contribution to IAQ index
    gas_reference = GetGasReference(); // get 10 gas readings and average.
    if ( Humidity >= 38.0f && Humidity <= 42.0f )
    {
      hum_score = 0.25f * 100.0f; // Humidity +/-5% around optimum
    } else  { //sub-optimal
      if (Humidity < 38)
      {
        hum_score = 0.25f / hum_reference * Humidity * 100.0f;
      } else {
        hum_score = ((-0.25f / (100.0f - hum_reference) * Humidity) + 0.416666f) * 100.0f;
      }
    }
    gas_score = (0.75 / (gas_upper_limit - gas_lower_limit) * gas_reference - (gas_lower_limit * (0.75 / (gas_upper_limit - gas_lower_limit)))) * 100.0f;
    //Combine results for the final IAQ index value (0-100% where 100% is good quality air)
    air_quality_score = hum_score + gas_score;
    log_i( "Temperature %f C, Pressure %f hPa, Humidity %f, IAQ %f", Temperature, Pressure, Humidity, air_quality_score );
    client.publish( "Home/oTemperature", String(Temperature).c_str() );
    client.publish( "Home/oHumidity", String(Humidity).c_str() );
    client.publish( "Home/oGas_Resistance", String(air_quality_score).c_str() );
    client.publish( "Home/oPressure", String(Pressure).c_str() );
    xSemaphoreGive ( sema_ReadBME680 );
  } // for loop
  vTaskDelete ( NULL );
} // void fDoBMP ( void *pvParameters )
////
float GetGasReference(){
  // Now run the sensor for a burn-in period, then use combination of relative humidity and gas resistance to estimate indoor air quality as a percentage.
  float gas_reference = 0.0f;
  int readings = 10;
  for (int i = 1; i <= readings; i++){ // read gas for 10 x 0.150mS = 1.5secs
    gas_reference += bme.readGas();
  }
  gas_reference = gas_reference / readings;
  return gas_reference;
}
////
void loop() {}
////