MFRC522 insert card-id to mysql database

Using Arduino Programming Questions
#1

Hi friends
I am new programming in Arduino but i am trying to make an access control with a MFRC522. Right now my problem is sending the card-id to mysql, when a wire the card-id string into mysql i receive an unreadable code on mysql data.

Part os my code is:

for (int i =0; i< sizeof(results); i++)
{
if (content.substring(1) == results ) //change here the UID of the card/cards that you want to give access

  • {*

  • //------------------------------MYSQL Server connection-----------------------------------*

  • Serial.println(“Connecting to MySQL Server …”);*

  • if (conn.connect(server_addr,3306,sqluser,sqlpassword)) {*

  • delay(1000);*

  • Serial.println(“Connected!”);*

  • // Initiate the query class instance*
    MySQL_Cursor *cur_mem = new MySQL_Cursor(&conn);

  • // Save*

  • sprintf(query, SAVE, content);*

  • // Execute the query*

  • Serial.println(query);*

  • cur_mem->execute(query);*

  • delete cur_mem;*

  • Serial.println(“Data recorded.”);*

  • conn.close();*

  • }*
    The result on Serial Monitor is like this:
    UID tag : 67 C7 76 34
    Message : Connecting to MySQL Server …
    Connected!
    INSERT INTO locker_db.teste (card_id) VALUES (‘h⸮⸮?’)
    Data recorded.
    Anyone can help me with this? i know that strings are not the best choice to work on Arduino, any help is appreciate.

#2

Part os my code is:

Why not all of your code ?

Is most of your code really in italics or could it be because you didn't use code tags ?

Please take note of the advice in Read this before posting a programming question which is a sticky post at the top of the forum page

Help us to help you by posting properly

#3

Hi!
Sorry for that, here is my complete code, i appreciate your help.

#include <WiFi.h>
#include <MFRC522.h>
#include <MFRC522Extended.h>
#include <SPI.h>
#include <MySQL_Connection.h>
#include <MySQL_Cursor.h>

// define pins for RFID
#define SS_PIN 5
#define RST_PIN 13
#define LED_G 16 //define green LED pin
#define LED_R 17 //define red LED
#define RELAY 26 //relay pin
#define ACCESS_DELAY 4000
MFRC522 mfrc522(SS_PIN, RST_PIN);   // Create MFRC522 instance.

// MySQL Settings
IPAddress server_addr(192, 168, 1, 71); // IP of the MySQL *server* here
char sqluser[] = "locker";              // MySQL user login username
char sqlpassword[] = "secret";        // MySQL user login password
// Querys
char SAVE[] = "INSERT INTO locker_db.teste (card_id) VALUES ('%s')";
char query[255];

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

// Wireless Settings
const char* ssid     = "home";
const char* password = "secret";

// Authorized Card-IDs
String results[] = { "67 C7 76 34", "F9 BC 98 84" };

void setup() 
{
  //Initialize serial and wait for port to open:
  Serial.begin(115200);
  while(!Serial) {
  }
  
  // Start by connecting to a WiFi network
  Serial.println();
  Serial.println();
  Serial.print("Connecting to ");
  Serial.println(ssid);
  
  WiFi.begin(ssid, password);
  
  // attempt to connect to Wifi network:
  while(WiFi.status() != WL_CONNECTED) 
  {
    // Connect to WPA/WPA2 network.
    delay(500);
    Serial.print(".");
  }
  Serial.println("");
  Serial.println("WiFi connected");
  Serial.println("IP address: ");
  Serial.println(WiFi.localIP()); 

  SPI.begin();          // Initiate  SPI bus
  mfrc522.PCD_Init();   // Initiate MFRC522
  pinMode(LED_G, OUTPUT);
  pinMode(LED_R, OUTPUT);
  pinMode(RELAY, OUTPUT);
  digitalWrite(LED_G, HIGH);
  digitalWrite(LED_R, HIGH);
  digitalWrite(RELAY, HIGH);
  Serial.println("Put your card to the reader...");
  Serial.println();
}

void loop() 
{
  //-------------------------------RFID Reader------------------------------------------
  
  // Look for new cards
  if ( ! mfrc522.PICC_IsNewCardPresent()) 
  {
    return;
  }
  // Select one of the cards
  if ( ! mfrc522.PICC_ReadCardSerial()) 
  {
    return;
  }
  //Show UID on serial monitor
  Serial.print("UID tag :");
  String content= "";
  byte letter;
  for (byte i = 0; i < mfrc522.uid.size; i++) 
  {
     Serial.print(mfrc522.uid.uidByte[i] < 0x10 ? " 0" : " ");
     Serial.print(mfrc522.uid.uidByte[i], HEX);
     content.concat(String(mfrc522.uid.uidByte[i] < 0x10 ? " 0" : " "));
     content.concat(String(mfrc522.uid.uidByte[i], HEX));
  }
  Serial.println();
  Serial.print("Message : ");
  content.toUpperCase();

  for (int i =0; i< sizeof(results); i++)
  {
    if (content.substring(1) == results[i] )
    {
      
      //------------------------------MYSQL Server connection-----------------------------------
      Serial.println("Connecting to MySQL Server ...");
      if (conn.connect(server_addr,3306,sqluser,sqlpassword)) {
        delay(1000);
        Serial.println("Connected!");

        // Initiate the query class instance
        MySQL_Cursor *cur_mem = new MySQL_Cursor(&conn);
        // Save
        sprintf(query, SAVE, content);
        Serial.println(query);
        
        // Execute the query
        cur_mem->execute(query);

        delete cur_mem;

        Serial.println("Data recorded.");
    
        conn.close();
      }
      else
      {
        Serial.println("Connection failed.");
        conn.close();
      }
      //END---------------------------MYSQL Server connection------------------------------------
      
      Serial.println("Authorized access");
      Serial.println();
      delay(500);
      digitalWrite(RELAY, LOW);
      digitalWrite(LED_G, HIGH);
      delay(ACCESS_DELAY);
      digitalWrite(RELAY, HIGH);
      digitalWrite(LED_G, LOW);
    }
    else
    {
      digitalWrite(LED_R, LOW);
      Serial.println("Denied access");
      digitalWrite(LED_R, HIGH);
    }
  } 
}
#4

When you print the query before executing it does it look as you expect ?

#5

Good Morning,
No, when i print is looks like this:

INSERT INTO locker_db.teste (card_id) VALUES ('h⸮⸮?')

#6 
        sprintf(query, SAVE, content);

content is a String
Does sprintf() work with Strings (as opposed to strings) ? The general advice is to use strings as opposed to Strings

Why bother with the SAVE array ? It only serves to confuse things. Why not simply put the string in the sprintf() function, assuming that you use it ?

#7