ESP32 Tutorial Part 11 (MySql Database Bagian 2)
Haii temen temen semua, balik lagi di tutorial ESP32. Kali ini kita akan mencoba untuk membuat chart yang berisikan pembacaan data dari sensor BMP280, jadi yukk langsung dicoba aja!
A. Komponen
Komponennya sama persis seperti kemarin
1. 1. ESP32
2. Jumper wire male to male
3. Sensor BMP280 (Ini bebas kok, aku pakenya BMP280 ehehehe)
4. Arduino IDE
5. Micro USB cable
6. WiFi untuk dicoba :)
7. Website/webhost (Bisa pakai yang gratis kayak 000webhost.com)
B. Skema
Nah kalo skema ini kita pake skema kayak gini ya!
Jadi,
SDA kita sambungkan dengan pin D21
SCL kita sambungkan dengan pin D22
GND kita sambungkan dengan pin GND
3v3 kita sambungkan dengan pin 3v3
C. Web
nah sebelum kita memulai compiling di ESP32 nya, kita harus membuat sebuah hosting server dan domain name. Nah disini aku pake 000webhost.com. Nah aku pake tutorial dari youtube, nah untuk link pembuatannya aku alihin di sini ya!
Untuk skema dari ESP32 dan web nya sendiri akan menjadi seperti ini, disclaimer dulu gambarnya aku pake dari randomnerdtutorials.com ya
Lalu setelah ituu kita bisa membuat database dengan nama esp_data
Lalu membuat tabel dengan nama Sensor dengan query seperti berikut
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
)Laluu setelah membuat database dan tabel, kita akan membuat file phpnya
Pergi ke 000webhost.com, lalu klik dashboard, lalu ke file manager.
Nah di file manager, pilih folder file_html lalu buat file php dengan nama post-esp-data.php dengan code seperti berikut
<?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;
}Nah ada yang harus kita ubah yaitu
// Your Database name
$dbname = “example_esp_data”; // Your Database user
$username = “example_esp_board”; // Your Database user password $password = “YOUR_USER_PASSWORD”;Disini kita akan mengubah nama database, username, dan password sesuai dengan yang telah kita buat pada database.
Lalu yang kedua, kita juga akan membuat file dengan nama esp-chart.php pada folder yang sama, isi data file esp-chart.php adalah sebagai berikut
<!--
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>Code ini akan menampilkan chart yang akan ditampilkan ketika kita membuka domain kita
pada hal yang sama, kita harus ganti beberapa atribut seperti
// Your Database name
$dbname = “example_esp_data”; // Your Database user
$username = “example_esp_board”; // Your Database user password $password = “YOUR_USER_PASSWORD”;D. Source Code
Karena sudah selesai dengan urusan database, kita dapat langsung menghubungkan ESP32 dengan laptop. Lalu buka Arduino IDE, dan compile code berikut. Code ini adalah adaptasi dari randomnerdtutorials.com. Karena pada randomnerdtutorials.com menggunakan sensor BME280, sedangkan penulis menggunakan sensor BMP280
/*
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 <SPI.h>
#include <Adafruit_BMP280.h>// Replace with your network credentials
const char* ssid = "nama_wifi";
const char* password = "password_wifi";// REPLACE with your Domain name and URL path or IP address with path
const char* serverName = "http://iariandova.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 = "Rumah";//uncomment the following lines if you’re using SPI
/*#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);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 BME280 sensor, check wiring or change I2C address!");
while (1);
}
bmp.setSampling(Adafruit_BMP280::MODE_NORMAL, /* Operating Mode. */
Adafruit_BMP280::SAMPLING_X2, /* Temperature oversampling */
Adafruit_BMP280::SAMPLING_X16, /* Pressure oversampling */
Adafruit_BMP280::FILTER_X16, /* Filtering. */
Adafruit_BMP280::STANDBY_MS_500); /* Standby time. */
}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 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);
}Lalu compile dan tunggu hingga pada compiler menghasilkan seperti gambar berikut
pada code berikut ubah dengan nama wifi dan password wifi yang kalian gunakan pada device teman-teman
// Replace with your network credentials
const char* ssid = "nama_wifi";
const char* password = "password_wifi";Lalu jika berhasil maka akan muncul gambar seperti dibawah ini
E. Analisis
Pembacaan sensor BMP280 akan dikirimkan ke jaringan yang kita pakai. Lalu jaringan akan mengakses domain yang telah kita buat. Ketika jaringan mengakses file post-esp-data.php, maka akan melakukan publish pembacaan sensor ke database yang telah kita buat. Lalu jaringan akan mengakses file esp-chart.php yang akan menampilkan hasil pembacaan dari database yang sebelumnya sudah di update oleh post-esp-data.php. Sehingga pembacaan nilai akan keluar dan akan menampilkan grafik yang sudah ada pada code esp-chart.php.
Berikut adalah video percobaan dari ESP32 Database bagian duaaa!!
Untuk itu, sekian tutorial ESP32 tentang Chart database menggunakan sensor BMP280 bagian 2. Mohon maaf jika ada kesalahan dari penulis yang masih belajar ini. Terima kasih telah membaca dan sampai jumpa di lain project!! :D
References:
1. www.randomnerdstutorial.com
Best Regards,
Ilham Alvindo Riandova (18219063),
Sistem dan Teknologi Informasi,
Institut Teknologi Bandung.
