RFID to SQL

Projects General Guidance
1

Hey everyone! Seems ive struck incredibly lucky finding an entry level job somewhere looking to develop something. Honestly I've only been messing with Arduinos for nine months or so and haven't done anything particularly seriously aside from modify existing utilities and code, but somehow here I am at place for whata essentially a front and back end developer (granted, it barely pays above minimum wage, but if i manage to make it work the experience is worth its weight in gold).

Essentially, they want me to use an RFID card/scanner with an SQL database that logs a user's information and time, a keypad (likely for their password), and when the time is up, this triggers a machine interlock through the Arduino gpio to shut down the machine. They also want this to be accessible through a web page (to be accessed/modified by an administrator), but that's its own separate thing entirely.

As for right now, I've found the corresponding libraries, for the RFID the MFRC522 library and for the SQL I've found MySQL connector, and I've found a keypad tutorial, so I have the parts there. From what I've read, it seems more wise to write a script that handles the SQL database indirectly for other applications to go through, else it can just generally mess things up more, but im just not sure where else to even go from here.

I'll have to double check, but as of right now they wanted each individual Arduino to be linked to this database though WiFi (I have an esp8266 chip) but this was up to my choosing and I see where it may also be more sensible to just have it send the data to each respective machine cpmputer via USB and update the SQL database from there instead.

But I am just looking for general advice, feasibility, approach etc... on this. Thanks!

2

Hi CatDadJynx,

That is a cool project. My advice to make your life a bit easier is to use a raspberry pi. Because the newest of those have built in wifi, loads more memory and are in my experience more simple to use for things that need functions with the internet. Also they have a few usb ports which means you can get a simple usb keyboard. Just searching for rfid and mysql and raspberry pi will yield loads of results like this one: similar project? (i know it says NFC tag, but the idea is the same). Since the pi has more power than the arduino you could run a webpage of themselves and even remote acces them through something like vnc viewer. The coding on the pi is different (although you can run c++ scripts on them which are similar to arduino sketches) but I think it would be a more suited solution for the goal.

Seems like a cool project though, and congrats on your job :slight_smile:

3

Thanks! Yeah, a pi definitely seems a more wise alternative as far as power and functionality... it could all be done on one board that way, rather than having to handle some parts externally. Unfortunately, I dont know any python... not that im at all opposed to learning it either, id just rather give them results sooner than later if i could (even if it isnt perfect) because taking the time to learn python might cost me the job altogether.

As of right now, Ive managed to create a MySQL database using Visual Studio, and Ive created the appropriate columns. As far as Arduino utilities go... Ive taken a better look at the MySQL Connector, however I see that it requires internet to pass data back and forth, so i would have to establish a connection there for sure. I dont have an ethernet or wifi shield, but I have an esp8266 chip. Ive tried to search for solutions using MySQL Connector and an ESP8266, but Ive had no luck. Aside from that, all I have to do is put the RFID sensor through a level shifting circuit (3.3v power but 4v logic pins) and I can use that, so for the most part my issues lie within the MySQL Connector/internet connection. Any suggestions?

4

Hi CatDadJynx,

Sorry for the late reply, I had a busy weekend. You can run c++ on the pi aswell and python is very simple if you already know another language. You could always tell your employer that taking some time now will save time in the future. But of course the decision is up to you.

How to actually do interaction between a MySql server and the arduino is something I wouldn't personally know. The only advice i can give you is the following. Divide your project into three parts and work them out.

  1. Arduino side of things. So get the whole setup of keypad, RFID reader and the locking mechanism working.

  2. The internet link. In order for this to work you need a constant internet link between the arduino (or pi, again this has a built in wifi module so would save a lot of hassle) and the server where the MySQL is running.

  3. The server side. A MySQL server with some sort of interface that can be accessed via the internet and the arduino.

For number one you can simply google for an arduino rfid reader and tutorials for that. Also there are tutorials and even standard libraries for keypads so that should be fine. Also there are multiple posts on the forum helping people with them.

Number two is the trickier one. I have no experience with this but it might be a good idea to have a look at the different kinds of arduinos (some of them have a built in wifi module which would be very usefull). Also there are some people doing MySQL stuff with arduinos so have a google around for a tutorial. Keep in mind that MySQL is just a way of running a database on a server and thus using MySQL has nothing to do with the internet connection (you just need the arduino to be able to communicate with the server over internet).

Number three should be relatively simple because MySQL is popular and there are thousands of good tutorials on them.

Try to slice the project up into these parts and bring them all together in the end. That way the task is more manageable and it is probably easier to find good guidance for them. I hope someone else can help you more with the MySQL setup side of things as that is really not my expertise.

Hopefully that gives you some idea on how to go about things for a while.

5

No worries on the delay, thanks a lot for the detailed response!

As of right now, im actually working along the lines of what you advised- I started with the RFID reader and have that all wired up to a relay (which is what it would ultimately control) however, Im having issues with figuring out how to get the key card to trigger the relay... Ive used the utilities here:

which includes an access control example program. When i give the system power i can hear the relay click, and ive been able to set and verify a master RFID card, as well as set (and verify) a secondary card, i just cant figure out how to get them to trigger the relay. But this is a question for another forum... ive posted this question in the sensor forums, so hopefully someone there can help me with that. I looked up some brief keypad tutorials and im sure i can work that in easily enough once i get the other parts figured out, too.

As for the MySQL database... it seems there is a utility already made for the Arduino called MySQL Connector, here: GitHub - ChuckBell/MySQL_Connector_Arduino: Database connector library for using MySQL with your Arduino projects.

which includes a reference manual in the 'extras' folder. However, its meant to be used either with the ethernet or wifi shield, or with an arduino with built in wifi. Using it through an ESP8266 chip requires the use of custom configuration. Basically, because the internet protocol typically used (and used with the ESP8266 chip) is TCP (transmission control protocol, typically complimentary of IP), but MySQL requires the use of another internet protocol, TDS (tabular data stream) which is the protocol SQL servers use for sending and receiving database queries.

BUT, the alternative solution (and what it seems most people have done) is send HTTP Requests over TCP using the arduino, allowing you to talk to a Web-based REST API. The API would act as a middle man, taking HTTP GET or POST Requests from the arduino, and translating them to SQL Server by means of some database driver. What ive been advised to do by my employer (who doesnt really know anything about this stuff, so more like my coworker told me) was to more likely use PHP for this, since theyd ultimately like this data on a web interface as well, anyway.

So for now Im working on the hardware side, next the software (and web development) side. Thanks!

6

Those utilities look cool. I am not sure if you would need complicated stuff for that per se since the code simply does a check of the rfid card and if the cards ID is equal to something trigger the relay for a while. But it looks like you already know more about arduinos and MySQL then me so good job haha. From a product point of view I would use the arduinos with built in wifi since this would just be easier to put together for multiple things, but if you can get an example working with what you have that sounds great.

Be carefull with using relays directly with the arduino since they take a lot of power (you can use a transistor to trigger the relay with an external power source). But im sure the other arduino forum section can be of use there.

Glad i could help :slight_smile:

P.S.
Just a piece of workplace advice: never be afraid to ask coworkers for help since they usually know a lot and more often then not are willing to help if you are nice. Also be as realistic with your boss as possible (if you think it will take three weeks just say so).

7

Yeah, unfortunately they want the complicated stuff, so the customer can look at their time from an online interface. Which is fine for me, because my boss said hes willing to pay for training for the web development part, and Ive been desperately trying to get out of manufacturing/into programming.

Ill definitely keep that in mind about the relay as well, and asking questions/being realistic on the job, thanks!