Raspberry pi + RFID + database

[Spalunkeldusch]

Hello,

I need some experienced advice what should I look into. Currently, I am working on a project with raspberry pi 2 model b and RFID RC522 reader. Basically, the idea is to store all the rfid tag data (id) to a database, then create a web access for users with different privileges to see the content. For example, RFID tags are handed to students at school and their attendance is tracked by calculating their tagging times.

Basically, what I am looking for is a suggestion which database/server/etc I should use for this? I am not really familiar with any of it so it would be a hard work, however I need some basic understanding how everything should work and what I need.

I assume i will need some kind of database server like mysql? but where should it be located? On my home PC? on raspberry itself? or in some kind of online webserver?

What else?

Thank in advance and sorry for stupid questions.

[PhatFil]

perhaps look at Node-RED https://nodered.org/ you can create a flow to access your rfid data, and use the dashboard feature to present the data graphically via a web page/s hosted on your pi

start perhaps saving your data to a file with the 'persist' node while you build your flows and once working ok you could look at using a database if you want a relational DB, mysql or mariadb are popular options.

[Spalunkeldusch]

Any other beginner friendly alternatives?

[danjperron]

Check my howto in that github

https://github.com/danjperron/MFRC522-python

It is a modified version which accept 7 and 10 Bytes UID cards. Also it doesn't need sudo since I use ioctl for the spi.

Download phpmyadmin webpage admin for sql. very handy.

Also I include a small python script which called the SQL server to get information and accept or refuse the entry.

[Spalunkeldusch]

Check my howto in that github

https://github.com/danjperron/MFRC522-python

It is a modified version which accept 7 and 10 Bytes UID cards. Also it doesn't need sudo since I use ioctl for the spi.

Download phpmyadmin webpage admin for sql. very handy.

Also I include a small python script which called the SQL server to get information and accept or refuse the entry.

Hello,

Sorry for stupid questions but I gotta figure it out to the max..

The plan of my project is to store RFID tag data to some kind of database, then later take that data from the database and work with it for example display user who owns that specific card.

However, my project requires so that the users are able to login to the webserver with specific credentials and find few user friendly tables with their attendance (=how many times they have scanned the tag etc). It's like a school attendance tracking system so every user logged in to website will be able to see their attendance. Basically, what I am telling is that is I install mysql, apache, phpmyadmin to my raspberry directly, and my raspberry is connected via WiFi to my home network, and when I have to present this project in the college, I will not be able to reach the database from for example random device like iPad, smartphone, that are on a different than my home network. Am I wrong? Will your shared github have any use in my case?

Sorry that this is kind of tricky to explain, but I really need to start working on it properly.

[danjperron]

For the wifi connection you could simply create an access point with your Raspberry Pi. This way you could do you presentation using any kind of device connect by wifi to your Pi. If you want simplier just bring a router with you and connect your devices to it!

Door system and attendance is basically similar. So check What I did in the database to identify the user to the RFID card and How I log the data.

It won't be the same but it is a good start on how to interact with the database.

This example lack of web pages but you should be able to figure it out using PHP.
But the interaction from the card reader to the database in my python example is a good start.

1 - Hook up the rfid card reader and check if you are able to read your RFID cards.
2- Create your data base with your corresponding tables. myphpadmin is very nice for that.
Think about what you need into your database , How many tables, What do I need in the tables.

3- Make your own python script to read the card and look for the owner in the database and fill up your log table.
4- Create php webpage to display your information. (who is there? who is missing, etc...)
5- Create you access point (AP) so everybody could log into your Pi. (Don't forget to change password)

[Spalunkeldusch]

For the wifi connection you could simply create an access point with your Raspberry Pi. This way you could do you presentation using any kind of device connect by wifi to your Pi. If you want simplier just bring a router with you and connect your devices to it!

Door system and attendance is basically similar. So check What I did in the database to identify the user to the RFID card and How I log the data.

It won't be the same but it is a good start on how to interact with the database.

This example lack of web pages but you should be able to figure it out using PHP.
But the interaction from the card reader to the database in my python example is a good start.

1 - Hook up the rfid card reader and check if you are able to read your RFID cards.
2- Create your data base with your corresponding tables. myphpadmin is very nice for that.
Think about what you need into your database , How many tables, What do I need in the tables.

3- Make your own python script to read the card and look for the owner in the database and fill up your log table.
4- Create php webpage to display your information. (who is there? who is missing, etc...)
5- Create you access point (AP) so everybody could log into your Pi. (Don't forget to change password)

Thank you for a detailed answer!

About that access point on the Pi. Could you by any chance be more specific? Maybe we misunderstood each other but what I meant was that I saw some guides for example https://pimylifeup.com/raspberry-pi-mysql-phpmyadmin/ and it basically says to install mysql, apache, and phpmyadmin on the raspberry itself, and then, when the raspberry is connected to my home internet, I can access phpmyadmin via *raspberry ip*/phpmyadmin

(I am using Rpi2 model B so it doesn't have wifi (i use edimax usb adapter for this)). I might be misunderstanding something myself but if I install database, phpmyadmin and apache (or something else) to my raspberry pi itself and I bring the whole project to my college and connect to the college's wifi, it would mean that I can access the website with attendance information only when im connected to college wifi (i couldnt access it from my home for example). Am I wrong?

TL;DR - I am just misunderstanding how it is better to host everything. At first I thought like it would be easier to get some kind of mysql/php online web host and work in there. Using python scripts make Rpi connect to that online server that is hosted somewhere else and send RFID card ID's using WiFi to that online web server/database. But I assume when I would have everything installed in the Rpi (mysql,php,etc) how would it work?

[danjperron]

(I am using Rpi2 model B so it doesn't have wifi (i use edimax usb adapter for this)). I might be misunderstanding something myself but if I install database, phpmyadmin and apache (or something else) to my raspberry pi itself and I bring the whole project to my college and connect to the college's wifi, it would mean that I can access the website with attendance information only when im connected to college wifi (i couldnt access it from my home for example). Am I wrong?

There is step on how we develop things.

Using only the Raspberry Pi

First step will be to make the RFID reader working.
Learn how to create,list and manage the database.
Create the webpage needed for the project.

Now it is time to go on the net!

When this is done you will need to transfer your database to the cloud or an internet SQL server.
The Webpage server will have to be also on the net.
The Raspberry Pi then will only read the cards and transfer the data into the SQL server.

There is a long way to go.
Check your things first using the Raspberry Pi alone.
If you are able to do all of it then it is your backup for your presentation.
After that it is just a question of porting the database and the webpage into the cloud.

[Spalunkeldusch]

(I am using Rpi2 model B so it doesn't have wifi (i use edimax usb adapter for this)). I might be misunderstanding something myself but if I install database, phpmyadmin and apache (or something else) to my raspberry pi itself and I bring the whole project to my college and connect to the college's wifi, it would mean that I can access the website with attendance information only when im connected to college wifi (i couldnt access it from my home for example). Am I wrong?

There is step on how we develop things.

Using only the Raspberry Pi

First step will be to make the RFID reader working.
Learn how to create,list and manage the database.
Create the webpage needed for the project.

Now it is time to go on the net!

When this is done you will need to transfer your database to the cloud or an internet SQL server.
The Webpage server will have to be also on the net.
The Raspberry Pi then will only read the cards and transfer the data into the SQL server.

There is a long way to go.
Check your things first using the Raspberry Pi alone.
If you are able to do all of it then it is your backup for your presentation.
After that it is just a question of porting the database and the webpage into the cloud.

Thank you for your answer!
Basically, my point now is to try reading RFID tags and placing them into tables created directly in Raspberry Pi, only then I should move to the net?
Also, will this tutorial work for setting up mySQL & phpmyadmin for completing the first step? https://howtoraspberrypi.com/how-to-ins ... y-pi-lamp/
+ about the website creation. Should it be created directly using phpmyadmin or I should use something like wordpress or such? Any advice on what to use?

[jbudd]

You might find this video useful - Andreas Speiss shows the use of Node-Red to store [weather station] data in a database, and Grafana to display it on a web page.
https://www.youtube.com/watch?v=JdV4x925au0

[danjperron]

Thank you for your answer!
Basically, my point now is to try reading RFID tags and placing them into tables created directly in Raspberry Pi, only then I should move to the net?

Did you tried my github and read the doorsystem.py to figure out how it works?

[Spalunkeldusch]

Thank you for your answer!
Basically, my point now is to try reading RFID tags and placing them into tables created directly in Raspberry Pi, only then I should move to the net?

Did you tried my github and read the doorsystem.py to figure out how it works?

Yes. Thank you for all the help. I have reviewed it and it seems sort of understandable but obviously requires lots or work.

My other question would be:
Would this be possible if I get some kind of webhosting like bluehost that offers unlimited mysql database, wordpress, phpmyadmin, etc and wont install anything on raspberry itself, and write a script that connects to that webhost and stores information to that specific database in that webhost. Would this work? If so, could you copy from your code or write an example of how the script part that forces a connection to external(webhost) database should look like?

[danjperron]

The sql query will be the same. You just need to change the security part which could be different.
The python is just an sql query than it is straight forward!