my sql connector select

hi

i am working on a project that is control my arduino over mysql.

the problem i have is to select more than 1 object from the sql and put it into a variabel on the arduino.
i can sent multiple data to the sql but can only receive 1 data from the sql.

i am not a programmer, just a hobyguy that can use some help :wink:

here is my code yet

#include <Ethernet.h>
#include <MySQL_Connection.h>
#include <MySQL_Cursor.h>
#include "DHT.h"

#define DHTPIN 2
#define DHTTYPE DHT11
DHT dht(DHTPIN, DHTTYPE);



int photocellPin = 0;     // the cell and 10K pulldown are connected to a0
int photocellReading;     // the analog reading from the sensor divider




byte mac_addr[] = { 0xDE, 0xAD, 0xBE, 0xEF, 0xFE, 0xED };

IPAddress server_addr(192, 168, 1, 219); // IP of the MySQL *server* here
char user[] = "steenmans";              // MySQL user login username
char password[] = "marlboro";        // MySQL user login password


//-------------------------------------------------SENDING/RECEIVE-----------------------------------------------------------------
// Sample query
char SAVE[] = "INSERT INTO growroom.test (message, vochtigheid, temperature, lichtstatus) VALUES ('%s',%d,%d,%d)";
char READTEMP[] = "SELECT temperature FROM growroom.setpoint ORDER BY temperature DESC LIMIT 1";
char READHUM[] = "SELECT vochtigheid FROM growroom.setpoint ORDER BY vochtigheid DESC LIMIT 1";// cant use this one?,please help:)
char query[128];
//-------------------------------------------------------------------------------------------------------------------------

int tempPin = NULL;    // variables
int humSetPoint = NULL;
int tempSetPoint = NULL;



EthernetClient client;
MySQL_Connection conn((Client *)&client);

void setup() {
Serial.begin(115200);
dht.begin();
while (!Serial); // wait for serial port to connect
;




}


void loop() {


photocellReading = analogRead(photocellPin);
Serial.println(photocellReading);


int photocellInteger;
photocellInteger = (int) photocellReading;// conversatie photocelReading(float) to photocellInteger







//-------------------------------------------TEMPERATURE-HUMIDITY-DHT-11-----------------------------------------------------------
// Reading temperature or humidity takes about 250 milliseconds!
// Sensor readings may also be up to 2 seconds 'old' (its a very slow sensor)
float h = dht.readHumidity();
// Read temperature as Celsius (the default)
float t = dht.readTemperature();
// Read temperature as Fahrenheit (isFahrenheit = true)
float f = dht.readTemperature(true);


// Check if any reads failed and exit early (to try again).
if (isnan(h) || isnan(t) || isnan(f)) {
  Serial.println("Failed to read from DHT sensor!");
  return;
}
// Compute heat index in Celsius (isFahreheit = false)
float hic = dht.computeHeatIndex(t, h, false);

Serial.print("Humidity: ");
Serial.print(h);
Serial.print(" %\t");
Serial.print("Temperature: ");
Serial.print(t);
Serial.println(" *C ");
int tInteger;
tInteger = (int) t;//converstaie t(float) naar tInteger (integer), sql connector kan geen float doorsturen,string of integer

int hInteger;
hInteger = (int) h;// conversatie h naar integer

int tempReading;
int TEMP;
//----------------------------------------------------------------------------------------------------------------------------------

//---------------------------------------------------------SENDING------------------------------------------------------------------
Ethernet.begin(mac_addr);
Serial.println("Verzenden");
Serial.println("Connecting...");
if (conn.connect(server_addr, 3306, user, password)) {
  delay(1000);
  // Initiate the query class instance
  MySQL_Cursor *cur_mem = new MySQL_Cursor(&conn);
  // Save

  sprintf(query, SAVE, "test sensor", hInteger, tInteger, photocellInteger);
  // Execute the query
  cur_mem->execute(query);



  //sprintf(query, TEST, photocellInteger);

  //cur_mem->execute(query);


  delete cur_mem;
  // Note: since there are no results, we do not need to read any data
  // Deleting the cursor also frees up memory used

  Serial.println("Data recorded.");
}
else
  Serial.println("Connection failed.");


//--------------------------------------------------------READING----------------------------------------------------------------------

Serial.println("Ontvangen");
Serial.println("Connecting...");

  MySQL_Cursor *cur_mem = new MySQL_Cursor(&conn);
// Supply the parameter for the query
// Here we use the QUERY_POP as the format string and query as the
// destination. This uses twice the memory so another option would be
// to allocate one buffer for all formatted queries or allocate the
// memory as needed (just make sure you allocate enough memory and
// free it when you're done!).



//------------------------------------------------------------------------------reading-setpoint-temp-------------------------------------------------------------------------
sprintf(query, READTEMP);


// Execute the query
cur_mem->execute(query);
// Fetch the columns and print them
column_names *cols = cur_mem->get_columns();
for (int f = 0; f < cols->num_fields; f++) {
  
  if (f < cols->num_fields-1) {
    Serial.print(',');
    
  }
}
Serial.println();
// Read the rows and print them
row_values *row = NULL;
do {
  row = cur_mem->get_next_row();
  if (row != NULL) {
    for (int f = 0; f < cols->num_fields; f++) {
      
      tempSetPoint = atoi(row->values[f]);
        Serial.print("Ingestelde Temperatuur:  ");
        Serial.println(tempSetPoint);
      if (f < cols->num_fields-1) {
        Serial.println(',');
       
      }
    }
    Serial.println();
  }
} while (row != NULL);
// Deleting the cursor also frees up memory used
delete cur_mem;
//----------------------------------------------------------------------------------------------------------reading-setpoint-hum------------------------------------------------------------------
 
????







Serial.println("Data Saved.");



conn.close();
//---------------------------------------------------------------------------------------------------------------------------------





delay(10000); // delay 10 sec

}

thanks!!!

Welcome to the forum!

Please use the code tags (</>) to make your code easier to read

Like this

Perhaps this example may help you. Its probably installed as one of the examples in your MySQL library already

rw950431:
Welcome to the forum!

Please use the code tags (</>) to make your code easier to read

Like this

Perhaps this example may help you. Its probably installed as one of the examples in your MySQL library already

Hey thanks

But it’s still only for 1 “select”.
Cant Fins An example of how you select multiple variabels from my SQL.
Ik find enough examples of how you sent multiple variabels tot SQL ( like i used in my code)
Ik somebody can make al little example it would be great :wink:

You could simply duplicate the section of code you use to load the first query and use it to run the humidity one.

But if you are trying to get the maximum values then maybe this query will work

char READTEMP[]="SELECT MAX(temperature), MAX(vochtigheid) FROM growroom.setpoint ";

You can also use MIN() if you want the lowest values.

rw950431:
You could simply duplicate the section of code you use to load the first query and use it to run the humidity one.

But if you are trying to get the maximum values then maybe this query will work

char READTEMP[]="SELECT MAX(temperature), MAX(vochtigheid) FROM growroom.setpoint ";

You can also use MIN() if you want the lowest values.

thanks

here is my new code

/*
  MySQL Connector/Arduino Example : complex insert

  This example demonstrates how to issue an INSERT query to store data in a
  table using data from variables in our sketch. In this case, we supply the
  values dynamically.

  This sketch simulates storing data from a sensor in a table.

  For this, we will create a special database and table for testing.
  The following are the SQL commands you will need to run in order to setup
  your database for running this sketch.

  CREATE DATABASE test_arduino;
  CREATE TABLE test_arduino.hello_sensor (
    num integer primary key auto_increment,
    message char(40),
    sensor_num integer,
    value float,
    recorded timestamp
  );

  Here we have a table that contains an auto_increment primary key, a text
  field, a field to identify the sensor, the value read, and timestamp of
  the recorded data.

  Note: Since this sketch uses test data, we place the INSERT in the setup()
        method so that it runs only once. Typically, you would have the
        INSERT in the loop() method after your code to read from the sensor.

  INSTRUCTIONS FOR USE

  1) Create the database and table as shown above.
  2) Change the address of the server to the IP address of the MySQL server
  3) Change the user and password to a valid MySQL user and password
  4) Connect a USB cable to your Arduino
  5) Select the correct board and port
  6) Compile and upload the sketch to your Arduino
  7) Once uploaded, open Serial Monitor (use 115200 speed) and observe
  8) After the sketch has run for some time, open a mysql client and issue
     the command: "SELECT * FROM test_arduino.hello_sensor" to see the data
     recorded. Note the field values and how the database handles both the
     auto_increment and timestamp fields for us. You can clear the data with
     "DELETE FROM test_arduino.hello_sensor".

  Note: The MAC address can be anything so long as it is unique on your network.

  Created by: Dr. Charles A. Bell
*/
#include <Ethernet.h>
#include <MySQL_Connection.h>
#include <MySQL_Cursor.h>
#include "DHT.h"

#define DHTPIN 2
#define DHTTYPE DHT11
DHT dht(DHTPIN, DHTTYPE);

int led = 3;

int photocellPin = 0;     // the cell and 10K pulldown are connected to a0
int photocellReading;     // the analog reading from the sensor divider




byte mac_addr[] = { 0xDE, 0xAD, 0xBE, 0xEF, 0xFE, 0xED };

IPAddress server_addr(192, 168, 1, 219); // IP of the MySQL *server* here
char user[] = "steenmans";              // MySQL user login username
char password[] = "marlboro";        // MySQL user login password


//-------------------------------------------------SENDING/RECEIVE-----------------------------------------------------------------
// Sample query
char SAVE[] = "INSERT INTO growroom.test (message, vochtigheid, temperature, lichtstatus) VALUES ('%s',%d,%d,%d)";
char READTEMP[] = "SELECT temperature, vochtigheid FROM growroom.test ORDER BY time DESC LIMIT 1";
char READHUM[] = "SELECT vochtigheid FROM growroom.setpoint ORDER BY vochtigheid DESC LIMIT 1;";//???

char query[128];
//-------------------------------------------------------------------------------------------------------------------------

int tempPin = NULL;    // variables
int humSetPoint = NULL;
int tempSetPoint = NULL;



EthernetClient client;
MySQL_Connection conn((Client *)&client);

void setup() {
  Serial.begin(115200);
  dht.begin();
  while (!Serial); // wait for serial port to connect
  
pinMode(led, OUTPUT);




}


void loop() {


  photocellReading = analogRead(photocellPin);



  int photocellInteger;
  photocellInteger = (int) photocellReading;// conversatie photocelReading(float) to photocellInteger







  //-------------------------------------------TEMPERATURE-HUMIDITY-DHT-11-----------------------------------------------------------
  // Reading temperature or humidity takes about 250 milliseconds!
  // Sensor readings may also be up to 2 seconds 'old' (its a very slow sensor)
  float h = dht.readHumidity();
  // Read temperature as Celsius (the default)
  float t = dht.readTemperature();
  // Read temperature as Fahrenheit (isFahrenheit = true)
  float f = dht.readTemperature(true);


  // Check if any reads failed and exit early (to try again).
  if (isnan(h) || isnan(t) || isnan(f)) {
    Serial.println("Failed to read from DHT sensor!");
    return;
  }
  // Compute heat index in Celsius (isFahreheit = false)
  float hic = dht.computeHeatIndex(t, h, false);

  Serial.print("Humidity: ");
  Serial.print(h);
  Serial.print(" %\t");
  Serial.print("Temperature: ");
  Serial.print(t);
  Serial.println(" *C ");
  int tInteger;
  tInteger = (int) t;//converstaie t(float) naar tInteger (integer), sql connector kan geen float doorsturen,string of integer

  int hInteger;
  hInteger = (int) h;// conversatie h naar integer

  int tempReading;
  int TEMP;
  //----------------------------------------------------------------------------------------------------------------------------------

  //---------------------------------------------------------SENDING------------------------------------------------------------------
  Ethernet.begin(mac_addr);
  Serial.println("Verzenden");
  Serial.println("Connecting...");
  if (conn.connect(server_addr, 3306, user, password)) {
    delay(1000);
    // Initiate the query class instance
    MySQL_Cursor *cur_mem = new MySQL_Cursor(&conn);
    // Save

    sprintf(query, SAVE, "test sensor", hInteger, tInteger, photocellInteger);
    // Execute the query
    cur_mem->execute(query);



    //sprintf(query, TEST, photocellInteger);

    //cur_mem->execute(query);


    delete cur_mem;
    // Note: since there are no results, we do not need to read any data
    // Deleting the cursor also frees up memory used

    Serial.println("Data recorded.");
  }
  else
    Serial.println("Connection failed.");


  //--------------------------------------------------------READING----------------------------------------------------------------------

  Serial.println("Ontvangen");
  Serial.println("Connecting...");
  
    MySQL_Cursor *cur_mem = new MySQL_Cursor(&conn);
  // Supply the parameter for the query
  // Here we use the QUERY_POP as the format string and query as the
  // destination. This uses twice the memory so another option would be
  // to allocate one buffer for all formatted queries or allocate the
  // memory as needed (just make sure you allocate enough memory and
  // free it when you're done!).



//------------------------------------------------------------------------------reading-setpoint-temp-------------------------------------------------------------------------
 
  column_names *c;
 int rows = 0;
row_values *r;
  
  
  sprintf(query, READTEMP);

  
  // Execute the query
  cur_mem->execute(query);
  // Fetch the columns and print them
  column_names *cols = cur_mem->get_columns();
  for (int f = 0; f < cols->num_fields; f++) {
    
    if (f < cols->num_fields-1) {
      Serial.print(',');
      
    }
  }
  Serial.println();
  // Read the rows and print them
  row_values *row = NULL;
  do {
    row = cur_mem->get_next_row();
    if (row != NULL) {
      for (int f = 0; f < cols->num_fields; f++) {
        
        tempSetPoint = atoi(row->values[f]);
          Serial.print("Ingestelde Temperatuur:  ");
          Serial.println(tempSetPoint);
        if (f < cols->num_fields-1) {
          Serial.println(',');
         
        }
      }
      Serial.println();
    }
  } while (row != NULL);
  // Deleting the cursor also frees up memory used
  delete cur_mem;
//----------------------------------------------------------------------------------------------------------reading-setpoint-hum------------------------------------------------------------------
   
///????







Serial.println("Data Saved.");
  









  conn.close();
  //---------------------------------------------------------------------------------------------------------------
}

the problem is that i still cant take 2 colums out of sql and put it in a variable on arduino.
with “temperature” it works but with “vochtigheid” it dont.

i am realy stuck at ideas:)
when i copy the code it gave me alot of errors .

grts

What are the errors? Please post them in code tags just like code.

Are you able to get the unmodified MySQL example sketches to work in your environment? Thats the first step..