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!

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!

A Dry Christmas (Light Show 8 Update)

In the first update for my next Arduino Light Show, I went through some of my experiments with my fountains. After some thought, I had planned on scaling it back but, in the end, I decided to scrap the fountains all together from the next light show. Despite the loss of the fountains, there are still new things to see!

img_20161019_181202

OK, so this is not really new. I made this weird Christmas tree last year. It was rather last minute so I didn’t do much with it. The plan now is to incorporate it into a new Light Show. Actually, it’s going to be the main feature.

I did a little bit of cleaning up. I cut the base into a circle and painted it black, trying hard to avoid painting over the LEDs.

img_20161021_194229

Here’s something new… well, again, sort of. I’ve done LED “spotlights” before by strapping 5mm LEDs to a servo motor. What’s new this time is that I’m using two servo motors per spotlight to make it pan and tilt. I’m also using WS2812B LED modules like on the tree. More movement and color should make the spotlights more interesting than before.

img_20161112_225030

After testing out the concept, I made an army of four.

img_20161119_204021

Some LED tests.

20161119_221619

I painted the spotlights all black as much as I could so that they’ll blend into the darkness.

So that’s the current state of the next Arduino Light Show. I had some other ideas in mind but I wanted to leave lots of time to get what I have here ready and programmed. We’ll see how the next little while goes. Thanks for reading!

The BIG opening update for the next Fountain Show

If everything works out in the very end, this will be one of my best projects to date. And really, with everything that has happened so far, it has been one of my best projects to date even if nothing actually works yet. I’ve experimented and learned a lot and, even with the bouts of wanting to quit, I really want this project to work because, again, it will be my best.

20160711_102151

I made a pool for the fountains out of scrap polypropylene sheets I got from work. I painted it all black because I thought it would benefit the lighting effects, as well as hide any imperfections better.

The white support beam running through the pool is where I would mount the fountain nozzles and LEDs.

20160711_113934

I’m trying my best to keep things organized and clean. I drilled holes for the pump wiring so that there wouldn’t be any excess wiring sitting in the water. I also used a silver marker to label them.

20160711_173741

Things started coming together as I had imagined. I used transparent tubing from the pumps to nozzles mounted on the support beam. The nozzles were made out of tubing that’s slightly more rigid. There’s also a smaller diameter tubing that I hot glued to the end to reduce the opening.

20160711_173830

Looks good so far…

20160711_173902

Heat shrink and electrical tape was good enough insulation with all of the water splashing every where. I decided to try another method of waterproofing certain connections which was to use short pieces of tubing and flooding it with hot glue on both ends. The connection in the picture is the splicing of the fountain wires to my own solid-core wire which is a likely point for failure.

20160711_194459

Once I was satisfied with the tubing, I threw on some more black paint.

20160714_145703

Next were the LEDs. They’re WS2812B modules (the little circle PCBs you find on eBay). I put them in little plastic cups to protect them from getting wet. They’re open on the bottomside but the LEDs are held inside of the cup with lots of hot glue anyway. I didn’t have any issues while testing them with water.

20160715_213212

And here’s where things started falling apart…

I barely ever used paint until recently, so I learned the hard way that it’s not very water proof, at least when applied on smooth plastic surfaces. As soon as the water started flying, the paint started peeling.

The other concern I saw was the aim of the fountains. The nozzles are round and are attached to a round support beam, so, while it looks good by eye, it didn’t turn out very straight. The pumps struggle if the fountain streams are exactly vertical so I tend to shoot them slightly backward so they sort of look vertical when viewing the fountains from the front. Anyways, I have some ideas on how to fix this which you’ll see in a future post.

The pump on the far left didn’t seem to respond so it either got through my quick dry tests before building or I fried it while setting up.

Also, no idea where the foam and bubbles are coming from…

20160715_215635

So clearly I’ve still got a lot of work ahead of me. Main things on the task list:

  • Scrape off all of the black paint on the inside of the pool. The outside black paint is fine.
  • Double check all of the pumps.
  • Replace support beam and nozzles.
  • Add a drain to the pool somehow.

Thanks for reading!

Happy holidays! (Christmas project 2015)

Last year’s Christmas holiday project was the “Make A Wish” fountain show. My original plans for this year was to follow it up with an updated set but I couldn’t find the time or motivation to do it. Instead, I decided to do something simpler.

20151222_190705_001Behold! A modern-style Christmas tree! It’s made out of foam-core board and has eight sides which are lit up by WS2812B addressable LEDs.20151222_190631With the room lights off, you can get a better sense of what I was going for. I am very happy with the way that it came together, considering how little time I gave myself. This was almost all done on a Sunday.20151221_193338It’s one of my larger projects, with the base being 50x66cm and the “tree” standing at 52cm. I’ll have to find somewhere to put it because I would love to work with it next year.20151222_190933_022One amusing observation which I hadn’t planned for was the fantastic pattern the project projects onto the ceiling.

Thanks for visiting my blog! I hope to find some time to write up a year-in-review-style post as I did last year. Stay tuned!

The lamp that wasn’t meant to be

Some time ago, I bought a few WS2812B addressable RGB LED modules. I’m still committed to using them in the next Light Show, whenever I get around to designing and building the next one. In the meantime, I wanted to make a simple project to play around a little more with these LEDs.

I decided to make a makeshift lamp. The lamp would be controlled by at Attiny85 board with a few potentiometers to control the intensity of each color.IMG_20150627_201909 After many thought out cuts, I abandoned the project. It became apparent that it would just look too silly and be too large for my limited desk space. Pictured above is the post and the top that would hang out on the top to hold the LED module.

IMG_20150718_131232I still wanted to test the LED modules out with the Attiny85 to see for myself that they can work on the small microcontroller (in comparison to the Atmega328p/Uno). I used the Adafruit Neopixel library and a USBtinyISP to program it. The code wouldn’t upload unless I burned the bootloader to use the internal 8MHz clock instead of the 1MHz clock. Otherwise, it worked great.

I would still like to make a small project with these LED modules, outside of the Light Show project. I need to brainstorm a little more.

Thanks for reading!

A look at a cheap USBtinyISP board

On my most recent revisions of my breakout boards for the Atmega328p and Attiny85, I added a 6-pin ISP header. ISP stands for In System Programming, which, as the name suggests, means that the header is used to program the microcontroller as it sits in a circuit, which is especially handy for boards without a way to plug directly into your computer’s USB port or a board with a surface-mount microcontroller.

I’ve never actually used these headers since I was used to using my Arduino Uno to program the microcontroller for both of my breakout boards. To make sure I could add the 6-pin ISP header to the boards correctly, I read up on it from different sources to understand how to make the connections.

attiny85pinoutisp

On the pinout of the microcontrollers, there are pins labelled SCK, MISO, and MOSI. These are three of the six pins of the 6-pin ISP header. The other three pins on the header are pins we all know well: Reset, Vcc, and Ground. The asterisk on my boards signify the first pin of the ISP header, which should be the MISO pin. The illustration of mine uses the pinout image in the Attiny datasheet where the six pins are underlined. I also drew up a simple diagram of the header pinout.

I received a cheap USBtinyISP board from China which took quite a while to get here. Thankfully it didn’t take much longer to get up and running.
IMG_20150704_125604The board came with a 6-pin ribbon cable and a USB cable. The USB cable is so short that it’s virtually unusable since the cable isn’t long enough to get the board to my desk from my computer. Thankfully, I have a longer cable I can use instead.

The first thing I did was to check the pinout of the cable so I don’t plug it in the wrong way on my boards. The easiest way the figure out the orientation of the header is to find Vcc and Ground with a multimeter, where you should see 5v across. It’s a 50/50 chance… so of course I got it wrong the first time as I saw the voltage reading fluctuate in the mV range.

Once I got the orientation of the connector right, I plugged it into one of my boards. I marked the first pin with a little sticker, as shown in the picture above.

Surprisingly, the Adafruit USBtinyISP drivers works with this board. I opened up Device Manager and updated the drivers with their files. The seller of this board had their own hosted driver files, though some poking around showed it was just the Adafruit drivers anyway.

IMG_20150704_124751First up was my Attiny85 Breakout Board. It’s not meant to fit into these half-sized breadboards but bending the power pins a little bit got it in. (If you bought one of these boards, don’t do this. This is a test unit, after all.)

I was able to upload the blink sketch directly from the Arduino IDE, with the Attiny85 settings and setting the programmer to “USBtinyISP “. The timing of the blink sketch was weird the first time I tried it, which made me realize I must have previously burnt the bootloader to use the 8MHz internal clock instead of the default 1MHz. I decided to try burning the bootloader so it would go back to using the 1Mhz clock. The USBtinyISP was able to do it and the sketch ran perfectly.IMG_20150704_124905Now for the Atmega328p Breakout Board. To upload through the USBtinyISP and the Arduino IDE, I can’t just click Upload like I did with the Attiny85 board. I have to hold shift when I click the Upload button to Upload Using Programmer. This is supposed to upload the sketch to the microcontroller without needing a bootloader. Once I upload a sketch with the USBtinyISP, I can’t upload a sketch if I place it in an Arduino Uno, so I guess that uploading with a programmer erases the bootloader…? To be able to use the microcontroller on an Arduino Uno again, I have to burn the bootloader with the USBtinyISP board. It’s not a big deal, but it’s something I have to remember to do so I don’t get confused why things aren’t working later on.

So with the FTDI and USBtinyISP programming tools at my disposal, I’m very excited to get the next revision of my Atmega328p Breakout Boards as they have headers for both devices. Stay tuned for news on that! Thanks for reading!