Issue getting ESP8266 to update MySQL

I finally finished building out my new environmental controllers. I am currently using Mega 2560s so these ESP8266s will give me the data collection that I've been wanting for some time now.


I've been learning how to work with php scripts but it hasn't been the easiest for me to grasp. After working through a bunch of mistakes I have it so I can see it on my network. I currently have it set as an access point and when I connect to it with my laptop, it gives me an IP address and I can ping the ESP.

On the PC I have XAMPP setup with Apache and MySQL. Both services are installed and running fine.There are no other apps using the ports(80, 443 and 3306). I've read through the log files, but nothing pops out at me. I created a user in PMA that has full rights to the db and I can login with it just fine in console.

#include <Arduino.h>
#include <Wire.h>
#include <LCD_I2C.h>
#include <ESP8266WiFi.h>
#include <ESP8266HTTPClient.h>
#include "Adafruit_SHT31.h"
//#include "ESP8266WebServer.h"
//#include "ESPAsyncWebServer.h"

bool enableHeater = false;
uint8_t loopCnt = 0;

Adafruit_SHT31 sht31 = Adafruit_SHT31();

//#define Addr 0x20
//#define Addr 0x44
//I2C Addresses 0x20(I2C Expander), 0x27(LCD), 0x44(SHT31)
LCD_I2C lcd(0x27, 16, 2); // Default address of most PCF8574 modules, change accordingly

//AsyncWebServer server(80);



// Set AP credentials
#define AP_SSID "Flower_1"
#define AP_PASS "flower123"

#define RELAY_ON 0
#define RELAY_OFF 1

//#define Relay_1 1
//#define Relay_2 2
//#define Relay_3 3
//#define Relay_4 4

//IPAddress local_ip(10, 0, 0, 240);
////IPAddress gateway(10, 0, 0, 240);
//IPAddress gateway(10, 0, 0, 1);
//IPAddress subnet(255, 255, 255, 0);

// Declare global variables which will be uploaded to server
String sendval, sendval2, postData;


void setup() {

  Wire.begin();
  Serial.begin(115200);

  lcd.begin(false);
  lcd.backlight();

  if (! sht31.begin(0x44)) {
    Serial.println("Couldn't find SHT31");
    while (1) delay(1);
  }

  if (sht31.isHeaterEnabled())
    delay(300);

  //digitalWrite(Relay_1, RELAY_OFF);
  //digitalWrite(Relay_2, RELAY_OFF);
  //digitalWrite(Relay_3, RELAY_OFF);
  //digitalWrite(Relay_4, RELAY_OFF);

  //pinMode(Relay_1, OUTPUT);
  //pinMode(Relay_2, OUTPUT);
  //pinMode(Relay_3, OUTPUT);
  //pinMode(Relay_4, OUTPUT);
  //delay(300);

  WiFi.mode(WIFI_AP);
  //name for the access point and 8 character password
  WiFi.softAP("flower1", "flower123");
  delay(200);
  //while (WiFi.softAPgetStationNum() != 1) { //loop here while no AP is connected to this station
  //Serial.print(".");
  //delay(100);
  //}

  // Begin WiFi
  WiFi.begin(WIFI_SSID, WIFI_PASS);

  // Connecting to WiFi...
  Serial.print("Connecting to ");
  Serial.print(WIFI_SSID);

  // Connected to WiFi
  Serial.println();
  Serial.print("Connected! IP address: ");
  Serial.println(WiFi.localIP());

  Serial.println();
  Serial.print("Setting soft-AP configuration ... ");
  Serial.println(WiFi.softAPConfig(local_ip, gateway, subnet) ? "Ready" : "Failed!");

  Serial.print("Setting soft-AP ... ");
  Serial.println(WiFi.softAP("Flower_1", "flower123") ? "Ready" : "Failed!");

  Serial.print("Soft-AP IP address = ");
  Serial.println(WiFi.softAPIP());
  delay(500);

  Serial.println();
  Serial.println("Connected!");
  Serial.print("IP address for network ");
  Serial.print(WIFI_SSID);
  Serial.print(" : ");
  Serial.println(WiFi.localIP());
  Serial.print("IP address for network ");
  Serial.print(AP_SSID);
  Serial.print(" : ");
  Serial.print(WiFi.softAPIP());

}

void loop() {

  float temperature = sht31.readTemperature();
  temperature = (temperature * 1.8) + 32;
  float humidity = sht31.readHumidity();
  // Toggle heater enabled state every 30 seconds
  // An ~3.0 degC temperature increase can be noted when heater is enabled
  if (++loopCnt == 30) {
    enableHeater = !enableHeater;
    sht31.heater(enableHeater);
    Serial.print("Heater Enabled State: ");
    if (sht31.isHeaterEnabled())
      Serial.println("ENABLED");
    else
      Serial.println("DISABLED");

    loopCnt = 0;
  }

  Serial.print("Relative Humidity : ");
  Serial.print(humidity);
  Serial.println(" %RH");
  Serial.print("Temperature in Fahrenheit : ");
  Serial.print(temperature);
  Serial.println(" F");

  delay(500);

  lcd.setCursor(0, 0);
  lcd.print("Temp - ");
  lcd.println(temperature);
  lcd.print(" F");
  lcd.setCursor(0, 1);
  lcd.print("Hum  - ");
  lcd.println(humidity);
  lcd.print(" %");
  delay(100);

  //WiFiClient wifiClient;
  HTTPClient http;
  sendval = float(temperature);
  sendval2 = float(humidity);
  postData = "sendval=" + sendval + "&sendval2=" + sendval2;
  http.begin("http://192.168.4.2/dbwrite.php");
  http.addHeader("Content-Type", "application/x-www-form-urlencoded");  //Specify content-type header
  int httpCode = http.POST(postData);   // Send POST request to php file and store server response code in variable named httpCode
  Serial.println("Values are, sendval = " + sendval + " and sendval2 = " + sendval2 );
  // if connection established then do this
  if (httpCode == 200) {
    Serial.println("Values uploaded successfully."); Serial.println(httpCode);
    String webpage = http.getString();  // Get html webpage output and store it in a string
    Serial.println(webpage + "\n");
  } else {
    // if failed to connect then return and restart
    Serial.println(httpCode);
    Serial.println("Failed to upload values. \n");
    http.end();
    return;
  }

This is the serial output

Relative Humidity : 41.31 %RH
Temperature in Fahrenheit : 72.73 F
Values are, sendval = 72.73 and sendval2 = 41.31
-1
Failed to upload values. 

Thanks for reading. I appreciate any assistance or direction.

Do you see entries in the XAMPP access_log file similar to the sample one here? :

If you change your code (sketch and PHP) to use the HTTP GET method instead of the POST method, then you can test the server part of the code by issuing a querystring command in a web browser and check that the data has got into the database. Something like

http://192.168.4.2/dbwrite.php?sendval=345&sendval2=987

EDIT

This is not exactly appropriate to your case but is an example of using the HTTP GET method, however you may be able use something: Arduino Project Hub You may also be able to find better examples which more closely match your setup.

Maybe try

  postData = "dbwrite.php?sendval=" + sendval + "&sendval2=" + sendval2;
  http.begin("http://192.168.4.2");
  http.addHeader("Content-Type", "application/x-www-form-urlencoded");  //Specify content-type header
  int httpCode = http.POST(postData);   // Send POST request to php file and store server response code in variable named httpCode

If that doesn't help, try talking out all the softAP code.

I know the OP is trying to get the http thingy to work and it does work.

Has the OP considered if they are going to use several ESP's that another more easily extensible scheme may work a bit better?

I run a Raspberry Pi that I, of its many uses, use as a MQTT Broker. I have 13 ESP32's that communicate with the MQTT Broker. On the RPi I use a Python program to interface with my website. Instead of a Python program Node-Red could have been used.

MQTT Broker and Node-Red can be run on a PC as well.

@6v6gt
I do have a log file filled with entries like that. I'll write a GET command in and see what happens and I'll report back. Thx

@PaulRB
Thanks Paul. I'll also give this a try. If I do remove the softAP lines how does the ESP communicate with the client, my laptop in this case? I suppose I could assign a static IP to it since I know what IP it will assign to it.

@Idahowalker
I am indeed using more. There will be a total of 3 controllers, for now, maybe one more in the future. Once deployed, they'll all connect to a desktop and insert their respective data there. From there, I'll connect to that PC to read/graph the results.

I've heard of MQTT and Node-Red, but I have no experience with either.

The Microcontroller library tool to use is PubSubClient, GitHub - knolleary/pubsubclient: A client library for the Arduino Ethernet Shield that provides support for MQTT., that connects to a MQTT Broker which works pretty good.

Eclipse Mosquitto, the MQTT Broker is free.

Node-RED (nodered.org) is free and is far easier to use than coding, though I prefer to code my own interface.

I'll look into them. Thx

Does it matter that the library you linked is for the ethernet shield as opposed to the ESP?

Never noticed that it was just for the Ethernet shield. I use it with an ESP32's WIFi without issue.

And you might take a look at his NodeRed examples, knolleary (Nick O'Leary) · GitHub.

Didn't work. This was the serial output...

Connected!
IP address for network S.4 : (IP unset)
IP address for network Flower_1 : 192.168.4.1Relative Humidity : 51.40 %RH
Temperature in Fahrenheit : 61.72 F
Values are, sendval = 61.72 and sendval2 = 51.40
-1
Failed to upload values. 


Looking into it now. It'll take me a bit to absorb it.

Another thing I noticed using the ESP modules is they lose their programming if they're disconnected from a power source.

I was imagining you had a wi-fi router/AP that both the laptop and esp could connect to. This is the way I have done it on previous projects. Yes, you could assign a static IP to the laptop in the router's config pages, but as a test, just hard code the laptop's IP into the esp code. As long as you don't reboot the laptop or the router, it's IP probably won't change.

Never had that happen to me before.

So the ESP will communicate on its default IP without any lines of code?

EDIT....
Scratch that. It seems that it does indeed communicate. I set a static IP in my 10.x subnet for the ESP as it didn't talk well from the laptop. That said, it still didn't work. -1 error

Yeah it's odd. I've never had it happen on the megas and unos that I've had running for years now.

So I tried another sketch and the results are still the same. The ESP grabs an IP from my router and I can ping it just fine.

Below is the sketch, the php write/read files, and the error log output.

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

#include <Wire.h>
#include "Adafruit_SHT31.h"

// Replace with your network credentials
const char* ssid     = "S.4";
const char* password = "imolayellow";

// REPLACE with your Domain name and URL path or IP address with path
const char* serverName = "http://10.0.0.187/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 = "SHT31";
String sensorLocation = "Test";

bool enableHeater = false;
uint8_t loopCnt = 0;

Adafruit_SHT31 sht31 = Adafruit_SHT31();


void setup() {
  Serial.begin(115200);
  Wire.begin();
  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());

  if (! sht31.begin(0x44)) {
    Serial.println("Couldn't find SHT31");
    while (1) delay(1);
  }

  if (sht31.isHeaterEnabled())
    delay(300);

}

void loop() {

  float temperature = sht31.readTemperature();
  temperature = (temperature * 1.8) + 32;
  float humidity = sht31.readHumidity();

  //Check WiFi connection status
  if (WiFi.status() == WL_CONNECTED) {
    WiFiClient client;
    HTTPClient http;

    // Your Domain name with URL path or IP address with path
    http.begin(client, 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(sht31.readTemperature())
                             + "&value2=" + String(sht31.readHumidity());
    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);
}
<?php

/*
  Rui Santos
  Complete project details at https://RandomNerdTutorials.com/esp32-esp8266-mysql-database-php/
  
  Permission is hereby granted, free of charge, to any person obtaining a copy
  of this software and associated documentation files.
  
  The above copyright notice and this permission notice shall be included in all
  copies or substantial portions of the Software.
*/

$servername = "localhost";

// REPLACE with your Database name
$dbname = "example_esp_data";
// REPLACE with Database user
$username = "espdata";
// REPLACE with Database user password
$password = "espdata";

// 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;
}
<!DOCTYPE html>
<html><body>
<?php
/*
  Rui Santos
  Complete project details at https://RandomNerdTutorials.com/esp32-esp8266-mysql-database-php/
  
  Permission is hereby granted, free of charge, to any person obtaining a copy
  of this software and associated documentation files.
  
  The above copyright notice and this permission notice shall be included in all
  copies or substantial portions of the Software.
*/

$servername = "localhost";

// REPLACE with your Database name
$dbname = "example_esp_data";
// REPLACE with Database user
$username = "espdata";
// REPLACE with Database user password
$password = "espdata";

// 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>Value 1</td> 
        <td>Value 2</td>
        <td>Value 3</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>
10.0.0.187 - - [20/Apr/2022:22:44:21 -0400] "GET /esp-data.php HTTP/1.1" 200 310 "-" "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/100.0.4896.88 Safari/537.36"
10.0.0.187 - - [20/Apr/2022:22:44:53 -0400] "GET /post-esp-data.php HTTP/1.1" 200 30 "-" "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/100.0.4896.88 Safari/537.36"
::1 - - [20/Apr/2022:23:01:09 -0400] "POST /phpmyadmin/index.php?route=/ HTTP/1.1" 200 1641 "-" "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/100.0.4896.88 Safari/537.36"

2022-04-20 23:04:39 0 [Note] InnoDB: Mutexes and rw_locks use Windows interlocked functions
2022-04-20 23:04:39 0 [Note] InnoDB: Uses event mutexes
2022-04-20 23:04:39 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
2022-04-20 23:04:39 0 [Note] InnoDB: Number of pools: 1
2022-04-20 23:04:39 0 [Note] InnoDB: Using SSE2 crc32 instructions
2022-04-20 23:04:39 0 [Note] InnoDB: Initializing buffer pool, total size = 16M, instances = 1, chunk size = 16M
2022-04-20 23:04:39 0 [Note] InnoDB: Completed initialization of buffer pool
2022-04-20 23:04:39 0 [Note] InnoDB: 128 out of 128 rollback segments are active.
2022-04-20 23:04:39 0 [Note] InnoDB: Creating shared tablespace for temporary tables
2022-04-20 23:04:39 0 [Note] InnoDB: Setting file 'C:\xampp\mysql\data\ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
2022-04-20 23:04:39 0 [Note] InnoDB: File 'C:\xampp\mysql\data\ibtmp1' size is now 12 MB.
2022-04-20 23:04:39 0 [Note] InnoDB: Waiting for purge to start
2022-04-20 23:04:39 0 [Note] InnoDB: 10.4.24 started; log sequence number 321208; transaction id 344
2022-04-20 23:04:39 0 [Note] InnoDB: Loading buffer pool(s) from C:\xampp\mysql\data\ib_buffer_pool
2022-04-20 23:04:39 0 [Note] Plugin 'FEEDBACK' is disabled.
2022-04-20 23:04:39 0 [Note] Server socket created on IP: '::'.
2022-04-20 23:04:39 0 [Note] InnoDB: Buffer pool(s) load completed at 220420 23:04:39
2022-04-20 23:04:39 0 [Note] Reading of all Master_info entries succeeded
2022-04-20 23:04:39 0 [Note] Added new Master_info '' to hash table
2022-04-20 23:04:39 0 [Note] C:\xampp\mysql\bin\mysqld.exe: ready for connections.
Version: '10.4.24-MariaDB'  socket: ''  port: 3306  mariadb.org binary distribution

httpRequestData: api_key=tPmAT5Ab3j7F9&sensor=SHT31&location=Test&value1=20.05&value2=48.04
Error code: -1

I would suggest following @6v6gt 's advice. Temporarily change from POST to GET and test the php script from your browser.

I am currently trying to figure out how to do exactly that. I'm learning how to program here on the fly so adapting the language doesn't come readily to me yet.

You need to change your PHP script so that it will work for GET instead of POST:

if ($_SERVER["REQUEST_METHOD"] == "GET") {

then you should be able to test the php script by using using your browser, and be able to see if it is failing and any error messages it is sending, opening a new browser tab and entering the address

http://10.0.0.187/post-esp-data.php?api_key=tPmAT5Ab3j7F9&sensor=SHT31&location=Test&value1=12.3&value2=45.6

I'm still not sure where to put that line or what other lines need to be changed or how specifically.

Looking at the lines in Loop here, I have Post commands throughout. I can't imagine that I would just simply change POST to GET.

  postData = "sendval=" + sendval + "&sendval2=" + sendval2;
  //http://192.168.4.2/dbwrite.php?sendval=345&sendval2=987
  http.begin("http://10.0.0.187/dbwrite.php");
  http.addHeader("Content-Type", "application/x-www-form-urlencoded"); //Specify content-type header
  int httpCode = http.POST(postData);   // Send POST request to php file and store server response code in variable named httpCode
  Serial.println("Values are, sendval = " + sendval + " and sendval2 = " + sendval2 );
  // if connection established then do this
  if (httpCode == 200) {
    Serial.println("Values uploaded successfully."); Serial.println(httpCode);
    String webpage = http.getString(); // Get html webpage output and store it in a string
    Serial.println(webpage + "\n"); 
  } else {
    // if failed to connect then return and restart
    Serial.println(httpCode);
    Serial.println("Failed to upload values. \n");
    http.end();
    return;
  }