Project so far: I have a website running php where a user can input their RFID card number, debit card details and make payment via Stripe. Their RFID card number, name, and amount paid is stored in a local MySQL database. The database runs a trigger whenever there is a change that updates a table including the persons RFID number and the total amount they have paid.
When the person swipes their card on the Arduino it needs to send a query with this RFID number to the database and return the credit amount for that card and store it in a variable. The problem is, I'm having trouble sending the RFID card number(obtained from swiping the card) in a MySQL query(MySQL Connector library). Code I have is:
#include <SPI.h>
#include <MFRC522.h>
#include <Ethernet.h>
#include <MySQL_Connection.h>
#include <MySQL_Cursor.h>
#define SS_PIN 53 //rfid pins
#define RST_PIN 5
byte mac_addr[] = { 0xDE, 0xAD, 0xBE, 0xEF, 0xFE, 0xED };
IPAddress server_addr(192,168,8,181); // IP of the MySQL *server* here
char user[] = "aaron"; // MySQL user login username
char password[] = "mypassword"; // MySQL user login password
// query to grab credit amount attached to rfid card
const char QUERY_CREDIT[] = "SELECT amount FROM paypage.colate WHERE product = %d;";
char query[128];
EthernetClient client;
MySQL_Connection conn((Client *)&client);
MFRC522 mfrc522(SS_PIN, RST_PIN);
void setup()
{
// Set up the ethernet and serial.
Serial.begin(115200);
while (!Serial); // wait for serial port to connect
Ethernet.begin(mac_addr);
Serial.println("Connecting...");
if (conn.connect(server_addr, 3306, user, password)) {
delay(1000);
Serial.println("Connected");
}
else
Serial.println("Connection failed.");
SPI.begin();
mfrc522.PCD_Init();
Serial.print("Startup");
pinMode (2, OUTPUT); //Pin 2 gets defined as output (here we are going to connect the LED)
}
void loop()
{
if ( ! mfrc522.PICC_IsNewCardPresent())
{
return;
}
if ( ! mfrc522.PICC_ReadCardSerial())
{
return;
}
long code=0;
for (byte i = 0; i < mfrc522.uid.size; i++)
{
code=((code+mfrc522.uid.uidByte[i])*10);
}
Serial.print("The Card number is:");
Serial.println(code);
Serial.println("Obtaining amount data.");
Serial.println("\nRunning SELECT and printing results\n");
// Initiate the query class instance
MySQL_Cursor *cur_mem = new MySQL_Cursor(&conn);
sprintf(query, QUERY_CREDIT, code);
// Execute the query
cur_mem->execute(query);
// Fetch the columns and print them
column_names *cols = cur_mem->get_columns();
for (int f = 0; f < cols->num_fields; f++) {
Serial.print(cols->fields[f]->name);
if (f < cols->num_fields-1) {
Serial.print(", ");
}
}
Serial.println();
// Read the rows and print them
row_values *row = NULL;
do {
row = cur_mem->get_next_row();
if (row != NULL) {
for (int f = 0; f < cols->num_fields; f++) {
Serial.print(row->values[f]);
if (f < cols->num_fields-1) {
Serial.print(", ");
}
}
Serial.println();
}
} while (row != NULL);
// Deleting the cursor also frees up memory used
delete cur_mem;
} //End of the sketch
The serial monitor doesn't output the value from the database.