ESP8266 Community Forum

- Thu May 05, 2016 1:25 am #46909 Here's an example to check a button (can be any contact like a reed contact to check doors/windows) and store the event to a MySQL table. Please replace the local ip address (this is where apache is running).

Program:

Code: Select alldev = "ESP1"
st = 0
p = 2
Pi p st
print "ESP ist waiting for Pin " & p & " to change..."
interrupt p [gpio]
Wait
[gpio]
Pi p nst
if nst = 0 then
   url = "192.168.178.23/esp/pin.php?Action=Message&mfrom=" & dev & "&mto=System&mtype=1&mess=Door/Window opened"
   gosub [urlencode]
   print wget(url)
else
   url = "192.168.178.23/esp/pin.php?Action=Message&mfrom=" & dev & "&mto=System&mtype=2&mess=Door/Window closed"
   gosub [urlencode]
   print wget(url)
end if
st = nst
wait

[urlencode]
url = replace(url," ", "+")
print url
return

PHP:

Code: Select all<?php

$host = "127.0.0.1";
$user = "homeauto";
$pw = "homeauto";
$db = "HomeAuto";

$con = mysqli_connect($host,$user,$pw) or die(mysqli_error());
mysqli_select_db($con,$db) or die(mysqli_error());
mysqli_query($con,"SET CHARACTER SET utf8");
mysqli_query($con,"SET NAMES 'utf8'");

$action=$_GET["Action"];

switch ($action) {
    Case "Message":
         $mfrom=$_GET["mfrom"];
        $mto=$_GET["mto"];
        $mtype=$_GET["mtype"];
        $mess=$_GET["mess"];
 
        $r = mysqli_query($con, "Insert into mess (st, mfrom, mto, mtype, mess) VALUES ('I', '$mfrom', '$mto', '$mtype', '$mess')") or die(mysqli_error());
        print $mess . "OK";
        break;

Case "GetMessages":
        $q = mysqli_query($con,"SELECT * FROM mess where mtype = '1' order by ts desc") or die(mysqli_error());
     
        $rows = array();
        while($r = mysqli_fetch_assoc($q))
        {
            $rows[] = $r;
        }
     
        $jsrows=json_encode($rows);
        print $jsrows;
        break;
 
default:
        print json_encode ("Error: Function not defined ->" . $action);
}

?>

MySQL table:

Code: Select allCREATE TABLE `mess` (
  `nr` int(11) NOT NULL,
  `st` varchar(200) COLLATE utf8_unicode_ci NOT NULL,
  `ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `mfrom` varchar(150) COLLATE utf8_unicode_ci NOT NULL,
  `mto` varchar(150) COLLATE utf8_unicode_ci NOT NULL,
  `mtype` varchar(200) COLLATE utf8_unicode_ci NOT NULL,
  `mess` varchar(2000) COLLATE utf8_unicode_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Wiring (Pin D4 -> GPIO2):

IMG_20160504_160925.jpg

Add a password if you like to protect it from beeing called by anyone else than you.

You do not have the required permissions to view the files attached to this post.

- Sat Jul 02, 2016 11:59 pm #50110 Interesting I missed this little gem good work!

Obviously V2.24 Stable Version based code I would assume.

Keep this in mind anyone reading.

Helpful? Donate To help support Mike http://www.esp8266basic.com click the donate button lets keep this project going and hope we can get the ESP32 in the mix. BEGINNERS GUIDE HERE http://www.esp8266.com/viewtopic.php?f=40&t=6732

Where I buy my ESP8266 boards from... (Banggood)