Getting a random string from MySQL for Arduino

When I modded Thomas Taylor’s Tweeting Dryer project to add a washing machine I also wanted to change the way the phrases used by the washer and dryer were implemented. In the original project the strings were hardcoded as a set of global variables. To add a new phrase to the washer or dryer the code needed to be changed in three places then uploaded to the Arduino. I didn’t want to recompile for a string change, so I decided to make my tweeting machines pull their random tweets from a MySQL database on the web.

A usb plug with a forbidden sign

Note: for details and code see my earlier post: Tweeting washer and dryer for Arduino with Ethernet.

Below is a snippet of the code before my change.

#define NUM_MSGS 2 // change this
prog_char msg_0[] PROGMEM = "Hey, I can't fold these things by myself.";
prog_char msg_1[] PROGMEM = "Come and get me while I'm all warm.";
// add a line here
PROGMEM const char* messages[] =
{
msg_0,
msg_1
// Add a line here
};

To add a new string you need to 1) update the #define to reflect the new message count 2) add the string as a new prog_char 3) add to the messages array. Just doing that much work to add a single string is irritating… and you still need to plug your Arduino into your PC to upload your changes (and I bet your development machine isn’t in the laundry room).

Moving the strings from code to MySQL was accomplished with a new function: strFromMySQL. The new function takes a string as input (“washer” or “dryer” in my case) and returns a random quote as a string. The function calls a PHP file which in turn requests a single, random row from a database of quotes. When the string is passed back to the Arduino by the PHP pages the Arduino uses the text finder library to parse the random quote string from the HTTP response.

Setup requires three things:

  1. Create one or more MySQL databases to host your quotes
  2. Modify and upload the PHP files
  3. Update the code with your server name and any needed changes to the path

The database schema is simple, you need tables for washer and dryer quotes and each of those tables need a column to hold the text. To use the code with minimal modifications create two tables, “Dryer” and “Washer”. In each table create a tinytext column named “thequote”. Fill in a few rows of data, using the strings from the original dryer code is a great place to start.

Once you’ve created your database download and modify the PHP files: strFromMySQL. You’ll need to update the conec.php file, adding the information for your MySQL database created in step 1. At this time you can also take a quick look into the washer and dryer PHP files to see what’s going on. The magic here is really not that magical at all, it’s just sorting the database in a random order and limiting the results to a single row.

select * from Washer order by rand() limit 1

Once you’ve uploaded the PHP files to your server it’s a good time to do a quick sanity test to make sure things are working correctly. Open up a web browser and open either your washer or dryer PHP page from the server. If all is working correctly you should see a blank page in your browser. It’s blank because the PHP is designed to return a minimal result so the Arduino has less work. The next step to testing is to right click in the page and view the source (e.g. “view page source” in Chrome or “view source” in IE). When you view the source you should see a quote in angle brackets (e.g. “<Hey, I can’t fold these things by myself.>”). If you don’t see a quote, circle back and check your settings in conec.php and check your MySQL settings. Also make sure PHP is correctly configured on your host and can successfully talk to MySQL.

Now that the PHP pages uploaded and talking to MySQL it’s time to customize the Arduino code.

  1. Turn the on pulling string from the web: set WEBMSG to true (1) on line 6
  2. Add the host name of your string server (the PHP pages, not the MySQL db): update stringServer on line 106
  3. Update strFromMySQL to use the path to your PHP files: change the YOUR_PATH_HERE string to match what is between your host name and, for example, washer.php (line 640). If the URL for your washer.php file is  http://example.com/arduino/strings/washer.php then line 640 should be:
    client.println(“GET /arduino/strings/”+ theDevice+”.php HTTP/1.1″);

You should be ready to go at this point. Recompile the code and upload to your Arduino.

With the strings and the code separated you are now free to add new tweets to your washer and dryer without coding and without fear of running out of memory. Any time you want to add a new string, just go to your database and add a new row, the new string is instantly added into the random queue.

How did this work out for you? Do you have any clever new tweets for a washer or dryer? Share your results in the comments below.

Tweeting washer and dryer for Arduino with Ethernet

My tweeting washer and dryer have been running great for a bit now and I’ve done some code cleanup to make it less embarrassing to share. The code attached to the post  is designed to use an Arduino Ethernet shield, not the WiFly module from the original example. If you already changed your tweeting dryer to use an Ethernet shield you can probably just download this code and upload it with little-to-no modification.

Download the Arduino code, circuit diagram and PHP files from my Git repo: WasherDryerMessenger

I’ve added a number of things to my code which weren’t in the original. I have, however, put them behind configs so I believe you should be able to use the code without hardware changes.

Here are two relevant configs at the top of the code:

  1. #define WASHER 0: In the sample code I have tweeting for the washing machine turned off. If you add another current sensor to your hardware you can turn this on to get tweets from your washer too.
  2. #define WEBMSG 0: This setting enables pulling the pithy tweets from a database on the web. Before enabling this there is some setup required, see: Getting a random string from MySQL for Arduino

If you want to have a tweeting washer as well as dryer, the hardware change you’ll need to make is to duplicate the current sensing circuit already in place for the dryer and add a third LED. In the diagram below left I have recreated the original circuit from Thomas’s Dryer Messenger in breadboard format (click for full size). On the right I show the added washer circuit (note: Ethernet shield not shown for simplicity).

Breadboard circuit diagram  Circuit diagram for washer and dryer

When you finish the modified circuit you can test it by plugging the dryer into the new transformer jack and updating the dryer pin setting in the code. Only after you’ve tested the hardware should you plug in both appliances and turn on the washer tweeting using the WASHER define (see the configuration notes earlier in this post). If you want the same account to tweet both washer and dryer status you can duplicate your dryer OAuth token for the washer on line 103. I elected to have separate handles for my washer and dryer and obtained a second OAuth token from Arduino-tweet.appspot.com.

At this point you should have a tweeting washer and dryer but you’ll notice the tweets are the same every time and rather boring. To add variety back to your tweets you should follow my instructions for enabling the WEBMSG for getting random tweets from a database on the web: Getting a random string from MySQL for Arduino.

Have you added a washer to your setup? Share your washer’s twitter handle in the comments below.