Pada kesempatan kali ini kita akan mengakses MySQL tanpa PHP (Perantara Lainnya). Kita akan menggunakan library MySQL_MariaDB_Generic dari :
https://github.com/khoih-prog/MySQL_MariaDB_Generic .
Pertama kita siapkan sebuah database " iot_db" dan table " tabel_iot " di phpmyadmin di XAMPP. Download .sql file disini.
Langkah selanjutnya Setting MYSQL agar bisa diakses dari luar:
- Buat user dengan password kuat
- Berikan hak akses hanya ke database yang diperlukan
- Reload table hak akses
- Verifikasi user apakah sudah ada
Setting my.ini (XAMPP) :
Ubah bind-address menjadi bind-address = 0.0.0.0
Setting agar localhost dapat diakses dari ip lain:
Konfigurasi PhpMy Admin (httpd-xampp.conf) cari dan ubah:
Alias /phpmyadmin "/xampp/phpMyAdmin/"
<Directory "/xampp/phpMyAdmin">
AllowOverride AuthConfig
Require all granted
ErrorDocument 403 /error/XAMPP_FORBIDDEN.html.var
</Directory>
Konfigurasi Apache (httpd.conf) cari dan ubah:
# Listen: Allows you to bind Apache to specific IP addresses
Listen 80
# atau
Listen 0.0.0.0:80
<Directory "C:/xampp/htdocs">
Options Indexes FollowSymLinks Includes ExecCGI
AllowOverride All
Require all granted
# Require local ← Komentari baris ini
</Directory>
Konfigurasi Firewall Windows
netsh advfirewall firewall add rule name="XAMPP HTTP" dir=in action=allow protocol=TCP localport=80
netsh advfirewall firewall add rule name="XAMPP HTTPS" dir=in action=allow protocol=TCP localport=443
Restart Apache dan mysql.
Selanjutnya kita akan membuat tampilan web page untuk mevisualkan data.
Terdapat 3 file dalam htdocs yaitu : config.php, get_data.php, index.php
config.php
<?php
// Tampilkan Error
ini_set('display_errors', 1);
ini_set('display_startup_errors', 1);
error_reporting(E_ALL);
// Database Configuration LOCAL
$hostname = "localhost";
$username = "root";
$password = "";
$database = "iot_db";
// Koneksi Database
$conn = new mysqli($hostname, $username, $password, $database);
// Cek koneksi
if ($conn->connect_error) {
die("Koneksi remote gagal: " . $conn->connect_error);
}
// Fungsi untuk mencegah SQL Injection
function clean($data) {
global $conn;
return mysqli_real_escape_string($conn, htmlspecialchars(trim($data)));
}
?>
get_data.php
<?php
header('Content-Type: application/json');
header('Access-Control-Allow-Origin: *');
require_once 'config.php';
// Set charset to utf8mb4
$conn->set_charset("utf8mb4");
// Fetch data from tabel_iot
$sql = "SELECT id, datasensor, datatemp, tanggal, jam FROM tabel_iot ORDER BY id ASC";
$result = $conn->query($sql);
if ($result) {
$data = array();
// Fetch all rows
while ($row = $result->fetch_assoc()) {
$data[] = $row;
}
// Return JSON response
echo json_encode([
'success' => true,
'data' => $data
]);
// Free result
$result->free();
} else {
echo json_encode([
'success' => false,
'error' => 'Query error: ' . $conn->error
]);
}
// Close connection
$conn->close();
?>
index.php
<?php
require_once 'config.php';
$data_tertinggi = mysqli_fetch_assoc(mysqli_query($conn, "SELECT MAX(datasensor) as datamax FROM tabel_iot"))['datamax'];
$data_terbaru = mysqli_fetch_assoc(mysqli_query($conn, "SELECT datasensor as dataterbaru FROM tabel_iot ORDER BY id DESC LIMIT 1"))['dataterbaru'];
$data_terendah = mysqli_fetch_assoc(mysqli_query($conn, "SELECT MIN(datasensor) as datamin FROM tabel_iot"))['datamin'];
$data_tertinggi_temp = mysqli_fetch_assoc(mysqli_query($conn, "SELECT MAX(datatemp) as tertinggi_temp FROM tabel_iot"))['tertinggi_temp'];
$data_terendah_temp = mysqli_fetch_assoc(mysqli_query($conn, "SELECT MIN(datatemp) as terendah_temp FROM tabel_iot"))['terendah_temp'];
// Handle Form Submit
if ($_SERVER['REQUEST_METHOD'] == 'POST') {
if (isset($_POST['action'])) {
if ($_POST['action'] == 'add') {
$datasensor = clean($_POST['datasensor']);
$tanggal = clean($_POST['tanggal']);
$jam = clean($_POST['jam']);
$query = "INSERT INTO tabel_iot (datasensor, tanggal, jam)
VALUES ('$datasensor', '$tanggal', '$jam')";
if (mysqli_query($conn, $query)) {
header("Location: index.php");
exit();
}
}
elseif ($_POST['action'] == 'delete') {
$id = clean($_POST['id']);
$query = "DELETE FROM tabel_iot WHERE id = '$id'";
if (mysqli_query($conn, $query)) {
header("Location: index.php");
exit();
}
}
}
}
//Get Data
$queryiot = "SELECT * FROM tabel_iot";
$hasil_iot = mysqli_query($conn, $queryiot);
?>
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<title></title>
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>Data IoT</title>
<link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0/dist/css/bootstrap.min.css" rel="stylesheet">
<link href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/6.4.0/css/all.min.css" rel="stylesheet">
<link href="https://cdn.datatables.net/1.13.6/css/dataTables.bootstrap5.min.css" rel="stylesheet">
<!-- Buat chart -->
<script src="https://cdn.jsdelivr.net/npm/chart.js"></script>
<style>
.navbar { background: linear-gradient(135deg, #667eea 0%, #764ba2 100%); }
* {
margin: 0;
padding: 0;
box-sizing: border-box;
}
.chart-container {
background: white;
border-radius: 15px;
padding: 30px;
box-shadow: 0 10px 10px rgba(0,0,0,0.1);
margin-bottom: 30px;
}
.chart-wrapper {
position: relative;
height: 300px;
}
.controls {
display: flex;
justify-content: center;
gap: 15px;
margin-bottom: 20px;
flex-wrap: wrap;
}
button {
background: #667eea;
color: white;
border: none;
padding: 12px 24px;
border-radius: 8px;
cursor: pointer;
font-size: 16px;
font-weight: 600;
transition: all 0.3s ease;
box-shadow: 0 4px 6px rgba(0,0,0,0.1);
}
button:hover {
background: #5568d3;
transform: translateY(-2px);
box-shadow: 0 6px 12px rgba(0,0,0,0.15);
}
button:active {
transform: translateY(0);
}
button.secondary {
background: #48bb78;
}
button.secondary:hover {
background: #38a169;
}
.status {
text-align: center;
color: white;
margin-top: 20px;
font-size: 14px;
padding: 10px;
background: rgba(255,255,255,0.1);
border-radius: 8px;
}
.data-table {
background: white;
border-radius: 15px;
padding: 30px;
box-shadow: 0 10px 40px rgba(0,0,0,0.2);
overflow-x: auto;
}
table {
width: 100%;
border-collapse: collapse;
}
th, td {
padding: 12px;
text-align: left;
border-bottom: 1px solid #ddd;
}
th {
background: #667eea;
color: white;
font-weight: 600;
}
tr:hover {
background: #f5f5f5;
}
.loading {
text-align: center;
color: #667eea;
font-size: 18px;
padding: 20px;
}
.stats-grid {
display: grid;
grid-template-columns: repeat(auto-fit, minmax(200px, 1fr));
gap: 20px;
margin-bottom: 30px;
}
.stat-card {
background: white;
border-radius: 15px;
padding: 10px;
box-shadow: 0 10px 10px rgba(0,0,0,0.1);
text-align: center;
}
.stat-card h3 {
color: #0d6efd;
font-size: 35px;
margin-bottom: 10px;
text-transform: uppercase;
}
.stat-card .value {
font-size: 32px;
font-weight: bold;
color: #333;
}
.badge {
display: inline-block;
padding: 4px 12px;
border-radius: 12px;
font-size: 12px;
font-weight: 600;
margin-left: 10px;
}
.badge.success {
background: #48bb78;
color: white;
}
</style>
</head>
</head>
<body>
<div class="container-fluid py-4">
<h2><i class="fa-solid fa-bolt text-primary"></i> Data Sensor</h2>
<div class="chart-container">
<div class="chart-wrapper">
<canvas id="sensorChart"></canvas>
</div>
</div>
<!-- Statistics Cards -->
<div class="row">
<div class="col" hidden>
<div class="card stat-card purple">
<div class="card-body">
<div class="d-flex justify-content-between">
<div>
<p class="text-muted mb-1">Terbaru</p>
<h3 class="mb-0"><?= $data_terbaru ?></h3>
</div>
<div class="text-primary" style="font-size: 2.5rem;">
<i class="fa-solid fa-circle-plus"></i>
</div>
</div>
</div>
</div>
</div>
<div class="col">
<div class="card stat-card blue">
<div class="card-body">
<div class="d-flex justify-content-between">
<div>
<p class="text-muted mb-1">Sensor Tertinggi</p>
<h3 class="mb-0"><?= $data_tertinggi ?></h3>
</div>
<div class="text-primary" style="font-size: 2.5rem;">
<i class="fa-solid fa-up-long"></i>
</div>
</div>
</div>
</div>
</div>
<div class="col">
<div class="card stat-card orange">
<div class="card-body">
<div class="d-flex justify-content-between">
<div>
<p class="text-muted mb-1">Sensor Terendah</p>
<h3 class="mb-0"><?= $data_terendah ?></h3>
</div>
<div class="text-primary" style="font-size: 2.5rem;">
<i class="fa-solid fa-down-long"></i>
</div>
</div>
</div>
</div>
</div>
<div class="col">
<div class="card stat-card green">
<div class="card-body">
<div class="d-flex justify-content-between">
<div>
<p class="text-muted mb-1">Temp Tertinggi</p>
<h3 class="text-danger mb-0"><?= $data_tertinggi_temp ?> °C</h3>
</div>
<div class="text-danger" style="font-size: 2.5rem;">
<i class="fa-solid fa-up-long"></i>
</div>
</div>
</div>
</div>
</div>
<div class="col">
<div class="card stat-card green">
<div class="card-body">
<div class="d-flex justify-content-between">
<div>
<p class="text-muted mb-1">Temp Terendah</p>
<h3 class="text-danger mb-0"><?= $data_terendah_temp ?> °C</h3>
</div>
<div class="text-danger" style="font-size: 2.5rem;">
<i class="fa-solid fa-down-long"></i>
</div>
</div>
</div>
</div>
</div>
</div>
<div class="d-flex justify-content-between align-items-center mb-4" style="margin-top: 25px;" >
<button class="btn btn-primary" data-bs-toggle="modal" data-bs-target="#addModal" hidden>
<i class="fas fa-plus me-1"></i>Tambah Data IoT
</button>
</div>
<table id="tableIot" class="table table-striped table-hover">
<thead>
<tr>
<th class="text-center align-middle">No</th>
<th class="text-center align-middle">Data Sensor</th>
<th class="text-center align-middle">Temperatur</th>
<th class="text-center align-middle">Tanggal</th>
<th class="text-center align-middle">Jam</th>
<th class="text-center align-middle">Aksi</th>
</tr>
</thead>
<tbody>
<?php
$no = 1;
while($row = mysqli_fetch_assoc($hasil_iot)):
?>
<tr>
<td class="text-center"><?= $no++ ?></td>
<td class="text-center"><?= $row['datasensor'] ?></td>
<td class="text-center"><?= $row['datatemp'] ?></td>
<td class="text-center"><?= $row['tanggal'] ?></td>
<td class="text-center"><?= $row['jam'] ?></td>
<td class="text-center">
<button class="btn btn-danger btn-sm" onclick="confirmDelete(<?= $row['id'] ?>)">
<i class="fas fa-trash"></i>
</button>
</td>
</tr>
<?php endwhile; ?>
</tbody>
</table>
</div>
<!-- Delete Form -->
<form id="deleteForm" method="POST" style="display:none;">
<input type="hidden" name="action" value="delete">
<input type="hidden" name="id" id="deleteId">
</form>
<!-- Add Modal -->
<div class="modal fade" id="addModal" tabindex="-1">
<div class="modal-dialog">
<div class="modal-content">
<div class="modal-header bg-primary text-white">
<h5 class="modal-title">Tambah Data Iot</h5>
<button type="button" class="btn-close btn-close-white" data-bs-dismiss="modal"></button>
</div>
<form method="POST">
<input type="hidden" name="action" value="add">
<div class="modal-body">
<div class="mb-3">
<label class="form-label">Data Sensor</label>
<input type="text" name="datasensor" class="form-control">
</div>
<div class="mb-3">
<label class="form-label">Tanggal</label>
<input type="text" name="tanggal" class="form-control" value="<?= date('Y-m-d');?>">
</div>
<div class="mb-3">
<label class="form-label">Jam</label>
<input type="text" name="jam" class="form-control" value="<?= date('h:i:s');?>">
</div>
</div>
<div class="modal-footer">
<button type="button" class="btn btn-secondary" data-bs-dismiss="modal">Batal</button>
<button type="submit" class="btn btn-primary">Simpan</button>
</div>
</form>
</div>
</div>
</div>
<script src="https://code.jquery.com/jquery-3.7.1.min.js"></script>
<script src="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0/dist/js/bootstrap.bundle.min.js"></script>
<script src="https://cdn.datatables.net/1.13.6/js/jquery.dataTables.min.js"></script>
<script src="https://cdn.datatables.net/1.13.6/js/dataTables.bootstrap5.min.js"></script>
<script src="https://cdn.jsdelivr.net/npm/sweetalert2@11"></script>
<script>
let chart;
let chartData = {
labels: [],
datasets: [
{
label: 'Sensor Data',
data: [],
backgroundColor: 'rgba(102, 126, 234, 0.2)',
borderColor: 'rgba(102, 126, 234, 1)',
borderWidth: 3,
pointBackgroundColor: 'rgba(102, 126, 234, 1)',
pointBorderColor: '#fff',
pointBorderWidth: 2,
pointRadius: 6,
pointHoverRadius: 8,
tension: 0
// tension: 0.4
},
{
label: 'Data Temp',
data: [],
borderColor: 'rgba(255, 99, 132, 1)', // Warna Merah Muda
backgroundColor: 'rgba(255, 99, 132, 0.2)',
borderWidth: 3,
pointBackgroundColor: 'rgba(255, 99, 132, 1)',
pointBorderColor: '#fff',
pointBorderWidth: 2,
pointRadius: 6,
pointHoverRadius: 8,
tension: 0
// tension: 0.4
}
]
};
// Initialize chart
function initChart() {
const ctx = document.getElementById('sensorChart').getContext('2d');
chart = new Chart(ctx, {
type: 'line',
data: chartData,
options: {
responsive: true,
maintainAspectRatio: false,
plugins: {
legend: {
display: true,
position: 'top',
labels: {
font: {
size: 14,
weight: 'bold'
}
}
},
title: {
display: true,
text: 'Data Sensor IoT',
font: {
size: 18,
weight: 'bold'
}
}
},
scales: {
y: {
beginAtZero: true,
title: {
display: true,
text: 'Data Sensor',
font: {
size: 14,
weight: 'bold'
}
}
},
x: {
title: {
display: true,
text: 'Waktu',
font: {
size: 14,
weight: 'bold'
}
}
}
}
}
});
}
// Load data using jQuery AJAX (Full syntax)
function loadData() {
updateStatus('Loading data with jQuery AJAX...');
$.ajax({
url: 'get_data.php',
type: 'GET',
dataType: 'json',
beforeSend: function() {
console.log('Sending AJAX request...');
},
success: function(response) {
console.log('Response received:', response);
if (response.success) {
processData(response.data);
updateStatus(`✅ Data loaded successfully! (${response.data.length} records) - jQuery AJAX`);
} else {
updateStatus('❌ Error: ' + response.error);
}
},
error: function(xhr, status, error) {
updateStatus('❌ AJAX Error: ' + error);
console.error('Error details:', xhr.responseText);
},
complete: function() {
console.log('AJAX request completed');
}
});
}
$(document).ready(function() {
$('#tableIot').DataTable({
// displayLength: 100,
});
initChart();
loadData();
// Auto-refresh every 10 seconds
setInterval(loadData, 60000);
});
function confirmDelete(id) {
Swal.fire({
title: 'Hapus Data?',
text: "Data yang dihapus tidak dapat dikembalikan!",
icon: 'warning',
showCancelButton: true,
confirmButtonColor: '#d33',
cancelButtonColor: '#3085d6',
confirmButtonText: 'Ya, Hapus!',
cancelButtonText: 'Batal'
}).then((result) => {
if (result.isConfirmed) {
document.getElementById('deleteId').value = id;
document.getElementById('deleteForm').submit();
}
});
}
// Process data and update chart
function processData(data) {
// Clear existing data
chartData.labels = [];
chartData.datasets[0].data = [];
chartData.datasets[1].data = [];
// Populate chart data
data.forEach(item => {
// const label = `${item.tanggal} ${item.jam}`;
const label = `${item.jam}`;
chartData.labels.push(label);
chartData.datasets[0].data.push(item.datasensor);
chartData.datasets[1].data.push(item.datatemp);
});
// Update chart
chart.update();
// Update table
// updateTable(data);
// Update statistics
// updateStatistics(data);
}
function updateStatus(message) {
}
</script>
</body>
</html>
Bagian terakhir kita akan membuat sketch untuk ESP32 yang akan memasukkan data ke MYSQL. Disini kita akan menggunakan data sensor serta data temp secara random. Dan penggunakan internal RTC date dan time dari ESP32.Sketch ino file :
Credentials.h#include <ESP32Time.h> #if ! (ESP8266 || ESP32 ) #error This code is intended to run on the ESP8266/ESP32 platform! Please check your Tools->Board setting #endif #include "Credentials.h" #define MYSQL_DEBUG_PORT Serial // Debug Level from 0 to 4 #define _MYSQL_LOGLEVEL_ 1 #include <MySQL_Generic.h> #define USING_HOST_NAME false #if USING_HOST_NAME // Optional using hostname, and Ethernet built-in DNS lookup char server[] = "localhost"; // change to your server's hostname/URL #else IPAddress server(192, 168, 1, 27); #endif ESP32Time rtc(0); // #define USING_HOST_NAME false // #if USING_HOST_NAME // // Optional using hostname, and Ethernet built-in DNS lookup // char server[] = "srv1367.hstgr.io"; // change to your server's hostname/URL // #else // IPAddress server(153, 92, 15, 25); // #endif uint16_t server_port = 3306; //3306; char default_database[] = "iot_db"; char default_table[] = "tabel_iot"; MySQL_Connection conn((Client *)&client); MySQL_Query *query_mem; void setup() { Serial.begin(115200); // Set time manually: sec, min, hour, day, month, year rtc.setTime(00, 55, 12, 30, 1, 2026); while (!Serial && millis() < 5000); // wait for serial port to connect MYSQL_DISPLAY1("\nStarting Basic_Insert_ESP on", ARDUINO_BOARD); MYSQL_DISPLAY(MYSQL_MARIADB_GENERIC_VERSION); // Begin WiFi section MYSQL_DISPLAY1("Connecting to", ssid); WiFi.begin(ssid, pass); while (WiFi.status() != WL_CONNECTED) { delay(500); MYSQL_DISPLAY0("."); } // print out info about the connection: MYSQL_DISPLAY1("Connected to network. My IP address is:", WiFi.localIP()); MYSQL_DISPLAY3("Connecting to SQL Server @", server, ", Port =", server_port); MYSQL_DISPLAY5("User =", user, ", PW =", password, ", DB =", default_database); } void runInsert() { String datasensor_value = String(random(0, 1000)); String datatemp_value = String(random(0, 100)); String tanggal_value = rtc.getTime("%Y-%m-%d"); String jam_value = rtc.getTime("%H:%M:%S"); // Sample query String INSERT_SQL = String("INSERT INTO ") + default_database + "." + default_table + " (datasensor, datatemp, tanggal, jam) VALUES ('" + datasensor_value + "','" + datatemp_value + "','" + tanggal_value + "','" + jam_value + "')"; // Initiate the query class instance MySQL_Query query_mem = MySQL_Query(&conn); if (conn.connected()) { MYSQL_DISPLAY(INSERT_SQL); // Execute the query // KH, check if valid before fetching if ( !query_mem.execute(INSERT_SQL.c_str()) ) { MYSQL_DISPLAY("Insert error"); } else { MYSQL_DISPLAY("Data Inserted."); } } else { MYSQL_DISPLAY("Disconnected from Server. Can't insert."); } } void loop() { MYSQL_DISPLAY("Connecting..."); //if (conn.connect(server, server_port, user, password)) if (conn.connectNonBlocking(server, server_port, user, password) != RESULT_FAIL) { delay(500); runInsert(); conn.close(); // close the connection } else { MYSQL_DISPLAY("\nConnect failed. Trying again on next iteration."); } MYSQL_DISPLAY("\nSleeping..."); MYSQL_DISPLAY("================================================"); // setiap 10 detik delay(60000); }
#ifndef Credentials_h #define Credentials_h char ssid[] = "essii laundry"; // your network SSID (name) char pass[] = "qwerty123"; // your network password char user[] = "esp32_user"; // MySQL user login username char password[] = "PasswordKuat123!"; // MySQL user login password #endif //Credentials_hBerikut tampilan Serial Monitor :
EmoticonEmoticon
Note: Only a member of this blog may post a comment.