Introduction: Control Access of Arduino YÚN With MySQL, PHP5 and Python
Hello Friends!
Well, as you know, in September the new premiere shield of Arduino, Arduino YUN. This little friend has a Linux embedded system with which we can run anything you can think of (at least so far).
While there is very little information on this new board, with departure in the guide that provides the Arduino page (which will link later), plus a little knowledge of many Linux, can carry out a major project a Control Access.
This time I will explain how to do this project, but in a manner sufficient particular all through our Arduino. This works, thanks a database server MySQL which will settle in our YUN, this database will store two tables, one for users associated with a particular RFID and another table to keep records of all cards that have passed through our reader. While we use programming languages PHP and Python scripts to perform some basic and work with our data.
Moreover, the hardware overhead, we use a reader ID-20 next to a Serial LCD of 16 characters, plus an RGB LED which will be our informant states.
We may use this access control everything we can think of, modify, and add a relay or another idea. It can be used on anything where you have cards and want a record.
Recall that this document is free publication, commercial use and exempt modifications are not allowed. I hope it's a way more extensive path of Open Source electronics.
So we go to the next step, the parties need!
Step 1: The Parts
For do this project, we need to have:
* Arduino YUN https://www.sparkfun.com/products/12053 $71.95
* RFID Reader ID-20 (125 kHz) https://www.sparkfun.com/products/11828 $34.95
* RFID Reader Breakout https://www.sparkfun.com/products/8423 $0.95
* Basic 16x2 Character LCD 5V https://www.sparkfun.com/products/790 $16.95
* Serial Enabled LCD Backpack https://www.sparkfun.com/products/258 $16.95
* LED RGB 5mm https://www.sparkfun.com/products/105 $1.95
* Buzzer https://www.sparkfun.com/products/7950 $1.95
* Somes Header Pin and wire $5.00 aprox.
In all, if you buy at the store Sparkfun, everything will be near $150.
If you live in Latin America, I would recommend buying the parts in www.olimex.cl, is a very good Chilean electronics store.
Step 2: The Assembling
Few connections to do, laying down briefly explain.
For RGB LED, red LED pin must be on pin 9 of the Arduino, pin the green LED should go on Arduino pin 8 and pin the blue LED should be on pin 7 of the Arduino.
For the ID-20 you must connect the following pins as indicated in the table in respective order, pin reader to Arduino pin:
Pin ID-20 to pin Arduino
ID-20 \ Arduino
PIN 1 ----> GND
PIN 2 ----> 5V
PIN 7 ----> GND
PIN 9 ----> PIN 10
PIN 10 ----> BUZZER
PIN 11 ----> 5V
And finally, for the Serial LCD connector will only be necessary to 5v and GND pins of the Arduino, while the LCD Serial RX pin goes to pin 11 of the Arduino.
Step 3: Programing of the Arduino Yun
- MySQL Server
- PHP5
- MySQLdb for Python 2.7
- Mod of PHP5 to MySQL
Recall that by default in Bridge comes installed Python 2.7, so you never need to install any update for this.
Start logging SSH dare to enter our Arduino Yun, once you started them in, type the following command to update the list of applications opkg:
- opkg update
Step 4: Installation of MySQL
- opkg install libpthread libncurses libreadline mysql-server
- sed -i 's,^datadir.*,datadir = /srv/mysql/,g' /etc/my.cnf
- sed -i 's,^tmpdir.*,tmpdir = /tmp/,g' /etc/my.cnf
- mkdir -p /srv/mysql
- mysql_install_db –-force
- /etc/init.d/mysqld start
- /etc/init.d/mysqld enable
- mysqladmin -u root password 'tu-nueva-clave'
The board shall consist of 5 courses, 'id ', ' name', ' name' , ' email ', ' rfid ' to market some of them I will give a brief explanation of their use.
- 'id' : will the field or attribute int column that will tell us the number assigned to the User ID , this number is assigned by the same database and will be the way to index our records.
- 'nombre' : attribute column will be ' varchar ' may be indicated by the name with which our card user is identified.
- ' apellido' : attribute column will be ' varchar ' may be indicated by the last name with which our user is associated.
- ' correo' : is the attribute column ' varchar ' which will contain the e- mail the associated user.
- ' rfid' : is the attribute column ' varchar ' which will contain the code of the RFID card you use .
(I will use the variables in Spanish, because is my native language and i like c: )
Now we can configure our database without problem, therefore we create a ' arduino ' call MySQL. You must type the following code:
- mysqladmin -u root -p create arduino
- mysql -root -p
Once inside the MySQL console command, we pointer ('mysql>') should appear ready to type. The first thing we will do is move the base for 'arduino' data to work on it. This is done by typing the following command at the MySQL console:
- USE arduino
- CREATE TABLE `usuariosrfid` (
- `id` int(255) NOT NULL AUTO_INCREMENT,
- `nombre` varchar(300) NOT NULL,
- `apellido` varchar(300) NOT NULL,
- `correo` varchar(300) NOT NULL,
- `rfid` varchar(300) NOT NULL,
- PRIMARY KEY (`id`)
- ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=0 ;
To complete the installation and configuration of MySQL, we fill some test fields within our base. Type in the following lines:
- INSERT INTO `usuariosrfid` (`id`, `nombre`, `apellido`, `correo`, `rfid`) VALUES
- (1, 'Pedro', 'Suarez', 'pedro.suarez@correo.com', '1234a-12345-b-123c'),
- (4, 'Matias', 'Lopez', 'matias.lopez@correos.com', '987a-9876b-987c');
- 'id' is the field or attribute int column which will contain the id of each record for indexing.
- 'rfid' attribute is the column 'varchar' containing the RFID tag code was read by the reader.
- 'date' is the attribute column 'varchar' which will contain the date the card was read.
- CREATE TABLE `ControlUsuarios` (
- `id` int(255) NOT NULL AUTO_INCREMENT,
- `rfid` varchar(300) NOT NULL,
- `fecha` varchar(300) NOT NULL,
- PRIMARY KEY (`id`)
- ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=0 ;
Step 5: Installation of PHP5
First, install the opkg package from the Arduino Yun downloading and installing, so type in the SSH console of Arduino this:
- opkg install php5 php5-cgi
To configure this, use the file editor 'vi' SSH, for this you must have minimum knowledge to use this editor. Begin typing this code in the console to access the settings file uHTTPd:
- vi /etc/config/uhttpd
You must restart the server uHTTPd, for this, you should type in the command console of SSH the following code:
- /etc/init.d/uhttpd restart
Step 6: Installation the Conector MySQL for PHP5 and Python
- opkg install php5-mod-mysql
- sed -i 's,;extension=mysql.so,extension=mysql.so,g' /etc/php.ini
Once ready it will not need to restart the server uHTTPd, ready for immediate use.
Now continue with the connector for Python, for that, you must enter the following code:
- opkg install python-mysql
- We installed the MySQL server on our Arduino Yun, then configure it, ending with an insertion of test data.
- Install the PHP complement to our server.
- We finished with the installation and configuration of MySQL Connector for both PHP and Python.
Step 7: Codes
In this area, we discuss the programming codes to be used for this project. We leave with Python code, which is divided into two files: 'comprobar.py', which will connect to the database and seek Yun Arduino if the result is in it, and the file 'control. py ', responsible for recording any card that is read by the reader ID-20, whether or not in the database of registered users. Then continue with the description of the PHP files, are: 'consultaRelacion.php', 'consultaControl.php', 'index.php' 'modificar2.php', 'modificar.php', 'borrar.php', 'delete2 . php ',' guardar.php '' consulta.php ',' configuracion.php '. For these files redundaremos both because they are easy to understand. Finally finish with the code which will go up as our Arduino sketch.
Step 8: Files Python
Start by creating a file with '.py' called 'comprobar.py' in our editor, I recommend using these cases for programming in Python, the editor Sublime Text 3, which you can download on their website www.sublimetext.com .
Let's start by importing the library to connect to our MySQL database, the library 'sys' which will allow us to communicate with our Arduino Yun:
- import MySQLdb
- import sys
- host = "127.0.0.1" # corresponds to the address of our MySQL server.
- user = "your-seat" # is the user of our database.
- passw = "your-password" is for the user's password. base = "arduino" # This is the name of the database you use.
- while True:
- db= MySQLdb.connect(host,user,passw,base)
- cur = db.cursor()
- resultado = cur.execute("""SELECT * FROM usuariosrfid WHERE rfid LIKE %s ORDER BY id""",(sys.argv[1],))
- if (resultado == 1 ):
- print 1
- sys.exit(1)
- else:
- print 2
- sys.exit(1)
We continue with the next file 'control.py. This file works the same way as above, only keeping an aftertaste on a table, these records has saved shall be collected by the reader ID-20 connected to our Arduino Yun, so we can keep all registered users who use our RFID reader.
Attachments
Step 9: Files PHP
The PHP files will be 10 ' consultaRelacion.php ', ' consultaControl.php ', ' index.php' ' modificar2.php ', ' modificar.php ', ' borrar.php ', ' borrar2.php ', ' guardar . php ' ' consulta.php ',' configuracion.php ' , which will be hyperlinked to the other, to maintain a basic menu always running and accessible .
Just explain the file ' configuracion.php ' , which functions as a connector with our database . In our editor , we schedule the following code in the file:
- <? php
- / / Data server and database
- $ server = "localhost";
- $ username = "your-seat";
- $ password = "password";
- $ database_name = "arduino";
Finish programming the connect statement, which used our script:
- $conexion=mysql_connect($server, $username, $password) or die("Problemas al tratar de establecer la conexion");
- $bd_sel=mysql_select_db($database_name) or die("Problemas al seleccionar la base de datos");
- ?>
Attachments
Step 10: Finally, the Program of the Arduino YÚN
- The code consists of 6 main functions and 13 secondary functions, which are only a support for the use of the Serial LCD.
- Be imported only three classes, 'SoftwareSerial.h', with which we will input Serial Attached to connect with the ID-20 and Serial LCD, the main class 'Bridge.h' with which we will make the connection between Linux and ATMEGA32U4, and 'Process.h' class will serve for consultation processes in Linux.
- Define only three pins, which use others be free.
Attachments
Step 11: Happy New Year !
I continue this meticulously to serve you and help you get more information about the Arduino Yun.
Leave all files attached to the end.
For speakers of Spanish lenguage, I leave this tutorial but in Spanish with the necessary files.
I hope you have a happy new year, enjoy it and success this 2014!