Select one row from one column for comparison

[3rc0]

I tried many methods to do select statement and to get the result one time to compare if value there or not all get an issue with output, any idea how to re-structure the function to can work without error, example:

(num1, num2)	1	44		2	55

char selectNum [] = "SELECT num2 FROM dbName.tableName WHERE num2 = %d";

int numRow;

sprintf(query, selectNum, numRow)

int getInput = 55;

strcmp(getInput,rowValues);

Thanks.

[ChuckBell]

I need to see the rest of your code. Remember, the row returned is an array so you’ll need to get the first item. See the sample sketches in the connector for how to get the row result.

On Thu, Mar 14, 2019 at 17:16 3rc0 notifications@github.com wrote:

I tried many methods to do select statement and to get the result one time to compare if value there or not all get an issue with output, any idea how to re-structure the function to can work without error, example:

(num1, num2) | 1 | 44 | | 2 | 55 |

char selectNum [] = "SELECT num2 FROM dbName.tableName WHERE num2 = %d";

int numRow;

sprintf(query, selectNum, numRow)

int getInput = 55;

strcmp(getInput,rowValues);

Thanks.

— You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub https://github.com/ChuckBell/MySQL_Connector_Arduino/issues/91, or mute the thread https://github.com/notifications/unsubscribe-auth/AH0j4M3BuiXZ78jQ8uMh0xwcrQkbAHoKks5vWrwhgaJpZM4b1O8o .

[3rc0]

Thank you for your reply. Rest of code is long and unfinished. The purpose I want a user typing a digits number by Keypad.h and passing to MySQL for comparing it if was available in the Database or not, I tried with examples available but the output is incorrect or gets an error.

UPDATE: It is working now,

Issue $001 When pressing A B C D # * will show is correct and this problem of atol. Issue $002 If the entered number was not equal to the select statement the MySQL return it as 0 value.

// Example of MySQL Connector, Keypad, and LCD match the password with Database

// MySQL Connector example, SELECT Statement: https://github.com/ChuckBell/MySQL_Connector_Arduino/blob/master/examples/basic_select/basic_select.ino
// Passsing the value to the MySQL statement  https://github.com/ChuckBell/MySQL_Connector_Arduino/blob/master/examples/complex_insert/complex_insert.ino
// Keypad  example  https://playground.arduino.cc/Main/KeypadPassword
// LCD  example  HelloWorld  https://www.arduino.cc/en/Tutorial/HelloWorld

#include  <Ethernet.h>
#include <MySQL_Connection.h>
#include <MySQL_Cursor.h>
#include <Keypad.h>
#include <LiquidCrystal_I2C.h>

LiquidCrystal_I2C lcd(0x3f, 20, 4);

const byte rowsKeypad = 4;
const byte columnsKeypad = 4;
char keys[rowsKeypad][columnsKeypad] = {
  {'1', '2', '3', 'A'},
  {'4', '5', '6', 'B'},
  {'7', '8', '9', 'C'},
  {'*', '0', '#', 'D'}
};

byte rowKeypadPins[rowsKeypad] = {25, 24, 23, 22};
byte columnKeypadPins[columnsKeypad] = {29, 28, 27, 26};
Keypad customKeypad(makeKeymap(keys), rowKeypadPins, columnKeypadPins, rowsKeypad, columnsKeypad);
char customKey;

#define passLength 3
char Data[passLength];
byte data_count = 0;

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

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

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

// Sample query
char querySelect[] = "SELECT num2 FROM dbName.tableName WHERE num2 =%d";
char query[128];

//Database Name: dbName
//Table Name:  tableName
//  (num1, num2)
//    | 1 | 44 |
//    | 2 | 55 |

void setup() {
  Serial.begin(115200);
  while (!Serial); // wait for serial port to connect
  Ethernet.begin(mac_addr);
  Serial.println("Connecting...");
  if (conn.connect(server_addr, 3306, user, password)) {
    delay(1000);
  }
  else
    Serial.println("Connection failed.");

  lcd.init();  // Initialize the LCD screen.
  lcd.backlight();
}

void loop() {
  lcd.setCursor(0, 0);
  lcd.print("Enter Password:");

  customKey = customKeypad.getKey();
  if (customKey) {
    Data[data_count] = customKey;
    lcd.setCursor(data_count, 1);
    lcd.print(Data[data_count]);
    data_count++;
  }

  if (data_count == passLength - 1) {

    row_values *row = NULL;
    long head_count = 0;

    delay(1000);

    MySQL_Cursor *cur_mem = new MySQL_Cursor(&conn);
    // Execute the query

    int storedPass = atol(Data);
    Serial.println(storedPass);
    sprintf(query, querySelect, storedPass);
    cur_mem->execute(query);
    column_names *columns = cur_mem->get_columns();
    do {
      row = cur_mem->get_next_row();
      if (row != NULL) {
        head_count = atol(row->values[0]);
      }
    } while (row != NULL);
    delete cur_mem;

    // Show the result
    Serial.print("  num2= ");
    Serial.println(head_count);

    lcd.clear();
    if (!strcmp(storedPass, head_count)) {
      lcd.print("Correct");
      delay(5000);
    }
    else {
      lcd.print("Incorrect");
      delay(1000);
    }

    lcd.clear();
    clearData();
  }
}

void clearData() {
  while (data_count != 0) {
    Data[data_count--] = 0;
  }
  return;
}

[yosra88]

Hello, I am working on my Project Graduation and it's about the RFID (radio frequency identification) the idea is to compare the value of the tag seen on my serial monitor to the value present on my database (MYSQL) if it is the same access is authorized, if there is no tag on my database similar to the one seen on the serial monitor so access is denied.. can you help me to write this code! Thanks a lot

[ChuckBell]

Hello. I will help you with specific issues, but I won't write the code for you. Please Google for examples of how to use RFID with Arduino. Get that working before you attempt to add the connector code. If you have issues using the connector, you may open an issue or contact me directly. Please do not over post on existing issues unless you have a similar problem. ;)

[3rc0]

@MariaMaria97 the question is are you going to use UID of RFID tag for authorization or write data on RFID tag for comparing with MySQL?! in this example source code taking input from Keypad to compare based what wrote on RFID for comparing.

[yosra88]

I'm going to use UID of RFID tag for authorization !!

[3rc0]

@MariaMaria97 which model of RFID you are using?! the library? or you start from scratch?!

[yosra88]

I use the RFID reader MFRC522, and the library "MFRC522.h"

[yosra88]

this is the library that I use: " https://github.com/miguelbalboa/rfid "

[3rc0]

Good, your deadline for the project must present to the college?! Based on your shared source code at #479 you wanna any time touched the RFID Tag to do comparing with MySQL for authorized, That means you wanna do input of a new UID tag manually in MySQL database?!

[yosra88]

the deadline is June 15 but I have to realize these days because I have other tasks to do (link my database Mysql to Django framework to achieve the graphical interface and ...) in reality I will use 2 RFID systems: one for administration where we enter new tags and delete tags (as needed) and the other system for users where we have a program that controls access (denied access or authorized) and where we control tags from the database: who and when they entered?

[yosra88]

by the way I'm using the Nodemcu esp8266, I wrote this sketch for the first RFID system (administration), but I didn't finish because I 'm not a good programmer:

include // Use this for WiFi instead of Ethernet.h

include

include

include

include

define RST_PIN D2 // Configurable, see typical pin layout above

define SS_PIN D4 // Configurable, see typical pin layout above

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

// Sample query char INSERT_SQL[] = "INSERT INTO rfiddata.tag (tag_id) VALUES ()";

// WiFi card example char ssid[] = "**"; // your SSID char pass[] = "***"; // your SSID Password

WiFiClient client; // Use this for WiFi instead of EthernetClient MySQL_Connection conn(&client); MySQL_Cursor* cursor;

MFRC522 mfrc522(SS_PIN, RST_PIN); // Create MFRC522 instance

void setup() { Serial.begin(115200); // Initialize serial communications with the PC while (!Serial); // Do nothing if no serial port is opened (added for Arduinos based on ATMEGA32U4) // Begin WiFi section Serial.printf("\nConnecting to %s", ssid); WiFi.begin(ssid, pass); while (WiFi.status() != WL_CONNECTED) { delay(500); Serial.print("."); }

// print out info about the connection: Serial.println("\nConnected to network"); Serial.print("My IP address is: "); Serial.println(WiFi.localIP());

Serial.print("Connecting to SQL... "); if (conn.connect(server_addr, 3306, user, password)) Serial.println("OK."); else Serial.println("FAILED.");

// create MySQL cursor object cursor = new MySQL_Cursor(&conn);

if (conn.connected()) cursor->execute(INSERT_SQL);

SPI.begin();            // Init SPI bus
mfrc522.PCD_Init();     // Init MFRC522
mfrc522.PCD_DumpVersionToSerial();  // Show details of PCD - MFRC522 Card Reader details
Serial.println(F("Scan PICC to see UID, SAK, type, and data blocks..."));

}

void loop() { // Reset the loop if no new card present on the sensor/reader. This saves the entire process when idle. if ( ! mfrc522.PICC_IsNewCardPresent()) { return; }

// Select one of the cards
if ( ! mfrc522.PICC_ReadCardSerial()) {
    return;
}

// Dump debug info about the card; PICC_HaltA() is automatically called
mfrc522.PICC_DumpToSerial(&(mfrc522.uid));

} Thanks

[3rc0]

@MariaMaria97 Please check this source code is working fine with you?! if was everything ok let me know to do next step will connecting MySQL and passing UID to the database.

P.S. Post the error with a screenshot of the error.

/// edition 0.03

#include <ESP8266WiFi.h> // Use this for WiFi instead of Ethernet.h
#include <MySQL_Connection.h>
#include <MySQL_Cursor.h>
#include <SPI.h>
#include <MFRC522.h>

///////////////////////////
//// RFID Part ///////////
//////////////////////////

#define RST_PIN D2 // Configurable, see typical pin layout above
#define SS_PIN D4 // Configurable, see typical pin layout above

MFRC522 mfrc522(SS_PIN, RST_PIN); // Create MFRC522 instance

//////////////////////////
/////  MySQL Connector  //
//////////////////////////

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

MySQL_Connection conn(&client);
MySQL_Cursor* cursor;

// Sample query
///'%s' is passsing the string
char Insert_UID[] = "INSERT INTO rfiddata.tag (tag_id) VALUES (%s)";

/////////////////////////////////////
///////// WiFi Part  ///////////////
////////////////////////////////////

// WiFi card example
char ssid[] = ""; // your SSID
char pass[] = "*"; // your SSID Password

WiFiClient wifiClient; // Use this for WiFi instead of EthernetClient

/////////////////////////////////////
////// Selection part //////////////
////////////////////////////////////

int selectOption;

void setup() {

  Serial.begin(115200);  // Initialize serial communications with the PC
  while (!Serial);  // Do nothing if no serial port is opened (added for Arduinos based on ATMEGA32U4)
  // Begin WiFi section
  Serial.printf("\nConnecting to %s", ssid);
  SPI.begin();      // Init SPI bus
  WiFi.begin(ssid, pass);
  while (WiFi.status() != WL_CONNECTED) {
    delay(500);
    Serial.print(".");
  }

  // print out info about the connection:
  Serial.println("\nConnected to network");
  Serial.print("My IP address is: ");
  Serial.println(WiFi.localIP());

  Serial.println("Connecting to SQL... ");
  if (conn.connect(server_addr, 3306, user, password)) {
    Serial.println("Connnected to MySQL Server");
  } else {
    Serial.println("Failed to connecting MySQL Server.");
  }
  Serial.println(F(" Initializing MFRC522."));

  mfrc522.PCD_Init();
  mfrc522.PCD_DumpVersionToSerial();

  Serial.println(F(" Please make sure the Tag card touched with RFID Reader before selecting the operation:\n\r [1] Authorization.\n\r [2] Add a new Tag card."));
}

void loop() {

  selectOption = Serial.read();

  switch (selectOption) {

    /// Option [1] for checking authorization of Tag card.
    case '1':
      Serial.println(F(" Option [1] procedure read tag card and checking the authorization."));

    if ( ! mfrc522.PICC_IsNewCardPresent()) {
        return;
    }

    // Select one of the cards
    if ( ! mfrc522.PICC_ReadCardSerial()) {
        return;
    }
mfrc522.PICC_DumpToSerial(&(mfrc522.uid));
        // Passing the UID to MySQL

        break;

      /// Option [2] for adding a  new Tag card.
      case '2':
        Serial.println(F(" Option [2] procedure adding a new Tag card:"));
          if ( ! mfrc522.PICC_IsNewCardPresent()) {
        return;
    }

    // Select one of the cards
    if ( ! mfrc522.PICC_ReadCardSerial()) {
        return;
    }
mfrc522.PICC_DumpToSerial(&(mfrc522.uid));

      //passing the UID to MySQL

      break;

  }
}

[yosra88]

thanks a lot I'm testing it, the source code is working fine but when I pass the tag I can't see my UID on my serial monitor and even on my table!§

[3rc0]

Screenshot of Serial Monitor from the beginning until tested both options!!

[yosra88]

I don't see anything on my serial monitor when I pass the tag!!

[3rc0]

You not supposed to pass over the Tag card, as I wrote must be already on (touched) the RFID reader then choose the option you want, I do not see the version of MySQL @ChuckBell is that mean there is no connection between Arduino and MySQL??! because with me will show the version while connected. @MariaMaria97 You do not see anything, of course, I mentioned in the next step will be about passing the UID to MySQL ( table) in the database first finished the initializing and connection first.

[yosra88]

I tested the example "basic_insert_esp8266" and w I wrote this query "char Insert_UID[] = "INSERT INTO rfiddata.tag (tag_id) VALUES ('FF10BA03')"; " and i can see it on my table without seeingthe version of mysql... the tag card was even on the rfid reader but I didn't see any thing!!

[yosra88]

i think that seeing the version of mysql is not important!

[yosra88]

look I can see the tag ID when I use an other example:

[3rc0]

Try the source code again a tiny change I did on it, MySQL must show the version based the instruction and I hope you followed because I took that steps are written in manual. https://github.com/ChuckBell/MySQL_Connector_Arduino/blob/master/extras/MySQL_Connector_Arduino_Reference_Manual.pdf

at comment #11 you wrote this way > ('FF10BA03') < but in normal as in your screenshot, there is space between each two digits numbers.

[yosra88]

when I wrote this query: char Insert_UID[] = "INSERT INTO rfiddata.tag (tag_id) VALUES ('FF10BA03')"; I can see what I sent on my table:

[3rc0]

I understand and of course, it will be recorded what you write but my point here going to pass from UID directly to MySQL and this point there is space.

[yosra88]

So what are we going to do? it does not display the id tag on the serial monitor !!

[3rc0]

I wrote I did changes, did you copy /paste and try it again? see the numbers ( /// edition 0.02 ) in begin of the source code, will increase each time I do update.

[yosra88]

okey, just minute!!

[yosra88]

Sorry, it didn't work :( the first sketch I sent shows me details about the tag id, I just didn't know how to sent the UID to my database!!

[3rc0]

Try again this time, updated.

Understand what you trying to do but first, let finished the issue and debugging steps to understand what need to do. In your sketch, the Insert Query will execute one time without passing anything because you wrote empty > rfiddata.tag (tag_id) VALUES ()";< and supposed to be rfiddata.tag (tag_id) VALUES (%s)"; , %s is means passing string and %d integer.

[yosra88]

Yes I didn't write anything in values just to let you write what you want,, one minute I' going to try the 3rd edition!!

[yosra88]

the same problem :(

[3rc0]

Screenshot?!

[yosra88]

this is what it shows me when I test the sketch I sent writing %s in values: connexion is okey... I see details qbout the tag I pass but there is nothing in my database!!

[yosra88]

screenshot about the third edition: the same I sent about the first edition!!

[ChuckBell]

Is this resolved? Can we close the issue?

On Jun 2, 2019, at 7:32 AM, 3rc0 notifications@github.com wrote:

@MariaMaria97 https://github.com/MariaMaria97 the question is are you going to use UID of RFID tag for authorization or write data on RFID tag for comparing with MySQL?! in this example source code taking input from Keypad to compare based what wrote on RFID for comparing.

— You are receiving this because you commented. Reply to this email directly, view it on GitHub https://github.com/ChuckBell/MySQL_Connector_Arduino/issues/91?email_source=notifications&email_token=AB6SHYEH2SVIETLSK3NXU7LPYOVVHA5CNFSM4G6U54UKYY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGODWXTTJI#issuecomment-498022821, or mute the thread https://github.com/notifications/unsubscribe-auth/AB6SHYEKS5W6I7RZUNOEALLPYOVVHANCNFSM4G6U54UA.

[yosra88]

we didn't resolve the problem !

[ChuckBell]

NAICT, the last iteration of the code did not have the query to insert the data into the MySQL database. You need to add the call(s) to do that after where the placeholder (comment) states. Do that, and you should see the data inserted in the table.

mfrc522.PICC_DumpToSerial(&(mfrc522.uid)); //passing the UID to MySQL

In pseudo code, it would be these statements:

create cursor build SQL string (hint: sprintf) execute query close cursor

Look in the connector example sketches and you'll see how to do those steps. The trick is getting the value read from the RFID into the query, which you can do easily since it is a string. Remember to use a format string with quotes and %s.

[yosra88]

I'm trying Dr. @ChuckBell I will let you know abot the result!!

[ChuckBell]

Closed due to inactivity. Please open a new ticket if this is still relevant.