Notes About Working with Various Arduino & Netduino Microcontroller Boards

Monday, October 28, 2013

Displaying Sqlite3 Data Using PHP on the Arduino Yun

I posted previously about using Sqlite3 as the database on an Arduino Yun for verifying RFID tag IDs and logging access. The example below shows how to use PHP to read from the access table and display the access data in a Web page.

See this post for information on installing PHP on the Arduino Yun.


Installing the PHP Sqlite3 Module


The first step is to install the PHP Sqlite3 module.  Run the following command at the Linux command line (logged in via SSH).

opkg install php5-mod-sqlite3

PHP Code


Use vim or nano at the command prompt to create the PHP file (access.php) in the /mnt/sd/arduino/www directory.

<?php
$db = new SQLite3('/mnt/sda1/rfid.db', 0666, $error)
        or die ($error);
$get_log_sql = <<<END_SQL
select a.date_time, c.name, c.card
from rfid_access a, rfid_card c
where a.card = c.card
order by date_time desc
END_SQL;
$result = $db->query($get_log_sql);
echo "<table cellpadding='5'>\n";
echo "<tr><th>Date & Time</th><th>Name</th><th>Card</th></tr>\n";
while ($row = $result->fetchArray()) {
        echo "<tr>";
        echo "<td>".$row['date_time']."</td>";
        echo "<td>".$row['name']."</td>";
        echo "<td>".$row['card']."</td>";
        echo "</tr>\n";
}
echo "</tr>\n";

?>

Access the page by going to http://<YourYunAddr>/sd/access.php.

Installing PHP on the Arduino Yun

Installing the CGI version of PHP5 on the Arduino Yun is quite simple.  I was able to get it installed in and running in a few minutes with the following steps:

  1. Use SSH to access the Yun's command Linux command line.
  2. Run opkg to install PHP:
    1. opkg update
    2. opkg install php5-cgi
  3. Run the following command to configure the PHP module for uhttp:uci add_list.  These commands are slightly modified from those given at wiki.openwrt.org/.
    1. uci set uhttpd.main.interpreter=".php=/usr/bin/php-cgi"
    2. uci set uhttpd.main.index_page="index.html index.htm default.html default.htm index.php"
    3. uci commit uhttpd
    4. sed -i 's,doc_root.*,doc_root = "",g' /etc/php.ini
    5. sed -i 's,;short_open_tag = Off,short_open_tag = On,g' /etc/php.ini
    6. Restart uhttpd with the command /etc/init.d/uhttpd restart
Assuming that you are using an SD card for your Web files, place your PHP files in the /mnt/sd/arduino/www directory.

Note (05/08/2014): After upgrading my OpenWrt-Yun image and after following the process described on the Arduino blog to use a micro-SD card in place of the Yun's internal memory, I find that the uhttpd Web server looks for its Web page files in the /www directory, not /mnt/sd/arduino/www.

Saturday, October 26, 2013

RFID Using the Innovations ID-20LA & Arduino Yún

On October 24, 2013, Zoe Romano posted on the Arduino Blog about using an RFID reader and an Arduino Yún.  I've posted before about using Innovations RFID readers with the Arduino (connected via the Sparkfun RFID Reader breakout board).  Since the Yún combines a compact Linux machine with the basic Arduino, it allows the inclusion of a small database to track cards and log access.

The stripped down example I present here uses an Innovations ID-20LA RFID module, the Sparkfun breakout board, and an Arduino Yún.  This example uses the light-weight Sqlite3 database, which can be installed on the Yún quite easily.  When an RFID card is in range of the reader, the card's code is checked agains a database table of valid card numbers.  If the card is in the DB table, access is granted.  To keep things simple for this example, messages are sent to the serial monitor rather than a physical access mechanism.

Software installation, Sqlite3 DB setup, and Python programming are done via SSH.  The Arduino sketch is uploaded and the serial monitor is accessed via the latest Arduino IDE.

Installing Sqlite


At the Yún's command prompt, run the following opkg commands to install Sqlite3 and the Python module for accessing it:

opkg install sqlite3-cli

opkg install python-sqlite3

I could not find these modules in the list of available modules in the browser-based software installation page for Yún administration, but they show up in the list of installed packages after running opkg from the command line.

Using the Sqlite3 command line interface at the command prompt, I created the database (rfid.db) and the tables to hold the valid card numbers (rfid_card) and successful access (rfid_access).  I won't go into the details here.  See the Sqlite3 documentation for more information.


Connections


Sparkfun RFID Board   Arduino Yún
VCC                   3.3V                          
GND                   GND
TX                    Digital 10 (using SoftwareSerial)

Arduino Code


The Arduino C sketch used the SoftwareSerial library for communication with the RFID module and the Bridge library for communication between the Arduino and Linux sides of the Yún.

The Arduino sketch reads the number from the RFID tag and sends it to the Python script via the Bridge library for verification and logging.

#include <Bridge.h>
#include <Process.h>
#include <SoftwareSerial.h>


SoftwareSerial rfidSerial(10, 11); // RX, TX (TX not used)
String card = "";

void setup()  
{
  Serial.begin(9600);
  rfidSerial.begin(9600);
  Bridge.begin();  
  while(!Serial);
}

void loop() 
{
  while(rfidSerial.available()) {
    char c = rfidSerial.read();
    if(c == '\n') {
      card.trim();
      // Remove 2 control chars at start of card data
      card = card.substring(2);
      if(card.length() == 12) {
        checkCard(card);
        break;
      }
      else {
        Serial.println("Error");
        card = "";
      }
    }
    else {
      card = card + c;
    }  
  }
}

void checkCard(String cardID) {
  Process p;
  String cmd = "/usr/bin/python";
  p.begin(cmd);
  // Adjust path to Python script as needed
  p.addParameter("/mnt/sda1/lookup_rfid.py");
  p.addParameter(cardID);
  p.run();
  while(p.available() > 0) {
    char result = p.read();
    if(result == '1') {
      Serial.println("OK");
      card = "";
      break;
    }
    else {
      Serial.println("Denied");
      card = "";
      break;
    }
  }
}

Python Code


The following code is saved in file called lookup_rfid.py.

import sys
import sqlite3

# Change path to DB file as required
conn = sqlite3.connect('/mnt/sda1/rfid.db')
cur = conn.cursor()
# Note space between ' and ''' in 2nd line    
lookup_sql = '''select count(*) from rfid_card  
where card = '%s' ''' % sys.argv[1]
result = cur.execute(lookup_sql)
data = cur.fetchone()           
# if 1, access allowed. Log access
if data[0] == 1:                                             
print 1                                                  
log_sql = '''insert into rfid_access(card, date_time)    
values('%s', date('now', 'localtime') || ' ' ||
time('now', 'localtime'))''' % sys.argv[1]
cur.execute(log_sql)
conn.commit()     
# if 0, access denied     
elif data[0] == 0: print 0
# Other errors
else: print 3
conn.close()

Note: For some reason, I always get an error when I scan the first card after starting the application, but subsequent scans work as expected.

Thursday, October 17, 2013

A Couple Notes about Working with the Arduino Yún Examples

I got an Arduino Yún from Adafruit a couple weeks ago but have been slow in getting started with it.  In trying to get the TermperatureWebPanel example to work, I ran into a couple issues.  These things are noted in the documentation at one place or another but I didn't get things right at first.

The TermperatureWebPanel example requires an SD card.  Be sure to create an arduino directory in the root of the SD card.  Under the arduino directory, create a www directory. Reboot the Yún after inserting the SD card.  The SD card should be linked automatically to /mnt/sd.

The directory for the TemperatureWebPanel example includes a www directory with the HTML and JavaScript files needed for the example.  The files in this directory will be uploaded to the correct location by the Arduino IDE (ver. 1.5.4 +) only when the upload is done via the wireless interface.  Uploading over a serial connection will not work.

If you are working on a desktop computer that cannot connect to the Yún wirelessly, you can use the ethernet interface to upload the sketches.  

I have been able to get the example to work when viewed in Chrome and Safari, but it does not work for me when accessed using Firefox or IE.