Arduino Forum

Forum 2005-2010 (read only) => Software => Interfacing => Topic started by: fabiomig on Jul 30, 2010, 02:54 am

Title: Arduino + mysql database + control acess
Post by: fabiomig on Jul 30, 2010, 02:54 am
Hi guys im new here  ;)

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!
Title: Re: Arduino + mysql database + control acess
Post by: tkbyd on Jul 30, 2010, 01:07 pm
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.)
Title: Re: Arduino + mysql database + control acess
Post by: PaulS on Jul 30, 2010, 02:05 pm
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?
Title: Re: Arduino + mysql database + control acess
Post by: fabiomig on Jul 30, 2010, 03:39 pm
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.
Title: Re: Arduino + mysql database + control acess
Post by: tkbyd on Jul 30, 2010, 05:15 pm
If there's going to be a PC running, what is the role of the Arduino?
Title: Re: Arduino + mysql database + control acess
Post by: fabiomig on Jul 30, 2010, 07:18 pm
the arduino will be connected to pc to acess mysql database.
Title: Re: Arduino + mysql database + control acess
Post by: tkbyd on Jul 30, 2010, 08:11 pm
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?
Title: Re: Arduino + mysql database + control acess
Post by: keeper63 on Jul 30, 2010, 09:19 pm
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.

:)
Title: Re: Arduino + mysql database + control acess
Post by: fabiomig on Jul 31, 2010, 03:22 am
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!
Title: Re: Arduino + mysql database + control acess
Post by: keeper63 on Jul 31, 2010, 08:07 am
Quote
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.

Quote
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!

:)
Title: Re: Arduino + mysql database + control acess
Post by: tkbyd on Jul 31, 2010, 02:41 pm
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
Title: Re: Arduino + mysql database + control acess
Post by: fabiomig on Jul 31, 2010, 10:33 pm
i know how to doo it in php.
but i want to doo it with arduino...
Title: Re: Arduino + mysql database + control acess
Post by: zoomkat on Aug 01, 2010, 01:39 am
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.
Title: Re: Arduino + mysql database + control acess
Post by: efrain42 on Dec 27, 2010, 11:15 pm
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
Title: Re: Arduino + mysql database + control acess
Post by: zoomkat on Dec 27, 2010, 11:45 pm
Sorry, but I've never used PHP so I can't help there.
Title: Re: Arduino + mysql database + control acess
Post by: PaulS on Dec 28, 2010, 12:35 am
Quote
I have the arduino sending a GET to the PHP

Can you post this code?

Quote
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.
Title: Re: Arduino + mysql database + control acess
Post by: bubulindo on Jan 08, 2011, 02:25 am
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?
Title: Re: Arduino + mysql database + control acess
Post by: PaulS on Jan 08, 2011, 02:33 am
Quote
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.
Title: Re: Arduino + mysql database + control acess
Post by: zoomkat on Jan 08, 2011, 06:40 am
Quote
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.
Title: Re: Arduino + mysql database + control acess
Post by: bubulindo on Jan 08, 2011, 08:08 am
"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.
Title: Re: Arduino + mysql database + control acess
Post by: robtillaart on Jan 08, 2011, 07:12 pm
Quote
can the Arduino connect to a remote mySQL DB and add the data directly to it?


IIRC An MySQL server has a socket open TCP port 3306 and the login needs encryption to access it. This encryption is quite heavy and my initial thought was it is way too big for the Arduino.

So I stopped and installed - http://www.apachefriends.org/en/xampp.html and used php as middleman (xampp includes mySQL)

my 2 cnts
Title: Re: Arduino + mysql database + control acess
Post by: bubulindo on Jan 09, 2011, 03:43 am
I got a reply from another forum with this link:

http://forge.mysql.com/wiki/MySQL_Internals_ClientServer_Protocol#Organization
It may become useful for someone else.

From what I see, it is possible. I haven't looked at the encryption definition, but implementing all this is too much for 30k... if you want some relevant space left for the running software. I do believe that creating the code just to insert code might be feasible well within the program memory limit.

The other thing I need to check are the timeouts between server-client... after all, this is an 8 bit 16 Mhz machine. LOL