Fail writing dht11 sensor in mysql with nodeMCU

Question

I'm having trouble writing my data collected with the DHT11 sensor using PHP + MYSQL with the nodeMCU (ESP8266MCU).

On the serial monitor says that it is not possible to connect to the server and not execute the get command

#include <ESP8266WiFi.h>
#include <WiFiClient.h>
#include <SimpleDHT.h>

// WiFi - Coloque aqui suas configurações de WI-FI
const char ssid[] = "WIFI-D767480";
const char psw[] = "986557D124D480";

// Site remoto - Coloque aqui os dados do site que vai receber a requisição GET
const char http_site[] = "192.168.23.1";
const int http_port = 80;

// Variáveis globais
WiFiClient client;
IPAddress server(192,168,23,1); //Endereço IP do servidor - http_site
int pinDHT11 = D2;
SimpleDHT11 dht11;

void setup() {
  delay(30000); //Aguarda 30 segundos 
  Serial.begin(9600);
  Serial.println("NodeMCU - writing data in BD via GET");
  Serial.println("Waiting connection");

  // Tenta conexão com Wi-fi
  WiFi.begin(ssid, psw);
  while ( WiFi.status() != WL_CONNECTED ) {
    delay(100);
    Serial.print(".");
  }
  Serial.print("\nWI-FI sucefull connection: ");
  Serial.println(ssid);

}

void loop() {

  //Leitura do sensor DHT11
  delay(3000); //delay entre as leituras
  byte temp = 0;
  byte humid = 0;
  if (dht11.read(pinDHT11, &temp, &humid, NULL)) {
    Serial.print("Fail in sensor.");
    return;
  }

  Serial.println("writing data in BD: ");
  Serial.print((int)temp); Serial.print(" *C, "); 
  Serial.print((int)humid); Serial.println(" %");

  // Envio dos dados do sensor para o servidor via GET
  if ( !getPage((int)temp,(int)humid) ) {
    Serial.println("GET request failed");
  }
}

// Executa o HTTP GET request no site remoto
bool getPage(int temp, int humid) {
  if ( !client.connect(server, http_port) ) {
    Serial.println("Failed to connect to site ");
    return false;
  }
  String param = "?temp=" + String(temp) + "&humid=" + String(humid); //Parâmetros com as leituras
  Serial.println(param);
  client.println("GET /weather/insert_weather.php" + param + " HTTP/1.1");
  client.println("Host: ");
  client.println(http_site);
  client.println("Connection: close");
  client.println();
  client.println();

    // Informações de retorno do servidor para debug
  while(client.available()){
    String line = client.readStringUntil('\r');
    Serial.print(line);
  }
  return true;
}

I tried to modify the following line, but without success:

client.println("GET /weather/insert_weather.php" + param + " HTTP/1.1");

for: client.println("GET /weather/insert_weather.php" + param); and: client.println("GET /weather/insert_weather.php?+param+\r\n");

the error is in the attached imageenter image description here I tried others code too, but withoud sucess.

My php code is:

<?php
$temp = filter_input(INPUT_GET, 'temp', FILTER_SANITIZE_NUMBER_FLOAT);
$humid = filter_input(INPUT_GET, 'humid', FILTER_SANITIZE_NUMBER_FLOAT);
if (is_null($temp) || is_null($humid) ) {
  //Gravar log de erros
  die("Dados inválidos");
} 
$servername = "localhost";
$username = "root";
$password = "root";
$dbname = "maker";
$conn = new mysqli($servername, $username, $password, $dbname);
if ($conn->connect_error) {
  //Gravar log de erros
  die("Não foi possível estabelecer conexão com o BD: " . $conn->connect_error);
} 
$sql = "INSERT INTO weather (wea_temp, wea_humid) VALUES ($temp,$humid)";

if (!$conn->query($sql)) {
  //Gravar log de erros
  die("Erro na gravação dos dados no BD");
}
$conn->close();
?>

I'm using WAMP v. 3.1.17 tank you!

Warning: You are wide open to SQL Injections and should really use parameterized prepared statements instead of manually building your queries. They are provided by PDO or by MySQLi. Never trust any kind of input, especially that which comes from the client side. Even when your queries are executed only by trusted users, you are still in risk of corrupting your data. — Dharman

exloser exloser · Accepted Answer · 2019-03-18T06:00:59

try using a POST request instead of a GET and in your PHP code have it use $_POST[myvariable] to get the contents of the body of the HTTP request. Also, there is an HTTP library for the ESP8266 that you can get that has functions made to send HTTP requests. Here is a snippet from one of my projects where I did pretty much exactly the same thing that you are trying to do now

 if(WiFi.status()== WL_CONNECTED){   //Check WiFi connection status

  HTTPClient http;    //Declare object of class HTTPClient

  postData = "reading=" + readingData + "&data=" + ADCData; //create the string that 
  // will be sent in the POST request

  http.begin("http://192.168.0.179:80/phptesting.php");      //Specify request 
  destination
  //send it to a .php file which can extract data from POST requests.

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

  int httpCode = http.POST(postData);   //Send the request
  String payload = http.getString();                  //Get the response payload

  Serial.println(httpCode);   //Print HTTP return code
  Serial.println(payload);    //Print request response payload. If you get anything 
  besides '200' something is up.

  http.end();  //Close connection

Try this and if it doesn't work let me know and I can send you the rest of the code from this project.

Fail writing dht11 sensor in mysql with nodeMCU

1 Answers