Introduction: Control Access of Arduino YÚN With MySQL, PHP5 and Python

By github.com/camilo-nunez/

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

For the purpose of making our project, we need to start with the part of the software, install some files on our Arduino opkg 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

Now We will establish the installation and configuration of MySQL Server, type the following commands in the console:
  1. opkg install libpthread libncurses libreadline mysql-server
  2. sed -i 's,^datadir.*,datadir         = /srv/mysql/,g' /etc/my.cnf
  3. sed -i 's,^tmpdir.*,tmpdir          = /tmp/,g' /etc/my.cnf
  4. mkdir -p /srv/mysql
  5. mysql_install_db –-force
  6. /etc/init.d/mysqld start
  7. /etc/init.d/mysqld enable
  8. mysqladmin -u root password 'tu-nueva-clave'
Once finished entering our codes and started the MySQL server , you should configure the database that will manipulate . But before starting to type the codes, we need to understand the fields that carry our table.
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:
  1. mysqladmin -u root -p create arduino
We ask for the password we entered earlier in the installation, we will submit it to complete the creation of the base. Finished all this, we enter the query to MySQL, you should type the following code in the console:
  1. mysql -root -p
Again we ask for the password, you must submit it again.

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:
  1. USE arduino
We believe the name table 'usuariosrfid' in the database will use for this project, type this code in the MySQL console:
  1. CREATE TABLE `usuariosrfid` (
  2.   `id` int(255) NOT NULL AUTO_INCREMENT,
  3.   `nombre` varchar(300) NOT NULL,
  4.   `apellido` varchar(300) NOT NULL,
  5.   `correo` varchar(300) NOT NULL,
  6.   `rfid` varchar(300) NOT NULL,
  7.   PRIMARY KEY (`id`)
  8.  ) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=0 ;
* Remember that when pressing the ENTER key at the end of the command line in the MySQL console, it will not run until you find the ';' Therefore, at the end of the code is ';'

To complete the installation and configuration of MySQL, we fill some test fields within our base. Type in the following lines:
  1. INSERT INTO `usuariosrfid` (`id`, `nombre`, `apellido`, `correo`, `rfid`) VALUES
  2.  (1, 'Pedro', 'Suarez', 'pedro.suarez@correo.com', '1234a-12345-b-123c'), 
  3.  (4, 'Matias', 'Lopez', 'matias.lopez@correos.com', '987a-9876b-987c');
Now continue with the creation of the table 'ControlUsuarios', which will house all RFID codes passing by the reader, this table consists of 3 fields, 'id', 'rfid', 'date'.
  • '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.
To create the table 'ControlUsuarios', we enter the following code into the MySQL console:
  1. CREATE TABLE `ControlUsuarios` (
  2. `id` int(255) NOT NULL AUTO_INCREMENT,
  3. `rfid` varchar(300) NOT NULL,
  4. `fecha` varchar(300) NOT NULL,
  5. PRIMARY KEY (`id`)
  6. ) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=0 ;
Finally type 'exit;' in the console to exit the MySQL Query, and return to SHH so we can start with PHP5.

Step 5: Installation of PHP5

Continue with the installation and configuration of PHP5. This installation is easier than MySQL, so it should not cost them anything.

First, install the opkg package from the Arduino Yun downloading and installing, so type in the SSH console of Arduino this:
  1. opkg install php5 php5-cgi
Once downloaded and PHP5 installed on our Arduino Yun, so we configured root file uHTTPd, the http server that brings Arduino default, I recommend you only use this http server because it is more versatile and accessible in configuration, not Apache or Lighttpd are more difficult to set up when you are new in this field.

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:
  1. vi /etc/config/uhttpd
Press 'i' to edit the file, then go to the line of code you have written '# list interpreter. "php = / usr / bin / php-cgi"'. You must delete the character '#' out at the beginning of the line, then press the escape (key 'ESC') key, once you are ready, you must type the command ': wq' to save the file and exit the.

You must restart the server uHTTPd, for this, you should type in the command console of SSH the following code:
  1. /etc/init.d/uhttpd restart

Step 6: Installation the Conector MySQL for PHP5 and Python

Proceed with the installation and configuration of the modules to connect the MySQL database with PHP and Python. Let's start with the PHP connector. Type the following code:
  1. opkg install php5-mod-mysql
  2. 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:
  1. opkg install python-mysql
With this last step, we will have our Arduino Yun ready for our project with the card reader ID-20 RFID 125khz. Finish this part with Feedback of what we have done:
  • 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

As mentioned earlier, our Python scripts functioned with the library or class 'MySQLdb', which is already installed previously.

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:
  1. import MySQLdb                                                                                                                                                                      
  2. import sys
Once imported these libraries or classes, we add variables to your code, which will be the connection data from our MySQL database, bone:
  1. host = "127.0.0.1" # corresponds to the address of our MySQL server.
  2. user = "your-seat" # is the user of our database.
  3. passw = "your-password" is for the user's password. base = "arduino" # This is the name of the database you use.
Now start with the base script codes:
  1. while True:                                                                                                                                                                         
  3.         db= MySQLdb.connect(host,user,passw,base)                                                                                                                                   
  4.         cur = db.cursor()                                                                                                                                                           
  5.         resultado = cur.execute("""SELECT * FROM usuariosrfid WHERE rfid LIKE %s ORDER BY id""",(sys.argv[1],))                                                                     
  6.         if (resultado == 1 ):                                                                                                                                                       
  7.                 print 1                                                                                                                                                             
  8.                 sys.exit(1)                                                                                                                                                         
  9.         else:                                                                                                                                                                       
  10.                 print 2                                                                                                                                                             
  11.                 sys.exit(1)
If we realize, in the judgment 'cur.execute result = ("" "SELECT * FROM WHERE rfid usuariosrfid LIKE% s ORDER BY id" "" (sys.argv [1]))' command to execute our query compare the variable '(sys.argv [1])' which is the code from the Arduino RFID card with all the field data 'rfid' table 'usuariosrfid', which, finding equality code from the arduino and the or codes stored in the database, will return 1, if this is not true and there is no equality between the code from the arduino and some of the base, we will return 2. These numbers will be received by the Arduino.

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

Then proceed with the files . ' Php' that will keep the folder from our server , to do this remember that it is necessary that these files once ready , are saved in the skit our project, which automatically generates the IDE Arduino , you also need to remember , although I anticipate this , that when we go up to our Arduino sketch Yun , we upload over Wi -Fi , so I leave it on the same page manual Arduino , www.arduino . cc / en / Guide / Arduino Yun # toc14 , which explains more about it , and how you should go prepared MicroSD card for this type of project where the files should be stored on the server.


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:
  1. <? php
  2. / / Data server and database
  3. $ server = "localhost";
  4. $ username = "your-seat";
  5. $ password = "password";
  6. $ database_name = "arduino";
These are the data which need to connect to our database in the arduino will be the same we use in our Python scripts.

Finish programming the connect statement, which used our script:
  1. $conexion=mysql_connect($server, $username, $password) or die("Problemas al tratar de establecer la conexion");
  2. $bd_sel=mysql_select_db($database_name) or die("Problemas al seleccionar la base de datos");
  3. ?>
The other files are attached.





Attachments

Step 10: Finally, the Program of the Arduino YÚN

We got to the main part of this tutorial, programming our Arduino Yun, no details will highlight all of the code as it is quite extensive, only mention the following important points:
  • 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.
The code is attached

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!

Attachments