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

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>