Pada tutorial ini mengirimkan data dari ESP32 ke database MySQL menggunakan HTTP POST hanya dengan Insert data dan delete table.
Menggunakan metode HTTP Post untuk mengirim data ke server database MySQL lebih baik dibandingkan menggunakan metode HTTP Get, jika menggunakan metode HTTP GET maka param yang digunakan mengakses melalui url
Contoh : http://192.168.43.140/postesp8266/api.php?suhu=23&arus=2
Karena mengakses melalui URL, ini mudah diketahui param-nya, sehingga sangat beresiko dalam hal keamanan, apalagi jika menyangkut keuangan seperti E-Money atau E-Tol.
Berikut adalah langkah - langkahnya :
1. Rancangan
2. Kode ESP32 :
2.1. Data random ke server database MySQL
#include <WiFi.h>
#include <HTTPClient.h>
const char *ssid = "Smart-PF"; //Nama Wifi
const char *password = "123456"; // pass wifi
void setup() {
delay(1000);
Serial.begin(115200);
WiFi.mode(WIFI_OFF);
delay(1000);
WiFi.mode(WIFI_STA);
WiFi.begin(ssid, password);
Serial.println("");
Serial.print("Connecting");
// Wait for connection
while (WiFi.status() != WL_CONNECTED) {
delay(500);
Serial.print(".");
}
Serial.println("");
Serial.print("Connected to ");
Serial.println(ssid);
Serial.print("IP address: ");
Serial.println(WiFi.localIP()); //IP address assigned to your ESP
}
void loop() {
if (Serial.available()) {
int a = Serial.parseInt();
if (a > 0) {
kirim_data();
}
}
}
void kirim_data() {
int tegangan, arus, suhu, kelembaban;
tegangan = random(0, 12); // ubah dengan data dari sensor
arus = random(0, 3); // ubah dengan data dari sensor
suhu = random(28, 32); // ubah dengan data dari sensor
kelembaban = random(50, 95); // ubah dengan data dari sensor
String postData = (String)"tegangan=" + tegangan + "&arus=" + arus
+ "&suhu=" + suhu + "&kelembaban=" + kelembaban;
HTTPClient http;
http.begin("http://192.168.43.140/postesp8266/api.php");
http.addHeader("Content-Type", "application/x-www-form-urlencoded");
auto httpCode = http.POST(postData);
String payload = http.getString();
Serial.println(postData);
Serial.println(payload);
http.end();
}
2.2. Sensor DHT11 untuk mengirim data suhu dan kelembaban ke database MySQL dengan ESP32
#include <WiFi.h>
#include <HTTPClient.h>
#include "DHT.h"
#define DHTPIN 15
#define DHTTYPE DHT11
DHT dht(DHTPIN, DHTTYPE);
const char *ssid = "Smart-PF"; //Nama Wifi
const char *password = "123456"; // pass wifi
float h, t;
void setup() {
delay(1000);
Serial.begin(9600);
WiFi.mode(WIFI_OFF);
delay(1000);
WiFi.mode(WIFI_STA);
WiFi.begin(ssid, password);
Serial.println("");
Serial.print("Connecting");
// Wait for connection
while (WiFi.status() != WL_CONNECTED) {
delay(500);
Serial.print(".");
}
Serial.println("");
Serial.print("Connected to ");
Serial.println(ssid);
Serial.print("IP address: ");
Serial.println(WiFi.localIP()); //IP address assigned to your ESP
dht.begin();
}
void loop() {
if (Serial.available()) {
int a = Serial.parseInt();
if (a > 0) {
read_dht();
kirim_data();
}
}
}
void read_dht() {
h = dht.readHumidity();
t = dht.readTemperature();
// Check if any reads failed and exit early (to try again).
if (isnan(h) || isnan(t)) {
Serial.println(F("Failed to read from DHT sensor!"));
return;
}
Serial.print(F("Humidity: "));
Serial.print(h);
Serial.print(F("% Temperature: "));
Serial.print(t);
Serial.println(F("°C "));
}
void kirim_data() {
float tegangan, arus, suhu, kelembaban;
tegangan = random(0, 12); // ubah dengan data dari sensor
arus = random(0, 3); // ubah dengan data dari sensor
suhu = t;
kelembaban = h;
String postData = (String)"tegangan=" + tegangan + "&arus=" + arus
+ "&suhu=" + suhu + "&kelembaban=" + kelembaban;
HTTPClient http;
http.begin("http://192.168.43.140/postesp8266/api.php");
http.addHeader("Content-Type", "application/x-www-form-urlencoded");
auto httpCode = http.POST(postData);
String payload = http.getString();
Serial.println(postData);
Serial.println(payload);
http.end();
}
3. Kode PHP
3.1. Koneksi.php
<?php
$servername = "localhost";
$username = "root";
$password = "";
$database = "monitoringdaya";
$conn = mysqli_connect($servername, $username, $password, $database);
function query($query){
global $conn;
$hasil = mysqli_query($conn, $query);
$rows = [];
while( $row = mysqli_fetch_assoc($hasil)){
$rows[] = $row;
}
return $rows;
}
function hapus($id){
global $conn;
mysqli_query($conn, "DELETE FROM sensor WHERE id = $id");
return mysqli_affected_rows($conn);
}
function tambah($data){
global $conn;
$idcard = htmlspecialchars($data["idcard"]);
$value = htmlspecialchars($data["val"]);
$now = new DateTime();
$datenow = $now->format("Y-m-d H:i:s");
$query = "INSERT INTO daya VALUES('','$datenow','$idcard', '$value')";
mysqli_query($conn, $query);
return mysqli_affected_rows($conn);
}
function ubah($data){
global $conn;
$no = $data["no"];
$idcard = htmlspecialchars($data["idcard"]);
$value = htmlspecialchars($data["val"]);
$query = "UPDATE rfid SET daya = '$idcard', val = '$value' WHERE no = $no ";
mysqli_query($conn, $query);
return mysqli_affected_rows($conn);
}
function registrasi($data){
global $conn;
$username = strtolower(stripslashes( $data["username"]));
$password = mysqli_real_escape_string($conn, $data["password"]);
$password2 = mysqli_real_escape_string($conn, $data["password2"]);
$result = mysqli_query ($conn, "SELECT username FROM user WHERE username = '$username' ");
if (mysqli_fetch_assoc ($result) ){
echo "
<script>
alert('Username sudah Terdaftar');
</script>
";
return false;
}
if ( $password !== $password2){
echo "
<script>
alert('Konfirmasi Password Tidak Sesuai')
</script>
";
return false;
}
$password = password_hash($password, PASSWORD_DEFAULT);
mysqli_query($conn, "INSERT INTO user VALUES('', '$username', '$password')");
return mysqli_affected_rows($conn);
}
?>
3.2. File : api.php
<?php
require 'koneksi.php';
ini_set('date.timezone', 'Asia/Jakarta');
$now = new DateTime();
$datenow = $now->format("Y-m-d H:i:s");
$tegangan = $_POST['tegangan'];
$arus = $_POST['arus'];
$suhu = $_POST['suhu'];
$kelembaban = $_POST['kelembaban'];
$sql = "INSERT INTO sensor VALUES ('', '$tegangan', '$arus', '$suhu', '$kelembaban', '$datenow')";
if ($conn->query($sql) === TRUE) {
echo json_encode("Ok");
} else {
echo "Error: " . $sql . "<br>" . $conn->error;
}
$conn->close();
?>
3.3. hapus.php
<?php
session_start();
if (!isset($_SESSION["login"])){
header("Location: login.php");
exit;
}
require 'koneksi.php';
$no = $_GET ["id"];
if ( hapus($no)> 0){
echo "
<script>
alert('Kartu Berhasil di Hapus');
document.location.href = 'index.php';
</script>
";
} else {
echo "
<script>
alert('Kartu Gagal di Hapus');
document.location.href = 'index.php';
</script>
";
}
?>
3.4. loginstyle.css
html {
height: 100%;
width: 100%;
}
body {
height: 100%;
overflow-x: hidden;
}
#main {
height: 100%;
padding: 120px 0px;
}
.design {
position: absolute;
top: 110%;
left: 50%;
transform: translate(-50%, -50%);
font-family: 'Muli', sans-serif;
color: #333;
}
img {
height: 400px;
width: 400px;
object-fit: cover;
}
h1 {
font-family: 'Muli', sans-serif;
font-size: 1.15em;
color: #ff80a4;
}
h1 span {
border-top: 2px solid #ff6692;
padding-top: 5px;
}
#main .form-control {
font-family: 'Muli', sans-serif !important;
height: calc(1.5em + 1rem + 2px);
border-right: unset;
border-top-left-radius: 0.5rem;
border-bottom-left-radius: 0.5rem;
}
#main .form-control:focus {
box-shadow: unset;
border-color: #ced4da;
}
::placeholder {
font-family: 'Muli', sans-serif !important;
color: #ced4da !important;
}
#main .input-group-text {
background-color: #fff;
border-left: unset;
border-top-right-radius: 0.5rem;
border-bottom-right-radius: 0.5rem;
color: #999;
}
#main form a {
font-family: 'Open Sans', sans-serif;
font-size: 0.9em;
color: #8c8c8c;
text-decoration: unset;
}
#main form button {
height: calc(1.5em + 1rem + 2px);
width: 100%;
background-color: #ff6692 !important;
border: unset;
border-radius: 1rem;
font-family: 'Muli', sans-serif !important;
color: #fff;
}
@media (max-width: 767px) {
#main {
padding: 60px 20px;
}
.col-md-6 {
border: unset !important;
}
.design {
top: 130%;
}
}
3.5. index.php
<?php
session_start();
if (!isset($_SESSION["login"])){
header("Location: login.php");
exit;
}
require 'koneksi.php';
$kartu = query("SELECT * FROM sensor");
?>
<!DOCTYPE html>
<html>
<head>
<meta http-equiv="refresh" content="1">
<title>Home</title>
</head>
<body>
<style>
.clogout {
float: right;
background-color: #1c87c9;
border: none;
color: white;
padding: 15px 28px;
text-align: center;
text-decoration: none;
display: inline-block;
font-size: 14px;
margin: 3px 2px;
cursor: pointer;
}
.title {
font-family: "Trebuchet MS", Arial, Helvetica, sans-serif;
}
#tbsensor {
font-family: "Trebuchet MS", Arial, Helvetica, sans-serif;
border-collapse: collapse;
margin-left: 10%;
width: 80%;
}
#tbsensor td, #tbsensor th {
border: 1px solid #ddd;
padding: 8px;
}
#tbsensor tr:nth-child(even){background-color: #f2f2E2;}
#tbsensor tr:hover {background-color: #eee;}
#tbsensor th {
padding-top: 12px;
padding-bottom: 12px;
text-align: left;
background-color: #1a75ff;
color: white;
}
</style>
<a href="logout.php" class="clogout">Logout</a>
<center><h1 class="title">Data Sensor Arus dan Tegangan</h1></center>
<table id="tbsensor">
<tr>
<th>No</th>
<th>Waktu</th>
<th>Tegangan</th>
<th>Arus</th>
<th>Suhu</th>
<th>Kelembaban</th>
<th>Aksi</th>
</tr>
<?php $i = 1; ?>
<?php foreach ( $kartu as $data ) :{
} ?>
<tr>
<td><?= $i; ?></td>
<td><?= $data["waktu"]; ?></td>
<td><?= $data["tegangan"]; ?></td>
<td><?= $data["arus"]; ?></td>
<td><?= $data["suhu"]; ?></td>
<td><?= $data["kelembaban"]; ?></td>
<td>
<a href="hapus.php?id=<?= $data["id"]; ?>">Hapus</a>
</td>
</tr>
<?php $i++; ?>
<?php endforeach; ?>
</table>
</body>
</html>
3.6. login.php
<?php session_start(); if (isset($_SESSION["login"])){ header("Location: index.php"); exit; } require 'koneksi.php'; if (isset($_POST["login"])){ $username = $_POST["username"]; $password = $_POST["password"]; $hasil = mysqli_query($conn, "SELECT * FROM user WHERE username = '$username' "); if (mysqli_num_rows($hasil) === 1 ){ $row = mysqli_fetch_assoc($hasil); if (password_verify($password, $row["password"]) ){ $_SESSION["login"] = true; header("Location: index.php"); exit; } } $error = true; } ?> <!doctype html> <html lang="en">
<head>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">
<link rel="stylesheet" href="/css/bootstrap.min.css" integrity="sha384-Vkoo8x4CGsO3+Hhxv8T/Q5PaXtkKtu6ug5TOeNV6gBiFeWPGFN9MuhOf23Q9Ifjh" crossorigin="anonymous">
<title>Halaman Login</title>
<link rel="icon" href="/wp-content/uploads/2019/11/cropped-icon-anom-2.png">
<link rel="stylesheet" href="/ajax/libs/font-awesome/5.11.2/css/all.css">
<link href="/css?family=Muli|Open+Sans&display=swap" rel="stylesheet">
<link rel="stylesheet" href="loginstyle.css">
</head>
<body>
<article id="main">
<div class="container">
<div class="row">
<div class="col-md-6 d-none d-sm-none d-md-block" style="border-right: 1.5px solid #f2f2f2">
<img src="image.jpg" alt="Image">
</div>
<div class="col-md-6" style="border-left: 1.5px solid #f2f2f2">
<a href="https://www.arduinoxyz.com" class="design">Design arduino</a>
<div class="row">
<div class="col-md-10 mx-auto">
<h1><span>Login</span> as a Administrator</h1>
<?php if (isset($error)): ?>
<p style="color : red; font-style: italic">Username / Password Salah</p>
<?php endif; ?>
</div>
</div>
<div class="row mt-5">
<div class="col-md-9 mx-auto">
<form action="" method="post">
<div class="form-group">
<label class="sr-only" for="username">Username</label>
<div class="input-group">
<input type="text" name="username" class="form-control" id="username" placeholder="Username">
<div class="input-group-prepend">
<div class="input-group-text">
<i class="far fa-user"></i>
</div>
</div>
</div>
</div>
<div class="form-group">
<label class="sr-only" for="Password">Password</label>
<div class="input-group">
<input type="password" name="password" class="form-control" id="password" placeholder="Password">
<div class="input-group-prepend">
<div class="input-group-text">
<i class="far fa-eye-slash"></i>
</div>
</div>
</div>
<a href="https://www.arduinoxyz.com" id="emailHelp" class="form-text text-muted text-right">Lost Password?</a>
</div>
<div class="form-group">
<button type="submit" name="login">L O G I N</button>
</div>
</form>
</div>
</div>
</div>
</div>
</div>
</article>
<script src="https://code.jquery.com/jquery-3.4.1.slim.min.js" integrity="sha384-J6qa4849blE2+poT4WnyKhv5vZF5SrPo0iEjwBvKU7imGFAV0wwj1yYfoRSJoZ+n" crossorigin="anonymous"></script>
<script src="https://cdn.jsdelivr.net/npm/popper.js@1.16.0/dist/umd/popper.min.js" integrity="sha384-Q6E9RHvbIyZFJoft+2mJbHaEWldlvI9IOYy5n3zV9zzTtmI3UksdQRVvoxMfooAo" crossorigin="anonymous"></script>
<script src="https://stackpath.bootstrapcdn.com/bootstrap/4.4.1/js/bootstrap.min.js" integrity="sha384-wfSDF2E50Y2D1uUdj0O3uMBJnjuUD4Ih7YwaYd1iqfktj0Uod8GCExl3Og8ifwB6" crossorigin="anonymous"></script>
</body>
</html>
3.7. logout.php
<?php
session_start();
$_SESSION = [];
session_unset();
session_destroy();
header("Location: login.php");
exit;
?>