ESP32/ESP8266 Publish Data to Raspberry Pi LAMP Server

In this project you’ll build an ESP32 or ESP8266 client that makes an HTTP POST request to a Raspberry Pi LAMP server (Linux, Apache, MySQL, PHP). The Raspberry Pi has a PHP script to insert data (sensor readings) into a MySQL database.

ESP32 ESP8266 Insert Data into MySQL Database running on Raspberry Pi

You’ll also have a web page that displays the sensor readings, timestamps and other information stored in the database. You can visualize your data locally from any browser in your network.

Note: you can make this project accessible from anywhere in the world follow this project.

As an example, we’ll be using a BME280 sensor connected to an ESP board. You can modify the code provided to send readings from a different sensor or use multiple boards.

To build this project, you’ll use these technologies:

  • Running LAMP server on Raspberry Pi
  • ESP32 or ESP8266 programmed with Arduino IDE
  • PHP script to insert data into MySQL and display it on a web page
  • MySQL database to store readings

0. Prerequisites

Before continuing with this tutorial:

If you like home automation and you want to build a complete home automation system, I recommend downloading my home automation course.

After having your Raspberry Pi board prepared with Raspbian OS and a LAMP server, you can continue with this tutorial.

1. Hosting Your PHP Application and MySQL Database – Raspberry Pi

The goal of this project is to have your Raspberry Pi running a LAMP server that allows you to store sensor readings from the ESP32 or ESP8266. You can visualize the readings from any browser in your local network.

Here’s a high level overview:

Hosting PHP Application and MySQL Database on Raspberry Pi to post ESP32 or ESP8266 Sensor Readings

If you want to make your data accessible from anywhere, I recommend reading one of these projects instead:

2. Preparing Your MySQL Database

After installing a LAMP server and phpMyAdmin on Raspberry Pi, you can login into phpMyAdmin. After that, follow the next steps to create your database and SQL table.

Open your browser and type http://Your-Raspberry-Pi-IP-Address/phpmyadmin), your should see the login page for phpMyAdmin web interface.

Raspberry Pi Open phpMyAdmin Login Page

After login, you should see a similar page:

Raspberry Pi phpMyAdmin Local Database MySQL

Creating a database

1. Select the “Databases” menu at the top, complete the “Create database” fields:

  • esp_data
  • utf8mb4_general_ci

Then, press the Create button:

Raspberry Pi phpMyAdmin create new database

That’s it! Your new database was created successfully. Now, save your database name because you’ll need it later:

  • Database name: esp_data

Creating a SQL table

After creating your database, in the left sidebar select your database name esp_data:

Raspberry Pi phpMyAdmin open new database

Important: make sure you’ve opened the esp_data database. Then, click the SQL tab. If you don’t follow these exact steps and run the SQL query, you might create a table in the wrong database.

Copy the SQL query in the following snippet:

CREATE TABLE SensorData (
    id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    sensor VARCHAR(30) NOT NULL,
    location VARCHAR(30) NOT NULL,
    value1 VARCHAR(10),
    value2 VARCHAR(10),
    value3 VARCHAR(10),
    reading_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)

View raw code

Open the “SQL” tab, paste it in the SQL query field (highlighted with a red rectangle) and press the Go button to create your table:

Raspberry Pi phpMyAdmin run SQL query

This should appear if your table was created successfully:

Raspberry Pi phpMyAdmin run SQL table created

After that, you should see your newly created table called SensorData in the esp_data database as shown in the figure below:

Raspberry Pi phpMyAdmin table created empty

3. PHP Script HTTP POST – Insert Data in MySQL Database

In this section, we’re going to create a PHP script that is responsible for receiving incoming requests from the ESP32 or ESP8266 and inserting the data into a MySQL database.

You can either run the next commands on a Raspberry Pi set as a desktop computer or using an SSH connection.

If you’re connected to your Raspberry Pi with an SSH connection, type the next command to create a file in /var/www/html directory:

pi@raspberrypi:~ $ nano /var/www/html/post-esp-data.php
Raspberry Pi Save post esp data PHP file

Note: if you’re following this tutorial and you’re not familiar with PHP or MySQL, I recommend creating these exact files. Otherwise, you’ll need to modify the ESP sketch provided with different URL paths.

Copy the following PHP script to the newly created file (post-esp-data.php):

<?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 = "REPLACE_WITH_YOUR_DATABASE_NAME";
// REPLACE with Database user
$username = "REPLACE_WITH_YOUR_USERNAME";
// REPLACE with Database user password
$password = "REPLACE_WITH_YOUR_PASSWORD";

// 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;
}

View raw code

Before saving the file, you need to modify the $dbname, $username and $password variables with your unique details:

// Your Database name
$dbname = "esp_data";
// Your Database user
$username = "root";
// Your Database user password
$password = "YOUR_USER_PASSWORD";

After adding the database name, username and password, save the file (Ctrl+X, y, and Enter key) and continue with this tutorial.

Raspberry Pi Saved file post esp data PHP

If you try to access your RPi IP address in the next URL path, you’ll see the following:

http://You-Raspberry-Pi-IP-Address/post-esp-data.php
ESP32 ESP8266 PHP Page Save Readings to Raspberry Pi Database MySQL

4. PHP Script – Display Database Content

Create another PHP file in the /var/www/html directory that will display all the database content in a web page. Name your new file: esp-data.php

pi@raspberrypi:~ $ nano /var/www/html/esp-data.php
Raspberry Pi Save esp data PHP file

Edit the newly created file (esp-data.php) and copy the following PHP script:

<!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 = "REPLACE_WITH_YOUR_DATABASE_NAME";
// REPLACE with Database user
$username = "REPLACE_WITH_YOUR_USERNAME";
// REPLACE with Database user password
$password = "REPLACE_WITH_YOUR_PASSWORD";

// 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>

View raw code

Add the $dbname, $username and $password:

// Your Database name
$dbname = "esp_data";
// Your Database user
$username = "root";
// Your Database user password
$password = "YOUR_USER_PASSWORD";

Save the file (Ctrl+X, y, and Enter key) and continue with this project.

Raspberry Pi Saved file esp data PHP

If you try to access your Raspberry Pi IP Address in the following URL path, you’ll see the following:

http://Your-Raspberry-Pi-IP-Address/esp-data.php
ESP32 ESP8266 PHP Page view Readings temperature humidity pressure BME280

That’s it! If you see that empty table printed in your browser, it means that everything is ready. In the next section, you’ll learn how to insert data from your ESP32 or ESP8266 into the database.

5. Preparing Your ESP32 or ESP8266

This project is compatible with both the ESP32 and ESP8266 boards. You just need to assemble a simple circuit and upload the sketch provided to insert temperature, humidity, pressure and more into your database every 30 seconds.

Parts Required

For this example we’ll get sensor readings from the BME280 sensor. Here’s a list of parts you need to build the circuit for this project:

You can use the preceding links or go directly to MakerAdvisor.com/tools to find all the parts for your projects at the best price!

Schematics

The BME280 sensor module we’re using communicates via I2C communication protocol, so you need to connect it to the ESP32 or ESP8266 I2C pins.

BME280 wiring to ESP32

The ESP32 I2C pins are:

  • GPIO 22: SCL (SCK)
  • GPIO 21: SDA (SDI)

So, assemble your circuit as shown in the next schematic diagram (read complete Guide for ESP32 with BME280).

BME280 wiring to ESP32

Recommended reading: ESP32 Pinout Reference Guide

BME280 wiring to ESP8266

The ESP8266 I2C pins are:

  • GPIO 5 (D1): SCL (SCK)
  • GPIO 4 (D2): SDA (SDI)

Assemble your circuit as in the next schematic diagram if you’re using an ESP8266 board (read complete Guide for ESP8266 with BME280).

BME280 wiring to ESP8266

Recommended reading: ESP8266 Pinout Reference Guide

ESP32/ESP8266 Code

We’ll program the ESP32/ESP8266 using Arduino IDE, so you must have the ESP32/ESP8266 add-on installed in your Arduino IDE. Follow one of the next tutorials depending on the board you’re using:

After installing the necessary board add-ons, copy the following code to your Arduino IDE, but don’t upload it yet. You need to make some changes to make it work for you.

/*
  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.

*/

#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     = "REPLACE_WITH_YOUR_SSID";
const char* password = "REPLACE_WITH_YOUR_PASSWORD";

// REPLACE with your Domain name and URL path or IP address with path
const char* serverName = "http://example.com/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){
    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(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);  
}

View raw code

Setting your network credentials

You need to modify the following lines with your network credentials: SSID and password. The code is well commented on where you should make the changes.

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

Setting your serverName

You also need to type your Raspberry Pi IP address, so the ESP publishes the readings to your LAMP server.

const char* serverName = "http://Your-Raspberry-Pi-IP-Address/post-esp-data.php";

For example:

const char* serverName = "http://192.168.1.86/post-esp-data.php";

Now, you can upload the code to your board. It should work straight away both in the ESP32 or ESP8266 board. If you want to learn how the code works, read the next section.

How the code works

This project is already quite long, so we won’t cover in detail how the code works, but here’s a quick summary:

  • Import all the libraries to make it work (it will import either the ESP32 or ESP8266 libraries based on the selected board in your Arduino IDE)
  • Set variables that you might want to change (apiKeyValue, sensorName, sensorLocation)
  • The apiKeyValue is just a random string that you can modify. It’s used for security reasons, so only anyone that knows your API key can publish data to your database
  • Initialize the serial communication for debugging purposes
  • Establish a Wi-Fi connection with your router
  • Initialize the BME280 to get readings

Then, in the loop() is where you actually make the HTTP POST request every 30 seconds with the latest BME280 readings:

// 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) + "";

int httpResponseCode = http.POST(httpRequestData);

You can comment the httpRequestData variable above that concatenates all the BME280 readings and use the httpRequestData variable below for testing purposes:

String httpRequestData = "api_key=tPmAT5Ab3j7F9&sensor=BME280&location=Office&value1=24.75&value2=49.54&value3=1005.14";

Demonstration

After completing all the steps, let your ESP board collect some readings and publish them to your server.

ESP32 BME280 Arduino IDE MySQL

If everything is correct, this is what you should see in your Arduino IDE Serial Monitor:

ESP32 ESP8266 Publish Readings to Raspberry Pi Database MySQL

If you open your Raspberry Pi IP address in this URL path /esp-data.php:

http://Your-Raspberry-Pi-IP-Address/esp-data.php

You should see the all the readings stored in your database. Refresh the web page to see the latest readings:

Raspberry Pi PHP MySQL view ESP32 ESP8266 readings

You can also go to phpMyAdmin to manage the data stored in your SensorData table. You can delete it, edit, etc…

Raspberry Pi phpMyAdmin table publish ESP32 ESP8266 readings

Wrapping Up

In this tutorial you’ve learned how to publish sensor data into a database in your own local Raspberry Pi LAMP server.

The example provided is as simple as possible so that you can understand how everything works. After understanding this example, you may change the appearance of the table, publish different sensor readings, publish from multiple ESP boards, and much more.

You might also like reading:

I hope you liked this project. If you have any questions, post a comment below and we’ll try to get back to you.

If you like ESP32, you might consider enrolling in our course “Learn ESP32 with Arduino IDE“. You can also access our free ESP32 resources here.

Thank you for reading.



Learn how to build a home automation system and we’ll cover the following main subjects: Node-RED, Node-RED Dashboard, Raspberry Pi, ESP32, ESP8266, MQTT, and InfluxDB database DOWNLOAD »
Learn how to build a home automation system and we’ll cover the following main subjects: Node-RED, Node-RED Dashboard, Raspberry Pi, ESP32, ESP8266, MQTT, and InfluxDB database DOWNLOAD »

Recommended Resources

Build a Home Automation System from Scratch » With Raspberry Pi, ESP8266, Arduino, and Node-RED.

Home Automation using ESP8266 eBook and video course » Build IoT and home automation projects.

Arduino Step-by-Step Projects » Build 25 Arduino projects with our course, even with no prior experience!

What to Read Next…


Enjoyed this project? Stay updated by subscribing our newsletter!

59 thoughts on “ESP32/ESP8266 Publish Data to Raspberry Pi LAMP Server”

  1. Hello Rui,
    thank you for this very interesting information.
    One question: Do you think is it possible and useful
    to install the LAMB Server on a Raspi 2 ?
    Greetings from Germany and thank your for very good site.
    Erhard

    Reply
  2. I’ve been trying to do this for along time. Now I built it for Raspberry Pi and in windows XAMPP for solar weather station. I added voltage reading. Thanks

    Reply
  3. Hey Rui and Sara,
    thanks a lot for your well done and easy to understand/follow tutorial!
    But i do have a small problem which i was not able to eliminate by my self (I’m total newbe to Arduino, PHP etc.). Im pretty shure that i followed your instuctions in every detail, but my ESP won’t write Data into the database. Everything looks like it does in your Screenshots, esp_data.php and post_esp_data.php are responding and my serial monitor shows that data is obtained by the sensor.
    What can i try or what could i have done wrong? 🙁
    Best regards
    Marius

    Reply
    • OK, mistakes were made and lessons learned.
      Problem was caused by a misspelling.
      Again: Very good Tutorial! 😀

      Reply
        • Hi everyone,
          first of, the tutorial is really great, everything worked smoothly.

          Unfortunately, I am running into the same issue, you described Ed.
          The ESP obtains the data and shows no error, when doing the HTTPRequest.
          But when I check the esp-data.php URL or log in to phpmyadmin, there is no data in the database.
          Can you tell me, what solved your problem, Ed?
          Or maybe give me some hints, where to look/what to google, Sara?
          I am a beginner, and without any error message, it’s hard to start troubleshooting.

          Reply
          • Hi again,
            not sure how I solved it, but something was wrong in the ESP8266 code. After redoing it, I got it to work.
            Great tutorial! Thanks a lot for writing this, helped me massively to get this data logger to work quickly!

          • I am in the same spot, not a fan or just trying to ‘redo’ everything, would prefer a fix, or what data you need from me or places to look. Fix while its broken is the only way to learn. Something to do with Allowed Hosts maybe?

  4. Hi sara,

    I’m very much interested in this project. I’m looking for a standalone weather station, where we can store my sensor readings.

    It’s possible to connect multiple ESP8266 with the same Raspberry Pi? Because I want to maintain multiple sensor readings in the same database.

    What are the possibilities are there?

    Please help me….

    Reply
  5. Hi, I’ve connected several esp8266 modules to the wifi network and they’re reporting the temp and humidity nicely, however, they’re also advertising themselves as APs – is there a way of stopping that?

    Thanks

    Reply
    • Try this:

      “void setup() {
      Serial.begin(115200);
      WiFi.mode (WIFI_STA);
      WiFi.begin(ssid, password);”
      Adding Wifi.mode(WIFI_STA);
      worked for me ok, though i am having some trouble getting the post-esp-data.php to work, everything else works fine.

      Reply
  6. Hi
    Thanks for the great tutorial. Had a few hiccups along the way, but got it working!

    I want to store the data on an external hard drive attached to my server. I’m wondering how I can change the default folder in mysql to the hard drive. Any idea?

    Thanks!

    Reply
  7. Hi
    great tutorial! I’ve set it all up and it is working perfectly.
    However, I would like to not show an entire table, but instead just have the web page to show the current value/sensor input from the esp32 board. So for example I have a soil sensor collecting data from a plant. On the web page I would just write “This is the current analog value for plant 1: ”

    So how can I do this – I’m new to php.

    Reply
  8. Hi, I followed this tutorial, and unfortunately, when I execute the post request I get a -1 error code.

    I used Postman to ensure that the RPi is fully working and it does.

    What do you suggest I do?

    Reply
    • I was getting this originally too. I figured out my issue when I started setting up a route through Postman. I had forgotten a dot in my IP address in my sketch.

      Everything was working fine for me for a while, but then randomly I’ll get the -1 (connection refused) errors, then randomly it’ll start working again. It doesn’t seem to be very stable for me.

      Reply
  9. The problem was with my home router. For some reason it blocked my ESP from communicating with the RPi due to a feature called device isolation. I went around it by making my RPi an access point and connect the ESP directly to it. Notice that using such method, you will not have an internet connection on your RPi via Wifi, but you jave to use an ethernet cable.

    Reply
    • Thanks for sharing your procedure. Quick question. For a weather project I will always have the ESP32 wired to an RPi4b. The RPi4b will transmit all data wirelessly or through ethernet. Is it possible to disable the ESP32 wifi connection to save some power?

      Reply
  10. I’m lost, tried everything I could think of, searched the web and still have an issue 🙁
    In my browser window I see this;
    Index of /
    [ICO] Name Last modified Size Description
    [ ] esp-data.php 2021-04-12 16:04 2.3K
    [DIR] phpmyadmin/ 2017-01-23 13:20 –
    [ ] post-esp-data.php 2021-04-11 14:11 2.1K
    Apache/2.4.38 (Raspbian) Server at 192.168.1.86 Port 80

    Click on phpmyadmin, enter username (admin) and password
 loads with no problem

    Click on esp-data.php and get this: Connection has failed: Access denied for user ‘admin’@’localhost’ (using password: YES)

    Click on post-esp-data.php and get this: No data posted with HTTP POST.

    Running an ESP8266, serial window getting this: httpRequestData: api_key=tPmAT5Ab3j7F9&sensor=BME280&location=Office&value1=25.76&value2=56.35&value3=1014.36
    HTTP Response code: 200

    Where did the wheels come off the bus?

    Reply
  11. Resolved –
    Do not use $ symbol for the first character in your password. PHP see’s the $ symbol as designator for a variable, ignoring the password and throwing an error message.

    Reply
  12. Very nice Project. After adapting to my problem it works smoothly.

    I’m working on expanding my Project. Therefore I wanna have a dozen of ESP32 with sensors send data to the server. I think it might overhead the capabilities of the raspberry. Is there a way to locally store data on the esp for a few seconds and send bulks of data to the server?
    I’m working with movement sensors so I’m getting hundreds of readings per second.

    Thank you!

    Reply
  13. This is a great tutorial and exactly what I’m needing for my project. I have encountered a strange issue though. All was working well, and then at some point my raspberry pi must have rebooted, and when it did, it got a different IP address – despite having set my router to assign a static IP to it by MAC. I changed my sketch for my esp32 to use the new IP address and am getting ‘connection refused’ errors on my POST. I am able to successfully POST to the pi using Postman from my laptop, but for some reason, the ESP32 is getting blocked… If I let it run long enough, then the esp32 will randomly start working and POST successfully, then a while later will start getting ‘connection refused’ again. Is there something I can do to make this more stable?

    Reply
    • I figured out this issue. I forgot about a wifi extender that I have in my basement that uses the same SSID. My raspberry pi would occasionally connect to it instead of to my normal access point and the extender would assign it a different IP. I just had to block my Pi MAC address on the extender and all works fine.

      Reply
  14. Hi,
    Your website is gloden source for everything related to electronics, thanks!
    I have a Website running, a Raspberry Pi 4b running, and ESP32 gathering data from a BME280. I have the RPi4b dedicated for an All-sky-camera and node-red displaying temp, hum, and pressure data. From here how would I ba able to send the BME280 data so it can be displayed on my website? I already have images of the all-sky-cam displayed on my website. Can this be done with MySQL? Your input is highly apriciated.

    Reply
  15. Hello,
    I’m wanting to add additional values (value4, value5) to my setup. I understand to continue using an API key for security, value4 and value5 would need to be appended to the api_key. Additionally, the api_key must equal api_key_value. How was your api_key_value generated so I can make a new one to match my new api_key?

    Reply
  16. I’d like to have separate table for each ESP like table SensorData1 for the first ESP, SensorData2 for the second ESP and so on. Is it possible for Raspi to publish data to multiple table based on the ESP id using simple if else if?

    Reply
    • You can do that absolutly. This can be achieved by simply creating an API endpoint and table per ESP on the MySQL DB hosted on the RasPi. Every ESP should POST a request to its respective API end point.

      ESP POST request -> RasPi API endpoint -> MySQL Table

      Reply
      • Thanks for the reply. So basically, I need to create X different APIs and tables if I use X ESPs, then the API key will be used in if-else to determine which table the Raspi should choose to publish the data from ESP, right?

        Reply
        • You dont have to use an if statement to determine which table to insert data to if you created a specific API for each ESP.

          API endpoint on the Rasp Pi: One endpoint for every ESP/Table(specific URL for example -> ‘http://localhost/ESPdata1.php’)

          If you want for example ESP1 to insert data to Table1, in the API endpoint you should specify Table1 in the insert query. Same goes for the other ESPs, Tables and endpoints.

          In the end you should have ‘n’ number of Tables and endpoints per ESP.

          Reply
          • Unfortunately I don’t know how to create API endpoint on Raspi since I’m not so familiar with it. Maybe I’ll just try using the if-else method since I think that’s easier for me. Thanks for your reply

  17. I set the delay to 1000ms but the actual delay happens is 30000ms. I’ve checked my code and it seems the problem lies on the http.POST that take a long time before it can proceed to next line. Any suggestion to fix the delay caused by http.POST?

    Reply
  18. Hello,
    I followed your tutorial and I managed without too much problem to make everything work. I am now trying to access my database from anywhere.. So I redirected a port on my box with the IP address of the raspberry and I thought I could access it by http://my external IP: port/esp-data.php..
    But I have the message: my external IP does not authorize the connection…
    yet I made other programs with redirects…..I don’t understand! can you help me. THANKS

    Reply
  19. Hi Guys,
    I have been trying to implement this project for several days now without success, I cannot seem to get rid of the -1 status code (connection refused?) when using POST on esp32. Everything looks good all my results match your screenshots and code, I am at a loss. Please help, before I tear out what little hair I have left :). Seriously, any help or guidance would be greatly appreciated.
    Many thanks in anticipation.
    Ted

    Reply
  20. Hi Rui, Sara.
    A wonderful tutorial! Everything works fine with your code and explanations, but trouble comes for me when I try to adapt the database stucture to my needs. If I change the name of the fields, or the amount of fields, or the name of the database, I keep on receiving an 500 error code. Can the API key be the same if I make any of these changes, or it depends on the names/amount of fields? The page Sara suggested for API keys generation (https://codepen.io/corenominal/pen/rxOmMJ) just gives API keys generated regardless of any of the previous things (unless I’m using the page wrongly). So, does the API key work as a password, or the script recalculates it everytime? Can the API key be ignored/omitted? (security is not a must this time) The fact (obvious) is that I have little/almost no experience with mysql, and I’m going mad with a persistent 500 error with the post code. I’ve carefully adapted both the scripts and the post code to the new names and fields, but it seems I’m making some kind of mistake (and I’d bet it’s not a typo error) Could you please help me?

    Reply
    • Hi.
      You can use the same API key we use in our code, any other string combination you may want to use as an API key, or you can remove the API key.
      Just make sure the same API key is used throughout the project.
      Regards,
      Sara

      Reply
  21. Bonjour a tous les deux,

    Merci beaucoup pour ce tuto et toute ces explications. RĂ©alisation sans problĂšme avec un PI3B+ supportant un serveur Lamp.
    Je souhaite faire la mĂȘme chose avec une sonde ds18b20 mais j’avoue ne pas y arriver.
    J’essaye de compiler un projet simple d’une sonde ds18b20 sur un esp8266 ( qui fonctionne ) et l’intĂ©grer dans votre projet en en supprimant les lignes du capteur BME280 mais j’avoue avoir beaucoup de mal.
    Si je peux avoir un peu d’aide?
    Merci beaucoup

    Reply
  22. Hi folks,
    again nice tutorial.
    Got some trouble with “Error: -1” message on the ESP-Board.
    That mean “connection refudes” – the http-request could not be send to the server, as i figured out.
    I missed to add “http:” in front of my servername-variable.

    Reply
  23. Sara, Rui, once again, my thanks for the quality of your tutorials. I’ve just put this one into practice, and not surprisingly, it works very well. Next step now!

    Reply
  24. I initially followed this tutorial back in 2019, and it was running for several months with up to 6 sensors. I then abandoned it, until this weekend… I now find that, although I can see data arriving into the database, when I look at http://“IP of server”/esp-data.php, it shows no data (just headings). I’ve tried re-creating post-esp-data.php and esp-data.php, but this hasn’t made any difference…

    BTW, the database now has 1,767,205 records! I did wonder if the browser was just taking ages to load all the data but it stopped loading after about 5 seconds…

    Reply
    • Yeah. Those huge datasets have bit me in the past. I was never good at php, but I know in other languages you can do some asynchronous calls that’ll retrieve smaller chunks of data and setup paging so it only displays a set number of rows per page. Not sure if that’s a possibility in php

      Reply

Leave a Comment

Download Our Free eBooks and Resources

Get instant access to our FREE eBooks, Resources, and Exclusive Electronics Projects by entering your email address below.