Arduino + mysql database + control acess

Hi guys im new here :wink:

Does someone eared about arduino query mysql to see if username and password matching if yes give a signal to a relay?

So for this im thinking about add a keyboard to arduino and an lcd.

the connection with mysql data i dont know if i can doo it with the usb or i need the ethernet shield?

thanks guys!

Maybe it CAN be done... but is it really worth the overhead/ extra ways to go wrong?

If you have a big database of authorized uses, and it doesn't change TOO frequently, how about storing that information in an SD card, filled on a "big" computer, and put into the Arduino as often as the database needs changing?

(If hard-coding the users into the Arduino program isn't up to your needs, of course.)

If the Arduino is going to have a USB connection to a PC running a MySQL database, what's the point of adding a keyboard to the Arduino?

You could have an application running on the PC that the Arduino talks to, using the serial port (USB cable). That application would receive a request to authenticate a user (however you got the user id and password) and receive a reply back that was true or false. That application would query the MySQL database.

Then, you need another application to add entries to the database, update entries in the database, and remove entries from the database.

Alternatively, you could leave the keyboard attached to the PC, and have one PC application that collect the user id and password, authenticate, and send commands to the Arduino. That application could have administrator capabilities, too.

What is the relay controlling that requires that only specific people be able to open or close it?

hey guys thanks for the replies.

Well the arduino can be connected to pc with usb cable but the persons no have direct acess of pc.

ya the relay is for open door only for authorized personel.

If there's going to be a PC running, what is the role of the Arduino?

the arduino will be connected to pc to acess mysql database.

Yes... but why?

I infer this is all part of an access control solution. What part of it can the PC not do without the Arduino's help?

I think what the OP is wanting to do is have a keypad/LCD combo sitting outside a locked door, with the PC on the other side of that locked door (perhaps in a locked cabinet). The PC would have a MySQL DB on it with the access codes, etc. The Arduino would control the LCD, read the keypad, and send the data to the PC, which would check the MySQL DB, and if the code entered was valid, would send an OK back to the Arduino and the Arduino would activate the doorstrike. Presumably the PC would also write an access log record to the DB (ie, this access key used at this time).

Could all this be done by the PC alone? Sure - but you would still likely use what the OP is trying to build - a microcontroller security access keypad and LCD connected by a serial link. Yes, they sell them; but have you ever priced them? They aren't cheap. Maybe the OP is trying to build his own in a cheaper manner? Maybe he can't afford the real deal? Maybe he wants to build one and sell it himself?

I am sure you could set up the PC to 1) read a keypad, 2) output data to the LCD, 3) update the MySQL DB, 4) activate the doorstrike; but doing all of these would probably require 2-3 separate cables from the PC to each part, or each part would have to be on a serial bus (RS-485 or something), or they would have to be set up like how the OP is trying to set things up - using a microcontroller.

Such a security automation system would be fairly easy to implement; the hard part might be the serial comms between the PC and the back-end DB application. Actually, it would probably be better if the Arduino was hooked up via an ethernet shield - your DB is likely to be something sitting on a server somewhere anyhow, with its own interface for users to add/revoke security keys, view access logs, etc. But, maybe the OP is doing something simpler, or doesn't have that kind of infrastructure, or money to implement it.

In the end, the process on the PC would be to listen to the port (via some kind of polled process like cron/task scheduler, or a service/daemon running in the background - either could be executing a script or process written in just about anything, as long as it can check/update MySQL and send data out a serial port), and check for data - if it sees any, then it would query the DB with it, and if found to exist, update the log, then send the data back to the Arduino (which is now waiting for data) to tell it to "unlock"; the Arduino would handle the activation, etc of the doorstrike and the LCD/keypad intefacing.

:slight_smile:

YAAAA thats it cr0sh you have understand me very well man!!!.

That is what i want to do. So its better with internet shield?

Do you know how to doo it? Or give me some tips?

btw thanks a lot men. I think its a wonderfull project to develop!

That is what i want to do. So its better with internet shield?

Well, that depends on a few things - your budget, and the amount of time you want to spend coding; an ethernet shield will most likely be more difficult to code for than a straight serial connection. However, a serial connection (via the USB cable) might be more difficult on the server end (it isn't as straightforward to do as you would think).

If you have an ethernet plug nearby, but your server is on the other side of the building, then that might be better? However, if your budget is tight, and you are going to put the PC in a locked cabinet, then maybe USB?

There's a lot of variables - some of which you haven't defined for us; so I really don't know which is necessarily best.

Do you know how to doo it? Or give me some tips?

To be honest, I've never done it, but the idea seems fairly easy. With the USB serial, you just need to set up communications between your server program and the Arduino over the serial port. This isn't as easy as you would think, but it can be done with some effort. For the server side of things, I would personally use Linux with a Perl or PHP program running as a CRON task or daemon that periodically polls the serial port, to see if there is a request waiting; if there is, then it would take the data, look it up in the MySQL DB, then based on the record data it returned, would send back a command to say "yes or no" to unlocking the door. The Arduino would then need to interpret that, and either open the doorstrike, or show an "error" message on the LCD (maybe with a buzzer or something?).

Its basically the same thing with an ethernet shield, except communications on the PC server side should be a little easier, but the comms on the Arduino side may become a little more difficult.

I actually have a project that I am working on that will do something similar - serial comms with the Arduino via PHP/Perl/Python; its a robot where a PC commands the Arduino (and gets telemetry data back for certain things) to tell the robot which wheels to move, which way to turn, control of servos for a pan/tilt system, and some other things (including a "heartbeat" so there is feedback in case the serial link is severed or rendered inoperable in some manner - the robot's weight will top 100 kg or more). I haven't gotten to this point yet (so many other things have gotten in the way, unfortunately - ugh).

I may not be able to help you, but there are tons of people out there and on this forum who have done serial communication and interfacing with PHP, MySQL, etc - with the Arduino. It's a fairly common task, so with some searching, you should be able to find enough information to hack your own system together.

Good luck - it sounds like a fun and useful project!

:slight_smile:

Maybe useful to people wondering about how PHP can be used for login... adaptable to what Fabio is doing, I think?

http://www.homeandlearn.co.uk/php/php4p1.html

i know how to doo it in php.
but i want to doo it with arduino...

Using an ethernet shield in the client mode one could have the button sequence entered on the keypad attached to the arduino sent to a server somewhere. The server would send back a yes/no type response to the arduino and the arduino would action the response.

ZOOMCAT this is exactly what I am trying to do, I have managed to get the Arduino connected to the PHP on another PC and compare the inputs to see if it is correct, then I am stuck at the point where I need the PHP to send a value I can use on my Arduino. Any ideas?

I did:

I have the arduino sending a GET to the PHP
The PHP checks that value against the database

I want to do:
The PHP to send a value I can use to the Arduino client

Sorry, but I've never used PHP so I can't help there.

I have the arduino sending a GET to the PHP

Can you post this code?

The PHP checks that value against the database

And this code?

Typically, when a GET request is made, the client expects an HTML page back. But, the server could send a text file or jpg file or zip file or an xml file instead.

PHP is capable of returning an HTML-based page, as well as simple text.

The Arduino then needs to check for some data coming back from the server, and extract the relevant data from the file.

I've done quite a bit with PHP (not recently, but I still remember a little) and MySQL, so it shouldn't be hard at all to make PHP reply with the appropriate information.

Hello,

I'm having a similar problem.

I also want to keep values logged in my house in a remote DB, so far mySQL is the choice (free). I haven't yet tried to use the arduino with ethernet shield to run a php script on a server. I used it as a server and it worked fine so it should be fairly simple (to be tested next month) to run a php script in a remote server.

My problem here is that I want to cut the "middle man", I already have an Ethernet shield, so why add a computer in the way? If I'm going to use the computer I might as well get an old computer with a serial or parallel port and do the entire thing there.

So my question is, can the Arduino connect to a remote mySQL DB and add the data directly to it? What would be involved?

So my question is, can the Arduino connect to a remote mySQL DB and add the data directly to it? What would be involved?

The Arduino can connect to a remote server, and run a script on that server.

If the server is also running MySQL, and the script has embedded SQL commands in it (like a PHP script could easily have), then the script that is executed could store data in the database on the remote server.

But, no the Arduino can not connect directly to a database. It doesn't speak the language.

My problem here is that I want to cut the "middle man", I already have an Ethernet shield, so why add a computer in the way?

If you know how to run a computer database program without the computer, then you are a very clever person.

"Cut the middle man" as in the computer between the arduino and the server...

I still don't know of ethereal DBs, but when I do, you'll be the first to know.