GETing a row of values from mysql - Arduino Web Interface

Hi all,

What I have is a web interface that writes a few simple values to a mysql database. I'd like to read the row of values into my Arduino and assign each of them as variables. One of the variables would be updateInterval with which I can control how often arduino checks the DB again for new values and reassigns accordingly.

I've done quite a lot of googling and found a lot of ways arduino can write to a web server or host one and store its own web interface on the arduino, but I haven't found much of arduino getting/reading values from a web server.

I've seen people using GET to get a URL with variables in it and parse them out, but that isn't how my interface is set up.

What are my options? How can I have arduino read in a single row from a database? Right now I have a simple PHP script that outputs the row values and I can GET that file from Arduino and display the page via serial port, but I'm not sure how to extract only the values and assign them to variables. The page comes in with all the headers and everything.

Any direction would be much appreciated.

The mechanism for reading data from the database would be very similar to the way you write data to the database. You don't say how the write works, so I guess you have a webapp running somewhere and the Arduino executes an HTTP request to post data to the webapp and the webapp then writes the data to the database. There are other ways to do it, so if that isn't what you're doing then I suggest you explain what approach you're using.

To read data, you would need the query that is executed to be a select statement rather than an insert/update. The webapp would incorporate the data into the HTTP response it returns to the Arduino. You can encode it however you like, but a single line of comma separated data per record would be a reasonable approach. The Arduino would need to read back the HTTP response, extract the part containing the data and parse that to obtain the data values.

For this project I’m not planning on using the arduino to write to the database. Unless it is easily incorporated with whatever method I use to read from it.

What I am doing is I have a web page with some different controls on it - checkboxes and sliders - that POST via AJAX to PHP script that writes them to the database. It works independently right now. Now I would like to program the Arduino to listen to the database and refresh at a specified interval for changes in values. Then reassign those variables and continue using them in the program (turn on fan, turn on led, blink faster, etc).

So if I make a script that simply outputs my row of values as comma separated values (eg. 0,1,0,3000), can I use GET to read that line and then parse the values into variables? What would that look like? How do I get rid of all the extraneous data (headers, etc)? Would rerunning the script every couple seconds for updated values be efficient or timely? It seems that the request takes a couple of seconds.

HTTP headers always end with the sequence , so its
easy to skip them. The rest is body.

If the character string returned from the server has specific characters as delimiters, then picking out the embedded data should be fairly simple. Below is some simple code that extracts the desired character string from what is returned from the server. There are many ways to extract the desired data.

//zoomkat 12-16-11
//simple client test
//for use with IDE 1.0
//open serial monitor and send an e to test
//for use with W5100 based ethernet shields

#include <SPI.h>
#include <Ethernet.h>

byte mac[] = { 0xDE, 0xAD, 0xBE, 0xEF, 0xFE, 0xED }; //physical mac address

char serverName[] = "web.comporium.net"; // zoomkat's test web page server
EthernetClient client;


String readString, readString1;
int x=0;
char lf=10;
//////////////////////

void setup(){

  if (Ethernet.begin(mac) == 0) {
    Serial.println("Failed to configure Ethernet using DHCP");
    // no point in carrying on, so do nothing forevermore:
    while(true);
  }

  Serial.begin(9600); 
  Serial.println("Better client test 9/22/12"); // so I can keep track of what is loaded
  Serial.println("Send an e in serial monitor to test"); // what to do to test
}

void loop(){
  // check for serial input
  if (Serial.available() > 0) //if something in serial buffer
  {
    byte inChar; // sets inChar as a byte
    inChar = Serial.read(); //gets byte from buffer
    if(inChar == 'e') // checks to see byte is an e
    {
      sendGET(); // call sendGET function below when byte is an e
    }
  }  
} 

//////////////////////////

void sendGET() //client function to send/receive GET request data.
{
  if (client.connect(serverName, 80)) {  //starts client connection, checks for connection
    Serial.println("connected");
    client.println("GET /~shb/arduino.txt HTTP/1.1"); //download text
    client.println("Host: web.comporium.net");
    client.println("Connection: close");  //close 1.1 persistent connection  
    client.println(); //end of get request
  } 
  else {
    Serial.println("connection failed"); //error message if no client connect
    Serial.println();
  }

  while(client.connected() && !client.available()) delay(1); //waits for data
  while (client.connected() || client.available()) { //connected or data available
    char c = client.read(); //gets byte from ethernet buffer
    Serial.print(c); //prints byte to serial monitor 
    if (c==lf) x=(x+1);
    if (x==9) readString += c;
  }

    Serial.println();  
    Serial.println();
    Serial.print("Current data row:" );
    Serial.print(readString);
    Serial.println();
    readString1 = (readString.substring(0,8));
    Serial.println();
    Serial.print("How we feeling?: ");
    Serial.println(readString1);
    Serial.println();      
    Serial.println("done");
  Serial.println("disconnecting.");
  Serial.println("==================");
  Serial.println();
  client.stop(); //stop client

}

Let me explain my overall project and you guys can tell me its feasibility or if I am approaching it all wrong and should be structuring it differently.

I have a 12 volt light and a 12 volt fan. I want to be able to do 4 things to them via a web interface that isn't hosted on the Arduino -- turn on the light, turn on the fan, blink the light a specified number of times per second, and set the interval at which the arduino checks for changed values of the controls.

My idea for the program was to have Arduino get the 4 values from a mysql database that the control panel writes to via ajax/php. Then the Arduino turns things on and off based on those values. I'm new at programming and having a hard time thinking through the logic. I keep getting stuck in loops (in my mind, haven't written the code yet).

The arduino gets the values and rechecks the database at whatever interval is specified by one of the values from the DB. if lightValue is set to 1, turn on the light (same with fan) leave the light on till the control panel turns it off. if I tell the light to blink, blink this many times per second, but also be checking for new database value so I can turn off the blinking.

Sorry if this is vague. Maybe you can help me clarify or at least get the gist of what I'm trying to do.

That could work, but it'd give you a pretty poor response time since your commands wouldn't be executed until the Arduino polled the database.

Can you connect the Arduino USB to the host that the web app is running on? If so, you could use a serial command interface and have the webapp send commands to the Arduino whenever it needed the Arduino to do anything. You can still use the database locally within the webapp to persist the current state and have it go back to that state if the webapp gets reset for any reason. This approach is simple to implement and also simple to test.

If a USB interface isn't available but your Arduino has a network interface then the next simplest approach would be to have the Arduino implement a web server which accepts HTTP requests containing commands. This would be equivalent to a conventional web service rather than a web app, in other words the Arduino would just expect HTTP requests containing commands to do something, rather than being asked to serve out the user interface too. With this approach the web app would act as a web server for the user interface, but the implementation would execute HTTP requests to the Arduino commanding it to do things - conceptually very similar to the serial approach but using HTTP as the communication mechanism.

PeterH: I really like that second approach. I think that is what I am after. What direction do you have on where to go from here? What do I look for? Examples somewhere to tinker with?

Thanks for the response

The simplest approach would be to URL-encode the request parameters. Look for examples of Arduino web servers to see how to extract URL-encoded parameters from the incoming HTTP request.

In a typical web server the path and/or request parameters would specify a resource to be supplied and the web server would be responsible for sending the corresponding resource back to the client; here, you wouldn't need to serve out any resources so your code would be simpler. I'm not sure whether the webapp would need to return any information to the UI. It wouldn't be a bad idea to return some status information to show whether the request was handled OK, at a minimum. Processing that within your client would be optional - you could just log everything and use the logs for troubleshooting.

Although this webapp wouldn't be serving up a UI, with the request parameters URL-encoded you would be able to test it by entering the appropriate URL into a browser address field. For example:

http://arduino/action?lamp=on

where arduino is the host name or address of your Arduino web server.