Visualize Your Sensor Readings from Anywhere in the World (ESP32/ESP8266 + MySQL + PHP)

In this project, you’ll create a web page that displays sensor readings in a plot that you can access from anywhere in the world. In summary, you’ll build an ESP32 or ESP8266 client that makes a request to a PHP script to publish sensor readings in a MySQL database.

ESP32 ESP8266 Insert Data into MySQL Database using PHP and Arduino IDE - Visualize Your Sensor Readings from Anywhere in the World

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 create this project, you’ll use these technologies:

  • ESP32 or ESP8266 programmed with Arduino IDE
  • Hosting server and domain name
  • PHP script to insert data into MySQL database and display it on a web page
  • MySQL database to store readings
  • PHP script to plot data from database in charts

You might also find helpful reading these projects:

Watch the Video Demonstration

To see how the project works, you can watch the following video demonstration:

1. Hosting Your PHP Application and MySQL Database

The goal of this project is to have your own domain name and hosting account that allows you to store sensor readings from the ESP32 or ESP8266. You can visualize the readings from anywhere in the world by accessing your own server domain. Here’s a high level overview of the project:

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

I recommend using one of the following hosting services that can handle all the project requirements:

  • Bluehost (user-friendly with cPanel): free domain name when you sign up for the 3-year plan. I recommend choosing the unlimited websites option;
  • Digital Ocean: Linux server that you manage through a command line. I only recommended this option for advanced users.

Those two services are the ones that I use and personally recommend, but you can use any other hosting service. Any hosting service that offers PHP and MySQL will work with this tutorial. If you don’t have a hosting account, I recommend signing up for Bluehost.

Get Hosting and Domain Name with Bluehost »

When buying a hosting account, you’ll also have to purchase a domain name. This is what makes this project interesting: you’ll be able to go your domain name (http://example.com) and see your ESP readings.

If you like our projects, you might consider signing up to one of the recommended hosting services, because you’ll be supporting our work.

Note: you can also run a LAMP (Linux, Apache, MySQL, PHP) server on a Raspberry Pi to access data in your local network. However, the purpose of this tutorial is to publish readings in your own domain name that you can access from anywhere in the world. This allows you to easily access your ESP readings without relying on a third-party IoT platform.

2. Preparing Your MySQL Database

After signing up for a hosting account and setting up a domain name, you can login to your cPanel or similar dashboard. After that, follow the next steps to create your database, username, password and SQL table.

Creating a database and user

1. Type “database” in the search bar and select “MySQL Database Wizard”.

CPanel select MySQL database wizard to create db

2. Enter your desired Database name. In my case, the database name is esp_data. Then, press the “Next Step” button:

ESP32 ESP8266 CPanel Create MySQL Database

Note: later you’ll have to use the database name with the prefix that your host gives you (my database prefix in the screenshot above is blurred). I’ll refer to it as example_esp_data from now on.

3. Type your Database username and set a password. You must save all those details, because you’ll need them later to establish a database connection with your PHP code.

ESP32 ESP8266 CPanel Create MySQL Database User and Password

That’s it! Your new database and user were created successfully. Now, save all your details because you’ll need them later:

  • Database name: example_esp_data
  • Username: example_esp_board
  • Password: your password

Creating a SQL table

After creating your database and user, go back to cPanel dashboard and search for “phpMyAdmin”.

ESP32 ESP8266 CPanel Open PHPMyAdmin

In the left sidebar, select your database name example_esp_data and open the “SQL” tab.

ESP32 ESP8266 PHPMyAdmin Open Database

Important: make sure you’ve opened the example_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 Sensor (
    id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    value1 VARCHAR(10),
    value2 VARCHAR(10),
    value3 VARCHAR(10),
    reading_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)

View raw code

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

ESP32 ESP8266 PHPMyAdmin Create SQL Sensor Table

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

ESP32 ESP8266 PHPMyAdmin View SQL Database

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

In this section, we’re going to create a PHP script that receives incoming requests from the ESP32 or ESP8266 and inserts the data into a MySQL database.

If you’re using a hosting provider with cPanel, you can search for “File Manager”:

ESP32 ESP8266 CPanel Open Edit PHP Files with File Manager

Then, select the public_html option and press the “+ File” button to create a new .php file.

ESP32 ESP8266 CPanel Create New PHP File using File Manager

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.

Create a new file in /public_html with this exact name and extension: post-data.php

PHP Create New file post data .php

Edit the newly created file (post-data.php) and copy the following snippet:

<?php
/*
  Rui Santos
  Complete project details at https://RandomNerdTutorials.com
  
  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 = $value1 = $value2 = $value3 = "";

if ($_SERVER["REQUEST_METHOD"] == "POST") {
    $api_key = test_input($_POST["api_key"]);
    if($api_key == $api_key_value) {
        $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 Sensor (value1, value2, value3)
        VALUES ('" . $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 = "example_esp_data";
// Your Database user
$username = "example_esp_board";
// Your Database user password
$password = "YOUR_USER_PASSWORD";

After adding the database name, username and password, save the file and continue with this tutorial. If you try to access your domain name in the next URL path, you’ll see the message:

http://example.com/post-data.php
ESP32 ESP8266 Test POST Data PHP URL

4. PHP Script – Visualize Database Content in a Chart

Create another PHP file in the /public_html directory that will plot the database content in a chart on a web page. Name your new file: esp-chart.php

PHP Create New file esp chart data .php

Edit the newly created file (esp-chart.php) and copy the following code:

<!--
  Rui Santos
  Complete project details at https://RandomNerdTutorials.com
  
  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.

-->
<?php

$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, value1, value2, value3, reading_time FROM Sensor order by reading_time desc limit 40";

$result = $conn->query($sql);

while ($data = $result->fetch_assoc()){
    $sensor_data[] = $data;
}

$readings_time = array_column($sensor_data, 'reading_time');

// ******* Uncomment to convert readings time array to your timezone ********
/*$i = 0;
foreach ($readings_time as $reading){
    // Uncomment to set timezone to - 1 hour (you can change 1 to any number)
    $readings_time[$i] = date("Y-m-d H:i:s", strtotime("$reading - 1 hours"));
    // Uncomment to set timezone to + 4 hours (you can change 4 to any number)
    //$readings_time[$i] = date("Y-m-d H:i:s", strtotime("$reading + 4 hours"));
    $i += 1;
}*/

$value1 = json_encode(array_reverse(array_column($sensor_data, 'value1')), JSON_NUMERIC_CHECK);
$value2 = json_encode(array_reverse(array_column($sensor_data, 'value2')), JSON_NUMERIC_CHECK);
$value3 = json_encode(array_reverse(array_column($sensor_data, 'value3')), JSON_NUMERIC_CHECK);
$reading_time = json_encode(array_reverse($readings_time), JSON_NUMERIC_CHECK);

/*echo $value1;
echo $value2;
echo $value3;
echo $reading_time;*/

$result->free();
$conn->close();
?>

<!DOCTYPE html>
<html>
<meta name="viewport" content="width=device-width, initial-scale=1">
  <script src="https://code.highcharts.com/highcharts.js"></script>
  <style>
    body {
      min-width: 310px;
    	max-width: 1280px;
    	height: 500px;
      margin: 0 auto;
    }
    h2 {
      font-family: Arial;
      font-size: 2.5rem;
      text-align: center;
    }
  </style>
  <body>
    <h2>ESP Weather Station</h2>
    <div id="chart-temperature" class="container"></div>
    <div id="chart-humidity" class="container"></div>
    <div id="chart-pressure" class="container"></div>
<script>

var value1 = <?php echo $value1; ?>;
var value2 = <?php echo $value2; ?>;
var value3 = <?php echo $value3; ?>;
var reading_time = <?php echo $reading_time; ?>;

var chartT = new Highcharts.Chart({
  chart:{ renderTo : 'chart-temperature' },
  title: { text: 'BME280 Temperature' },
  series: [{
    showInLegend: false,
    data: value1
  }],
  plotOptions: {
    line: { animation: false,
      dataLabels: { enabled: true }
    },
    series: { color: '#059e8a' }
  },
  xAxis: { 
    type: 'datetime',
    categories: reading_time
  },
  yAxis: {
    title: { text: 'Temperature (Celsius)' }
    //title: { text: 'Temperature (Fahrenheit)' }
  },
  credits: { enabled: false }
});

var chartH = new Highcharts.Chart({
  chart:{ renderTo:'chart-humidity' },
  title: { text: 'BME280 Humidity' },
  series: [{
    showInLegend: false,
    data: value2
  }],
  plotOptions: {
    line: { animation: false,
      dataLabels: { enabled: true }
    }
  },
  xAxis: {
    type: 'datetime',
    //dateTimeLabelFormats: { second: '%H:%M:%S' },
    categories: reading_time
  },
  yAxis: {
    title: { text: 'Humidity (%)' }
  },
  credits: { enabled: false }
});


var chartP = new Highcharts.Chart({
  chart:{ renderTo:'chart-pressure' },
  title: { text: 'BME280 Pressure' },
  series: [{
    showInLegend: false,
    data: value3
  }],
  plotOptions: {
    line: { animation: false,
      dataLabels: { enabled: true }
    },
    series: { color: '#18009c' }
  },
  xAxis: {
    type: 'datetime',
    categories: reading_time
  },
  yAxis: {
    title: { text: 'Pressure (hPa)' }
  },
  credits: { enabled: false }
});

</script>
</body>
</html>

View raw code

After adding the $dbname, $username and $password save the file and continue with this project.

// Your Database name
$dbname = "example_esp_data";
// Your Database user
$username = "example_esp_board";
// Your Database user password
$password = "YOUR_USER_PASSWORD";

If you try to access your domain name in the following URL path, you’ll see the following:

http://example.com/esp-chart.php
ESP32 ESP8266 Test ESP Data PHP URL

That’s it! If you see three empty charts in your browser, it means that everything is ready. In the next section, you’ll learn how to publish your ESP32 or ESP8266 sensor readings.

To build the charts, we’ll use the Highcharts library. We’ll create three charts: temperature, humidity and pressure over time. The charts display a maximum of 40 data points, and a new reading is added every 30 seconds, but you change these values in your code.

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.

ESP32 vs ESP8266 Development Boards

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 I2C wiring to ESP32 - circuit schematic diagram SDA SCL

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 I2C wiring to ESP8266 - circuit schematic diagram SDA SCL

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
  
  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-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-data.php also needs to have the same key 
String apiKeyValue = "tPmAT5Ab3j7F9";

/*#include <SPI.h>
#define BME_SCK 18
#define BME_MISO 19
#define BME_MOSI 23
#define BME_CS 5*/

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){
    HTTPClient http;
    
    // 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 + "&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&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 domain name, so the ESP publishes the readings to your own server.

const char* serverName = "http://example.com/post-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)
  • 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 + "&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&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 Temperature Humidity and Pressure sensor publishing readings using Arduino IDE, MySQL, and PHP

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

ESP32 ESP8266 BME280 Readings Arduino IDE Serial Monitor

If you open your domain name in this URL path:

http://example.com/esp-chart.php

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

ESP32 ESP8266 View BME280 Sensor Readings (Temperature, Humidity, and Pressure) using PHPMyAdmin and SQL Database

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

ESP32 ESP8266 View Sensor Readings PHPMyAdmin SQL Database

Wrapping Up

In this tutorial you’ve learned how to publish sensor data into a database in your own server domain that you can access from anywhere in the world. This requires that you have your own server and domain name (you can also use a Raspberry Pi for local access).

With this setup you control your server and can move to a different host if necessary. There are many cloud solutions both free and paid that you can use to publish your readings, but those services can have several disadvantages: restrictions on how many readings you can publish, number of connected devices, who can see your data, etc. Additionally, the cloud service can be discounted or change at any time.

The example provided is as simple as possible so that you can understand how everything works. After understanding this example, you may change the web page appearance, 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 program and build projects with the ESP32 and ESP8266 using MicroPython firmware DOWNLOAD »

Learn how to program and build projects with the ESP32 and ESP8266 using MicroPython firmware DOWNLOAD »


Enjoyed this project? Stay updated by subscribing our weekly newsletter!

62 thoughts on “Visualize Your Sensor Readings from Anywhere in the World (ESP32/ESP8266 + MySQL + PHP)”

  1. do you have example that use input data from web server save to database, and from data base, we use data to control machine, for exemple if user input 1(led 1 on), input 2 led 2 on) and 3 to turn off led

  2. I’m glad to read about the possibility to use Raspberry Pi instead of a server. Do you have additional information to do so?
    Friedhelm

  3. Can I do the same by installing PHP and mySQL standalone on my mac ?
    I don’t want to use the hosting services.

    • You can definitely do it. Search for “PHP + MySQL” on Mac OS X and you’ll find plenty of tutorials on that subject.
      Note: with that setup data will be stored locally (not from anywhere in the world).

  4. Hi, When i execute from my FileManager the file esp-chart.php visualize the following lines:

    Notice: Undefined variable: sensor_data in /storage/ssd3/152/2515152/public_html/esp-chart.php on line 38

    From Monitor Arduino I view following:

    httpRequestData: api_key=tPmAT5Ab3j7F9&value1=24.75&value2=49.54&value3=1005.14
    HTTP Response code: 200

    But my database ‘sensor’ don’t update and HTTP response is 200.

    When execute post-data.php in my screen view is the following:
    No data posted with HTTP POST.

    When I view the structure the ‘sensor’ database ,I view following:

    # Name Type Collation Attributes Null Default Comments Extra Action
    1 idPrimary int(6) UNSIGNED No None AUTO_INCREMENT Change Change Drop Drop
    More More
    2 value1 varchar(10) utf8_unicode_ci Yes NULL Change Change Drop Drop
    More More
    3 value2 varchar(10) utf8_unicode_ci Yes NULL Change Change Drop Drop
    More More
    4 value3 varchar(10) utf8_unicode_ci Yes NULL Change Change Drop Drop
    More More
    5 reading_time timestamp No current_timestamp() ON UPDATE CURRENT_TIMESTAMP() Change Change Drop Drop
    More More

    What happens with in this case?

    Thanks you

    • Do you have any values stored in your database already?
      Even though the request is successful… Is data actually being inserted in your Sensor table?

      Regards,
      Rui

      • No, unfortunately only the following appears when I run esp-data.php:
        ID Sensor Location Value 1 Value 2 Value 3 Timestamp

        Please, view the following link:

        qpower.000webhostapp.com/esp-data.php

          • Hello Rui:
            From Monitor Arduino I view following:

            httpRequestData: api_key=tPmAT5Ab3j7F9&value1=24.75&value2=49.54&value3=1005.14
            HTTP Response code: 200

            But my database ‘sensor’ don’t update and HTTP response is 200.

            Is it possible to have some kind of return from the database if it recorded well or not?
            Thanks for helping

          • I’m honestly not sure why that happens… I can only imagine some small error in the post-data.php script (like wrong credentials) that can’t insert data in your Sensor table…

          • I had a problem posting to the database.

            I solved it by noting the URL of the SQL server as indicated in phpMyAdmin and I changed the $servername value from ‘localhost’.

            Thank you Santos’ for this tutorial.

  5. I’m a fan of you guys. You are doing a wonderful job. This tutorial is but one of your great efforts. I appreciate it.
    Please is it possible to make this same platform used in this tutorial to control appliances from anywhere in the world? Thanks for your reply.

    • It’s definitely possible, but it’s a bit tricky. I’ll probably create that tutorial too in a couple of weeks.

      Right now I don’t have any tutorial on that subject.

      • Stunningly good example, like all your others 😊
        I just wanted to add in that if things don’t work immediately as was the case with mine, try talking to your isp internet service provider.
        I put this in my existing site and just couldn’t push data from sensor to database. I KEPT GETTING A FAILURE 301 Redirect Status
        It turned out my isp had as Default a redirect in place. They commented that out and data started to appear in the database, but, not on the page.

        More talking and it turned out this was a question of cache.

        With super cache turned off I now get live data… After refresh…

        I now want to find out what I need to do to have an auto-refresh as data arrives.

        As I say, just my experience, but it may help someone.

        Keep up the great work guys. I have been through my course twice now and feel quite empowered.
        😁😁

  6. got this when visited *****.com/esp-chart.php “Connection failed: Access denied for user ‘*****_esp_brd’@’localhost’ to database ‘*****_esp_data’ “

  7. Ciao, thamks for your fantastic tutorials, i have learned a lot from them.
    In this one i have found a problem. Opening the file esp-chart.php it give me this error and i don’t know how to solve it:

    Fatal error: Uncaught Error: Call to a member function fetch_assoc() on boolean in /home/mhd-01/www.”myhost”.it/htdocs/ESP/esp-chart.php:34 Stack trace: #0 {main} thrown in /home/mhd-01/www.”myhost”.it/htdocs/ESP/esp-chart.php on line 34

    Obviusly in “myhost” i have my domain name

      • Hi Rui,
        I have almost the same error. I’m trying to mimic this data visualisation on my local computer, using XAMPP. I don’t understand your answer to the question of Davide. What do you mean by “leave localhost in the host”?
        This is the error text:
        Fatal error: Uncaught Error: Call to a member function fetch_assoc() on bool in /Applications/XAMPP/xamppfiles/htdocs/websiteSWB/esp-chart.php:34 Stack trace: #0 {main} thrown in /Applications/XAMPP/xamppfiles/htdocs/websiteSWB/esp-chart.php on line 34

        • After hours of internet searching and trial and error I finally had success in using the XAMPP-environment to receive the ESP-data and plotting the chart in my browser locally. First I changed in the ‘esp-chart.php’ file the $username to “root” and the $password to “” (empty). Then in the Arduino sketch I set the servername to “http://192.168.0.16/websiteSWB/post-data.php”. This means: first the IP-address of my computer, followed by the foldername ‘websiteSWB’ which contains the ‘post-data.php file’. The folder is inside the xammp/htdocs/ folder structure.

  8. Hi,
    Im trying to follow and try the tutorial according to the code and step, I managed to setup the mysql database, create post-data.php, and esp-chart.php, seem to be ok, except that for esp-chart.php I didnt see nothing, it did not show the X-Y chart and the title, it just doesnt show anything, I just copy and paste from ur rawcode, please advise, the php code seem to be able to connect to the sql database, it just doest display the html or chart portions of it.

  9. Hi Rui,

    Sent a msg yesterday about my code not working. Actually ended up a problem with the php version stored on my ISP’s server.

    Just a suggestion, adding a sleep mode to the esp’s code, will make this project portable 😉

    Thanks again and keep up with the excellent job!

  10. esp-chart.php is returning me this error:
    Connection failed: Access denied for user ‘xxxx_esp_boa’@’localhost’ (using password: YES)

    My database name, user and password are correct, the same as in post-data.php, which is working fine. My user has all privileges. I have a online mysql server, I work with wordpress websites, so it would have to do the job.
    What could be happening?

    Your project is very interesting, I wish I could see it working for me. Thank you and congratulations.

    Fabio.

    • To be honest that error only happens if you entered the wrong settings in your PHP script… Can you double-check the database name, username, and password again?

      The user must have all the privileges as you’ve mentioned in order to work

    • I have had the same Problem and solved it. My fault was to use the wrong kind of “-Key around the username and password. It was the „username“ instead of “username”.

  11. Forgot to mention the function which solved my php version issue…

    if (! function_exists(‘array_column’)) {
    function array_column(array $input, $columnKey, $indexKey = null) {
    $array = array();
    foreach ($input as $value) {
    if ( !array_key_exists($columnKey, $value)) {
    trigger_error(“Key \”$columnKey\” does not exist in array”);
    return false;
    }
    if (is_null($indexKey)) {
    $array[] = $value[$columnKey];
    }
    else {
    if ( !array_key_exists($indexKey, $value)) {
    trigger_error(“Key \”$indexKey\” does not exist in array”);
    return false;
    }
    if ( ! is_scalar($value[$indexKey])) {
    trigger_error(“Key \”$indexKey\” does not contain scalar value”);
    return false;
    }
    $array[$value[$indexKey]] = $value[$columnKey];
    }
    }
    return $array;
    }
    }

  12. Hi, Rui good work Rui, I want a bit modification, can I transfer and plot my sensor data within micro seconds delay rather than 30 seconds? Actually, I want to plot the real time current wave of my induction motor. So, please suggest what I have to change?

  13. Hi,
    many thanks for the good tutorial. I like the presented architecture very much.
    I use the Highcharts library for the first time. Unfortunately the scaling on the x-axis is not nice because of the crooked values. I haven’t managed to get a reasonable scaling yet.
    Do you have any idea to get this?

    • You can definitely modify the charts look as they are highly customizable (show less points or hide x axis data). Unfortunately I don’t have any examples on that subject…

  14. I have entered the Sensor table in phpMyAdmin in me account at ONE.COM.
    But where and how do I enter the PHP files?
    There is no cPanel file manager.
    Sorry if this is a silly question, I am new to all this sql php stuff.

    • You should definitely have a file manager of some sort where you’ll find a folder called public_html (you might need to contact your host for specific details on where they have that folder).

  15. Hi guys, thanks again for a brilliant tutorial. I have set up an ESP32 and have it logging to my home computer hosting a webserver and SQL database using Wamp64. Your code worked fine after a bit of setting up localhosts on the Wamp server. Also had some errors initially in the chart php code in line 35. The code returned an error until there was some data in the sensor table. The php code is looking for an array which only gets populated when there is some data. After the first posting by the ESP all was fine.
    Keep up these great tutorials………
    Regards
    Alan

  16. Hello
    I followed the tutorial to the letter.
    A problem has arisen when I connect the ESP32 to an external power source, the ESP32 if it turns on but does nothing, does not send data to the web. It only works correctly when it is connected via USB to my PC.
    What could be the problem?

  17. Hi guys,
    just finished a project where I combined 4 of your previous projects:
    1. Sensor data to SQL database and available anywhere
    2. BME280 temp, humidity and pressure sensor data logging
    3. SD1306 OLED display with DS3231 Real Time clock
    4. ESP32 Dual Core for split processing

    Basically, I wanted to capture and display multiple sensor data together with real time video, real time clock data and display this on a dashboard with selectable update timing.
    I used the 2 ESP32 cores to periodically poll the SQL database for data (5 minute intervals) and also to update a Real Time Clock display every second.
    The results were posted to an IOBroker dashboard using Node Red logic behind the scenes. The outcome was a superb display with historical trends updated every 5 minutes and an IP camera live video.
    I have to thank you guys for the great work done on these individual projects which enabled me to get the final result.
    A big shout out to IOBroker and Node Red in providing the open source tools to integrate and display the data in a professional manner.
    Anybody interested in the details please contact me via RNT Forum as I’m pleased to share RNTs valuable work.

  18. Hello and thanks a lot for that tuto. I’m noob on that : just began today
    I followed everything in your tuto.
    The only diff is that i’ve setup a ubuntu server with apache2 MySQL PhpMyadmin etc…
    Created databases etc…
    Finally at the end i loaded the sketch and i only add a auto-refresh in “esp-chart.php”
    I also put a DeepSleep function in the sketch, the chart is working and giving the good values : it’s great

    But on MySQL side i always get that errors :
    Unfortunately iam not a SQL expert so if someone can help me please ?
    Also as a no SQL expert, is it possible to add a button to reset data-base to restart from zero value


    Warning in ./libraries/sql.lib.php#613
    count(): Parameter must be an array or an object that implements Countable

    Backtrace

    ./libraries/sql.lib.php#2128: PMA_isRememberSortingOrder(array)

  19. Hi
    Thanks for another interesting project. Works fine.
    I only wonder if I change my domain to ‘SSL’ or https from http,
    do I need to include WiFiClientSecure etc. in the sketch?

    Did one program that sends to Google Sheet and the fingerprint
    (client.verify(fingerprint, host)) does not seem to make any difference.
    Janne

  20. Hello again 😉
    I’ve a question please :
    I noticed that the chart is limited for one day display history
    Is it possible to custom that view or get more large than now
    a view for one week or one month
    Is it possible to add an option like that in main esp_*.php page ?
    or at minimum setup a larger view

    Waiting for your feedback
    thanks in advanced

  21. Guys, this tutorial is just awesome! Even though I’ve never created a website, a database or even registered a domain before, I was able to complete the whole project and it works perfectly. I managed to change the sensor to others and, with some changes of the script, measure different variables and check them from anywhere in the world! 🙂
    This tutorial is so well explained that it wasn’t as complicated to follow as it may seem at the beginning.

    Thank you very much for posting these projects and helping so many people.
    Best regards,
    Joan

    • Hi Joan.
      Thank you so much for your feedback.
      We spend a lot of time making the tutorials as easy to follow as possible even if you don’t have any experience in these fields.
      We’ll try to create more projects on this subject on a near future (like sending email notifications, setting a threshold on a web form, etc…)
      Regards,
      Sara

  22. Hi Rui, Hi Sara, please let me know how to do to implement Autoconnect on ESP32 (Not ESP8266!)

    I tried to do it but I’m getting compiling errors.

    Thanks

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.