Tutorial: ESP8266 + PHP and MySQL Database

I hear you guys

I see it in the view counts, comments, and emails asking for information about my ESP8266 and MySQL project I made two years ago. People are having trouble with their ESP8266 and MySQL Database projects. The reasons why I haven’t posted about it since then is because I haven’t been doing much with the ESP8266, and the project is so old that the way it’s programed is out-of-date. Development for the ESP8266 has evolved so much and made it easier. You no longer have to deal with AT commands with all of the libraries and tutorials for the Arduino IDE.

I’m going to do a quick tutorial to explain how I’ve been doing my ESP8266 with MySQL project. It’s not going to have much code but more explaining so you get what’s happening.

Before we start, some links for you

The ESP8266 blog post that everyone is showing up to see: https://mwhprojects.wordpress.com/2015/01/18/esp8266-with-a-mysql-database/

The Github repo for this, though sort of irrelevant at this point because it’s outdated: https://github.com/mwhprojects/Arduino-ESP8266

The recent NodeMCU project that showed me what’s new with the ESP8266: https://mwhprojects.wordpress.com/category/projects-2/nodemcu/garage-monitor/

The Github repo for that: https://github.com/mwhprojects/NodeMCU-MySQL

GETing values and INSERTing them

Here’s what happens in the code uploaded onto the ESP8266/NodeMCU (see Github):

  1. The ESP8266 connects to the webhost.
  2. The switch values are read.
  3. These switch values are then inserted into a URL which the ESP8266 tries to load.
  4. Repeat.

I think that step three is what people are most interested in, so let me explain that a little further. PHP has the GET method, which basically reads variables that are in the URL itself. For instance, if we have a URL like: thisisanexample.com/example.php?a=1&b=2, we could use PHP to get the values of a and b. In PHP, we’d write $somevariable = $_GET[‘a’]; and again for b. We can then use PHP to connect up to a MySQL database and insert $somevariable and whatever else you’d like into the table.

In the PHP code (see Github), the file begins with code that checks for variables in the URL as explained above. If there is, it uses the GET method to get the variables and then inserts them into the MySQL database table.

Just a heads up, the deep sleep in the NodeMCU hasn’t been working for me, as I explained with my NodeMCU project posts. Keep that in mind if you plan on adopting the code. Heck, let me know what happens with your project as it could just be something with my hardware.

So now what?

Like good old XDA… YOU TELL ME.

I’m not an expert with the ESP8266 but I’d like to help if I can. Please let me know if there’s any clarifications I should make to this post or any more specifics on what problems you’re having with a similar project.

Thanks a lot for reading and good luck with your projects!

Advertisements

DIY Digital Clock: Take 2!

What time is it?

Time to make a new clock!

About a year ago, I designed and assembled my own custom made clock. You couldn’t say it was in an enclosure since its guts were spilled out on both sides of a piece of foam core. I felt like, a year later, it was time to redo it and put it into a proper enclosure.

So, what time is it? Time to build us a new clock!

The Guts

1

I tend to get carried away and too focused to take proper progress pictures. This is literally the first picture I have from the electronics part.

Soldering all of those LEDs and components took a full day. I used hot glue to try to keep multiple wires in place to solder as fast as I could but it didn’t do the best job to hold them in. At time, the glue would fall away from the PCB. Still, it’s better than fiddling with one wire at a time.

The only difference from the prototype build is a lower resistor value for the LED resistors.

Putting Together a Box

2

Foam core is a favorite in my “lab”. It’s all I use these days because all it takes is a knife to cut and it’s inexpensive and accessible (Dollarama rocks). I built a simple black box with a white cover place. I was hoping with a lower resistor value on the LEDs, they’d be able to shine through the white foam core.

3

With the soldered parts and the enclosure ready, it was time to put it all together.

4

I glued a piece of foam core behind the control board to isolate the connections on the back with the display connections. I ended up mounting the two display panels on it’s own piece of foam core anyway so I guess that wasn’t really necessary. The display foam core backing fits tight with no need for pin or glues to hold it in place.

5

The white foam core was still too think for the LEDs so I ended up going back to a plain white sheet of paper. It’s not noticeably brighter than the original prototype with the paper.  The piece of paper is held up by two strips of foam core on either side.

I didn’t like the look of it at this point but it was the end of the weekend so I left it for now.

6

I still like how sleek the black foam core looks, even with a few imperfections here and there from a not-so-sharp knife.

7

After a few days to think about it, I realized simply turning around my diffusing screen pulled the look together.

8

Time to pull the plug on the prototype and enjoy something new.

Thanks for reading!

Putting the Garage Door Monitor on hiatus

img_20170211_080857Let’s take a break

The deep sleep issues haven’t been solved, yet. It seemed like it got better when I put a resistor between D0 and Reset but it still stopped working after a while. After catching a few YouTube videos about the ESP8266 deep sleep, I’ve come to conclusion that I’ll have to rearrange my code big time. I’d rather regroup at this point and try something else.

Since the weather will be getting warmer, maybe I’ll leave the garage door monitor project until the summer. The motivation of the project was from the door problems that stem from the cold weather. I have an enclosed temperature sensor on the way from China so maybe I’ll try my hand at a weather station again.

So I’ll leave it there for now…

Garage Door Monitor likes its sleep

Garage Door Monitor Update #2

The NodeMCU project is still chugging along. I’ve gotten more experiments done which I’ll share with you shortly. Unfortunately, all of this information has left me more indecisive of how I want things. To recap, I want to make something to be able to tell me whether or not my garage door is open or closed without going outside to check. Click here to see the first post about the project.

garage1Problem: Is it dead or does it just like to sleep?

The problem with the NodeMCU working for a while and then giving up on life is still the big one. I’ve done a few overnight tests with a few tweaks (mostly giving it delays/time to do things) with them all resulting in entries stopping after about six hours.

When I use my bench power supply, I’m able to see how much current it’s drawing. When it’s on, it’s pulling about 69mA and peaks to about 71mA when it’s transmitting data. It doesn’t seem like much, but it registers as pulling no current on my power supply when it’s in its deep sleep. Seeing that, it’s hard not to want that sort of power saving on the final build.

More likely than not, I won’t let it go to sleep and have it on all the time. I haven’t actually tested this yet to see if it will stop working again. Stay tuned!

Experiment 1: They still can’t hear each other

I tried to get two NodeMCUs to talk to one another again but it’s still not working out. Basically, I had one that would set up as an access point with a webpage that I’d try to get the other NodeMCU to connect to and read. I could get it to connect once but then it failed at reading any data. After that, I couldn’t get it to connect to the AP NodeMCU anymore. I have no idea what changed in that time.

The alternative solution is to continue sending my data to my webhost, and then having the NodeMCU connect to a webpage I have online which will feed it the information it needs. The webpage it would connect to would give it raw unlabeled data that it knows how to decipher.

Experiment 2: Keeping it local

After banging my head for a few hours, I strayed away into another alternative, which would eliminate the second NodeMCU where I use my phone to see what the NodeMCU in the garage is up to.

It went very well since it was easy to modify the WifiAP example sketch (at least that’s what I think it’s based off of, I’ve been accidentally saving over the example sketches). I was able to see the values on my phone by visiting an IP address once I connected to the NodeMCU. However, the fact that I have to switch my wifi is a bit annoying which makes this an undesirable option. It’s still good to know I can do this though as I could use it in some other project.

So now what?

With everything in mind, I’ve got some updated objectives:

  1. Garage NodeMCU has a limit switch which will tell whether the door is open or not. It will connect to my webhost and send data. This NodeMCU is no longer allowed to go to sleep!
  2. Webhost will present the data to me on a page much like the screenshots I’ve been sharing. It will also have a seperate webpage with raw recent values.
  3. Inside NodeMCU will connect to my wifi network and then to that webpage with the raw recent values. It will take those values and decide what to do. (Light up an LED, make a sound after a few minutes, etc.)

New NodeMCU Project: Garage Door Monitor

The Objective

For whatever reason, our garage door opener doesn’t work reliably when it’s cold. Only the remotes that are inside in the warmth actually work so we have to remember to close the garage from inside the house. Sometimes it’s forgotten and the garage door is left open. There’s no way to see whether or not the door is open from inside the house. Thus, this project came to mind.

The main objective is to create a wireless way to know whether or not the garage door is open.

The Planimg_20170126_190721

The plan is to use two NodeMCU boards. One inside the garage will talk to the other inside the house. The one in the garage will be connected to a limit switch which will be closed when the door is closed. The one inside the home will have some sort of indicator that will tell us whether the door is closed or not. This will likely just be a labelled LED.

These Two Don’t Like Each Other

I received the two NodeMCU boards in two weeks from China. The experiments trying to get them to talk to each other (one as an access point, the other as a client) did not work out. I went hours trying to get something going but I couldn’t, so I went back to something I knew already, which is to have it talk to some PHP code hosted on my website.

The NEW Plan

I wasn’t planning on having the internet involved but I actually think it’s going to be a better idea. This way, I can get something going with just one board and then integrate the second one into the project later. I can check the status of my garage door right from my phone’s web browser from anywhere. The future is here people!

 

The new plan has a couple phases.

Phase 1: Set up one NodeMCU in the garage with the limit switch. The switch will send the status to my website which I can then check to see the status of the door. I can use some PHP code to send me an email if the door has been open unusually long.

Phase 2: Have the second NodeMCU connect to my website and build hardware around it so that anyone that walks in the front door can see whether or not the door is closed.

Progress Update

img_20170129_151905

It’s been going pretty well so far, even with it’s problems. I’ve got two limit switches sending values to a MySQL database hosted on my website. (The full process: It accesses a URL with the switch values inserted into them, and then some PHP code on that page grabs them and inserts them into the MySQL database table.)

BTW, I am using the Arduino IDE to program the NodeMCU.

screenshot_20170130-091040-01

I have no issues with it inserting values. The big issue I’ve been having is I haven’t gotten it running for longer than two hours at a time. I don’t know if it’s something on the server’s end or if the NodeMCU is hanging or something. It took me a while to get it to wake from deep sleep without doing something weird so I suspect it may have something to do with that. If this issue keeps going on, I may stop it from deep sleep and see how that goes. It’ll draw more energy but I’m leaning toward giving it a wall plug.

Keep Following the Project

I hope you enjoyed this first update post about my first NodeMCU project! Stay tuned for more!

screenshot_20170131-174839

If you’ve got Instagram, follow me as I’ve found it to be an easy way to share progress updates as they happen.

MWH Projects LED Foam Core Sign!

Happy New Year: Clean up time!

img_20170106_223932

I spent the last weeks of 2016 and the first weeks of 2017 cleaning up my electronics and projects storage areas. I’m happy with how most of my areas are now, especially my soldering work station pictured above. I moved the power supply from the main work table to the shelf above so now I have lots more room to work on. Of course, it would be nice to have more but it is what it is.

First project of 2017: MWH Projects Sign!

img_20170109_193734

I came up with this rather quick project for my first project for 2017. It would be a basic sign for my MWH Projects “brand” or whatever you may call it. The idea was to have nice clean lettering on a base with some LEDs. To make the letters, I printed out an outline of the MWH Projects text in Photoshop, taped it on some foam core, and carefully cut away at it.

The “MWH” was very easy since it’s all straight lines and large letters. The smaller “Projects” text was a little more difficult. I didn’t like the jagged rough edges so I soaked them in white paint. It gave the letters a rough texture but cleaner corners and curves.

img_20170111_201435

The base was a simple box made out of black foam core board. I hot glued on the “projects” text before working on the LED circuit.

There are three WS2812B LEDs in this project which are controlled by my Attiny85 breakout board. For power, I’m using 3-AA batteries and a step-up converter to get it up to 5V. I’m not sure if the step-up was necessary but, at the time, I wasn’t sure if I was going to be using 2 or 3 AA batteries.

img_20170111_205007

All of the parts fit nicely inside of the box, except for the battery holder which is stuck onto the back of it.

img_20170111_204913

And that’s it! I think it looks pretty nice, although the holes for the LEDs could be a bit cleaner.

img_20170111_204930

Speaking of the holes for the LEDs, I actually used a drill bit the size of the WS2812B round PCB modules. Leaving it like that, it wasn’t putting enough light on the letters so I cut a little more around them. I wasn’t sure I liked how the lights were showing on the letters but, after a while, I got used to it.

img_20170111_205021

I’ve been finding myself stuck on project ideas so I’m super glad this project worked out well.

Thanks for reading!

Happy Holidays! (Arduino Christmas Light Show 2016 Debut!)

Enjoy the Show!

Build Notes

A Rough Sleigh Ride

It’s been a strange ride these past few weeks developing the show. It’s gone from this elaborate idea of servos, LEDs, fountains, and more Christmas-themed props to a very simple stage. With that, there’s a feeling of the show being unsatisfactory… but I’m still satisfied anyway.

From the original four pan and tilt contraptions I had built, it dwindled down to using just two… and then a big fat zero. The two constraints were the small stage size which was too small to fit four, and the FastLED library didn’t seem to play nicely with the Servo library. I didn’t do much testing to determine whether it was this or too much current draw for both at the same time, but each library behaved properly when the other was removed.

Again with the small stage thing, there wasn’t much space to fit in the fountains or any props. (The big workbench is out of service, for reasons.) There was also the mess I made with some experiments that didn’t get my hopes up very much. It would have been nice to continue on with fountains for Christmas again… there’s always next year.

Despite all of the compromises and cuts, I can’t honestly say that this wasn’t a good project though. I’ve learned and will move on to something bigger and better.

Press Play on the Holidays

capture

One of the highlights of this project for me was creating a Visual Basic program to control the show. I used to manually sync the music while I was programming the show by basically giving my best guess as to when to hit play. With the VB program, starting the show with synced up music was as simple as hitting play.

There were a few problems I encountered. One was that I didn’t know how to stop the show mid-way if I needed to. I thought about interrupts and all that but I felt like time wasn’t on my side to figure that out. With my current setup, I could only stop the show from playing anything else after the sequence was done.

Another problem, although not one cause by the VB program, was that the sketch size became to big for all of the shows to be in one single sketch. I think it may have to do with the size of the FastLED library but I’m not certain. Since I couldn’t have all of the shows on one sketch, I couldn’t play them back to back through the VB program. I’d like to experiment with programming the show in the Visual Basic program itself so that the lengthy show code is saved on my computer as opposed to the Arduino.

Want the code?

Check out my GitHub for both the Arduino sketches and Visual Basic files!

Thanks for Visiting!

Merry Christmas, happy holidays, and best wishes for the new year!