Sebelum melanjutkan, maka baca dahulu : Arduino ESP8266 menggunakan MySQL Pada XAMPP/web hosting - I sebelum melanjutkan pada tutorial ini
XAMPP webserver + database MySQL
1. Instal XAMPP, Setelah itu jalankan/aktifkan webserver dan MySQL
2. Web Hosting + Database MySQL
Server hosting umumnya memiliki fitur keamanan, masuk ke cpanel kemudian membuat sebuah database dengan memilih menu ‘MySQL® Database Wizard‘ dan mengisi nama database
Contoh : ‘arduino_mysql’.
3. Selanjutnya ‘next step’ buatlah akun dengan memasukkan nama dan paswword , selanjutnya pilih privileges yang akan digunakan.
4. Buat File : function.php
<?php
function databaseConnect()
{
/* XAMPP
$servername = "localhost";
$username = "root";
$password = "";
$database = "arduino_mysql";
*/
$servername = "localhost";
$username = "******_Arduino";
$password = "Arduino";
$database = "******_arduino_mysql";
// Create connection
$conn = new mysqli($servername, $username, $password);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
echoDebug("Connected successfully<br>");
// Create database
$sql = "CREATE DATABASE IF NOT EXISTS ".$database;
if ($conn->query($sql) === TRUE) {
echoDebug("Database created successfully<br>");
} else {
echoDebug("Error creating database: " . $conn->error);
}
// Connect to database
$conn = new mysqli($servername, $username, $password, $database);
// Check connection
if ($conn->connect_error) {
die("Database connection failed: " . $conn->connect_error);
}
echoDebug("Database connected successfully<br>");
// sql to create table
$sql = "CREATE TABLE IF NOT EXISTS arduino_data (
id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
variabel VARCHAR(30) NOT NULL,
nilai VARCHAR(30) NOT NULL
)";
if ($conn->query($sql) === TRUE) {
echoDebug("Table arduino_data created successfully</br>");
} else {
echoDebug("Error creating table: " . $conn->error);
}
// sql to create table
$sql = "CREATE TABLE IF NOT EXISTS browser_data (
id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
variabel VARCHAR(30) NOT NULL,
nilai VARCHAR(30) NOT NULL
)";
if ($conn->query($sql) === TRUE) {
echoDebug("Table arduino_data created successfully</br>");
} else {
echoDebug("Error creating table: " . $conn->error);
}
return $conn;
}
function echoDebug($message)
{
// hapus komen '//' jika ingin men-debug pesan
//echo $message;
}
?>
5. File : index.php
<?php
include("function.php");
$conn = databaseConnect();
if(isset($_GET['message']))
{
echo $_GET['message'];
echo "<br>";
echo "<br>";
}
$sql = "SELECT * FROM arduino_data";
$result = $conn->query($sql);
echo "<html>";
echo "<head>";
echo "<meta http-equiv='refresh' content='10'>";
echo "</head>";
echo "<body>";
echo "Arduino Data";
echo "<table border='1'>";
echo "<tr>";
echo "<td width='50'>id</td><td width='100'>Variabel</td><td width='200'>Nilai</td>";
echo "</tr>";
if ($result->num_rows > 0) {
// output data of each row
while($row = $result->fetch_assoc()) {
echo "<tr>";
echo "<td>".$row["id"]."</td><td>".$row["variabel"]."</td><td>".$row["nilai"]. "</td>";
echo "</tr>";
}
} else {
echo "<td colspan='3'>";
echo("tidak ada hasil");
echo "</td>";
}
echo "</table>";
echo "<form action='dariBrowser.php' method=GET>";
echo "<input type='hidden' name='aksi' value='hapus'><br>";
echo "<input type='submit' value='Hapus semua data'>";
echo "</form>";
echo "<br>Kirim data ke Arduino<br>";
echo "<form action='dariBrowser.php' method=GET>";
echo "Nama variabel:<br><input type='text' name='variabel'><br>";
echo "Nilai:<br><input type='text' name='nilai'><br>";
echo "<input type='submit' value='Kirim'>";
echo "</form>";
echo "</body>";
echo "</html>";
$conn->close();
?>
6. File : keArduino.php
<?php
include("function.php");
$conn = databaseConnect();
// Kirim respon bila ada
if(isset($_GET["variabel"]))
{
$variabel = $_GET["variabel"];
$sql = "SELECT * FROM browser_data WHERE variabel='".$variabel."'";
if($result = $conn->query($sql))
{
$row = $result->fetch_assoc();
echo $row["variabel"]."=".$row["nilai"];
}
}
$conn->close();
?>
7. File : dariArduino.php
<?php
include("function.php");
$conn = databaseConnect();
if(isset($_GET["variabel"]) && isset($_GET["nilai"]))
{
// Simpan data yang diterima ke database
$variabel = $_GET["variabel"];
$nilai = $_GET["nilai"];
$sql = "INSERT INTO arduino_data (variabel, nilai) VALUES ('".$variabel."', '".$nilai."')";
if ($conn->query($sql) === TRUE) {
echoDebug("New record created successfully</br>");
} else {
echoDebug("Error: " . $sql . "<br>" . $conn->error);
}
}
$conn->close();
?>
8. File : dariBrowser.php
<?php
include("function.php");
$conn = databaseConnect();
if(isset($_GET["variabel"]) && isset($_GET["nilai"]))
{
// Simpan data yang diterima ke database
$variabel = $_GET["variabel"];
$nilai = $_GET["nilai"];
$sql = "SELECT * FROM browser_data WHERE variabel='".$variabel."'";
$result = $conn->query($sql);
if ($result->num_rows > 0)
{
$sql = "UPDATE browser_data SET nilai='".$nilai."' WHERE variabel='".$variabel."'";
if ($conn->query($sql) === TRUE) {
echoDebug("Record updated successfully</br>");
} else {
echoDebug("Error updating record: " . $sql . "<br>" . $conn->error);
}
}
else
{
$sql = "INSERT INTO browser_data (variabel, nilai) VALUES ('".$variabel."', '".$nilai."')";
if ($conn->query($sql) === TRUE) {
echoDebug("New record created successfully</br>");
} else {
echoDebug("Error: " . $sql . "<br>" . $conn->error);
}
}
echo "<script type='text/javascript'> document.location = 'index.php?message=Entri data berhasil'; </script>";
exit();
}
else if(isset($_GET['aksi']))
{
if($_GET['aksi'] == "hapus")
{
// sql to delete a record
$sql = "TRUNCATE arduino_data";
if ($conn->query($sql) === TRUE) {
echoDebug("Record deleted successfully");
} else {
echoDebug("Error deleting record: " . $conn->error);
}
echo "<script type='text/javascript'> document.location = 'index.php?message=database telah dikosongkan'; </script>";
exit();
}
}
$conn->close();
?>
Cara penggunaan
- Hidup wifi (pastikan terhubung ke internet)
- Masukkan perintah dari serial monitor, data akan tercatat dalam tabel ‘arduino_data’, dalam contoh ini nama variabelnya adalah ‘dataDariSerial’ dengan nilai sesuai dengan entry dari Serial monitor (pastikan setting serial ke “both NL & CR”)
- Buka halaman “localhost/arduino_mysql/” atau “**nama server**/arduino_mysql/” di browser untuk melihat data yang dikirim dari arduino.
- Masukan perintah dari formulir di halaman web, dan klik kirim untuk mengirim data ke Arduino, (dalam contoh ini arduino membaca variabel ‘perintah’ jadi masukkan nama variabel sebagai ‘perintah’ serta isikan juga kotak ‘nilai’nya)
- Arduino akan membaca perintah setiap 5 detik dan akan dilaporkan di Serial monitor.
tampilan halaman web:
