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:
- ESP32 board
- 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
Let’s start!
Hosting PHP Application and MySQL Database
This is the high level overview of this project by randomnerdtutorials.
I need to buy a domain and hosting server. The tutorial recommends using BlueHost or Digital Ocean. But, I found a free hosting 000webhost. First, sign up to 000webhost and create a domain. You can choose your name or anything and create password. Then, we will create the database.
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.
Then, go to PhpMyAdmin to run SQL query.
Copy this query and click ‘Go’ on the bottom right corner.
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
)Then, you can see table named ‘SensorData’ when you go to ‘Structure’.
Okay, finished the SQL part, let’s go to the PHP script.
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;
}Change the database credentials to the earlier database you’ve created (with ID).
// 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";Save and upload the file.
You can access it by going to yourdomain.000webhostapp.com/post-esp-data.php
After this appeared, we can proceed to the next step.
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>Don’t forget to insert the credentials with the ID.
// 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";Again, upload the file and you can check it by visiting yourdomain.000webhostapp.com/post-esp-data.php. This should appear and you’re done.
Finally, ESP32
Prepare the parts and components needed for this project. This project’s schematic is still the same with the previous project:
BMP180 — ESP32
- GND — GND
- VCC — VIN
- SDA — GPIO 21
- SCL — GPIO 22
Then, prepare Arduino IDE and copy this code.
/*
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);
}
Don’t forget to change the credentials and the serverName. Connect the ESP32, verify and upload. Open the serial monitor at 115200 baud rate.
Result
Open the web and voila!
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.
First, serverName. I typed it as https, it should be http.
https://chintyaw.000webhostapp.com/esp-data.phpexpected:
http://chintyaw.000webhostapp.com/esp-data.php
Second, database credentials. I didn’t include the ID earlier so it can’t recognize the database. Hence, the database couldn’t be updated.
// REPLACE with your Database name
$dbname = "esp_data";
// REPLACE with Database user
$username = "esp_board";
// REPLACE with Database user password
$password = "password";It should be:
// REPLACE with your Database name
$dbname = "id16568877_esp_data";
// REPLACE with Database user
$username = "id16568877_esp_board";
// REPLACE with Database user password
$password = "password";After that, I verified and uploaded the sketch. And voila, it worked fine!
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 :)
Hope this story can help and stay tune for the next projects!