ESP8266 – Read and Write Data to a Database

ESP8266 Daughterboard
ESP8266 Daughterboard
ESP8266 Daughterboard
ESP8266 Daughterboard

Got my ESP8266 Daughterboards back from OshPark and (minus a missing GND trace to the voltage regulator… whoops) everything seems to work well.  The boards (read my previous post) basically just break out the necessary USART and power lines for prototyping, add a 5V-to-3.3V 1Amp regulator, and have a programming and RESET button for putting the ESP8266 in boot mode.

Using the ESP8266 modules from adafruit I came to realize that the firmware version was different then the ESP8266-EVB from OLIMEX.  Adafruit ships with version v0.924 because they say it’s “much-easier-to-use” but it just messed me up.  The baud rate was only 9600 instead of 115200 and the line termination was different.  To get things a bit more to what I’m used to, I updated the board to version 0.952.  (There are tons of how-to’s on re-flashing the module, so I won’t rehash that here.)

For this post, I’ll detail the setup I applied to my server to allow reading and writing of data from the ESP8266.

First, I created a MySQL database with a table and 3 variables.  See the variable description below as seen by phpMyAdmin.

Database Table and Variables
Database Table and Variables

Next I added 2 *.php scripts to receive and process the read/write requests from the module.

<?php
$servername = “localhost”;
$username = “username″;
$password = “password”;
$dbname = “some_database″;
$now = new DateTime();

$field = $_GET['field'];
$value = $_GET['value'];

$conn = mysql_connect("localhost","username","password");
if (!$conn)
{
    die('Could not connect: ' . mysql_error());
}
$con_result = mysql_select_db("some_database", $conn);
if(!$con_result)
{
	die('Could not connect to specific database: ' . mysql_error());	
}

	$datenow = $now->format("Y-m-d H:i:s");
	$hvalue = $value;

	$sql = "INSERT INTO `DataTable`(`logdata`, `field`, `value`) VALUES (\"$datenow\",\"$field\",$value)";
	$result = mysql_query($sql);
	if (!$result) {
		die('Invalid query: ' . mysql_error());
	}
	echo "<h1>THE DATA HAS BEEN SENT!!</h1>";
	mysql_close($conn);
?>
<?php
$servername = “localhost”;
$username = “username″; // username for your database
$password = “password”;
$dbname = “some_database″; // Name of database
$now = new DateTime();
$CRLF = "\n\r";

$fieldToGet = $_GET['field'];

$conn = mysql_connect("localhost","username","password");

if (!$conn)
{
    die('Could not connect: ' . mysql_error());
}
$con_result = mysql_select_db("some_database", $conn);
if(!$con_result)
{
	die('Could not connect to specific database: ' . mysql_error());
}

/*
 *  Database was created with a table called "DataTable" and has
 *  a column called "field" and a column called "value" and a 
 *  column called "logdata"
 */
$sql = "SELECT * FROM `DataTable` WHERE `field` = \"$fieldToGet\"";
$result = mysql_query($sql);

if (!$result) {
	die('Invalid query: ' . mysql_error());
}
echo "<h1>THE DATA HAS BEEN RECEIVED!!</h1>";


while($row = mysql_fetch_array($result, MYSQL_ASSOC))
{
echo "timedate: " . $row["logdata"]. " - field: " . $row["field"]. " - value: " . $row["value"]. "<br>";
}

mysql_close($conn);
?>
To test the scripts from a browser, I typed the following
192.168.1.23/getdata.php?field=relayState // This gets all data with the field
192.168.1.23/store.php?field=relayState&value=1 // This stores data to field with value
When sending the data from the ESP8266, I used the following once it was connected to WiFi.
STORE A VALUE:
AT+CIPMUX=1
AT+CIPSTART=4,”TCP”,”192.168.1.23”,80
AT+CIPSEND=4,XXX
>GET /store.php?field=relayState&value=1 HTTP/1.1
Host: 192.168.1.23
Connection: close
READ ALL VALUES ASSOCIATED WITH A FIELD:
AT+CIPMUX=1
AT+CIPSTART=4,”TCP”,”192.168.1.23”,80
AT+CIPSEND=4,XXX
>GET /getdata.php?field=relayState HTTP/1.1
Host: 192.168.1.23
Connection: close
Here’s the log from me reading and writing using the ESP8266.
AT

OK

AT+CIFSR

+CIFSR:
APIP,"192.168.4.1"
+CIFSR:APMAC,"1a:fe:34:9c:e1:2c"
+CIFSR:STAIP,"192.168.1.13"
+CIFSR:STAMAC,"18:fe:34:9c:e1:2c"

OK

AT+CIPMUX=1

OK

AT+CIPSTART=4,"TCP","192.168.1.23",80

4,CONNECT

OK

AT+CIPSEND=4,88

OK
> 
GET /store.php?field=relayState&value=1 HTTP/1.1Host: 192.168.1.23Connection: close

busy s...

SEND OK

+IPD,4,243:HTTP/1.1 200 OK
Date: Sat, 11 Apr 2015 22:22:32 GMT
Server: Apache/2.2.22 (Debian)
X-Powered-By: PHP/5.4.39-0+deb7u2
Vary: Accept-Encoding
Content-Length: 33
Connection: close
Content-Type: text/html

<h1>THE DATA HAS BEEN SENT!!</h1>
4,CLOSED

AT
+CIFSR

+CIFSR:APIP,"192.168.4.1"
+CIFSR:APMAC,"1a:fe:34:9c:e1:2c"
+CIFSR:STAIP,"192.168.1.13"
+CIFSR:STAMAC,"18:fe:34:9c:e1:2c"

OK

AT+CIPMUX=1

OK

AT+CIPSTART=4,"TCP","192.168.1.23",80

4,CONNECT

OK

AT+CIPSEND=4,82

OK
> 
GET /getdata.php?field=relayState HTTP/
1.1Host: 192.168.1.23Connection: close

busy s...

SEND OK

+IPD,4,1460:HTTP/1.1 2
00 OK
Date: Sun, 12 Apr 2015 00:28:35 GMT
Server: Apache/2.2.22 (Debian)
X-Powered-By: PHP/5.4.39-0+deb7u2
Vary: Accept-Encoding
Content-Length: 1253
Connection: close
Content-Type: text/html

<h1>THE DATA HAS BEEN RECEIVED!!</h1>
timedate: 2015-04-11 18:22:32 - field: relayState - value: 1<br>
timedate: 2015-04-11 18:17:03 - field: relayState - value: 1<br>
timedate: 2015-04-11 18:16:54 - field: relayState - value: 1<br>
timedate: 2015-04-11 18:10:29 - field: relayState - value: 1<br>
timedate: 2015-04-11 18:10:09 - field: relayState - value: 1<br>
timedate: 2015-04-11 18:10:08 - field: relayState - value: 1<br>
timedate: 2015-04-08 06:10:31 - field: relayState - value: 1<br>
timedate: 2015-04-08 06:10:39 - field: relayState - value: 1<br>
timedate: 2015-04-08 06:10:40 - field: relayState - value: 1<br>
timedate: 2015-04-08 06:10:41 - field: relayState - value: 1<br>
timedate: 2015-04-08 06:10:42 - field: relayState - value: 1<br>
timedate: 2015-04-08 06:12:57 - field: relayState - value: 1<br>
timedate: 2015-04-08 06:12:59 - field: relayState - value: 0<br>
timedate: 2015-04-08 06:13:01 - field: relayState - value: 0<br>
timedate: 2015-04-08 06:13:03 - field: relayState - value: 0<br>
timedate: 2015-04-08 06:13:04 - field: relayState - value: 1<br>
timedate: 2015-04-08 06:13:07 - field: relayState - value: 1<br>
timedate: 2015-04-08 06:13:10 - field: relayState - value: 1<br>
timedate: 2015-04-08 06:13:10 - field: relayState - value: 
+IPD,4,5:0<br>
4,CLOSED

(Behind Corporate Lines) ESP8266 Sends Emails through the Firewall

In my last post, I just ordered my ESP8266 boards and an ESP8266-EVB from OLIMEX.  I don’t have my PCBs yet, but I did get in the ESP8266-EVB and it was very simple to wire up.

  • FTDI RED (+5V)  <–> +5V of ESP8266-EVB
  • FTDI BLK (GND) <–> GND of ESP8266-EVB
  • FTDI ORG (TX)    <–> RX of ESP8266-EVB
  • FTDI YEL (RX)    <–> TX of ESP8266-EVB

The FTDI cable provides enough power when connected to a typical USB port to supply the ESP8266.  (Note: I used an TTL-232R-3V3 which provides 5V on the Red wire, but the Orange (TX) and Yellow (RX) are at 3.3V levels.  If you do any reading on the ESP8266 you’ll see everyone SCREAMING at you to not connect 5V TTL levels to the chip or you could damage it.

Initially, I just wanted to see how easy it was to connect the ESP8266 to Wifi and get some simple data going back and forth.  (Full Disclosure: I’m not a web developer!!  GET, POST, HTML, PHP…. These are all pretty foreign concepts to me, but for the sake of doing something cool with this chip, I dove in.)  I initially looked at the list of AT commands from the data sheet but found that it was more helpful to follow some step-by-step tutorials by some of the kind folks in the community who wanted to share their experience as well and that really helped. I recommend the same to anyone getting into this chip as well.

Finally, I decided that a fun first “project” would be to have my ESP8266 email me something.  Having developed (interpreted as “copied someone’s open source example online”) some Java and .Net applications that send emails from Windows and Linux machines, one of the problems I ran into was corporate firewalls that block anything but Port 80 and don’t allow emails sent out from their network unless it’s one of their own.  To get around this, I created a simple PHP script using the example from Inmotion Hosting.  My PHP script resides in a LAMP (Linux Apache MySQL PHP) server outside of whatever network I intend to send the emails from.  To send an email, I simply issue an HTML POST command to the script location with the FROM, MESSAGE, etc… content and my script sends the email from the server.  The advantage of this is that using this method, I am able to utilize Port 80 which is open by most corporate firewalls.

My goal is to ultimately connect the ESP8266 to microcontrollers, but because the communication is simply a serial protocol, I’ve been doing all my initial testing using terminal programs.  This quickly proved frustrating because I had to keep switching back and forth between CR (Carriage Return) termination and CRLF (Carriage Return + Line Feed) termination when sending POST and GET HTML commands.  I also had to manually count how many characters I was going to send with the HTML commands each time.  Too slow…  To speed up my development, I wrote a simple Java application that allows me to do all this automatically.

Java ESP8266 UI for easy Development
Java ESP8266 UI for easy Development

The following output shows the ESP8266 sending requesting an email be sent from my@email.com with a message of 12345.  The script on the server has the email location where the message will be sent but it could be modified so that recipient is just another variable provided by the ESP8266.

AT+RST

OK

 ets Jan  8 2013,rst cause:4, boot mode:(3,7)

wdt reset


load 0x40100000, len 24444, room 16 


tail 12
chksum 0xe0
ho 0 tail 12 room 4
load 0x3ffe8000, len 3168, room 12 
tail 4
chksum 0x93
load 0x3ffe8c60, len 4956, room 4 
tail 8
chksum 0xbd
csum 0xbd

ready

AT+CIFSR

192.168.1.22

AT+CIPMUX=1

OK

AT+CIPSTART=4,"TCP","xxx.xx.xx.xxx",80

OK

Linked

AT+CIPSEND=4,<# of characters to send>
> 

POST /email.php HTTP/1.1Host: www.yourwebsite.com
Content-Type: application/x-www-form-urlencoded
Content-Length: 34
email=my%40email.com&message=12345

busy

SEND OK

+IPD,4,193:

HTTP/1.1 200 OK
Date: Sat, 04 Apr 2015 20:00:37 GMT
Server: Apache
X-Powered-By: PHP/5.4.39
Transfer-Encoding: chunked
Content-Type: text/html; charset=utf-8

15
Message has been sent

OK

+IPD,4,5:0

OK

OK
Unlink

Here is the email.php script used to send the email.  This is residing on my server.

<?php

// $email and $message are the data that is being
// posted to this page from our html contact form
$email = $_REQUEST['email'] ;
$message = $_REQUEST['message'] ;

// When we unzipped PHPMailer, it unzipped to
// public_html/PHPMailer_5.2.0
require("lib/PHPMailer/PHPMailerAutoload.php");
require("PHPMailer_5.2.0/class.phpmailer.php");
$mail = new PHPMailer();

// set mailer to use SMTP
$mail->IsSMTP();

// As this email.php script lives on the same server as our email server
// we are setting the HOST to localhost
$mail->Host = "www.yourserver.com";  // specify main and backup server

//$mail->Host = "smtp.gmail.com";
$mail->SMTPDebug  = 1;
$mail->SMTPAuth = true;     // turn on SMTP authentication

$mail->SMTPSecure = "ssl";
$mail->Port= 465;
//$mail->SMTPSecure = "tls";
//$mail->Port= 587;

// When sending email using PHPMailer, you need to send from a valid email address
$mail->Username = "you@whatever.com";  // SMTP username
$mail->Password = "password"; // SMTP password

// $email is the user's email address the specified
// on our contact us page. We set this variable at
// the top of this page with:
// $email = $_REQUEST['email'] ;
$mail->From = $email;

// below we want to set the email address we will be sending our email to.
$mail->AddAddress("someperson@whatever.com", "Person's Name");

// set word wrap to 50 characters
$mail->WordWrap = 50;
// set email format to HTML
$mail->IsHTML(true);

$mail->Subject = "Sending you a note from my microcontroller with a $5 wifi module!!";

// $message is the user's message they typed in
// on our contact us page. We set this variable at
// the top of this page with:
// $message = $_REQUEST['message'] ;
$mail->Body    = $message;
$mail->AltBody = $message;

if(!$mail->Send())
{
   echo "Message could not be sent. <p>";
   echo "Mailer Error: " . $mail->ErrorInfo;
   exit;
}

echo "Message has been sent";
?>

My next project with this chip will be to set up a similar PHP script and a MySQL database that allows me to pass data back and forth from the ESP8266 to any other phone or computer through my server again utilizing Port 80 to avoid firewall blockage in corporate environments.

ESP8266 – Low-cost Wifi Module for Embedded Applications

ESP8266 Module
ESP8266 Module

I can only read all the fun project posts on Hackaday using this cool Wifi module for so long before I have to try it out for myself.  I’m guessing this will be the first of many posts on my experience with this module.

From all that I read, this seems to be a fairly robust little chip (albeit poorly documented) and has a pretty lively community of hackers and developers willing to share their experience as well.  Many are attempting to use this as a standalone device, but my near-term aspirations are to use it with an array of my microcontroller platforms (at least all that have a USART available.)  The ESP8266 comes with a nice set of AT commands used to setup and use the integrated TCP/IP protocol stack.

My goal here isn’t to teach anyone how to use this module, but give a synopsis of what I’m doing with it, how it’s going, and to share any designs that may be helpful for those who want to give it a go as well.  There are many good websites that provide information about the module. Here, here, and here.

Ultimately, I’ll be using this device to communicate directly with the USART ports of my embedded projects, but until then, I’ll debug and develop using terminal programs from my computer or some Java code to simulate what will run in the microcontrollers.  There are several development boards out there to provide power and breakout pins so I decided to go with 2 options initially.

I liked the breakout board (ESP8266-EVB) and ESP8266 modules from Olimex to get up and running quickly.  They also break out all the GPIO from the chip in case I decide to utilize that down the road.  In the mean time, it’s done and ready for me to use while I wait for my boards to come in from OshPark.  Since I’m in the US, I purchased mine from Microcontroller Pros LLC for $11.50.  I’ll power it initially directly from my FTDI cable’s 5V supply since USB can handle up to 500mA and the ESP8266 draws peak current of around ~230mA.  If you do the same, make sure you use a cable similar to the TTL-232R-3V3 since the max voltage levels capable by the 8266 is 3.6V.  The TTL-232R-3V3 has a 5V VDD line (Red wire) and the RX/TX lines are all 3.3V.

ESP8266-EVB from OLIMEX
ESP8266-EVB from OLIMEX

For my own personal development, I wanted a certain form-factor and connectors in a certain orientation and location for prototyping.  I also wanted a fairly versatile board that I could use in the future with my embedded projects that has the requisite power supply and connections to the ESP8266 module.  I didn’t find exactly what I was looking for from the community, so I turned to my new favorite low-cost board house OshPark again to get some prototypes made up.

Here’s the schematic: ESP8266 Daughterboard R1

Boards available from OshPark, $11.40 for 3 boards: PCB Files

ESP8266_Daughter_TOP

ESP8266_BOT

Connectors J4 and J5 are wired identically and are used to plug directly into the TTL-232R-3V3 cable.  I like to add a second connector sometimes to make probing with a scope easier without splicing wires and soldering jumpers.

U1 is the 5V-to-3.3V regulator and can handle up to 1A.  The schematic has a regulator that’s rated for up to a max input of 6.5V, but I think I’m going to change it to one with a greater max input like the NCP1117ST33T3G which has a 20V input limit so I can use it on some of my automotive systems as well.

Connectors J1 and J3 break out all the ESP8266 lines and also have the input voltage from the regulator and have 0.100″ spacing to work with a breadboard for development.

Keep checking back for more updates once I get the boards and system up and running.