ESP32 Project #10 Insert Data into MySQL Database using PHP and Arduino IDE

Hello! In this project I’ll show you how to build ESP32 client to insert sensor reading data into a MySQL database using PHP script.

Requirements

On this project, we’ll need:

  • BMP180 sensor
  • Arduino IDE
  • Breadboard
  • Jumper wires
  • USB cable
  • Hosting server and domain name
  • PHP script to insert data into MySQL and display it on a webpage
  • MySQL database

Hosting PHP Application and MySQL Database

This is the high level overview of this project by randomnerdtutorials.

High Level Overview

MySQL Database

To create a MySQL database, go to Tools > Database manager > New Database. You will be asked to input the database name, username, and password. Remember the credentials for the next use.

Create Database
PhpMyAdmin
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
)
SensorData Table

PHP script

Back to the 000webhost and go to Tools > File Manager > Upload Files. You will be asked to upload a file. For this, copy the PHP script to a text editor and save it to 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;
}
// 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";
Upload the .php file
Web display

Display Database Content

Back to the File Manager, upload another .php file to display database content to the web. Copy the following code and save it to esp-data.php. Again, don’t forget to change the credentials.

<!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>
// 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";
esp-data

Finally, ESP32

Prepare the parts and components needed for this project. This project’s schematic is still the same with the previous project:

  • VCC — VIN
  • SDA — GPIO 21
  • SCL — GPIO 22
Wiring
/*
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_BMP085.h>
// Replace with your network credentials
const char* ssid = "FAMILY";
const char* password = "password";
// REPLACE with your Domain name and URL path or IP address with path
const char* serverName = "http://chintyaw.000webhostapp.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 = "BMP180";
String sensorLocation = "Home";
#define SEALEVELPRESSURE_HPA (1013.25)Adafruit_BMP085 bmp; // I2Cvoid 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 = bmp.begin(0x76);
if (!status) {
Serial.println("Could not find a valid BMP180 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 + "&sensor=" + sensorName
+ "&location=" + sensorLocation + "&value1=" + String(bmp.readTemperature())
+ "&value2=" + String(bmp.readAltitude()) + "&value3=" + String(bmp.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 BMP180 sensor)
//String httpRequestData = "api_key=tPmAT5Ab3j7F9&sensor=BMP180&location=Home&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(5000);
}
Serial Monitor showed the value1, value2, and value3

Result

Open the web and voila!

Weather Station

Problems

Before the weather station can be displayed, I experienced a problem. The sensor worked perfectly fine, but the database couldn’t be updated. I tried to find out what was wrong.

https://chintyaw.000webhostapp.com/esp-data.phpexpected:
http://chintyaw.000webhostapp.com/esp-data.php
// REPLACE with your Database name
$dbname = "esp_data";
// REPLACE with Database user
$username = "esp_board";
// REPLACE with Database user password
$password = "password";
// REPLACE with your Database name
$dbname = "id16568877_esp_data";
// REPLACE with Database user
$username = "id16568877_esp_board";
// REPLACE with Database user password
$password = "password";

Final say

In this project I’ve learned about how to create my domain and how to insert sensor readings to SQL database and display it to the web domain. I experienced some problems and thanks to my friend I could solve the problem :)