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.

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.