Pratik_Roy98
Published © LGPL

Smart Logging of Data Using NodeMCU, MySQL, RFID

Automated entry/exit using RFID and MySQL database.

IntermediateFull instructions provided7 hours2,491
Smart Logging of Data Using NodeMCU, MySQL, RFID

Things used in this project

Hardware components

NodeMCU ESP8266 Breakout Board
NodeMCU ESP8266 Breakout Board
×1
MFRC522 RFID Reader
×1
rfid TAGS
×1
barcode scanner
×1

Software apps and online services

Arduino IDE
Arduino IDE
MYSQL

Story

Read more

Schematics

Schematic

Code

RFID_Registration

C/C++
#include <SPI.h>
#include <MFRC522.h>


#include <MySQL_Connection.h>
#include <MySQL_Cursor.h>
#include <ESP8266WiFi.h>
#include <WiFiClient.h>

#define SS_PIN D4
#define RST_PIN D3

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

char ssid[] = "ssid";                 // Network Name
char pass[] = "pwd";                 // Network Password
byte mac[6];



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

String query="";
char query1[128];


IPAddress server_addr(192, xxx ,x,xxx);          // MySQL server IP
char user[] = "";           // MySQL user
char password[] = "";       // MySQL password

void setup() 
{
  Serial.begin(9600);   // Initiate a serial communication

  Serial.println("Initialising connection");
  

  Serial.println("");
  Serial.println("");
  Serial.print("Connecting to ");
  Serial.println(ssid);
  
  WiFi.begin(ssid, pass);

  while (WiFi.status() != WL_CONNECTED) {
    delay(200);
    Serial.print(".");
  }

  Serial.println("");
  Serial.println("WiFi Connected");

  Serial.print("Assigned IP: ");
  Serial.print(WiFi.localIP());
  Serial.println("");

  Serial.println("Connecting to database");

  while (conn.connect(server_addr, 3306, user, password) != true) {
    delay(200);
    Serial.print ( "." );
  }

  Serial.println("");
  Serial.println("Connected to SQL Server!");  

  
  SPI.begin();      // Initiate  SPI bus

  mfrc522.PCD_Init();   // Initiate MFRC522
 
  Serial.println("Put your card to the reader...");
  Serial.println();
  
  
}
void Register(String UID)
{
 
  Serial.println(UID);
   UID="'"+UID+"'";

  Serial.println("Enter your name");  
   
  while (Serial.available()==0)  {}  
   String Nam=Serial.readString();  
   Serial.println(Nam);
   Nam="'"+Nam+"'";
   Nam.trim();
   
      
  Serial.println("Enter your Reg No.");  
  while (Serial.available()==0)  {}  
   String reg=Serial.readString(); 
   Serial.println(reg);
    reg="'"+reg+"'";
    reg.trim();
    

  Serial.println("Enter your Contact No.");  
  while (Serial.available()==0)  {}  
   String con=Serial.readString(); 
   Serial.println(con);
    con="'"+con+"'";
    con.trim();

  query="INSERT INTO `rfid`.`rfid_reg` (`UID`, `NAME`, `Reg_No`, `Contact_No.`) VALUES ("+UID+","+Nam+","+reg+","+con+");";
  

  Serial.println("Recording data.");
  //Serial.println(query);
  strcpy(query1, query.c_str()); 
  
  MySQL_Cursor *cur_mem = new MySQL_Cursor(&conn);
  
  cur_mem->execute(query1);

  delete cur_mem;
  Serial.println("Registered");
}
void check(String UID)
{
  int flag=0;
    Serial.println(UID);

  query="SELECT * FROM rfid.rfid_reg where UID=\""+UID+"\"";
   strcpy(query1, query.c_str()); 

  MySQL_Cursor *cur_mem = new MySQL_Cursor(&conn);
  
  cur_mem->execute(query1);
  
  column_names *cols = cur_mem->get_columns();

  // Read the rows and print them
  row_values *row = NULL;
  do {
    row = cur_mem->get_next_row();
    if (row != NULL) {
      flag=1;
      Serial.println("Already Registered");
    }
} while (row != NULL);
  // Deleting the cursor also frees up memory used
  delete cur_mem;

  if(flag==0)Register(UID);
}

void loop() 
{

  // 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 UID= "";
  for (byte i = 0; i < mfrc522.uid.size; i++) 
  {
     UID.concat(String(mfrc522.uid.uidByte[i] < 0x10 ? "0" : ""));
     UID.concat(String(mfrc522.uid.uidByte[i], HEX));
  }
  UID.toUpperCase();

  

  check(UID);

  
    delay(1000); //10 sec
  Serial.println();
   Serial.println("Put your card to the reader...");
  Serial.println();

 
} 

RFID_entry/exit_logging

C/C++
#include <NTPClient.h>
#include <WiFiUdp.h>

WiFiUDP ntpUDP;
NTPClient timeClient(ntpUDP, "asia.pool.ntp.org",19800);

#include <SPI.h>
#include <MFRC522.h>


#include <MySQL_Connection.h>
#include <MySQL_Cursor.h>
#include <ESP8266WiFi.h>
#include <WiFiClient.h>

#define SS_PIN D4
#define RST_PIN D3

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

char ssid[] = "ssid";                 // Network Name
char pass[] = "pwd";                 // Network Password
byte mac[6];

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

String query="";
char query1[128];
char query2[128];
char query3[128];
char query4[128];

String Name="",regno="";
String UID= "";


IPAddress server_addr(192,xxx ,x, xxx);          // MySQL server IP
char user[] = "";           // MySQL user
char password[] = "";       // MySQL password

void setup() 
{
  Serial.begin(9600);   // Initiate a serial communication

  Serial.print("Connecting to ");
  Serial.println(ssid);
  //WiFi.config(ip, gateway, subnet); 
  WiFi.begin(ssid, pass);

  while (WiFi.status() != WL_CONNECTED) {
    delay(200);
    Serial.print(".");

     

  }

  Serial.println("");
  Serial.println("WiFi Connected");
   timeClient.begin();

  Serial.print("Assigned IP: ");
  Serial.print(WiFi.localIP());
  Serial.println("");

  Serial.println("Connecting to database");

  while (conn.connect(server_addr, 3306, user, password) != true) {
    delay(200);
    Serial.print ( "." );
  }

  Serial.println("");
  Serial.println("Connected to SQL Server!");  

  
  SPI.begin();      // Initiate  SPI bus

  mfrc522.PCD_Init();   // Initiate MFRC522

  Serial.println("Put your card to the reader...");
  Serial.println();
 

  
  
}
void entry()
{
 
  timeClient.update();
  //Serial.println(UID+Name+regno);
  Serial.println("Enter the Barcode");
  while (Serial.available()==0)  {}  
   String bar=Serial.readString();  
   Serial.println("Barcode :"+bar);
   
  String formattedDate=timeClient.getFormattedDate();
  int splitT =  formattedDate.indexOf("T");
  String Date = formattedDate.substring(0, splitT);
 

  String Time = formattedDate.substring(splitT+1, formattedDate.length()-1);

  
    


  query="INSERT INTO `rfid`.`rfid_check` (`UID`, `Name`, `Reg_No.`, `Issue_Date`, `Issue_Time`, `Remote_Code`) VALUES ('"+UID+"','"+Name+"','"+regno+"','"+Date+"','"+Time+"','"+bar+"');";
  

  Serial.println("Recording data.");
  //Serial.println(query);
  strcpy(query3, query.c_str()); 
  
  MySQL_Cursor *cur_mem = new MySQL_Cursor(&conn);
  
  cur_mem->execute(query3);

  delete cur_mem;

}
void Exit()
{
 
  timeClient.update();
  //Serial.println(UID+Name+regno);
  String formattedDate=timeClient.getFormattedDate();
  int splitT =  formattedDate.indexOf("T");
  String Date = formattedDate.substring(0, splitT);
 

  String Time = formattedDate.substring(splitT+1, formattedDate.length()-1);
    
  

  query="UPDATE `rfid`.`rfid_check` SET `Return_Date`='"+Date+"', `Return_Time`='"+Time+"' WHERE `UID`='"+UID+"';";
  

  Serial.println("Recording data.");
  //Serial.println(query);
  strcpy(query4, query.c_str()); 
  
  MySQL_Cursor *cur_mem = new MySQL_Cursor(&conn);
  
  cur_mem->execute(query4);

  delete cur_mem;
}
void entry_exit()
{
  int flag=0;
    //Serial.println(UID+Name+regno);
  query="SELECT * FROM rfid.rfid_check where UID=\""+UID+"\"";
  //Serial.println(query);
   strcpy(query2, query.c_str()); 

  MySQL_Cursor *cur_mem1 = new MySQL_Cursor(&conn);
  
  cur_mem1->execute(query2);

  column_names *cols = cur_mem1->get_columns();
   

  // Read the rows and print them
  row_values *row = NULL;
  row=cur_mem1->get_next_row();

     while(row != NULL) {
   
      flag=1;
      
 
     
         row=cur_mem1->get_next_row();
    }
 delete cur_mem1;
  query="";

    if(flag==0)
    {
      Serial.println("Proceed with entry");
      entry();
      Serial.println("Entry Done");
    }
    else
    {
        Serial.println("Proceed with exit");
        Exit();
        Serial.println("Exit Done");
        
    }
}
void check()
{
  int flag=0;
    Serial.println(UID);
  query="SELECT * FROM rfid.rfid_reg where UID=\""+UID+"\"";
  //Serial.println(query);
   strcpy(query1, query.c_str()); 

  MySQL_Cursor *cur_mem1 = new MySQL_Cursor(&conn);
  
  cur_mem1->execute(query1);

  column_names *cols = cur_mem1->get_columns();
   

  // Read the rows and print them
  row_values *row = NULL;
  row=cur_mem1->get_next_row();
  
     while(row != NULL) {
   
      flag=1;
      Name=row->values[1];
  regno=row->values[2];
 
     
         row=cur_mem1->get_next_row();
    }
 delete cur_mem1;
  query="";
  
    if(flag==1)
    {
      // Serial.println("Proceed with entry/exit");
        entry_exit();
    }
    else
    {
      Serial.println("Faculty not registered");
    }


  
}

void loop() 
{
timeClient.update();

  // 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 :");
  
  for (byte i = 0; i < mfrc522.uid.size; i++) 
  {
     UID.concat(String(mfrc522.uid.uidByte[i] < 0x10 ? "0" : ""));
     UID.concat(String(mfrc522.uid.uidByte[i], HEX));
  }
  UID.toUpperCase();


  if(UID!="" or UID.length()==8)check();

  UID="";
  Name="";
  regno="";
    delay(1000); //10 sec
  Serial.println();
   Serial.println("Put your card to the reader...");
  Serial.println();

 
} 

Credits

Pratik_Roy98

Pratik_Roy98

4 projects • 8 followers
IOT Hobbyist.

Comments