Trybotics Logo

A NodeMCU RFID Controller

DESCRIPTION

Long before there were smart homes ruin by Alexa, Google Home, etc., there were smart homes running a mashup of protocols and controllers. Shortly after the advent of RFID cards and key fobs and the controllers that used them, DIY'ers started playing with micro-controllers to utilize RFID to control access to doors and devices.

My attempt was built around a small RFID transceiver made by Parallax, their serial RFID Card Reader that used a TTL (5vdc) serial connection to a ZBasic micro-controller, their ZX-24r, which was and still is based on an ATmel AVR ATmega1284P micro-controller which, in turn, used a RS-232 serial connection to a Wiznet WIZ110SR serial to Ethernet controller to connect to the local network with a hardwired twisted pair cable. Like I said, it was a mashup. (Surprisingly, these boards are still available though the current total cost for the three boards is well over $150.) The key fobs were EM-4100 protocol fobs which were and still are inexpensive. (Currently, Amazon sells a bag of 100 fobs for $15 or $.15 per fob.)

The software running on the ZBasic controller polled the Parallax board for a key card or key fob swipe. If it found one, it took the 12 byte RFID code and sent a request through the Ethernet control to a client running on a Windows machine to check the validity of the swiped code. The Windows client was needed because the ZBasic controller did not have the ability to talk directly to the MySQL database server where the RFID code information was stored.

The Windows client checked code against the entries in a MySQL database running on a Linux server that runs on still another machine on the network. If the code was found in the database as valid, the client returned an 'Accept' code to the ZBasic controller. If the code was found as not valid, a 'Reject' code was sent to the ZBasic controller. The software was a pretty serious mashup, too.

The system worked well for years. But eventually, the Windows client that did the database heavy lifting was replaced by by a small Linux workstation that ran on a Raspberry Pi so the decision for the RFID system came down to either rewrite the client to continue to support the now very old hardware mashup or start fresh with new hardware and software. Since the software replacement would be pretty simple and straight forward (the platform specific code could be replaced with a few simple webpages running on the existing Apache web server and the existing MySQL database server), I decided to try a hardware replacement built around a wifi enabled WeMos NodeMCU ESP8266 micro-controller. That's what this Instructable is about.

This instructable will show how the build and program an RFID controller that uses a network accessible Apache web server and a MySQL database server to control access.

Description:

The Hardware
5 More Images

This project is based on one of the stars of the IoT world, the ESP8266 based WeMos Mini NodeMCU micro-controller. This little gem is low-power, wifi-enabled with a full TCPIP stack, a few GPIO, USB connectivity builtin, an on-board 3.3vdc regulator, a tap for the incoming USB 5vd, 4 megs of memory and programmable using the Arduino programming suite. What's not to like?

You'll need:

1 - D1 Mini NodeMcu Lua ESP8266 ESP-12F Find it

1 - RDM6300 RFID Controller w/ antenna Find it

1 - 3 VDC, AQY282EH SSR SPST-NO Relay Find it

# - EM4100 Key Fob Find it

1 - 6 cm X 8 cm perf board Find it

1 - Flanged Case for the RFID controller Find it

1 - Flanged case for the RFID antenna Find it

1 - Red/Green dual LED Find it

2 - 470 ohm resistors for LED

1 - 1D4148 Diode

1 - 10k ohm resister

1 - 5 VDC Power Supply

Description:

Because an RFID controller is usually used as a security device for a door in lieu of a key or combination or keypad, the controller is separated into 2 cases. One case containing the LED and the antenna is placed on the "outside" of the door while the the other case containing the actual components of the controller is placed on the "inside" of the door.

All of the components are mounted on the perf board as shown in the layout drawing. The photo of the finished controller photo and the layout drawing have some minor differences. The position of the LED resistors are shown in the layout drawing along the top edge of the layout. In the photo of the controller, the resistors are actually on the bottom of the perf board. Also, the jumper labelled J1 in the component picture has been moved in the layout drawing to the bottom to the left of the D1 diode.

Power is supplied to the controller by a standard USB micro connector on the left side of the NodeMCU board. The connector socket is on the bottom side of the board, primarily to reduce the height of the plug. Control lines for the RDM6300 antenna and the dual color LED pass through the bottom of the "inside" case. The line enter the "outside" case through the bottom also.

Description:

When a user places a key fob against the front of the "outside" case near the LED, the antenna excites the RFID chip in the key fob which causes it to send it's embedded code to the antenna which connects to the RDM6300 RFID controller in the "inside" case. The RDM6300 decodes the signal from the RFID chip and sends the decoded RFID code to the NodeMCU.

The message is 12 bytes long. The first 10 bytes are the 5 ASCII hexi-decimal characters of the RFID chip in the key fob followed by the 2 byte hexi-decimal checksum for the first 10 characters.

The message is sent from the Tx line on the RDM5300 to the cathode of diode D1. The combination of diode D1 and resistor R3 acts as a level shifter. This is necessary because the NodeMCU is expecting a 3.3vdc signal but the RDM6300 sends a 5vdc signal. The junction of the D1 and R3 is then connected to the Rx input of the NodeMCU.

The software running on the NodeMCU determines if the received RFID code is valid. If the code is valid, the NodeMCU raises the output of GPIO D7 which energizes the solid state relay Rly1 and closes the normally open contacts of Rly1. If the code is valid, the NodeMCU raises the output of GPIO D2 (the green LED). If the code is not valid, the NodeMCU raises the output of GPIO D1 (the red LED).

If 4 consecutive invalid RFID codes are received, the software raises the fires the output of GPIO D6 which connected to the Rst GPIO when the J1 jumper is in place. This causes the NodeMCU to do a hard reset and reload itself. This is done to solve some potential problems, the first being that the RFID code is valid but the code has not been loaded when the NodeMCU last booted. This could easily happen if the RFID code was added to the RFID database after the last NodeMCU boot. There is also the possibility of the Wifi link going down. There is also the possibility of the Wifi DHCP lease timing out if the software is setup for DHCP IP address assignment which is the default.

When an invalid RFID code swipe is followed by a valid RFID code swipe, the invalid code counter is reset to zero and the NodeMCU continues as though there had not been an ivalid code swipe.

A note about J1: normally, J1 has a jumper shorting the two connectors that connect GPIO D6 to GPIO Rst. This allows the software to reset the NodeMCU under certain conditions which I'll explain later. Be aware, though, that if J1 is shorted, the NoderMCU can not be reprogrammed. In order to program the NodeMCU, the shorting jumper on J1 must be removed so that the programmer can access the the bootloader on the NodeMCU. In other words, remove the jumper to program and install the jumper to allow the software to reset the NodeMCU. More info follows during the discussion of the operation of the NodeMCU software.

Description:

The code for the RFID Controller is separated into two parts. The first, obviously, is the code for the NodeMCU that receives RFID codes from the RDM6300 receiver, determines the validity of the RFID code and, if the code is valid, operates the relay that closes a contact signalling the door operating equipment. The other side of the siftware is run on a remote web server on the local network that the RFID Controller is attached to. We'll talk about the web server first.

The heart of this RFID controller system is the MySQL database that contains the information on the key fobs and the RFID controllers that are on the network. The web server and the database server do not need to be on the local network. The two servers just have to be accessible from the local network.

The machine hosting the servers can be a Linux machine, a Windows machine, an Apple Mac or even a Raspberry Pi. The only absolute requirement is that the host machine has to be online whenever the RFID controller is online. This requirement may make a Windows or Mac desktop a bit of overkill if the host server is not already in place. In that case, a Raspberry Pi is a perfect hosting solution solution.

Whatever the choice for the host server, the host must be running a web server that is PHP enabled and a database server that can be queried by the web server. My choices are a PHP enabled Apache web server on a Linux machine running a MySQL database server. The code that I provide in this Instructable assumes that there is a host running a PHP and MySQL enabled web server and a MySQL (or MariaDB) database server.

If this is your first attempt at setting up a web and database server, don't despair. It's way easier than you might suspect and this will be a great place to start. Fortunately, there are some great resources for setting up the servers. For Windows machines, there is a package available on the web called WAMP which stands for Windows Apache MySQL PHP exactly what we need. It can be found here. The WAMP installer does the entire installation for you and when completed is ready to go. This is by far the easiest WAMP installation.

You can find resources on the web for similar installations for MacOS, Linux and the Raspberry Pi. BTW, this is a great opportunity to delve into the fascinating world of the Raspberry Pi which is a great cost-effective solution for this project. Building a LAMP (Linux Apache MySQL PHP) stack isn't quite as simple as installing the WAMP stack but the web has every resource, tutorial and support forum you could possibly need to get you up and running on a web and database server that will cost considerably less than $100. If you haven't tried the Raspberry yet you can start here.

Assuming that you've got the web and database server up and running, you need some code running on the server to supply the data that the RFID Controller needs to verify code swipes. Not surprisingly, the NodeMCU doesn't have the ability to talk directly to a database server of any flavor so we have to use a proxy to do that for us. Fortunately for us, the NodeMCU does know how to talk to a web server using the HTTP protocol. That's where the web server comes in. The NodeMCU will send requests to the web server. The web server will query the database and send the result back to the NodeMCU which will interpret the result and either validate or invalidate the RFID code swipe.

Here's the code for the NodeMCU. You'll need the Arduino programming software to load this code on the NodeMCU. I'll explain the bits after you jump past the code listing.

#include <ESP8266Wifi.h><br>#include <ESP8266HTTPClient.h>
  
//Access point credentials
const char* ssid = "Your_AP_SSID";
const char* pwd = "Your_AP_Password";
const char* deviceID = "gar_1";

// IPAddress Used to set static ip if desired/needed

IPAddress staticIP(192, 168, 1, 198); //ESP static ip
IPAddress gateway(192, 168, 1, 3);    //IP Address of your WiFi Router (Gateway)
IPAddress subnet(255, 255, 255, 0);   //Subnet mask
IPAddress dns(8, 8, 8, 8);            //DNS

String get_host = "http://your_host.com";

// Requires remote webserver running php and mysql
// get_rfid_list.php
// saveRFID_Swipe.php
// get_rfid.php

unsigned long startTime = 0;
String str = "";
String found = "N";

// This must match the securityKey value in the php code
String securityKey = "7zuZWINHpBVTUo30hwRa"; 

int resetCnt = 0;
int relay_timer = 0;
int reset_timer = 0;
int start_led_timer = 0;
int green_led_on_timer = 0;
int green_led_off_timer = 0;
int red_led_on_timer = 0;
int red_led_off_timer = 0;
int not_found_timer = 0;
int total_run_timer = 0;
int saCnt = 0;

String sa[200];     // Array for known rfid codes. Each code is 12 bytes.
String pay[50];     // Array for configuration variable storage. Each config variable requires 2 array slots, 1 for variable name and 1 for variable value
String pLoad[2];

#define GREEN_PIN 5;
#define RED_PIN 4;
#define RELAY_PIN 13;
#define RESET_PIN 1;

WiFiServer server(80);  // open port 80 for server connection
 
void setup() {
  startTime = millis();
  Serial.begin(9600); //initialise the serial communication
  delay(20);
  
  WiFi.begin(ssid, pwd);
//  WiFi.config(staticIP, subnet, gateway, dns); // Uncomment for static IP

  Serial.println("Connecting");

  while(WiFi.status() != WL_CONNECTED) { 
    delay(500);
    Serial.print(".");
  }
  Serial.println();
  Serial.print("Connected to WiFi network with IP Address: ");
  Serial.println(WiFi.localIP());
  Serial.println();

  //starting the server
  server.begin();

  Serial.flush();

  str = Serial.readString();
  str = "";

  get_rfid_list();    // Get known RFID codes
  pinMode(RESET_PIN, OUTPUT);
  digitalWrite(RESET_PIN, HIGH);

  pinMode(RELAY_PIN, OUTPUT);
  digitalWrite(RELAY_PIN, LOW);

  for(int i=0;i<5;i++) {
    pinMode(GREEN_PIN, OUTPUT);   // Indicator LED
    digitalWrite(GREEN_PIN, HIGH);
    delay(start_led_timer);  
    digitalWrite(GREEN_PIN, LOW);
    pinMode(RED_PIN, OUTPUT);   // Indicator LED
    digitalWrite(RED_PIN, HIGH);
    delay(start_led_timer);  
    digitalWrite(RED_PIN, LOW);
  }
}

void loop() {
  unsigned long timeDiff = ((millis() - startTime)/1000); // Convert to seconds
  if(timeDiff >= total_run_timer) {   // Reset every 21,600 seconds (6 hours)
    resetFunc();
  }
  
  if(Serial.available() > 0) {
    char chr = Serial.read();
    if(((chr >= char(48) && chr <= char(57))) || ((chr >= char(65)) && chr <= char(70))) {
      str += char(chr);
    }if(str.length() >= 12) {
       Serial.println(str);
      
//    Uncomment below to calculate checksum
//      String chkSumRet = getCkSum(str);
//      if(chkSumRet == "N") {
//        Serial.println("Bad code sent. Checksum fail.");
//      }

       String ret = get_device_status(str,"Device Code");
       if(ret == "Y") {
        resetCnt = 0;
        found = "Y";
        pinMode(GREEN_PIN, OUTPUT);   // Indicator LED
        digitalWrite(GREEN_PIN, HIGH);
        pinMode(RELAY_PIN, OUTPUT);   // Indicator LED

        digitalWrite(RELAY_PIN, HIGH);  // Close Relay contacts
        delay(relay_timer);
        digitalWrite(RELAY_PIN, LOW);   // Open Relay contacts
        
        delay((green_led_off_timer - relay_timer));  
        digitalWrite(GREEN_PIN, LOW);
      } else {
        found = "N";
        pinMode(RED_PIN, OUTPUT);   // Indicator LED
        resetCnt++;
        Serial.println(resetCnt);

	for(int i=0;i<resetCnt;i++) {
          digitalWrite(RED_PIN, HIGH);
          delay(not_found_timer);  
          digitalWrite(RED_PIN, LOW);
          delay(not_found_timer);  
        }
        delay(1000);
      }
      Serial.flush();

      if(found == "Y") {<br>        // Save swipe in rfid_swipes_nodemcu
        WiFiClient client = server.available();
        HTTPClient http;
        String url = get_host+"/homeinet/saveRFID_Swipe.php?device_name="+str+"&found="+found+"&deviceID="+deviceID+"&securityKey="+securityKey;
        http.begin(url);
        int httpCode = http.GET();
        String payload = http.getString();
        http.end();
      }    
      str = Serial.readString();
      str = "";

      if(resetCnt == 4) {
        resetFunc();
      }
    }
  }
}

String get_device_status(String device_name,String device_text) {
  String inArray = "N";
// Check to see if we have the RFID code in memory. 
  for(int i=0;i<saCnt;I++) {
    String a = sa[i];
    if(a == device_name) {
      inArray = "Y";
      break;
    }
  }

  if(inArray == "Y") {
    // If found, return "Y"
    Serial.println(device_text + " found in array!");
    return inArray;
  } else {
    // If not found, see if RFID code added to database since codes in memory loaded
    WiFiClient client = server.available();
  
    HTTPClient http;
    String url = get_host+"/get_rfid.php?device_name="+device_name+"&deviceID="+deviceID+"&securityKey="+securityKey;
    http.begin(url);
   
    //GET method
    int httpCode = http.GET();
    String payload = http.getString();
    if(payload=="Yes") {
      // If found in database, reload codes in memory and return "Y"
      inArray = "Y";
      Serial.println(device_text+" found on server");
      get_rfid_list();
    } else {
      // Else return "N"
      Serial.println(device_text+" not found");
    }
    http.end();
  }
  return inArray;
}

void get_rfid_list() {
  WiFiClient client = server.available();
  HTTPClient http;
  String url = get_host+"/get_rfid_list.php?device_id="+deviceID+"&securityKey="+securityKey;
  http.begin(url);
 
  //Get RFID codes and program config variables from database using GET method
  int httpCode = http.GET();
  String payload = http.getString();
  http.end();
   // Break payload into rfid codes (pLoad[0]) and config vaiables (pLoad[1])
  int r1=0, t1=0;
  for (int i=0; i < payload.length(); i++) { 
   if(payload.charAt(i) == '~') { 
      pLoad[t1] = payload.substring(r1, i); 
      r1=(i+1); 
      t1++; 
      pLoad[t1] = payload.substring(r1); 
      i = payload.length();     
    }
  }

  // Break pLoad[0] into rfid codes
  int r=0;
  saCnt = 0;
  payload = pLoad[0];
  for (int i=0; i < payload.length(); i++) { 
   if(payload.charAt(i) == '^') { 
      saCnt++; 
      sa[saCnt] = payload.substring(r, i); 
      r=(i+1); 
    }
  }

  // Break pLoad[1] into config vaiables
  r=0;
  int payCnt=0;
  String vName = "", vVal = "";
  payload = pLoad[1];
  for (int i=0; i < payload.length(); i++) { 
   if(payload.charAt(i) == '^') { 
      pay[payCnt] = payload.substring(r, i);
      r=(i+1);
      if((payCnt % 2) == 1) {
        vName = pay[payCnt - 1];
        vVal = pay[payCnt];
        if(vName == "relay_timer") {
          relay_timer = vVal.toInt();
        }
        if(vName == "reset_timer") {
          reset_timer = vVal.toInt();
        }
        if(vName == "start_led_timer") {
          start_led_timer = vVal.toInt();
        }
        if(vName == "green_led_on_timer") {
          green_led_on_timer = vVal.toInt();
        }
        if(vName == "green_led_off_timer") {
          green_led_off_timer = vVal.toInt();
        }
        if(vName == "red_led_on_timer") {
          red_led_on_timer = vVal.toInt();
        }
        if(vName == "red_led_off_timer") {
          red_led_off_timer = vVal.toInt();
        }
        if(vName == "not_found_timer") {
          not_found_timer = vVal.toInt();
        }
        if(vName == "total_run_timer") {
          total_run_timer = vVal.toInt();
        }
      }
      payCnt++; 
    }
  }
}

void resetFunc() {
  pinMode(GREEN_PIN, OUTPUT);   // Indicator LED
  digitalWrite(GREEN_PIN, HIGH);
  delay(reset_timer);  
  digitalWrite(GREEN_PIN, LOW);
  for(int i=0;i<5;i++) {
    pinMode(RED_PIN, OUTPUT);   // Indicator LED
    digitalWrite(RED_PIN, HIGH);
    delay(reset_timer);  
    digitalWrite(RED_PIN, LOW);
    delay(reset_timer);  
  }
  Serial.flush();
  Serial.println("Resetting...");
  delay(reset_timer);  
  digitalWrite(RESET_PIN, LOW);
}

String getCkSum( String str) {
  // XOR Checksum = Val1 XOR Val2 XOR Val3 XOR Val4 XOR Val5  
  unsigned long ckSum = 0;
  String test = "";
  
  test = str.substring(0,2);
  char copy[50];
  test.toCharArray(copy, 50);
  unsigned long val = strtoul(copy, NULL, 16);  
  ckSum = val; 
  
  for(int i=1;i<5;i++) {
    int r = i * 2;
    test = str.substring(r,r+2);
    char copy[50];
    test.toCharArray(copy, 50);
    unsigned long val = strtoul(copy, NULL, 16);  
    ckSum = ckSum xor val;
  }
  // Get sent chksum byte
  test = str.substring(10,12);
  test.toCharArray(copy, 50);
  unsigned long val1 = strtoul(copy, NULL, 16);  
  
  //ckSum = 0;
  val1 = val1 - ckSum;
  if(val1 == 0) {
    return "Y";
  }
  return "N";
}

The NodeMCU code starts with the necessary include files for the ESP8266 processor to communicate with the network. This is followed by a bunch of variable declarations that'll be used later in the program. You have to provide the correct SSID and password or key phrase for your network wifi access point. You also need to provide the correct web address in the get_host variable for the host for your web and database servers.

A bit farther down is another variable, securityKey, which is just a string of random characters. This variable is sent with every request to the web server and must match the value in the same variable in the PHP files that are used by the web server to access the database server. This is just a simple security feature that prevents unwanted visitors to the web server from accessing the database. If an unwanted request to the web server doesn't have a posted securityKey variable that matches the value in the PHP file, the request will be ignored. Every RFID controller that accesses this web server will have to use the same securityKey string value.

When we get to the setup routine, a number of things happen. First we store the time that the program started. This is simply the zero time of the NodeMCU's internal timer. Later in the loop routine, the program will check the time that has elapsed since the start of the program. If the eleapsed time exceeds 6 hours, the RFID Controller will automatically reset itself, reload the program and reconnect to the network. You can decide if your network is reliable enough to remove this function.

Next, the program tries to connect to the Wifi network. Assuming that the NodeMCU connects, the program starts a web client to communicate with the web server so that the program can get a list of valid RFID codes from the web server using the get_rfid_list function. Finally, the setup function signals that it is complete by flashing the red and green leds 5 times. Now that the setup function is complete, the program enters a continuously running loop function called (what else?) loop.

The loop function's task is to look for codes sent by the RDM6300 receiver and validate the codes. For every iteration through the loop, the program checks for a character from the RDM6300. When the loop has received 12 characters, the program will check to see if the code is valid. The first 10 characters of the 12 received characters are the actual RFID code. The remaining 2 characters are the the 2 checksum characters that the RDM6300 adds to the RFID code. The 12 character codes are stored in the database so if desired, the checksum of the 10 character received code can be calculated using the getCkSum function. In this program listing the getCkSum is disabled because I have never had a bad RFID code transmitted where the calculated checksum is different to the transmitted checksum. I guess in a noisy RF environment a corrupted message could be transmitted by the RDM6300 but I haven't experienced one. Your mileage may vary. If you need it, uncomment the 4 lines to enable the getCkSum function.

The program next sends a request to the web server to validate the received RFID code using the get_device_status routine. This routine first checks an array of valid RFID codes that were loaded by the setup function when the program first loaded. If the RFID code is not found in the array, the program composes a database request and requests the web server to run get_rfid.php using the received RFID code as a search value. If the web server finds the RFID code in the database, it returns a "Y" to the NodeMCU program. If not, it returns a "N" to the NodeMCU program. The PHP programs on the web server do a couple of other things which we will get to when we discuss the operation of the web server programs.

If the web server returns a "Y", the program fires the green led and resets the resetCnt variable to zero and sends another request to the webserver requesting the web If the web server returns a "N", the program fires the red led and increments the resetCnt variable by one. If the resetCnt variable adds to 4, the program executes the resetFunc routine.

Description:

The PHP code on the web server is the work horse of the RFID code validation system. This is the code that actually determines whether or not a RFID code swipe is a valid code. Without this code, nothing happens. Alright, already. How does it work?

Assuming that you have a working web server and a working MySQL server, the following code requests the data from the MySQL database and returns the result to the RFID Controller NodeMCU.

First, get_rfid_list.php:

<?php

$server = "localhost";<br>$user = "your_user_id";
$pw = "your_password";
$db = "your_db_name";
    
$link = @mysqli_connect ($server,$user,$pw,$db)
    or die ("Connect Failed!");

$securityKey = $_REQUEST["securityKey"];
// this securityKey must match the securityKey sent by the ESP8266NodeMCU
if($securityKey <> "7zuZWINHpBVTUo30hwRa") {
    die;
}

$sql = "SELECT rfid_code from rf_id_nodemcu WHERE LENGTH(rfid_code) = '12'";
$res = mysqli_query($link,$sql);
$nr = mysqli_num_rows($res);

if($nr > 0) {
  //$found = "Y";
  $a = "";
  for($i=0;$i < $nr;$i++) {
    $row = mysqli_fetch_array($res);
    $a .= $row["rfid_code"] . "^";
  }
    $a .= "~";

    $sql = "SELECT * from rfid_config_nodemcu WHERE device_id = '" . $_REQUEST["device_id"] . "'";
    $res = mysqli_query($link,$sql);
    $nr = mysqli_num_rows($res);
    
    $b = "";
    for($i=0;$i<$nr;$i++) {
      $row = mysqli_fetch_array($res);
      $b .= $row["variable_name"] . "^" . $row["variable_value"] . "^";
    }
    $a .= $b;
  } else {
    $a = "nr: " . $nr;
  }
die($a);

?>

The first lines of the get_rfid_list. php program assign values to the $server, $user, $pw and $db variables. Assuming that the MySQL server is running on the same host as the web server, leave the $server variable set to "local_host". If the database is on a different host, enter the IP address or domain name of the remote host in the $server variable. Lastly, be sure to change the values for $user, $pw and $db to the value that are correct for your database server.

This code retrieves the known RFID codes stores in the rf_id_nodemcu table and adds them to a message that will be returned to the NodeMCU. The second task is to retrieve the configuration variables from the rfid_config_nodemcu table and add them to the second half of the message. The get_rfid_list routine in the ESP8266 code will parse the return message.

The first half of the message will be stored in an array the will be checked when the is a swipe. The second half of the message will be parsed and the configuration variables in the ESP8266 code will be populated with the values found in the second half of the returned message.

The next program, get_rfid.php:

<?php

// Set this to your timezone
// You can find a list of timezones here - <a href="https://www.php.net/manual/en/timezones.php">here</a>

date_default_timezone_set("America/New_York");

$server = "localhost";<br>$user = "your_user_id";
$pw = "your_password";
$db = "your_db_name";
    
$link = @mysqli_connect ($server,$user,$pw,$db)
    or die ("Connect Failed!");

$securityKey = $_REQUEST["securityKey"];
if($securityKey <> "7zuZWINHpBVTUo30hwRa") {
    die;
}

$device_name    =   $_GET['device_name'];

$sql = "SELECT * from rf_id_nodemcu WHERE rfid_code = '" . $device_name . "'";
$res = mysqli_query($link,$sql);
$nr = mysqli_num_rows($res);

$msg = "No";
$found = "N";
if($nr > 0) {
    $found = "Y";
    $msg = "Yes";
}

$remote_ip = $_SERVER['REMOTE_ADDR'];$sql = "INSERT INTO rfid_swipes_nodemcu (rfid_code,swipe_dt,ip_address,device_name,success) ";
$sql .= "VALUES('" . $_REQUEST["device_name"] . "','" . date("Y-m-d H:i:s") . "','" . $remote_ip . "','" . $_REQUEST["deviceID"] . "','" . $found . "')";
$res = mysqli_query($link,$sql);

if($res === true) {
    //$msg = "okay";
} else {
    $msg = "Insert failed!";
}

die($msg);

?>

When a swipe is detected by the RDM6300 receiver, the ESP8266 code calls the get_device_status routine which first checks the array populated by the get_rfid_list routine that is run at program startup. If the RFID code is found in the array, the ESP8266 sends an HTTP request to the web server to save the appropriate data for the swipe in the rfid_swipes_nodemcu table.

If the code is not found in the array, the ESP8266 code sends a request to the web server asking the web server to check if the RFID code is in the rf_id_nodemcu table. This can happen if the RFID code was added to the table after the ESP8266 code was started during power up or on a reset.

The web server runs the get_rfid.php program to check for the RFID code in the rf_id_nodemcu table. If the RFID code is found, the program saves the appropriate data in the rfid_swipes_nodemcu table and returns a message "Yes", to the ESP8266 indicating that the code has been found. If the code is not found, the program saves the appropriate data in the rfid_swipes_nodemcu table and returns a message, "No", to the ESP8266 indicating that the code has not been found.

If the return message received by the ESP8266 is "Yes", the ESP8266 code turns on the green led, energizes relay Rly1 to close the normally open relay contacts and resets the reset counter to zero.

If the returned message is "No", the reset counter is incremented by one. The ESP8266 code flashes the red led as many times as the reset counter. If the reset counter is equal to 4, the ESP8266 resets itself.

The last php program, saveRFID_Swipe.php:

<?php

// Set this to your timezone
// You can find a list of timezones here - <a href="https://www.php.net/manual/en/timezones.php">here</a>

date_default_timezone_set("America/New_York");<br>
$server = "localhost";
$user = "your_user_id";
$pw = "your_password";
$db = "your_db_name";
    
$link = @mysqli_connect ($server,$user,$pw,$db)
    or die ("Connect Failed!");
    
$securityKey = $_REQUEST["securityKey"];
if($securityKey <> "7zuZWINHpBVTUo30hwRa") {
    die;
}

if($_REQUEST["found"] == "Y") {
    $sql = "SELECT * from rf_id_nodemcu WHERE rfid_code = '" . $_REQUEST["device_name"] . "'";
    $res = mysqli_query($link,$sql);
    $nr = mysqli_num_rows($res);
    if($nr > 0) {
        $rfidRow = mysqli_fetch_array($res);
        $ownerName = $rfidRow["firstname"] . " " . $rfidRow["lastname"] . " - " . $rfidRow["location"];
    }
} else {
    $ownerName = "";
}

$remote_ip = $_SERVER['REMOTE_ADDR'];

$sql = "INSERT INTO rfid_swipes_nodemcu (rfid_code,swipe_dt,ip_address,owner,device_name,success) ";
$sql .= "VALUES('" . $_REQUEST["device_name"] . "','" . date("Y-m-d H:i:s") . "','" . $remote_ip . "','";
$sql .= $ownerName . "','" . $_REQUEST["deviceID"] . "','" . $_REQUEST["found"] . "')";
$res = mysqli_query($link,$sql);

if($res === true) {
    $msg = "okay";
} else {
    $msg = "Insert failed!";
}

die($msg);

?>

The program simply save the appropriate data in the rfid_swipes_nodemcu table if the swiped RFID code was found in the array of known RFID codes loaded at program startup.

Description:

The RFID Controller uses a MySQL (or MariaDB) database to determine which RFID codes are valid and which are not. The database is comprised of 3 tables: rf_id_nodemcu, rfid_config_nodemcu and rfid_swipes_nodemcu. I'll go over these three tables in some detail.

First, the rfid_config_nodemcu table.

This table is used to store configuration variables that are used by timers in the ESP8266 code. These variable and their associated values are kept in this table so that you can change the operating conditions of the code without having to re-compile and reload the ESP8266 code. The table has 5 fields: variable name, variable value, device_id (the RFID Controller that uses this variable), comments (related to this particular table entry) and trx (a unique record identifier.) If you like the way the code works, you should leave these records alone. If you want to experiment with flashing duration, for example, study the code and chage the appropriate timer. Maybe a backup would be a good idea. Just sayin'...

The next table is the rf_id_nodemcu table.

This table has 8 fields: rfid_code (the code transmitted by the key fob when activated by the RDM6300), last_name and first_name (of the person who has the key fob), location (where the key fob nominally is location), serialnum (the number printed on the key fob case - this is not the RFID code - just an identifier of the physical fob), allowed (not used in this ESP8266 code but could be used to restrict the key fob to only certain RFID controllers if so desired) and comment and comment2 (free to use for whatever you want.) This is the table that the ESP8266 code uses to validate a key fob swipe.

The last table is the rfid_swipes_nodemcu.

This table has 7 fields: rfid_code (the rfid code of the swiped key fob), swipe_dt (the date and toime of the swipe), ip_address (the IP address of the RFID controller requesting the swipe validation), owner (if the swipe is successful, the owner of the key fob), device_name (the id of the RFID controller requesting swipe validation), success (Y if the swipe is successfully validated, N if not validated) and trx (a unique record identifier.) The ip_address field is probably less than useful if the controller does not use a static IP address or have an address reservation if the RFID controller is using DHCP.)

These tables are essentially "fitted" to the ESP8266 and PHP code so any changes in the tables or fields will require changes in the ESP8266 and PHP code and vice-versa. Feel free to change as you feel the need. Backups are your friend. It's amazing how far down the rabbit hole you can go making changes before you realize that you need to back up and you can't get back to where you started. Again, backups are your friend (sometimes your only friend.)

Description:

I've posted a zip file with the Arduino sketch for the NodeMcu, the three PHP files for the web server and an sql to create MySQL tables in the MySQL datasbase of your choice. If you are not familar with the operation of MySQL databases, there are a huge number of tutorials on the web to help you get started. But what you will need is graphical front ent to manipulate the MySQL database.

There are many freeware MySQL front ends that you can choose from but one of the most common is phpMyAdmin which is included in the WAMP installer. It has a bit of a learning curve but it provides you with a graphical front end for adding, editing and deleting data in a MySQL database.

A second choice (and my favorite) is SQLYog. Though SQLYog is not freeware, there is a freeware community edition on GitHub that you can download. It also has a bit of a learning curve but, imo, the SQLYog gui front end is more intuitive than phpMyAdmin and it is available in Windows and Linux versions for both 64-bit and 32-bit systems.

There are others available so you should choose the one that best suits your needs and style.

One last note: this design and software is released under a Creative Commons Attribution-NonCommercial 4.0 International (CC BY-NC 4.0) license which means that you can download, use, modify and share for non-commercial purposes. Commercial use or derivatives, however, are not allowed. Here's a link that explains the license in full.

Otherwise, have at it and enjoy.

PD

Attachments


YOU MIGHT ALSO LIKE