MYSQL quarry

Using Arduino Programming Questions
#1

HI all

how can I quarry MySQL with a value

I use

#include <UIPEthernet.h>
#include <MySQL_Connection.h>
#include <MySQL_Cursor.h>

uint8_t mac[6] = {0x00,0x01,0x02,0x03,0x04,0x05};

IPAddress server_addr(10,0,0,231);  // IP of the MySQL *server* here
char user[] = "root";                        // MySQL user login username
char password[] = "";

char query_value = "me@gmail.com";
char query[] = "SELECT UID,Holder FROM arduino.tab1 WHERE Email = query_value";

EthernetClient client;
MySQL_Connection conn((Client *)&client);
// Create an instance of the cursor passing in the connection
MySQL_Cursor cur = MySQL_Cursor(&conn);


void setup() {


  Serial.begin(115200);
  while (!Serial); // wait for serial port to connect
  Ethernet.begin(mac); //Configure IP address via DHCP

  Serial.print("ip address: ");
  Serial.println(Ethernet.localIP());
  Serial.print("subnetMask: ");
  Serial.println(Ethernet.subnetMask());
  Serial.print("gatewayIP: ");
  Serial.println(Ethernet.gatewayIP());
  Serial.print("dnsServer: ");
  Serial.println(Ethernet.dnsServerIP());

  Serial.print(server_addr);Serial.print(" ");
  Serial.println("Connecting...TO MYSQL PORT 3306");
  if (conn.connect(server_addr, 3306, user, password)) {
    delay(1000);
  }else
    Serial.println("Connection failed.");
    //conn.close();
}

void loop() {

      if (client.available()){}

      
 delay(2000);

  Serial.println("\nRunning SELECT and printing results\n");

  // Initiate the query class instance
  MySQL_Cursor *cur_mem = new MySQL_Cursor(&conn);
  // 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++) {
    Serial.print(cols->fields[f]->name);
    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++) {
        Serial.print(row->values[f]);
        if (f < cols->num_fields-1) {
          Serial.print(", ");
        }
      }
      Serial.println();
    }
  } while (row != NULL);
  // Deleting the cursor also frees up memory used
  delete cur_mem;

}

so I wont to quarry with query_value
I cant do that Iam getting error all the time

Error: 55 = Unknown column ‘query_value’ in ‘where clause’.

#2

instead of this:

char query_value = "me@gmail.com";
char query[] = "SELECT UID,Holder FROM arduino.tab1 WHERE Email = query_value";

try this:

char query[] = "SELECT UID,Holder FROM arduino.tab1 WHERE Email = \'me@gmail.com\'";

just to see if the error goes away.

#3

If you want to use two different variables, and that IS a good idea, then you need to combine the value in one variable into the other yourself. You can't expect that to automatically happen.

char query_value[] = "me@gmail.com"; // This string will NOT fit in a char
char query[80] = "SELECT UID,Holder FROM arduino.tab1 WHERE Email = "; // Remove the useless name and define the size so that it can have the value concatenated
strcat(query, query_value);

This is just to give you an idea. It will NOT work, because in the query, the value for the where clause needs to be in single quotes, so you really need to concatenate three strings, one of which contains only a single quote.

#4