send data from sensors to mysql db

Projects Programming
1

Guys, i have a problem.. i have a weather shield and i take some data from sensors. i want these data to send them in a mysql database with wifi shield.. i have try to send them with mysql connector library but the code stucks when i send the query.. Does anyone knows the solution, or any other way..? i dont know anything of php so can someone explain me..this is a part of my code, because i can't upload it. i want send data from get_wind_speed() function.

#include <Wire.h> 
#include <SPI.h>
#include "MPL3115A2.h" 
#include "HTU21D.h" 
#include <WiFi.h>
#include <sha1.h>
#include <mysql.h>
#include <stdlib.h>
#include <stdio.h>

char ssid[] = "WLAN-ITI2";    //wifi & database
int status = WL_IDLE_STATUS;  
/* Setup for the Connector/Arduino */
IPAddress server_addr(160,40,50,181);
char user[] = "dimos";
char password[] = "burton198";
Connector my_conn;      // The Connector/Arduino reference

float get_wind_speed()
{
    float deltaTime = millis() - lastWindCheck; //750ms
  
    deltaTime /= 1000.0; //Covert to seconds
 
    float windSpeed = (float)windClicks / deltaTime; //3 / 0.750s = 4
    // checkWiFiConnection();   
   //checkMySQLConnection();
    windClicks = 0; //Reset and start watching for new wind
    lastWindCheck = millis();
  
   char charwind[20];
  
    windSpeed *= 1.492; //4 * 1.492 = 5.968MPH
    windSpeed = windSpeed* 1.6;  //speed in kmh

    sprintf(charwind, "%f", windSpeed);
 
   char query[128];  //upload data to database
//   const char TEST_SELECT_QUERY[] = "SELECT * FROM web.weather LIMIT 10";

  
   char INSERT_DATA[] = "INSERT INTO web.weather VALUES ('open door')";
            my_conn.cmd_query(INSERT_DATA),charwind;
         // my_conn.cmd_query(charwind);
              
     Serial.println();
     
     Serial.print(charwind[25]);
    Serial.print("Windspeedkmh:");  
    Serial.println(windSpeed);
    
    return(windSpeed);  
       
}
2 
    sprintf(charwind, "%f", windSpeed);

The %f format
specifier is not
supported on the Arduino.

   char INSERT_DATA[] = "INSERT INTO web.weather VALUES ('open door')";
            my_conn.cmd_query(INSERT_DATA),charwind;

You want to insert the string "open door" into the weather column of the web table? Why are you then sending "?" as the data? There is no place holder for data in your insert statement.

i dont know anything of php

It's a very easy scripting language to learn, if you know anything about C. This is not an excuse. It isn't even a relevant statement, because you are not using PHP.

can someone explain me..this is a part of my code, because i can't upload it.

Do you mean that you can't even compile this snippet? You know what? I can't either. Head on over to http://snippets-r-us.com for help with your snippets.

3

You want to insert the string "open door" into the weather column of the web table? Why are you then sending "?" as the data? There is no place holder for data in your insert statement.

i want to convert the float windspeed to char in order to upload it in database.. i change this part of code with this

 char query[64];
  const char INSERT_DATA[] = "INSERT INTO web.weather VALUES (%s)";
    windSpeed *= 1.492; //4 * 1.492 = 5.968MPH
    windSpeed = windSpeed* 1.6;  //speed in kmh
    char wind[10];
   dtostrf(windSpeed, 1, 1, wind);  
sprintf(query, INSERT_DATA, temperature);
  my_conn.cmd_query(query);

but continues to stuck before my_conn.cmd_query...

4

can someone explain me..this is a part of my code, because i can't upload it.

Do you mean that you can't even compile this snippet? You know what? I can't either. Head on over to http://snippets-r-us.com for help with your snippets.
[/quote]

my code runs perfect, but when i add the mysql connector part in order to send queries in database then stucks...

5 
   dtostrf(windSpeed, 1, 1, wind);

Use a one character field, with one character after the decimal point. How is THAT going to work?

sprintf(query, INSERT_DATA, temperature);

Is temperature a string?

but continues to stuck before my_conn.cmd_query...

How do you know that?

Last time: Post ALL of your code!

6

but continues to stuck before my_conn.cmd_query...

How do you know that?
Last time: Post ALL of your code!
[/quote]

#include <Wire.h> 
#include <SPI.h>
#include "MPL3115A2.h" 
#include "HTU21D.h" 
#include <WiFi.h>
#include <sha1.h>
#include <mysql.h>
#include <stdlib.h>
#include <stdio.h>
MPL3115A2 myPressure; 
HTU21D myHumidity; 
const byte WSPEED = 3;
const byte RAIN = 2;
const byte STAT1 = 7;
const byte STAT2 = 8;
const byte REFERENCE_3V3 = A3;
const byte LIGHT = A1;
const byte WDIR = A0;
long lastSecond; 
byte seconds; 
byte seconds_2m; 
byte minutes; 
byte minutes_10m; 
long lastWindCheck = 0;
volatile long lastWindIRQ = 0;
volatile byte windClicks = 0;
char ssid[] = "WLAN-ITI2";    //wifi & database
int status = WL_IDLE_STATUS;  
/* Setup for the Connector/Arduino */
IPAddress server_addr(160,40,50,181);
char user[] = "dimos";
char password[] = "burton198";
Connector my_conn;      
volatile float rainHour[60];
int winddir = 0; 
float windspeedmph ; 
float humidity = 0;
float tempf = 0;
float rainin = 0; 
volatile float dailyrainin = 0; 
float pressure = 0;
float light_lvl = 455; 
volatile unsigned long raintime, rainlast, raininterval, rain;

void rainIRQ()
{
  raintime = millis();
  raininterval = raintime - rainlast; 
    if (raininterval > 10) 
  {
    dailyrainin += 0.011; 
    rainHour[minutes] += 0.011; 

    rainlast = raintime; 
  }
}

void wspeedIRQ()
{
  if (millis() - lastWindIRQ > 10) 
  {
    lastWindIRQ = millis(); 
    windClicks++; 
  }
}


void setup()
{
  Serial.begin(9600); 
   Serial.println(F("Weather Shield online"));
  
  if (WiFi.status() == WL_NO_SHIELD) {
    Serial.println(F("WiFi shield not present")); 
    
    while(true);
  } 
  
  connectToWiFi();  
  connectToMySQL(); 

  pinMode(STAT1, OUTPUT);
  pinMode(STAT2, OUTPUT); 
  pinMode(WSPEED, INPUT_PULLUP); 
  pinMode(RAIN, INPUT_PULLUP); 
  pinMode(REFERENCE_3V3, INPUT);
  pinMode(LIGHT, INPUT);
  myPressure.begin(); 
  myPressure.setModeBarometer(); 
  myPressure.setOversampleRate(7); 
  myPressure.enableEventFlags(); 
  myHumidity.begin();
  seconds = 0;
  lastSecond = millis();
  attachInterrupt(0, rainIRQ, FALLING);
  attachInterrupt(1, wspeedIRQ, FALLING);

  interrupts();
}








void loop()
{
    //Keep track of which minute it is
    if(millis() - lastSecond >= 1000)
    {
      digitalWrite(STAT1, HIGH); 
      
      lastSecond += 1000;

      if(++seconds_2m > 119) seconds_2m = 0;
  
      float currentSpeed = get_wind_speed();
      
      int currentDirection = get_wind_direction();
  
      if(++seconds > 59)
      {
        seconds = 0;
  
        if(++minutes > 59) minutes = 0;
        if(++minutes_10m > 9) minutes_10m = 0;
  
        rainHour[minutes] = 0; 
      }
 
      printWeather();

      
    }

    delay(100);
}


void calcWeather()
{
  
    humidity = myHumidity.readHumidity();
    tempf = myPressure.readTempF();
  
    rainin = 0;
    for(int i = 0 ; i < 60 ; i++)
      rainin += rainHour[i];
      
      
  
    
    pressure = myPressure.readPressure();
  
   
    light_lvl = get_light_level();

  
}


float get_light_level()
{
    float operatingVoltage = analogRead(REFERENCE_3V3);
  
    float lightSensor = analogRead(LIGHT);
    
    operatingVoltage = 3.3 / operatingVoltage; //The reference voltage is 3.3V or 5V
    
    lightSensor = operatingVoltage * lightSensor;
    
    return(lightSensor);
}








float get_wind_speed()
{
    float deltaTime = millis() - lastWindCheck; //750ms
  
    deltaTime /= 1000.0; //Covert to seconds
 
    float windSpeed = (float)windClicks / deltaTime; //3 / 0.750s = 4
    checkWiFiConnection();   
    checkMySQLConnection();
    windClicks = 0; //Reset and start watching for new wind
    lastWindCheck = millis();
    char query[64];
  const char INSERT_DATA[] = "INSERT INTO web.weather VALUES (%s)";
    windSpeed *= 1.492; //4 * 1.492 = 5.968MPH
    windSpeed = windSpeed* 1.6;  //speed in kmh
    char wind[10];
   dtostrf(windSpeed, 1, 1, wind);  
sprintf(query, INSERT_DATA,wind);
  my_conn.cmd_query(query);   
   //Serial.println();

   Serial.print("Windspeedkmh:");  
   Serial.print(windSpeed);
    
    return(windSpeed);  
       
}







//Read the wind direction sensor, return in degrees
int get_wind_direction()
{
    unsigned int adc;
  
    adc = analogRead(WDIR);
  
    
    if (adc < 380) return (113);
    if (adc < 393) return (68);
    if (adc < 414) return (90);
    if (adc < 456) return (158);
    if (adc < 508) return (135);
    if (adc < 551) return (203);
    if (adc < 615) return (180);
    if (adc < 680) return (23);
    if (adc < 746) return (45);
    if (adc < 801) return (248);
    if (adc < 833) return (225);
    if (adc < 878) return (338);
    if (adc < 913) return (0);
    if (adc < 940) return (293);
    if (adc < 967) return (315);
    if (adc < 990) return (270);
    return (-1); // error, disconnected?
}





void printWeather()
{
    calcWeather(); //Go calc all the various sensors
    winddir = get_wind_direction();
   
    Serial.print("$,winddir=");
    Serial.print(winddir);
   
    
    Serial.print(", humidity=");
    Serial.print(humidity, 1);
    Serial.print(", tempC=");
    float tempC;
    tempC=(tempf-32)/1.8;  //temperature C
    Serial.print(tempC, 1);
    Serial.print(", rainin=");
    Serial.print(rainin, 2);
    Serial.print(", dailyrainin=");
    Serial.print(dailyrainin, 2);
    Serial.print(", pressure=");
    Serial.print(pressure, 2);
    
    Serial.print(", light_lvl=");
    Serial.print(light_lvl, 2);
    Serial.print(",");
    Serial.println("#");
    delay (3000);

}







//functiion for connect to mysql db
void connectToMySQL()
{
    Serial.print(F("Connecting to Mysql..."));
    if (my_conn.mysql_connect(server_addr, 3306, user, password)) {                
     delay(1000);
     Serial.println(F("Success!"));
    } 
    else {
    Serial.println(F("Connection failed. Stopping execution..."));
    while(true);
    } 
    
   /* while(true)
    {
      if (my_conn.mysql_connect(server_addr, 3306, user, password))
      {
                delay(500);
        Serial.println("Connected to MySql");
        break;
      }
    }*/

}






void connectToWiFi()
{
        //attempt to connect to Wifi network:
        while ( status != WL_CONNECTED) { 
        Serial.print(F("Attempting to connect to SSID: "));
        Serial.println(ssid);
        // Connect to network:    
        status = WiFi.begin(ssid);

            // wait 10 seconds for connection:
            delay(1000);
    }
        Serial.println(F("Connected to network"));
         printWifiStatus();
     /* WiFi section
  status = WiFi.begin(ssid);
  // if you're not connected, stop here:
  if ( status != WL_CONNECTED) {
    Serial.println("Couldn't get a wifi connection");
    while(true);
  }
  // if you are connected, print out info about the connection:
  else {
    Serial.println("Connected to network");
    IPAddress ip = WiFi.localIP();
    Serial.print("My IP address is: ");
    Serial.println(ip);
  } 
  
  delay(2000);
    */

}









//function for wifi status
void printWifiStatus() {
    // print the SSID of the network:
    Serial.print(F("SSID: "));
    Serial.println(WiFi.SSID());
  
    // print your WiFi shield's IP address:
    IPAddress ip = WiFi.localIP();
    Serial.print(F("IP Address: "));
    Serial.println(ip);
  
    // print the received signal strength:
    long rssi = WiFi.RSSI();
    Serial.print(F("signal strength (RSSI):"));
    Serial.print(rssi);
    Serial.println(F(" dBm"));
}






void checkWiFiConnection()
{
  if (WiFi.status() != WL_CONNECTED)
  {
        Serial.println(F("WiFi connection is lost"));
        status = WL_IDLE_STATUS;
        connectToWiFi();                //attempt to reconnect to wifi

  }
}







void checkMySQLConnection()
{
  if (my_conn.is_connected() == 0)
  {
        
    Serial.println(F("Connection to MySQL is lost"));
    Serial.println(millis());
        connectToMySQL();
                
  }
}
7

but continues to stuck before my_conn.cmd_query...

How do you know that?

Last time: Post ALL of your code!
[/quote]
and i want to upload the variables from sensors to my mysql database in order to store them

8

eldimious:
the code stucks when i send the query.

There are three parts to the problem:

Understand what query you want to execute. This requires knowledge of SQL and of your database design.
Construct the query string to be executed.
Execute the query.

Which part are you having trouble with?

9

There are three parts to the problem:

Understand what query you want to execute. This requires knowledge of SQL and of your database design.
Construct the query string to be executed.
Execute the query.

Which part are you having trouble with?

i make a mysql database with name web and a table weather with 1columne char[25]. so i dont know in which part i have problem.. i think that i dont use the correct query in the code

10

There are three parts to the problem:

Understand what query you want to execute. This requires knowledge of SQL and of your database design.
Construct the query string to be executed.
Execute the query.

Which part are you having trouble with?

can you give me a clue, in order to achieve this :slight_smile:

11 
   dtostrf(windSpeed, 1, 1, wind);

You ARE going to fix this, right? (That really isn't a question!)

12

First thing, is the ip to a web server or mysql database?

13

PaulS:

   dtostrf(windSpeed, 1, 1, wind);

You ARE going to fix this, right? (That really isn't a question!)

sorry, but really i dont any idea of what to change, because i look a example of mysql connector and i make the same thing...

14

mistergreen:
First thing, is the ip to a web server or mysql database?

in my pc i run with xampp a database. so its the ip of my computer.

15

sorry, but really i dont any idea of what to change, because i look a example of mysql connector and i make the same thing...

Let's start with you explaining what the 4 arguments for the function are, and why you chose the values for each that you did.

16

in my pc i run with xampp a database.

So, you are running PHP on the server. Why are you not making a simple GET request (with data) to a PHP script and having the PHP script store the data in the database? Far easier to debug THAT scenario.

17

PaulS:

sorry, but really i dont any idea of what to change, because i look a example of mysql connector and i make the same thing...

Let's start with you explaining what the 4 arguments for the function are, and why you chose the values for each that you did.

the first one windSpeed is a float variable, where i store the measurement of sensor.
the last wind is a char ,where i convert the windspeed to char.
the other 2 arguments are for width

18

PaulS:

in my pc i run with xampp a database.

So, you are running PHP on the server. Why are you not making a simple GET request (with data) to a PHP script and having the PHP script store the data in the database? Far easier to debug THAT scenario.

and i want to upload the char wind to mysql because in order to use the value windspeed in an INSERT statement, we must construct a string that has the value inserted in it.

19

eldimious:

There are three parts to the problem:

Understand what query you want to execute. This requires knowledge of SQL and of your database design.
Construct the query string to be executed.
Execute the query.

Which part are you having trouble with?

i make a mysql database with name web and a table weather with 1columne char[25]. so i dont know in which part i have problem.. i think that i dont use the correct query in the code

Do you understand what query you want to execute? You should be able to execute it outside the Arduino to confirm that it runs without error and does what you want. It is pointless doing anything else until you know what query you want to execute.

20

the other 2 arguments are for width

True, but how do they relate? The first defines the total number of characters in the string (1). The second defines the number of characters after the decimal point.

Suppose that the variable contains 14.827. What do you expect the string produced to look like when there is one character total, and one after the decimal point?

What would the answer be if the values were 8 and 2, instead? (Hint: " 14.83").

and i want to upload the char wind to mysql because in order to use the value windspeed in an INSERT statement, we must construct a string that has the value inserted in it.

Constructing the GET statement would be even easier:

float windSpeed = 14.827;

char wind[10];
dtostrf(windSpeed, 8, 2, wind);

char request[50];
sprintf(request, "GET insertWindIntoDB.php?wind=%s HTTP1.0", wind);

client.println(request);

Then, all you need to do is create insertWindIntoDB.php that takes one argument (key/value pair wind = value), and inserts the value in the appropriate table in the database.