Hello everyone I am new here my English level is not very good but I will try to explain myself.
i have 2 arduino codes working, and i wanted to make an access control system but i cant get it to work.
when I pass my badge I wanted it to read in the mysql database to know if the badge is valid
Can someone help me assemble the 2 codes?
I am using an arduino uno with ethernet shild
Thank you
access control with mysql
#include <Ethernet.h>
#include <MySQL_Connection.h>
#include <MySQL_Cursor.h>
byte mac_addr[] = { 0xDE, 0xAD, 0xBE, 0xEF, 0xFE, 0xED };
IPAddress server_addr(10,0,1,88); // IP of the MySQL *server* here
char user[] = "root"; // MySQL user login username
char password[] = "pass1234"; // MySQL user login password
// Sample query
char query[] = "SELECT * FROM arduino.teste WHERE porte = 'P3' ";
EthernetClient client;
MySQL_Connection conn((Client *)&client);
void setup() {
Serial.begin(9600);
while (!Serial); // wait for serial port to connect
Ethernet.begin(mac_addr);
Serial.println("Connection en cour...");
if (conn.connect(server_addr, 3306, user, password)) {
delay(1000);
}
else
Serial.println("Impossible de se connecter .");
}
void loop() {
delay(2000);
Serial.println("\nExécution de SELECT et impression des résultats\n");
// Initier l'instance de classe de requête
MySQL_Cursor *cur_mem = new MySQL_Cursor(&conn);
// Execute the query
cur_mem->execute(query);
// Récupérez les colonnes et imprimez-les
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();
// Lire les lignes et les imprimer
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);
// La suppression du curseur libère également de la mémoire utilisée
delete cur_mem;
}
/////////////////////////////////////////////////////////////////////////////////////////////////////
volatile unsigned long tagID = 0;
volatile unsigned long lastBitArrivalTime;
volatile int bitCount = 0;
void ISRone(void)
{
lastBitArrivalTime = millis();
bitCount++;
tagID <<= 1;
tagID |= 1;
}
void ISRzero(void)
{
lastBitArrivalTime = millis();
bitCount++;
tagID <<= 1;
}
void setup()
{
Serial.begin(9600);
pinMode(2, INPUT);
digitalWrite(2, HIGH); // Enable pull-up resistor
attachInterrupt(0, ISRzero, FALLING);
pinMode(3, INPUT);
digitalWrite(3, HIGH); // Enable pull-up resistor
attachInterrupt(1, ISRone, FALLING);
tagID = 0;
bitCount = 0;
}
void loop()
{
// See if it has been more than 1/4 second since the last bit arrived
if(bitCount > 0 && millis() - lastBitArrivalTime > 250){
Serial.print(bitCount, DEC);
Serial.print(" bits: ");
Serial.println(tagID);
tagID = 0;
bitCount = 0;
}
}
Well combine the setups into one, and put the database query into loop at the point you print out the ID currently? You'll need to use sprintf or similar to construct the query string with the badge ID in it.
What SQL query do you need to match a badge id? If you know this,
you simply build it up using sprintf or similar, and run the query and
read the result...
in fact I got a script which serves to send the values of a temperature sensor to mysql and I tried to adapt it to but needs but I can't happen there is something that I can't send my tagID on mysql
You need to retrieve the result of a query to check if the database has an entry. Have you looked at the documentation and examples for the MySQL_Cursor library?
yes I took the examples of mysql and I can read in mysql but after to make it work with my tagID I can not
#include <Ethernet.h>
#include <MySQL_Connection.h>
#include <MySQL_Cursor.h>
byte mac_addr[] = { 0xDE, 0xAD, 0xBE, 0xEF, 0xFE, 0xED };
IPAddress server_addr(10,0,1,35); // IP of the MySQL *server* here
char user[] = "root"; // MySQL user login username
char password[] = "secret"; // MySQL user login password
// Sample query
char query[] = "SELECT population FROM world.city WHERE name = 'New York'";
EthernetClient client;
MySQL_Connection conn((Client *)&client);
// Create an instance of the cursor passing in the connection
MySQL_Cursor cur = MySQL_Cursor(&conn);
void setup() {
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);
}
else
Serial.println("Connection failed.");
}
void loop() {
row_values *row = NULL;
long head_count = 0;
delay(1000);
Serial.println("1) Demonstrating using a cursor dynamically allocated.");
// Initiate the query class instance
MySQL_Cursor *cur_mem = new MySQL_Cursor(&conn);
// Execute the query
cur_mem->execute(query);
// Fetch the columns (required) but we don't use them.
column_names *columns = cur_mem->get_columns();
// Read the row (we are only expecting the one)
do {
row = cur_mem->get_next_row();
if (row != NULL) {
head_count = atol(row->values[0]);
}
} while (row != NULL);
// Deleting the cursor also frees up memory used
delete cur_mem;
// Show the result
Serial.print(" NYC pop = ");
Serial.println(head_count);
delay(500);
Serial.println("2) Demonstrating using a local, global cursor.");
// Execute the query
cur.execute(query);
// Fetch the columns (required) but we don't use them.
cur.get_columns();
// Read the row (we are only expecting the one)
do {
row = cur.get_next_row();
if (row != NULL) {
head_count = atol(row->values[0]);
}
} while (row != NULL);
// Now we close the cursor to free any memory
cur.close();
// Show the result but this time do some math on it
Serial.print(" NYC pop = ");
Serial.println(head_count);
Serial.print(" NYC pop increased by 12 = ");
Serial.println(head_count+12);
}