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!

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!

A closer look at Light Show 7

IMG_0659It’s time to take a guided tour around the newest light show! In case you missed it, the newest show was released yesterday.

IMG_0634I kind of consider the new light show as the second generation of the first fountain show. However, there was so much more added that I couldn’t let it be an incremental update like version 5, 5.1 and 5.2. This was a great situation where I could sit back and watch the previous version and the current version back to back and see the improvements.

Something I wanted to focus on was to use the fountains better. With the backdrop and spotlights, it was a little easier, but looking back I found that there were too many dark moments in the first fountain show.

I tried to address the reliability issues from the previous version with a new nozzle design (even though it wasn’t perfect, at least I tried) and using wire to brace the nozzles to better hold its direction. It’s one challenge of the show that will continue until I come up with a better permanent solution.
IMG_0538The servo spotlights is something that I tried keeping a secret (I should stop doing that). I was a little too conservative with them, but the show still wouldn’t be the same without them. In the next show, I’d like to work on hiding the wiring and gearboxes of the servos.IMG_0664The backdrop performed well, though it still had the lighting issue I was afraid of. If there is more than one color on, the colors start washing out into white or an unwanted blended color. Still, some of the chasing effects look good.IMG_0406The light shows have never been overly complicated to wire since I used to give each LED its own pin on the Arduino Mega. In this version, I decided to use shift registers since I had made some shift register boards in my Eagle experiments and hadn’t used them yet. This actually made life easier because just a few wires have to run back to the microcontroller which is closer to the fountains. Without the shift registers, I’d be running a ton of wires back, and I barely had enough wire as it was. The only downside to shift registers is that programming takes a little more time to get right.

All in all, I’m happy with the results. I hope you enjoyed the show and I hope you’ll share it with others. Thanks for reading and happy holidays! Stay tuned for my last couple posts for the year!

Light Show 7 Journal Entry #10: Reflection

I’m currently working on a couple of year-end posts that will be published after the new Light Show debuts. The posts contain a lot of reflection on what has gone on in the past year, and one of those things is how much the Light Show project has grown. I thought it would be interesting to pull out the holiday Light Shows I did last year so that the growth of the show will be more obvious when the new one is released in the coming days (December 23rd!).

I remember being so exhausted from trying to find a song and programming to it that I rushed through and ended up with that… and then I ended up using a certain other song:

I’m always anxious to hear the feedback once a show is released. I’ve been surprised in the past, both pleasantly and unfortunately, with certain show releases. I hope the new show will fall under pleasant.

Come back to watch the new Light Show on December 23rd!

Chaining Shift Registers (74HC595 PCBs)

IMG_20140916_123053I felt like doing some soldering as I wait for some PCBs in the mail, so I decided to assemble two more of my 74HC595 shift register breakouts. Up to now, I still hadn’t checked to see if the boards chain together as I designed them to. I chained them to the setup I already had with my first breakout board test and it was great to see that they work together just fine.

IMG_20140916_123258Shift registers can be a little confusing to program at first, but the benefit of being able to control so many outputs using just three pins is worth the learning period. Today, I controlled 48 outputs with my little Attiny85, though I didn’t bother setting an LED up to all 48 for the demo. For the new shift registers, I put an LED on every other output.IMG_20140916_123250These are the newly assembled breakouts. You can see the jumper wire that corrects the missing trace problem I discovered a little while back. For these boards, I decided to solder the output headers so that they plug into a breadboard. I don’t have enough male-female jumper wires to use them as I did before. Doing it like that makes everything messier anyway. I decided against soldering the boards directly together in case they didn’t work. If and when I use these in a project, I will solder them together by the headers.

Here’s a video of my example program. It’s just each shift register running from the first to last output.

The reason why the LEDs on the mini-breadboards look like they’re blinking is because there are times when all of the LEDs are off because they’re connected to every other shift register output pin. The LEDs on the large breadboard (blue LEDs) fill up all 16 outputs of those two shift registers so their sequence is a lot more fluid.

Here’s a screenshot of the loop code:codeLike I said earlier, all the code does is run through each output of each shift register. The for loops make it so that all shift registers are doing the same thing (each “data” byte is the 8 outputs of each shift register). Also, I probably should have declared “o” earlier in the code… Anyways, check out my shift register tutorial for more information on programming them!

Thanks for reading!

“How do you program the light show?”

Untitled-1

Programming the Light Shows really isn’t that complicated, just time consuming. It seems to be a mystery for a lot of people and I still don’t really understand why. Not a lot of people seem impressed by my crude technique (people always seem to expect some kind of audio processing). In this post, I’ll share some of the things involved in programming the light shows and you’ll finally see how simple it is (again, it’s just time consuming).

The Functions: The show’s programs are made up of delays and for-loops. To use the hardware, the program uses basic digital and analog write functions, as well as the servo library.

The Process: The process is simply replaying the song over and over again and adding more code to it on each run. I work on the song in verses that I mark off in the code using either a line of ////////////// or the first line of lyrics of the next verse, commented out of course. Doing it in verses gives me points where I can decide to take a break… but really, it helps me keep track of where certain pieces of code is so I can go back to it and tweak it. Sometimes I will put a really simple pattern just to figure out the timing and then go back to it later and add more effects to that section.

Cuing It Up: I use VLC to play the song when I’m programming. In the earlier shows, I used to try and cue the song manually by watching the blinking TX lights on the Arduino as the program uploaded and then started the song once it was complete. It was kind of a guessing game that I’d figure out well enough to get things done. Eventually I realized I could just have an LED counting down like this:

// Count down (4, 3, 2, 1)
for(a=4; a>=1; a–){
digitalWrite(22, HIGH);
delay(500);
digitalWrite(22, LOW);
delay(500);
}

Time: It takes about a week to program 1 minute of show. I try my best to create fresh patterns so it gets incrementally more difficult as each new show is released. It’s also gotten harder as I’ve added more components to the stage in the later versions of the light show. This is why the shows have gotten shorter over time.

CAN I HAVE THE CODE?!!?: Yes! For the first time in a long time, I’m releasing code for some of the shows, specifically the most recent three. You can get the files here: http://www.mwhprojects.com/lightshow/code

I hope this has answered your questions about programming the Light Show. If you have any more questions, send me a message or leave a comment. Now go out and blind yourself with LEDs like I have!