Pages

Thursday, 4 April 2013

Arduino, MYSQL and PHP - Part 3


Final installment of the Mysql, PHP and Arduino trilogy.

But, far from the end of the subject. This completes the description of a simple PHP script and Arduino sketch that allows a user to add RFID unique ID's to a database. To make a full card reader system we still have a few steps to go.
  • A sketch that sends the uid to a script for access or start/finish time stamps.
  • A PHP script that will work with the above sketch.
  • A CRUD (create, update, update & delete) interface for the database that will allow us to manage adding employees to the database and associating them with a RFID card ( actually working and has been is use for 4 months now)
  • And of course the hardware
 As you may have guessed by now, I like thinking of projects at the system level.  Many books have been written on all three of these technologies and I am certainly not expert in any, it's the journey of making and learning .
For me the joy is in taking functional blocks and putting them together to do something.
The way I do something like this is to look at other peoples work ,  pull it apart , see what makes it go and best of all modify it to fit an idea in your mind.

That is the motivation behind this blog , to give back a little to the hacker/maker community. ( The other is it makes me document my projects before I forget what or why I have done something)
All that was leading up to a thank you and some links.
Thanks to Lady Ada for some excellent hardware and supporting libraries. Having building blocks that don’t need  debugging is a real joy. The two blocks in these page are the Oled library and the NFClibrary. Much of the following code is based on the examples in the excellent Adafruit NFC library.

On to the core snippets of code that make this work .

This first procedure simply takes the RFID UID in as thisData and uses it in a call the the PHP script on the server.
Within the procedure I call  readPage(), below which handles the response from the server and displays the result on the oled display.
All of this is quite straightforward, but the power of the combination of the three technologies
(Mysql, PHP and Arduino) are  are really highlighted with these examples. We can use the techniques in these last three blog entries to communicate bi-directionally between the arduino and a server ( and yes a Raspberry Pi is a server & I have installed apache, mysql and PHP and tried this - a fully functional web enabled that only consumes 5 watts ! another story)
Some uses that spring to mind are logging of power generation and or consumption, weather stations, pool chemical monitoring, bi-directional control via a web interface............

The following two Arduino  snippets are really where the action takes place between the Arduino and the PHP server.
Both are quite straightforward,

void sendData(String  thisData,  String InOut)
{
    if (client.connect(DBserver, 80))
    {
        delay(100);
        // format and  send the HTTP GET request:
        client.print("GET /idreader/addcard.php?");
        // client.print(InOut);
        client.print("&cardid=");
        client.print(thisData);
        client.println(" HTTP/1.1");                 //
        client.println("Host: localhost");          //run the php script on the server
        client.println("Accept: text/html");        //
        client.println("Connection: close");        //
        client.println();                           //
        client.println();
        //  client.println();
        Serial.println();
        Serial.println("data sent");
        //Connected - Read the page
        readPage();
        //go and read the output
        // if there are incoming bytes available
        // from the server, read them and print them:
        Serial.println(inString);
        lcd.setCursor(0, 1);
        lcd.print(inString);

        beep (200);
        delay (2000);
        beep (100);
        delay (1000);
        lcd.setCursor(5, 0);

        client.flush();
        client.stop();
        client.flush();  // close the connection network connection
    }


    else
    {
        // if you couldn't make a connection to the database server:

        lcd.clear();
        lcd.setCursor(2, 1);
        lcd.print(" send fail");

        // Serial.println("connection failed");
        beep (3000); // a long obnoxious beep to flag an error
        delay (1000);

        client.flush();
        client.stop();
        client.flush();
        lcd.clear();
        lcd.setCursor(5, 0);
        lcd.print("Ready ");
    }





}



The other important function is here. We are looking for strings being returned from the server delimited by <' and '>' and displaying them on the Oled.


 

String readPage()
{
    //read the page returned py the PHP script we called
    // and capture & everything between '<' and '>' to display on the OLED
    stringPos = 0;
    memset( &inString, 0, 32 ); //clear inString memory

    while (true)
    {

        if (client.available())
        {
            while (client.connected())
            {
                char c = client.read();
                Serial.print(c);
                // How long to wait for it all to arrive?
                if (c == '<' )   //'<' is our begining character
                {
                    startRead = true; //Ready to start reading the part
                }
                else if (startRead)
                {

                    if (c != '>') //'>' is our ending character
                    {
                        inString[stringPos] = c;
                        stringPos ++;
                        // Serial.print(c);
                    }
                    else
                    {
                        //got what we need here! We can disconnect now
                        startRead = false;
                        client.stop();
                        client.flush();
                        Serial.println("disconnecting.");
                        return inString;

                    }
                }
            }
        }
    }


}







The full sketch can be downloaded from here Arduino Sketch to communicate with php script

Have fun

Friday, 29 March 2013

Arduino, MYSQL and PHP - Part 2

Storing Arduino acquired data in MySQL Part 2


Now we have created the database, the next step is to insert some data. This post concentrates on one of the PHP scripts the Arduino will call to insert information.

 The script is a house keeping function to enroll an RFID card into the data base. Thisis intended to be used by the administrator of the 'bundy' clock / access log to add RFID tags into the system and/or read a tag and find to whom it is associated.

 A summary of operation is;

  • It accepts the ID of the card passed by the Arduino, first trying to querying the database using the cardid to find the associated employee name. If there is an employee name associated with the card it will be returned in a way that the Arduino sketch , (to be posted next) can handle and exits 
  • If there is no associated employee the query will return zero rows found, this being the case the script will try to insert the cardid into the database. This is where the structure of the database previously posted is critical to this script. The cardid in the database is a primary key - a unique value.
  • If the cardid already exists it will cause the query to fail, we then send the "card not allocated" message back to the Arduino to indicate the card is known but not tied to an employee and exit
  •  The other case is if the card is not in the database it is inserted and we send "new card added" message back to the Arduino.
 I have added quite a lot of commenting to script so it can be pulled apart, modified and improved as wished.
The first step is to create the file we’ll connect with. We will call the file ‘connect.php’

<?php
// FileName="connect.php" 
// IMPORTANT substitue your local setting for locahost, user and password

$hostname = "localhost";
$database = "timeclock";
$username = "root";
$password = "";
?>
The second step is to create the file that the arduino will call. We will call the file 'addcard.php.To separate this scripts from other files I will be creating later I store them in a folder named idreader in the web root of the server, in this case the default htdocs folder.
Once you have these two files on your server you can test your handy work by point a web browser at the your server and the following url ;http://localhost/idreader/addcard1.php?&cardid=1234

This is assuming the server is on your PC, if not substitute local host for the hostame or IP address.

The first time you visit this address < new card added>  will appear in your browser, any subsequent visit will give the message << card not allocated>>  changing the number is the equivalent of using an different id card. 
<?php
// FileName="addcard.php"
//Include the connect.php file this file contains the information
// needed to connect to you database.
include('connect.php');
// time zone set to ensure the correct time stamp is added  to the database with each rfid store event.

date_default_timezone_set('Australia/Sydney');


//get the card id comming in from the arduino
$card = $_GET['cardid'];


// setup the conection to the database server,
//if the attempt fails exit and send the message back to the arduino
$connect = mysql_connect($hostname, $username, $password)
    or die( '<'."can't connect to DB" .'>'. mysql_error());

// setup the conection to the database
//if the attempt fails exit and send the mesage back to the arduino
$bool = mysql_select_db($database, $connect);
if ($bool === False){
    echo "< DB Error >";
}
 
// using the cardid search the database to
//see if it is associated with an employee.
   

$emp_search_result =  mysql_query("SELECT * from employees where cardid =  ('".$card."')");

if (mysql_num_rows($returned ) == 0) {
//if the number of row returned from the search is zero,
//there is no employee associated with that card
//If no employee, lets see if the card is in the database
//To do this we will try to add the card, as the cardid is a primary
//key if we try to add it twice the following will fail.

    $query = "INSERT INTO cards (cardid) VALUES ('" .$card. "')";
    $result = mysql_query($query) or die( '<'.'< card not allocated>'.'>' );

// if the cardid exists the "not allocated" message is sent back to the Arduino
// otherwise it is added to the database and the new card message is sent
    
     echo '<'." new card added".'>';


    } else


     {
// this section executed if $emp_search_result in not
//zero i.e there is an employee associated with the card.
// now we handle this caseby sending the employee name back to the Arduino

//The name is sent back as first initial only
//and  surname as I only use a 2x16 character display

        while ($row = mysql_fetch_assoc($emp_search_result  )) {
        echo '<'.$row['firstname'][0]. " ". $row['surname'] .'>';}
      }

  mysql_close($connect);
?>

Tuesday, 26 March 2013

Arduino, MYSQL and PHP Part 1

Storing ardino acquired data in MySQL Part 1


This post has been a longtime coming.Getting Alex Gorbatchev's fantastic Syntaxhighlighter working on a blogger dynamic template has been " interesting", thank you Ana Sayfa for the solution 

Now, hopefully the following snippets of code will help someone else.

To me, the Arduino seems to be an ideal way of acquiring data for small projects. Easy to hack together a sensor of some kind and with boards like Freetronics Etherten and Ethermega, easy to interface to a network.
There are loads of other ways of getting data to and from an Arduino Xbee, NRF24L01, bluetooth, infrared
etc but for this project Ethernet was the only logical choice.
While my posts for the near future are about a "bundy"timeclock/access log, the following structure can easily be modified to log temperature, humidity,swimming pool Ph in fact pretty much anything your obsessive compulsive heart desires. Personally mine will be a swimming pool controler/log.

As I have mentioned the for Arduino platform that forms the basis of this project, I choose the Etherten/Ethermega.  They simply had nearly all the peripherals I needed in one board including a flexible POE (power over Ethernet) solution. I only have to have one cable run! 
The board was changed to the Ethermega  from the Etherten in the late stages of the project simply because the code had grown to large for an ATMega328

On to storing the data. 

I am using  Apache, PHP and Mysql as the server solution. If you want to try this code Bitami have some nice ,easily installed WAMP ( windows)  and MAMP (mac) stacks and you can have a test server up in minutes with very little pain.

Firstly you will need a database. The structure for this project is very simple as shown below.



A quick script for creating the schema, is shown below.

Firstly, using the phpMyAdmin tool or other database admin tool ( command line if you are more hardcore than me) create as database


Now we need to create the database tables


Select the database and the SQL tab in phpMyAdmin, then paste the code below into the window (1) click go (2).

Congratulations you now have a test database ready to receive data.
In the following post I will cover the server side PHP scripts and the code the Arduino uses to communicate with the server.



 
 CREATE TABLE `cardevents` (

`deptcode` tinytext NULL,

`cardid` varchar(16) NULL DEFAULT NULL,

`event` int(32) NOT NULL AUTO_INCREMENT,

`servertime` timestamp NULL DEFAULT CURRENT_TIMESTAMP,

PRIMARY KEY (`event`) ,

INDEX `fk_accessevent_card_1` (`cardid`)

)

ENGINE=InnoDB

AUTO_INCREMENT=2416
;



CREATE TABLE `cards` (

`cardid` varchar(8) NOT NULL,

`allocated` tinyint(4) NOT NULL DEFAULT 0,

`start` datetime NULL DEFAULT NULL,

`end` datetime NULL DEFAULT NULL,

PRIMARY KEY (`cardid`) ,

UNIQUE INDEX `carddex` (`cardid`)

)

ENGINE=InnoDB
;



CREATE TABLE `employees` (

`employeenum` bigint(9) NOT NULL,

`firstname` char(40) NOT NULL,

`surname` char(40) NOT NULL,

`cardid` varchar(16) NULL DEFAULT NULL,

`precedaid` int(6) NOT NULL AUTO_INCREMENT,

PRIMARY KEY (`precedaid`) 

)

ENGINE=InnoDB

AUTO_INCREMENT=3128
;





ALTER TABLE `cardevents` ADD CONSTRAINT `cardevents_ibfk_1` FOREIGN KEY (`cardid`) REFERENCES `cards` (`cardid`);


Wednesday, 13 March 2013

Arduino Motherboard


The Arduino Motherboard ?

There seems to be a gap in the Arduino world – the products to finish a project to be presentable, something that you would be happy to hang on a wall.
I know the Arduino is a development system not intended to be a final product, but how many times have you made something you are really proud of for it's functionality but looks like pile of wire and circuit boards?

I needed to do a batch of about 30 NFC Card readers.  At this volume any of the mechanical aspects of a project get tiresome. I mean, where is the fun in cutting a square hole in a case? Cutting it so it actually looks neat is another problem entirely.
So the process of building this NFC card reader has been more of a “design for manufacture” learning experience than actually circuit design or programming.
Cutting holes for 16x2 displays, then there was the issue of the cutouts for Ethernet and power. This didn't exactly thrill me.
I started looking for a case with a nice neat cutout for the display; I posted questions on forums for suggestions, I Googled.  For hours and hours.

There is just not much out there. None of the regular suppliers I use Digikey RS, Element14, Jaycar etc had what I wanted. Finally I found this case as Seeed studio
Nice case with a tinted window for the display, perfect for a 16x2 Oled display.




So, version 2 of the NFC card reader was born.

Summary of the outcome - a partial success. It looked OK and would be fine for a single project, however;

The case was a little too small, the windows for the display still need to be enlarged, more cutting.
The standoffs for the display didn't match so I needed to glue them in, as with the PCB stand offs.
I could just fit everything into the case but it was tight – making assembly difficult.

On to version 3.

I found a larger version of the case; unfortunately I could only source it from wholesaler in China.
As it was going to this trouble I decided to go the whole way.


I designed a mounting board for the OLED so it was simply a case of soldering the display and 4 screws to mount in the case – no gluing or drilling for stand offs.

Next was the main system board. This is an inverted shield- a motherboard?  I needed to find a way to fit the Arduino. I have now moved to a Arduino Mega256 as I need the memory for the application and a shield.
I had seen side by side shields around, that then the thought occurred. Is there any reason the Arduino needs to be connector side up? – Not really. So the inverted shield arrived, nee motherboard


I designed a shield that holds the Arduino mega, fits into the case I had selected and had a connectors  for another standard shield  that I could plug the NFC card into and of course the display.
Along the way I also added a few extras I needed

  • A hardware reset for the Arduino – a 555 monostable triggered by an output pin so I could reboot remotely when required, particullary important for network settings.
  • A “real-world” MAC address and 2k of eeprom , several of the card readers need to be on the same sub-net. I didn't want to have to modify the firmware for each and the microchip 24AA025E48 is only about  50 cents from element14
  • An optional battery backed realtime clock.
  • An optional POE (power over ethernet) module 





I will put all of the PCB designs on the blog shortly.

Monday, 11 March 2013

Soldering, no blogging

Soldering a batch of version 3 card readers and display boards.