Visualize your Sensor with Sensor + ESP32 + MySQL + PHP
Hi guys, selamat datang kembali di blog ini. Pada kesempatan yang berbahagia ini, kita akan membahas mengenai cara melakukan visualisasi data berupa plot, dimana data ini berasal dari pembacaan sensor, dan data akan disimpan di server, sehingga data kita dapat diakses semua orang. Keren bangett kan?
Ada beberapa hal yang diperlukan untuk menunjang eksperimen pada kali ini, yaitu sebagai berikut.
- ESP32
- Breadboard
- Kabel Jumper
- Sensor
- Kabel USB + Laptop
- Jaringan Internet
- Hosting Server + Database MySQL
Sebenarnya, praktikum kali ini hampir mirip-mirip dengan praktikum yang kita lakukan minggu lalu. Namun, mungkin temen-temen ada yang belum membaca minggu lalu, tidak apa-apa. Aku akan menulis from zero to hero. Kalemmmm. Berikut penjelasannya.
Hosting PHP Application and MySQL Database
Coba kita pelajari terlebih dahulu gambar berikut ini.
Sederhananya, program dimulai dari pembacaan data melalui sensor ESP32, yang akan dilakukan update HTTP Post pada hosting server setiap 30 detik, dan akan di-plot oleh PHP sehingga visualiasi data dapat diakses kapan saja dan dimana saja dan oleh siapa saja.
Dari gambar diatas jugadari pembacaan sensor yang terhubung kita perlu yang namanya domain untuk menaruh database kita. Dilansir dari randomnerdtutorials, ada dua layanan hosting yang disarankan. Berikut rinciannya.
- Bluehost. Nama domain tersedia secara gratis untuk paket 3 years.
- Digital Ocean. Server Linux yang dikelola melalui baris perintah. Direkomendasikan untuk pengguna tingkat lanjut.
3. 000webhost. Server ini menyediakan layanan free hosting
Pada ketiga server, sudah tau dong aku pilih mana? Tentu saja yang gratisan, dalam hal ini 000webhost. Ini merupakan layanan free hosting. Daripada bayar, mending cari yang gratisan gak sih? Disini kita bisa create domain, pilih nama sesuka kita, bikin password. Dan voila, kita bisa langsung create database.
Prepare Web Hosting
Kita akan menggunakan 000webhost sebagai server kita. Silakan cari di mbah gugel mengenai 000webhost, dan log-in jika sudah punya akun, sign-up jika belum punya akun.
Jika sudah, kita akan buat “rumah” kita sebagai tempat kita “berteduh”. Klik saja + Create New Site dan follow the instructions.
Kalau sudah punya “rumah”, silakan klik Manage Website pada “rumah” kalian.
Prepare MySQL Database
Setelah kalian masuk ke dashboard, kira-kira tampilannya seperti ini.
Di sebelah kiri, temen-temen bisa klik Tools > Database Manager > + New Database.
Lalu disini temen-temen semua bisa isi nama database, username database, serta passwordnya. JANGAN SAMPAI LUPA YA BRO!
Setelah itu klik Create, dan silakan ditunggu prosesnya.
SELESAII, temen-temen berhasil membuat database baru!
Create a MySQL Database Table
Setelah berhasil membuat database, selanjutnya klik Manage > PhpMyAdmin.
Nanti tampilan awalnya seperti ini.
Kita pergi ke tab SQL.
Perhatikan bahwa database yang terpilih adalah database yang baru saja kita buat. Untuk lebih jelasnya coba lihat bagian di sebelah kiri.
Jika sudah benar, tidak apa-apa. Jika belum, tolong diganti ya.
Setelah itu, temen-temen bisa copy paste kode ini. Kode ini berfungsi untuk membuat tabel.
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
)Jika sudah, klik Go.
Jika temen-temen mendapatkan notifikasi “MySQL returned an empty result set (i.e. zero rows). (Query took X.YYY seconds.)” maka tabel sudah berhasil diciptakan. Selamat ya :)
PHP Script HTTP POST - Insert Data in MySQL Database
Kita kembali ke laptop, eh maksudnya kembali ke 000webhost, setelah itu pergi ke Tools > File Manager dan klik Upload Files.
Inilah tampilan awalnya.
Buka folder public_html, lalu crete New File, dengan nama post-data.php.
Lalu kamu bisa langsung aja kopas kode ini.
<?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;
}
Ohiya, kamu jangan sampai kelupaan ya untuk menggantikan $dbname, $username, dan $password yang telah kita buat tadi.
Lalu buka <namadomain>.000webhostapp.com/post-data.php dan lihat.
Kira-kira hasilnya akan menjadi seperti ini.
PHP Script - Visualize Database Content in a Chart
Sama seperti langkah diatas, didalam folder public_html buat file baru dengan nama esp-chart.php kemudian kopas dan save.
<! —
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-altitude” 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: ‘BMP280 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-altitude’ },
title: { text: ‘BMP280 Altitude’ },
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: ‘Altitude (m)’ }
},
credits: { enabled: false }
});
var chartP = new Highcharts.Chart({
chart:{ renderTo:’chart-pressure’ },
title: { text: ‘BMP280 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>
Kamu juga jangan lupa untuk menggantikan $dbname, $username, dan $password yang telah kita buat tadi.
Lalu buka <namadomain>.000webhostapp.com/esp-chart.php dan lihat.
Kira-kira hasilnya akan menjadi seperti ini.
Namun adakalanya diantara kalian yang seperti ini.
Seperti pada eksperimen minggu lalu, kalau kamu mendapatkan notifikasi seperti gambar diatas, coba perhatikan lagi nama databasenya, idnya juga harus masukin yaa supaya berhasil.
Prepare ESP32 and Sensor
Ada beberapa hal yang diperlukan untuk menunjang eksperimen pada kali ini, yaitu sebagai berikut.
- ESP32
- Breadboard
- Kabel Jumper
- Sensor
- Kabel USB + Laptop
Kalau udah siap, coba perhatiin dulu gambar skema diagram berikut ini.
Wiring daripada ESP32 terhadap sensor adalah sebagai berikut.
- GPIO 22: SCL (SCK)
- GPIO 21: SDA (SDI)
Nah jika sudah, pastikan kalian sudah menginstall board serta library dari sensor dan Adafruit (seharusnya kalau temen2 ikutin medium aku dari awal harusnya udah install).
Copy paste kode berikut ini.
/*
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 = "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 = "Your server name";
// 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; // I2C
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);
}
}
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(30000);
}Adafruit_BMP085 bmp;
// 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-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 = "BME280";
String sensorLocation = "Office";
/*#include <SPI.h>
#define BME_SCK 18
#define BME_MISO 19
#define BME_MOSI 23
#define BME_CS 5*/
#define SEALEVELPRESSURE_HPA (1013.25)
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 + "&sensor=" + sensorName
+ "&location=" + sensorLocation + "&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&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);
}Perhatikan kode bagian ini.
// 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 = "Your server name";Disini kalian bisa ganti SSID dengan nama wifi, password, serta nama server kalian yang file post-data.php.
Jangan lupa untuk verify, dan lihat bahwa akan ada grafik ESP32.
Okeii sekian dulu dari aku, kurang lebihnya mohon maaf, terimakasih semuanya :)
Referensi.
