Access control with mysql

Hello everyone I am new here my English level is not very good but I will try to explain myself.
i have 2 arduino codes working, and i wanted to make an access control system but i cant get it to work.
when I pass my badge I wanted it to read in the mysql database to know if the badge is valid

Can someone help me assemble the 2 codes?
I am using an arduino uno with ethernet shild
Thank you

access control with mysql


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

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

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

// Sample query
char query[] = "SELECT * FROM arduino.teste WHERE porte = 'P3' ";

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

void setup() {
  Serial.begin(9600);
  while (!Serial); // wait for serial port to connect
  Ethernet.begin(mac_addr);
  Serial.println("Connection en cour...");
  if (conn.connect(server_addr, 3306, user, password)) {
    delay(1000);
  }
  else
    Serial.println("Impossible de se connecter .");
}


void loop() {
  delay(2000);

  Serial.println("\nExécution de SELECT et impression des résultats\n");

  // Initier l'instance de classe de requête
  
  MySQL_Cursor *cur_mem = new MySQL_Cursor(&conn);
  // Execute the query
  cur_mem->execute(query);
  // Récupérez les colonnes et imprimez-les
  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();
  // Lire les lignes et les imprimer
  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);

 
  // La suppression du curseur libère également de la mémoire utilisée

  delete cur_mem;
}

/////////////////////////////////////////////////////////////////////////////////////////////////////

volatile unsigned long tagID = 0;
volatile unsigned long lastBitArrivalTime;
volatile int bitCount = 0;

void ISRone(void)
{
  lastBitArrivalTime = millis();
  bitCount++;
  tagID <<= 1;
  tagID |= 1;
}

void ISRzero(void)
{
  lastBitArrivalTime = millis();
  bitCount++;
  tagID <<= 1;
}

void setup()
{
  Serial.begin(9600);

  pinMode(2, INPUT);
  digitalWrite(2, HIGH);  // Enable pull-up resistor
  attachInterrupt(0, ISRzero, FALLING);

  pinMode(3, INPUT);
  digitalWrite(3, HIGH);  // Enable pull-up resistor
  attachInterrupt(1, ISRone,  FALLING);

  tagID = 0;
  bitCount = 0;
}

void loop()
{
  //  See if it has been more than 1/4 second since the last bit arrived
  if(bitCount > 0 && millis() - lastBitArrivalTime >  250){
    Serial.print(bitCount, DEC);
    Serial.print(" bits: ");
    Serial.println(tagID);
    tagID = 0;
    bitCount = 0;
  }
}

Well combine the setups into one, and put the database query into loop at the point you print out the ID currently? You'll need to use sprintf or similar to construct the query string with the badge ID in it.

Hello

for the moment I have managed to make the access control work like that but I cannot get the id tag on mysql

volatile unsigned long tagID = 0;
volatile unsigned long lastBitArrivalTime;
volatile int bitCount = 0;
int Q4 = 4;
void ISRone(void)
{
  lastBitArrivalTime = millis();
  bitCount++;
  tagID <<= 1;
  tagID |= 1;
}

void ISRzero(void)
{
  lastBitArrivalTime = millis();
  bitCount++;
  tagID <<= 1;
}

void setup()
{
  Serial.begin(9600);

  pinMode(2, INPUT);
  pinMode(Q4,OUTPUT);
  digitalWrite(Q4,LOW);
  
  digitalWrite(2, HIGH);  // Enable pull-up resistor
  attachInterrupt(0, ISRzero, FALLING);

  pinMode(3, INPUT);
  digitalWrite(3, HIGH);  // Enable pull-up resistor
  attachInterrupt(1, ISRone,  FALLING);

  tagID = 0;
  bitCount = 0;
  
}


void loop()
{
  
  //  ********************************************LIRE LE NUMERO DE BADGE *************************
 
   if(bitCount > 0 && millis() - lastBitArrivalTime >  250){
    Serial.print(bitCount, DEC);
    Serial.print(" bits: ");
    Serial.print("BADGE N:  ");
    Serial.println(tagID);
    tagID = 0;
    bitCount = 0;
    
  }
  
  
  if (tagID == 58729871 || tagID == 8378992 ){
    
    digitalWrite(Q4,HIGH);
     Serial.println("S4 activer ");
     delay(1000); 
     digitalWrite(Q4,LOW);
    Serial.println("S4 desactiver");
    }
    
   
   


}

hello can someone help me.
because I can't make the 2 codes work together

thank you

What SQL query do you need to match a badge id? If you know this,
you simply build it up using sprintf or similar, and run the query and
read the result...

in fact I is not what works best I just need that it do an sql request every time I badge to check if my badge is in my database

I tried to follow tutorials on the internet but it still doesn't work someone can help me because I don't see where the problem comes from

#include <Ethernet.h>
#include <MySQL_Connection.h>
#include <MySQL_Cursor.h>
#include <SPI.h>


//-----------------------------------C A  ----------------------------------------------

volatile unsigned long tagID = 0;
volatile unsigned long lastBitArrivalTime;
volatile int bitCount = 0;
int Q4 = 4;
void ISRone(void)
{
  lastBitArrivalTime = millis();
  bitCount++;
  tagID <<= 1;
  tagID |= 1;
}

void ISRzero(void)
{
  lastBitArrivalTime = millis();
  bitCount++;
  tagID <<= 1;
}

//------------------------------------------------------------------------------------



int leitura;
float leituraconvertida;
char sentenca[128];
char valortagID[10];
byte mac_addr[] = { 0xDE, 0xAD, 0xBE, 0xEF, 0xFE, 0xED };
IPAddress server_addr(192,168,1,48);  
char user[] = "arduino";              
char password[] = "arduino1234";        
char INSERIR_TEMP[] = "INSERT INTO teste (message,porte) VALUES (0001,%s)";
char BANCODEDADOS[] = "USE arduino";
EthernetClient client;
MySQL_Connection conn((Client *)&client);
void setup() 
{ 
   Serial.begin(9600);
   while (!Serial); 
   Ethernet.begin(mac_addr);
   Serial.println("Conectando...");
   if (conn.connect(server_addr, 3306, user, password)) 
   {
      delay(1000);
      
      MySQL_Cursor *cur_mem = new MySQL_Cursor(&conn);
      cur_mem->execute(BANCODEDADOS);
      delete cur_mem;
   }
   else
   {
      Serial.println("A conexão falhou");
      conn.close();
   }


//---------------------------------------C A --------------------------------------------------------
   pinMode(2, INPUT);
  pinMode(Q4,OUTPUT);
  digitalWrite(Q4,LOW);
  
  digitalWrite(2, HIGH);  // Enable pull-up resistor
  attachInterrupt(0, ISRzero, FALLING);

  pinMode(3, INPUT);
  digitalWrite(3, HIGH);  // Enable pull-up resistor
  attachInterrupt(1, ISRone,  FALLING);

  tagID = 0;
  bitCount = 0;
  //---------------------------------------------------------------------------------------------------



 

   
}
void loop() 
{
//------------------------------------------------------------------------------------------------------
  if(bitCount > 0 && millis() - lastBitArrivalTime >  250){
    Serial.print(bitCount, DEC);
    Serial.print(" bits: ");
    Serial.print("BADGE N:  ");
    Serial.println(tagID);
    tagID = 0;
    bitCount = 0;
//----------------------------------------------------------------------------------------------------------
    
    Serial.println("Executando sentença");
   leitura = tagID;
   
   leituraconvertida = (float(tagID));
   dtostrf(leituraconvertida, 4, 1, tagID);
   sprintf(sentenca, INSERIR_TEMP, tagID);
   
   MySQL_Cursor *cur_mem = new MySQL_Cursor(&conn);
   cur_mem->execute(sentenca);
   delete cur_mem;
   
 }
}

You perform the query, then throw away the cursor before retrieving the results...

Not sure "execute" is right for a query too - is there an executeQuery() method or similar?

in fact I got a script which serves to send the values ​​of a temperature sensor to mysql and I tried to adapt it to but needs but I can't happen there is something that I can't send my tagID on mysql

You need to retrieve the result of a query to check if the database has an entry. Have you looked at the documentation and examples for the MySQL_Cursor library?

yes I took the examples of mysql and I can read in mysql but after to make it work with my tagID I can not

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

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

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

// Sample query
char query[] = "SELECT population FROM world.city WHERE name = 'New York'";

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_addr);
  Serial.println("Connecting...");
  if (conn.connect(server_addr, 3306, user, password)) {
    delay(1000);
  }
  else
    Serial.println("Connection failed.");
}


void loop() {
  row_values *row = NULL;
  long head_count = 0;

  delay(1000);

  Serial.println("1) Demonstrating using a cursor dynamically allocated.");
  // Initiate the query class instance
  MySQL_Cursor *cur_mem = new MySQL_Cursor(&conn);
  // Execute the query
  cur_mem->execute(query);
  // Fetch the columns (required) but we don't use them.
  column_names *columns = cur_mem->get_columns();

  // Read the row (we are only expecting the one)
  do {
    row = cur_mem->get_next_row();
    if (row != NULL) {
      head_count = atol(row->values[0]);
    }
  } while (row != NULL);
  // Deleting the cursor also frees up memory used
  delete cur_mem;

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

  delay(500);

  Serial.println("2) Demonstrating using a local, global cursor.");
  // Execute the query
  cur.execute(query);
  // Fetch the columns (required) but we don't use them.
  cur.get_columns();
  // Read the row (we are only expecting the one)
  do {
    row = cur.get_next_row();
    if (row != NULL) {
      head_count = atol(row->values[0]);
    }
  } while (row != NULL);
  // Now we close the cursor to free any memory
  cur.close();

  // Show the result but this time do some math on it
  Serial.print("  NYC pop = ");
  Serial.println(head_count);
  Serial.print("  NYC pop increased by 12 = ");
  Serial.println(head_count+12);

}

Well, initially you're going to have to change your query to point to the table you keep your badge IDs in.

as I said above I am not an expert in programming I manage to do some small projects but I do not understand how to do the request