Printing variables to MySQL

Projects Programming
1

Hi all! First post here and I hope it's answerable! Please bear with me as I frame the problem...

I have an Uno r3 + WiFi Shield (firmware 1.1.0) that posts a single push-button sensor output to a MySQL database. I run into trouble when I try to connect more than one sensor.

The way I see it, I have two options when it comes to the MySQL DB.

The good enough solution:
The first is to create a DB with three columns, one for each sensor that I want to record. This requires me to post three outputs each time the Arduino sends data because it has to provide a result for each column in order to post. I could live with this if someone can provide an demo (nobody has thus far).

The best solution:
The second is to have two columns (plus a primary key and a timestamp). The "PIN#" column represents the pin from which the data originates and the "Value" field is the sensor value (let's call it a 1/0 output).

Example of ideal format:
KEY | PIN# | Value | DateTime
1 3 1 10:00
2 2 1 10:04
3 7 1 10:07
etc...

I like this option because it allows an infinite number of inputs.

My question is this:
Is it possible to print the pin number from which a signal originates? In other words, if I have three push buttons on my bread board and I press the button connected to pin "A1" can I create a code that recognizes that and records "A1" in the "PIN#" column of my MySQL db? What does this look like?

The code snippet below is the relevant part of the Arduino code. The bold line in the code is what currently prints into my "PIN#" column regardless of which button is pressed. Can "senseval=" be converted into a variable that will print the pin number instead of a constant "sensval" output?

I'm using the Salinas/Benoit Arduino /Post tutorial here: GitHub - bsalinas/POST-Arduino-Data: A simple way to send data from an Arduino and save it to a database (MySQL) over WiFi.

#include <SPI.h>
#include <WiFi.h>

char ssid[] = "linksys";
int status = WL_IDLE_STATUS;
WiFiClient client;
IPAddress server(xxx,xxx,xxx,xxx);

// This is the data that will be passed into your POST and matches your mysql column

int inPin_0 = A0; // choose the input pin (sensor #1)
int inPin_1 = A1; // choose the input pin (sensor #2)
int sensorSense_0 = 0; //variable
int sensorSense_1 = 0; //variable
String SensorVal = "senseval=";// "yourdata="
String senseval;//yourdata //MUST KEEP sensval_x for PHP

void setup() {
Serial.begin(9600);
pinMode(inPin_0,INPUT);
pinMode(inPin_1,INPUT);
connectWifi();
printWifiStatus();
}

2

Is it possible to print the pin number from which a signal originates?

Of course it is. The Arduino had to take some action to discover that there was a signal on that pin, so it knows which pin the signal occurred on.

The code snippet below is the relevant part of the Arduino code.

The snippet below is the relevant part of the answer.

If you missed that, it's because snippets have no place here. You post ALL of your code, and we post all of the answer.

3

Wow! I didn't mean to make you angry.

When I get a chance to post the code, I will. I left the bulk of it out because I thought taking up more space than necessary was poor form.

4

@acpilot: Take a look at the number of posts that Paul has made. My guess is that something like half of those posts would have been removed/simplified had the OP posted all of their code in the first place. He isn't mad at you per se, he's just a little tired of writing the same directions 30,000 times. If you're going to ask questions here, you need to have some pretty thick skin, and you need to realize that some of your best answers will likely come from old salts who have been in the trenches for a while and get a little miffed when the same issue crops up over and over. I get tired of telling newbies to read Nick gammon's two posts at the top of this Forum and use code tags when posting. Already you're ahead of most new posters. Just stick with it.

5

PLease post all your code, and use [code] tags, not [blockquote] tags. Additionally, please format your code with ctrl-T first.

6

Here is the complete sketch:

#include <SPI.h>
#include <WiFi.h>

char ssid[] = "linksys";
int status = WL_IDLE_STATUS;
WiFiClient client;

IPAddress server(xxx,xxx,xxx,xxx);

int inPin_0 = A0; // choose the input pin (sensor #1)
int inPin_1 = A1; // choose the input pin (sensor #2)
int sensorSense_0 = 0; //variable
int sensorSense_1 = 0; //variable
String SensorVal = "senseval=";// "yourdata="
String senseval;//yourdata //MUST KEEP sensval_x for PHP

void setup() {
  Serial.begin(9600);
  pinMode(inPin_0,INPUT);
  pinMode(inPin_1,INPUT);
  connectWifi();
  printWifiStatus();
}

void loop() {
  sensorSense_0=analogRead(inPin_0);
  if (sensorSense_0 == LOW){
    postData();
    delay(5000);  
  }
  sensorSense_1=analogRead(inPin_1);
  if (sensorSense_1 == LOW){
    postData();
    delay(5000);
  }
}

void connectWifi() {
  while ( status != WL_CONNECTED) {
    Serial.print("Attempting to connect to SSID: ");
    Serial.println(ssid);
    status = WiFi.begin(ssid);
    delay(7000);
  }
}

void printWifiStatus() {
  Serial.print("SSID: ");
  Serial.println(WiFi.SSID());
  IPAddress ip = WiFi.localIP();
  Serial.print("IP Address: ");
  Serial.println(ip);
  long rssi = WiFi.RSSI();
  Serial.print("signal strength (RSSI):");
  Serial.print(rssi);
  Serial.println(" dBm");
}

void postData() {
  senseval=SensorVal+String(inPin_0);
  senseval=SensorVal+String(inPin_1);

  if (client.connect(server, 80)) {
    Serial.println("connecting...");

    client.println("POST /insert_mysql_doc.php HTTP/1.1");
    client.println("Host: www.<domain>.com");
    client.println("User-Agent: Arduino/1.0");
    client.println("Connection: close");
    client.println("Content-Type: application/x-www-form-urlencoded;");
    client.print("Content-Length: ");
    client.println(senseval.length());//yourdata
    client.println();
    client.println(senseval);//yourdata
    client.stop();
  } 
  else {
    // If you couldn't make a connection:
    Serial.println("Connection failed");
    Serial.println("Disconnecting.");
    client.stop();
    connectWifi();
    printWifiStatus();
  }
}
7 
  sensorSense_0=analogRead(inPin_0);
  if (sensorSense_0 == LOW){
    postData();
    delay(5000);  
  }

This does not make sense. analogRead() returns a value in the range 0 to 1023. Posting data only if the value is 0 doesn't make sense.

  senseval=SensorVal+String(inPin_0);
  senseval=SensorVal+String(inPin_1);

Stomping all over senseval in the second line makes the first one useless.

Even if you fix that, posting hardcoded pin numbers, with no data read from the pins, hardly makes sense.

8

Posting if 0 is the condition I'm seeking.

I don't want to post hardcoded pin numbers when no data present. I do want to post the pin number and value when my "low" condition exists.

As mentioned in the original post, the sketch works well with only one sensor. The complexity of a second sensor and the requirement to post the origination pin rather than a set value have stumped me.

9

I don't want to post hardcoded pin numbers when no data present. I do want to post the pin number and value when my "low" condition exists.

I expect that you want to post something like:

pin1=n&val1=nnnn&pin2=n&val2=nnnn

(where n and nnnn are the real values of the pin numbers and sensor readings)

If that IS what you want,

char buff[80];
sprintf(buff, "pin1=%d&val1=%d&pin2=%d&val2=%d",
      inPin_0, sensorSense_0, inPin_1, sensorSense_1);

and

    client.println(strlen(buff));
    client.println();
    client.println(buff);

You can, of course, change the names in the name=value pairs to whatever you want, as long as each name is unique.

Of course, one of the values for the valN pair will always be 0, since that is the condition that causes posting. The other may be nonsense, because it will not be a current reading.

10

This is interesting. I don't understand exactly what's going on there or how to apply it (yet) but I think I understand the direction you're pointing.

Looks like I have some research to do tonight.

11

If I understand this correctly, and it's likely that I do not, this...

char buff[80];
sprintf(buff, "pin1=%d&val1=%d&pin2=%d&val2=%d", inPin_0, sensorSense_0, inPin_1, sensorSense_1);

...belongs before the voidSetup() portion and this...

    client.println(strlen(buff));
    client.println();
    client.println(buff);

...belongs in the void postData() portion. Am I wrong?

12

and it's likely that I do not, this...

You DO do that.

...belongs before the voidSetup() portion

No, it belongs in the postData() function.

...and this...belongs in the void postData() portion.

In place of:

    client.println(senseval.length());//yourdata
    client.println();
    client.println(senseval);//yourdata