Blog 4 – Sending the UID

This time I’m working on sending the UID to a cloud MySQL database. This will be using the test environment that I setup in the last blog.
I have already created a program that will just pull the UID off a student ID card. Now I need to work out how to send it to a server and then the database. I know there are several ways to get the UID into the database. These include;

  • Using a Library on the Arduino that allows it to directly communicate with the database. It allows you to using SQL language to insert straight from the Arduino. I found there are a few downfalls to this method. The first big one is that the library is very big, therefore effectively limiting it to only run on the Arduino Mega. I am trying to avoid this as it would be cheaper to run this on smaller Arduino units, which will benefit this project if it was to be deployed. Another possible problem is not being able to manipulate the data before it reaches the database.
  • Using Bash. This is the Linux based programing language. I don’t know this language very well or much of how it works. This would possibly be better for a local deployment.
  • Using PHP. This the way I have originally intended to get the data into the database. This is done using a webserver with PHP code pages that can be called by the Arduino to do selected tasks. It has lots of features that can be implemented around security and ways to insert the data. Looking on the internet I found this was the most common way and seemed the easiest. I also know quite a bit of PHP. I think I will go with this method.

I looked online of other people using the PHP method to get a set of information to the internet. I found this forum post where a user wanted to send their power usage readings to a database where he could analyze and plan his power usage. He was using an Arduino that read the power meter and then would send the readings to a PHP server and then a MySQL database. He had uploaded a complete set of code that he was using the the forum post. This included the code for the Arduino, the PHP to insert the data into the database and a PHP script that would display the collect data on a page.
I used the Arduino code that he had written and modified it to work with the setup I had. Below is the modified code;

/*
Ethernet shield attached to pins 10, 11, 12, 13
*/

#include <SPI.h>
#include <Ethernet.h>
#include <MFRC522.h>

#define RST_PIN 5 // Configurable, see typical pin layout above
#define SS_PIN 53 // Configurable, see typical pin layout above

MFRC522 mfrc522(SS_PIN, RST_PIN); // Create MFRC522 instance.

int sendTime = 60000;
int bstate = 0;
String txdata;
String read_rfid;
String rfid_uid;

//Mac and ip address of php server
byte mac[] = { 0x00, 0xAB, 0xBA, 0xBC, 0xDD, 0x02 };
char server[] = "******************.ap-southeast-2.compute.amazonaws.com";

EthernetClient client;


void setup() {
 
 Serial.begin(9600); // Initialize serial communications with the PC
 while (!Serial); // Do nothing if no serial port is opened (added for Arduinos based on ATMEGA32U4)
 SPI.begin(); // Init SPI bus
 mfrc522.PCD_Init(); // Init MFRC522 card
 mfrc522.PCD_DumpVersionToSerial(); // Show details of PCD - MFRC522 Card Reader details
 Serial.println(F("Scan PICC to send UID"));
 rfid_uid="";

Ethernet.begin(mac);
 delay(1000);
 startEthernet();
}

//------------------------------------------------------

/*
 * Helper routine to dump a byte array as hex values to Serial.
 */
void dump_byte_array(byte *buffer, byte bufferSize) {
 read_rfid="";
 for (byte i = 0; i < bufferSize; i++) {
 read_rfid=read_rfid + String(buffer[i], HEX);
 }
}

//------------------------------------------------------

void loop() {
//Let Reader start
delay(200);

// Look for new cards
 if ( ! mfrc522.PICC_IsNewCardPresent())
 return;

// Select one of the cards
 if ( ! mfrc522.PICC_ReadCardSerial())
 return;
 
 dump_byte_array(mfrc522.uid.uidByte, mfrc522.uid.size);
 
 if ( read_rfid != rfid_uid) {
 if (read_rfid != rfid_uid) {
 Serial.println(read_rfid);
 txdata = read_rfid;
 rfid_uid = read_rfid;
 DataToMySQL();
 }
 }
}

//------------------------------------------------------

void DataToMySQL(){

bstate++;

EthernetClient client;

if (client.connect("************.ap-southeast-2.compute.amazonaws.com", 80))
{
 Serial.print("Connected to MySQL server. Sending UID...");
 Serial.println(txdata);

client.print(" POST /add_UID.php HTTP/1.1\n");
 client.print("Host: *************.ap-southeast-2.compute.amazonaws.com\n");
 client.print("Connection: close\n");
 client.print("Content-Type: application/x-www-form-urlencoded\n");
 client.print("Content-Length: ");
 client.print(txdata.length());
 client.print("\n\n");
 client.print(txdata);
 Serial.println("Successfull");
}

else{
 Serial.println("Connection failed");
 Serial.println();

}
}

//----------------------------------------------------
void startEthernet()
{
 client.stop();

Serial.println("Connecting Arduino to network...");

delay (1000);

//connect to network and obtain an IP address using DHCP
if (Ethernet.begin(mac) ==0)
{
 Serial.println("DHCP failed, reset Arduino to try again");
 Serial.println();
}
else
{
 Serial.println("Arduino connected to network using DHCP.");
 Serial.print("IP address is ");
 Serial.println(Ethernet.localIP());
 Serial.println();
}

delay(1000);
}

I then created a temporary table in my test database to test sending the UID too. From there I used the PHP files in the forum post and modified them to suit what the information being received and the inserting into the database.

I then uploaded the files to the correct places and tested the system. It didn’t work. For a start there was no information being uploaded into the database. I found that there was an issue with the Insert statement in the PHP. I rectified this. Now when I tested it the I found that there was a new line inserted into the database but no data in it. I played around with the way the Arduino posts the data to the PHP page but with no success. I spent several hours going back and forward trying different things. In the end I there was nothing being inserted into the database. I know that the PHP is executing the Insert statement but there is no UID being sent from the Arduino to the PHP. I stopped here.

I have had a think about it and decided to stop here. I need to do some more research. I also want to implement a more secure way of inserting the data into the database. I want to try using procedures loaded into the MySQL server that the PHP will call. This way I can test and see that they are functioning correctly. This will also be more useful in terms of securely uploading the data to the database.

Edit; I found what looks like the original that the user in the forum post used to create their code. This goes into more depth on how it works and some security issues. I will read over this to see what is done differently. Maybe it can help with the problems I encountered.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s