Printing arduino pin numbers to MySQL

Question

I mistakenly posted this on stack overflow first. The community was kind enough to point me to this forum. I apologize for the double-post.

I've been working on an Arduino Uno r3 + WiFi Shield project for a few months now building up the complexity as I go. I've hit a wall. I started this project with exactly zero experience writing code of any kind and exactly zero experience working with boards. If you have any doubts as to whether or not I'll understand your response, please dumb it down!

I've made a simple arduino + wifi shield setup that can report a button push to a MySql database. All of the .php stuff on the backend works and the database is managed with phpmyadmin. I'm trying to add a second button but can't seem to make it work and need some help.

I have settled on a four-field database table as the output:

1st column: the key. A sequential, unique identifier for each entry
2nd column: the pin number that the sensor is connected to
3rd column: the sensor value
4th column: the date and time

Notice that I'm not using a dedicated column for each sensor. By using the sensor pin input, I want to simply print the sensor pin number in a column. This (a) saves space in the table and (b) allows the Arduino to use as many sensors as it has pins.

The idea is that each button press (even if two are pressed simultaneously) will get it's own line, sensor pin source, value, and timestamp. No values will be sent unless a button is pressed.

I haven't the foggiest idea of how to implement this! Help!

My question:

How can I ask the sketch to retrieve the pin from which a signal originates and write it to the db as a number in the second column? The 13th line of code is what will write the pin number (I think). The "senseval=" needs to be a variable that reports the source pin.

I don't know how to set this up and google has led to nothing but dead ends. I'd like this thread to answer the questions I'm sure a few more people have/will have!

My sketch is included below and the insert_php_doc below that. I'm using the Silinas/Benoit "Arduino /Post" example from github:

#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) //ADDED FOR SECOND BUTTON//
int sensorSense_0 = 0; //variable
int sensorSense_1 = 0; //variable //ADDED FOR SECOND BUTTON//
String SensorVal = "senseval=";// "yourdata="
String senseval;//yourdata //MUST KEEP sensval for PHP

void setup() {
  Serial.begin(9600);
  pinMode(inPin_0,INPUT);
  pinMode(inPin_1,INPUT);  //ADDED FOR SECOND BUTTON//
  connectWifi();
}

void loop() {
  sensorSense_0=analogRead(inPin_0);
  if (sensorSense_0 == LOW){
    postData();
    delay(5000);  
  }
  sensorSense_1=analogRead(inPin_1); //ADDED FOR SECOND BUTTON//
  if (sensorSense_1 == LOW){ //ADDED FOR SECOND BUTTON//
    postData(); //ADDED FOR SECOND BUTTON//
    delay(5000); //ADDED FOR SECOND BUTTON//
  }

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

    delay(7000);
  }
}
}
void postData() {
  senseval=SensorVal+String(inPin_0);
  senseval=SensorVal+String(inPin_1); //ADDED FOR SECOND BUTTON//

  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 {
    Serial.println("Connection failed");
    Serial.println("Disconnecting.");
    client.stop();
    connectWifi();
    printWifiStatus();
  }
}

The insert PHP from https://github.com/ericbenwa/POST-Arduino-Data-Wireless:

<?php

foreach ($_REQUEST as $key => $value)
{
    if ($key == "senseval") {
        $senseval = $value;
    }
}

// EDIT: Your mysql database account information
$username = "test_user";
$password = "test_password";
$database = "test_db_name_here";
$tablename = "test_table_name_here";
$localhost = "localhost";

// Check Connection to Database
if (mysql_connect($localhost, $username, $password))
  {
    @mysql_select_db($database) or die ("Unable to select database");

    // Next two lines will write into your table 'test_table_name_here' with 'yourdata' value from the arduino and will timestamp that data using 'now()'
    $query = "INSERT INTO $tablename VALUES ($senseval,now())";
    $result = mysql_query($query);
  } else {
    echo('Unable to connect to database.');
  }

?>

Nick Gammon · Accepted Answer · 2016-02-10 04:30:16Z

int inPin_0 = A0; // choose the input pin (sensor #1)
int inPin_1 = A1; // choose the input pin (sensor #2) //ADDED FOR SECOND BUTTON/

...

  senseval=SensorVal+String(inPin_0);
  senseval=SensorVal+String(inPin_1); //ADDED FOR SECOND BUTTON//

The above is wrong because you are sending the pin numbers and not the values read from those pins. You previously read them here:

  sensorSense_0=analogRead(inPin_0);
  ...
  sensorSense_1=analogRead(inPin_1); //ADDED FOR SECOND BUTTON//

It is sensorSense_0 and sensorSense_1 that you should be sending.

Also, as Damiano Verzulli pointed out, your second assignment to senseval clobbers the first. You want something more like:

  senseval  = SensorVal + String(analogRead(inPin_0)) + ", ";
  senseval += SensorVal + String(analogRead(inPin_1));

Even that may not be perfect, I didn't read your PHP side.

Looking at the PHP side now:

$query = "INSERT INTO $tablename VALUES ($senseval,now())";

That isn't going to work too well for two values. I don't know your database schema, but that won't handle two values very well. A bit of tweaking and it might, or you could do two INSERT statements.

Damiano Verzulli · Accepted Answer · 2015-09-12 20:25:09Z

First of all, your statement: "I've made a simple arduino + wifi shield setup that can report a button push to a MySql database." is incorrect (or, at best, misleading) as the code you're running in Arduino is not talking with MySQL at all, but simply calling a remote PHP script via an HTTP/POST request.

So now your problem can be split in two parts:

checking if your arduino (via its own wifi shield) is correctly talking with remote HTTP server (invoking proper PHP script with related parameters);
checking if your PHP script is correctly dealing with MySQL.

As for 1), my first suggestion is to check web-server log files. Unfortunately you didn't tell us which web server (Apache? IIS? Other) are you running, as well as which operating system are you relying on (Linux? Windows? Other?). With Apache running on linux, tipically log files are stored in /var/log/apache or /var/log/httpd folders. There are plenty of other troubleshooting approaches but... please, let's start checking log files, ensuring that PHP script is invoked;

As for 2), your PHP script is lacking basic error-handling: should the insert (launched by the mysql_query function call) fail, you'll never know. I strongly suggest you to add proper error-handling code. Based on official documentation it's as easy as checking the result value of the mysql_query call: "...For other type of SQL statements, INSERT, UPDATE, DELETE, DROP, etc, mysql_query() returns TRUE on success or FALSE on error...." so what you have to do to improve your code (...and being notified about errors raised by MySQL via PHP) is to use something like following code:

$result = mysql_query($query);
if (!$result) {
   die('Invalid query: ' . mysql_error());
}

Having said all the above, let me add a couple of other notes.

in your OP, you wrote: "I'm trying to add a second button but can't seem to make it work". So it seems that previously you were testing with only one button and, in such a case, everything worked. Is it right? If yes, please, can you tell us what you changed to add proper handling of second button?
in your code I see following lines:

.

[...]
senseval=SensorVal+String(inPin_0);
senseval=SensorVal+String(inPin_1);
[...]

Please note that they are probably wrong, as the second line will override the assignment of the first one. So, with such lines, you'll surely/always loose the value related to inPin_0 (in other words: the first line, is useless).

giving a look to the server-side PHP script, I noted that it has been built to handle only one parameter (sensval passed to PHP via GET/POST parameter; with PHP storing it in the $sensval variable). So... to change the behaviour so to handle two different values... it's not trivial (in other words: such a change require a minimun understanding of web-programming and... getting deep to analyze such requirement is out of scope --I think-- of this answer).

1. Yes, you are right: Arduino > PHP > MySQL. I've noted in the code what has "broken" the sketch. A single button works perfectly. I don't know how to make more than one work. My ideal state is to have a four column db but I'm also OK with creating a column for each sensor. I can't find any good tutorials on that though. The little information that does exist is fragmented. I'm running Ubuntu using phpmyadmin to manage the Apache db hosted by Network Solutions. — acpilot, Sep 12 '15 at 20:50
You might want to improve your testing, use some mocking or "unit tests". You'll want to be sure your queries are right. Send some PHP queries for multiple buttons and check if it works. If that works, you can try to make the arduino check the buttons and activate the queries through your PHP webapp. Questions on PHP/MySql itself are unlikely to be well answered on the arduino forum. — Paul, Oct 13 '15 at 14:25
So... to change the behaviour so to handle two different values... it's not trivial - it can be done, but you (the OP, not Damiano) need to brush on on SQL. I suggest testing the sort of statements you want purely in SQL first (forget the Arduino) and get the syntax errors out of the SQL side. Take a look at 13.2.5 INSERT Syntax - in particular the form for multiple inserts: INSERT INTO tbl_name (a,b,c) VALUES(1,2,3),(4,5,6),(7,8,9); — Nick Gammon♦, Feb 10 '16 at 3:51

Stack Exchange Network

current community

your communities

more stack exchange communities

Printing arduino pin numbers to MySQL

2 Answers 2

Your Answer

Not the answer you're looking for? Browse other questions tagged arduino-uno c++ wifi pins or ask your own question.

Hot Network Questions

Printing arduino pin numbers to MySQL

2 Answers 2

Your Answer

Sign up or log in

Post as a guest

Not the answer you're looking for? Browse other questions tagged arduino-uno c++ wifi pins or ask your own question.

Related

Hot Network Questions