Proyek 9 Pengembangan Sistem Embedded, ESP32 : Database
Selamat Pagi. Kembali bersama saya pada rangkaian proyek bonus Pengembangan Sistem Embedded menggunakan ESP32. Kali ini kita akan membangun ESP32 client yang dapat membuat request HTTP POST ke PHP script untuk meng-input data sensor pada database.
Preparation
Pada proyek kali ini kita akan menggunakan domain dan server hosting. Saya menggunakan 000webhost yang menyediakan fasilitas tersebut secara gratis. Pertama-tama, buatlah nama website seperti pada gambar berikut.
Selanjutnya, buatlah database dengan mengakses Tools > Database Manager > New Database. Untuk membuat database kita membutuhkan nama, username, dan password.
Setelah database dibuat, klik tombol Manage dan pilih PhpMyAdmin. Selanjutnya, kita akan diarahkan untuk membuka phpmyadmin.
Pada database yang tertera, pilih tab SQL dan isi kode seperti berikut dan klik Go
CREATE TABLE SensorMeasurements (
id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
sensor VARCHAR(30) NOT NULL,
location VARCHAR(30) NOT NULL,
temperature VARCHAR(10),
pressure VARCHAR(10),
altitude VARCHAR(10),
reading_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)
Lakukan pengecekan jika database telah selesai dibuat dengan klik tab Structure
Selanjutnya, kita akan membuat file PHP script. Buatlah file bernama post-esp-data.php yang berisi kode berikut. Ganti dbname, username, dan password seperti pada pengaturan sebelumnya.
<?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 = $temperature = $pressure = $altitude = "";
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"]);
$temperature = test_input($_POST["temperature"]);
$pressure = test_input($_POST["pressure"]);
$altitude = test_input($_POST["altitude"]);
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
$sql = "INSERT INTO SensorMeasurements (sensor, location, temperature, pressure, altitude)
VALUES ('" . $sensor . "', '" . $location . "', '" . $temperature . "', '" . $pressure . "', '" . $altitude . "')";
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;
}Buka tab Tools > File Manager lalu klik Upload Files. Buka folder public_html dan klik upload files pada kanan atas. Lalu, klik Select Files dan pilih post-esp-data.php. Klik upload
Data dapat diakses pada https://yourdomain.000webhostapp.com/post-esp-data.php. Output yang dikeluarkan adalah sebagai berikut. Hal ini disebabkan karena belum ada data dengan request HTTP POST.
Selanjutnya, buatlah sebuah file dengan nama esp-data.php pada folder yang sama berisi kode seperti berikut. Jangan lupa untuk mengubah dbname, username, dan password.
<!DOCTYPE html>
<html>
<head>
<style>
table {
border-collapse: collapse;
width: 100%;
margin-bottom: 1em;
color: #333333;
font-family: Arial, sans-serif;
font-size: 14px;
text-align: left;
background-color: #F5F5F5;
}
table td, table th {
padding: 8px;
border: 1px solid #DDDDDD;
}
table th {
background-color: #B3B3B3;
color: #FFFFFF;
font-weight: bold;
}
table tr:nth-child(even) {
background-color: #EFEFEF;
}
table tr:nth-child(odd) {
background-color: #FFFFFF;
}
h1 {
text-align: center;
}
</style>
</head>
<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, temperature, pressure, altitude, reading_time FROM SensorMeasurements ORDER BY id DESC";
echo '<h1>ESP32 Database</h1>
<table cellspacing="5" cellpadding="5">
<tr>
<td>ID</td>
<td>Sensor</td>
<td>Location</td>
<td>Temperature (*C)</td>
<td>Pressure (hPa)</td>
<td>Altitude (m)</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_temperature = $row["temperature"];
$row_pressure = $row["pressure"];
$row_altitude = $row["altitude"];
$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 + 7 hours (you can change 7 to any number)
$row_reading_time = date("Y-m-d H:i:s", strtotime("$row_reading_time + 7 hours"));
echo '<tr>
<td>' . $row_id . '</td>
<td>' . $row_sensor . '</td>
<td>' . $row_location . '</td>
<td>' . $row_temperature . '</td>
<td>' . $row_pressure . '</td>
<td>' . $row_altitude . '</td>
<td>' . $row_reading_time . '</td>
</tr>';
}
$result->free();
}
$conn->close();
?>
</table>
</body>
</html>File ini dapat diakses dengan link https://yourdomain.000webhostapp.com/esp-data.php. Output yang dikeluarkan adalah sebagai berikut. File ini digunakan untuk menunjukkan data yang telah dideteksi dari sensor.
Project
Selanjutnya, siapkanlah alat-alat berikut
- ESP32 DOIT DEVKIT V1
- Breadboard jenis 830 Point Solderless
- Kabel USB Type A to micro USB
- Sensor eksternal (BMP280)
- Male to male jumper wires
- Software Arduino IDE yang sudah kita install dan setting seperti pada postingan sebelumnya Proyek 1 Pengembangan Sistem Embedded, Memulai dengan ESP32 : LED Blink
Rangkailah alat-alat tersebut seperti pada gambar
Selanjutnya, upload kode di bawah ini pada aplikasi Arduino IDE. Jangan lupa untuk menekan tombol boot ketika mengunggah kode.
/*
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.
*/
#include <WiFi.h>
#include <WiFiClientSecure.h>
#include <HTTPClient.h>
#include <Wire.h>
#include <Adafruit_Sensor.h>
#include <Adafruit_BMP280.h>
// Replace with your network credentials
const char* ssid = "WRITE_YOUR_SSID_HERE";
const char* password = "WRITE_YOUR_PASSWORD_HERE";
// REPLACE with your Domain name and URL path or IP address with path
const char* serverName = "https://yourdomain.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 = "BMP280";
String sensorLocation = "Home";
/*#include <SPI.h>
#define BMP_SCK 18
#define BMP_MISO 19
#define BMP_MOSI 23
#define BMP_CS 5*/
#define SEALEVELPRESSURE_HPA (1013.25)
Adafruit_BMP280 bmp; // I2C
//Adafruit_BMP280 bmp(BMP_CS); // hardware SPI
//Adafruit_BMP280 bmp(BMP_CS, BMP_MOSI, BMP_MISO, BMP_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 = bmp.begin(0x76);
if (!status) {
Serial.println("Could not find a valid BMP280 sensor, check wiring or change I2C address!");
while (1);
}
}
void loop() {
//Check WiFi connection status
if(WiFi.status()== WL_CONNECTED){
WiFiClientSecure *client = new WiFiClientSecure;
client->setInsecure(); //don't use SSL certificate
HTTPClient https;
// Your Domain name with URL path or IP address with path
https.begin(*client, serverName);
// Specify content-type header
https.addHeader("Content-Type", "application/x-www-form-urlencoded");
// Prepare your HTTP POST request data
String httpRequestData = "api_key=" + apiKeyValue + "&sensor=" + sensorName
+ "&location=" + sensorLocation + "&temperature=" + String(bmp.readTemperature()) +
+ "&pressure=" + String(bmp.readPressure()/100.0F) + "&altitude=" + String(bmp.readAltitude(SEALEVELPRESSURE_HPA)) + "";
Serial.print("httpRequestData: ");
Serial.println(httpRequestData);
// You can comment the httpRequestData variable above
// then, use the httpRequestData variable below (for testing purposes without the BMP280 sensor)
//String httpRequestData = "api_key=tPmAT5Ab3j7F9&sensor=BMP280&location=Office&value1=24.75&value2=49.54&value3=1005.14";
// Send HTTP POST request
int httpResponseCode = https.POST(httpRequestData);
// If you need an HTTP request with a content type: text/plain
//https.addHeader("Content-Type", "text/plain");
//int httpResponseCode = https.POST("Hello, World!");
// If you need an HTTP request with a content type: application/json, use the following:
//https.addHeader("Content-Type", "application/json");
//int httpResponseCode = https.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
https.end();
}
else {
Serial.println("WiFi Disconnected");
}
//Send an HTTP POST request every 30 seconds
delay(30000);
}Bukalah link berikut https://yourdomain.000webhostapp.com/esp-data.php untuk melihat data yang telah diambil oleh sensor BMP280.
Closing
Sekian proyek kali ini, sampai jumpa di proyek selanjutnya! Dadah
