Posting data to a database using Arduino

Hi everyone,

I have had my Arduino Uno for some months now and I am currently developing a ‘credit card’ payment system using RFID tags.

I am using an LCD screen, a keypad, the RFID sensor, and a buzzer and the program is working really well. Each card corresponds to a specific serial number and a user can add money to his account or buy items.

Now I want to post the money left in the account to a database every time a user makes a purchase. I then want to access the data using SQL and display on a website.

Would this be possible using ESP8266? I am not quite sure how you are supposed to send data to a database…

Kind regards and thank you in advance :slight_smile:

p.s. here is the code for my current program which stores the data locally.

#include <LiquidCrystal_I2C.h>
#include <Wire.h>
#include <SPI.h>
#include <MFRC522.h>
#include <Keypad.h>

const byte ROWS = 4; //four rows
const byte COLS = 4; //three columns
char keys[ROWS][COLS] = {
    {'1','2','3','A'},
    {'4','5','6','B'},
    {'7','8','9','C'},
    {'*','0','#','D'}
};
float nums[5] ={};
byte rowPins[ROWS] = {8, 7, 6, 5}; //connect to the row pinouts of the keypad
byte colPins[COLS] = {A3, A2, A0, A1}; //connect to the column pinouts of the keypad
int decimal =0;
LiquidCrystal_I2C lcd = LiquidCrystal_I2C(0x27, 16, 2); 
Keypad keypad = Keypad( makeKeymap(keys), rowPins, colPins, ROWS, COLS );
byte ledPin = 13; 
#define RST_PIN         9          // Configurable, see typical pin layout above
#define SS_PIN          10         // Configurable, see typical pin layout above
float price = 0;
char MoneyLeft = 10;
MFRC522 mfrc522(SS_PIN, RST_PIN); 
String value = "A9 A4 DE 8B";// Create MFRC522 instance
float cost = 0;
int c = 0;
boolean add =false;
int rs = 7;//lcd screen
int en=8;
int d4 = 3;
int d5= 4;
int d6= 5;
int d7 = 6;//lcd screen

int buzzPin = 2;
int buzztime = 500;
int t= 0;
float addition = 0;
const int buzzer =2;
boolean entered[4] = {};
String thirdscan;
String lastscan;


String serialnumbers[] = {"A9 A4 DE 8B", "0A FA 82 16"};
String names[] = {"Alex", "Marta"};
float account[] = {10, 20};
bool schoolLunch[] = {true, false};


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();   
 lcd.init();
  lcd.backlight();// Init SPI bus
  mfrc522.PCD_Init();   // Init MFRC522
  mfrc522.PCD_DumpVersionToSerial();  // Show details of PCD - MFRC522 Card Reader details
  Serial.println(F("Scan PICC to see UID, SAK, type, and data blocks..."));
 
 pinMode(buzzer, OUTPUT);
 
}

void loop() {
  // Reset the loop if no new card present on the sensor/reader. This saves the entire process when idle.
lcd.setCursor(0,0);


  // Look for new cards
  if ( ! mfrc522.PICC_IsNewCardPresent()) 
  {
    return;
  }
  // Select one of the cards
  if ( ! mfrc522.PICC_ReadCardSerial()) 
  {
    return;
  }
  //Show UID on serial monitor
  Serial.print("UID tag :");
  String content= "";
  byte letter;
  
  for (byte i = 0; i < mfrc522.uid.size; i++) 
  {
     Serial.print(mfrc522.uid.uidByte[i] < 0x10 ? " 0" : " ");
     Serial.print(mfrc522.uid.uidByte[i], HEX);
     content.concat(String(mfrc522.uid.uidByte[i] < 0x10 ? " 0" : " "));
     content.concat(String(mfrc522.uid.uidByte[i], HEX));
  }
  Serial.println();


  content.toUpperCase();
  for (c = 0; c <= 1; c++){
       if (content.substring(1) == serialnumbers[c]) //change here the UID of the card/cards that you want to give access
  {

           
        tone(buzzer, 1600);
        delay(100);// ...for 1 sec
        noTone(buzzer);
        lcd.clear();
        if (lastscan != content.substring(1)){
          lcd.print(names[c]);
          lcd.print("'s account:");
          lcd.setCursor(0,1);
          lcd.print(account[c]);
          delay(1500);
          lcd.clear();
          lcd.print("Scan again to");
          lcd.setCursor(0,1);
          lcd.print("buy something...");
          
          }
        else{  
          lcd.clear();
          delay(50);
     Serial.print("How much does this cost");
       lcd.print("Item Cost: ");
       lcd.setCursor(0,1);
int c=0;
    int key=0;
   float price = 0;
   int decimal = 0;
   int z=1;
   boolean add=false;
   for (z=1;z<=4;z++){
    nums[z] = 0;
    entered[z] = false;
   }
  for (int x=1; x<=4; x++){
    int key = keypad.getKey();
    
    while(key == NO_KEY) {
      key = keypad.getKey();
    }
    if (key >= 48 and key<=57){
      key = key-48;
      nums[x] = key;
      entered[x] = true;
    }
    else{
      if (key==42){
        decimal =x;
      }
      if (key==65){
        add=true;
        break;
      }
      if (key==35){
        break;
      }
     }
      
 if (x-decimal==1 and decimal > 0){
  nums[x] = nums[x]/10  ;
 }
 if (x-decimal==2 and decimal > 0){
  nums[x] = nums[x]/100  ;
 }
 if (decimal == 0 and x ==2){
  nums[1] = nums[1]*10;
 }
if (entered[x]){
  Serial.print(key);
  
  
  
}
else{
  if (decimal==x){
    
    
  }
}

  }
  
   
 int x=1;
   for (x=1;x<=4;x++){
    price += nums[x];
   }
  Serial.println();  
if (add==false){
  lcd.setCursor(0,1);
  lcd.print(price);
  Serial.println(price);
}
        if (add==true) {
          lcd.clear();
          lcd.print ("How much do");
          lcd.setCursor(0,1);
          lcd.print("you want to add?");
          
          for (int x=1; x<=4; x++){
    int key = keypad.getKey();
    while(key == NO_KEY) {
      key = keypad.getKey();
    }
    if (key >= 48 and key<=57){
      key = key-48;
      nums[x] = key;
      entered[x] = true;
    }
    else{
      if (key==42){
        decimal =x;
      }
      if (key==65){
        add=true;
        break;
      }
      if (key==35){
        break;
      }
     }
      
 if (x-decimal==1 and decimal > 0){
  nums[x] = nums[x]/10  ;
 }
 if (x-decimal==2 and decimal > 0){
  nums[x] = nums[x]/100  ;
 }
 if (decimal == 0 and x ==2){
  nums[1] = nums[1]*10;
 }
if (entered[x]){
  Serial.print(key);
  lcd.print(key);
  
}
else{
  if (decimal==x){
    lcd.print(".");
  }
}

  }
  if (decimal==3){
    nums[1] = nums[1]*10;
  }

 int x =1;
   for (x=1;x<=4;x++){
    addition += nums[x];
   }
          lcd.clear();
          lcd.print("Money added:");
          lcd.setCursor(0,1);
          lcd.print(addition);
          account[c] += addition;
          delay(1500);
          lcd.clear();
          lcd.print("Money in account:");
          lcd.setCursor(0,1);
          Serial.println(account[c]);
          lcd.print(account[c]);
          delay(1500);
          lcd.clear();
          lcd.setCursor(4,0);
    lcd.print("Success!");
  
          addition = 0;
        }
      else{
        
        delay(1500);
        lcd.clear();
      float money = account[c];
      if (money - price >= 0){
      float (money -= price);
      account[c] = (float)money;
    Serial.println();
    Serial.println(money);
    lcd.print("Money left: ");
    lcd.setCursor(0,1);
    lcd.print(money);
    delay(1500);
    lcd.clear();
    lcd.setCursor(4,0);
    lcd.print("Success!");
    
   }
    else{
      lcd.print("Not enough");
      lcd.setCursor(0,1);
      lcd.print("money in account");
    }
        }
        }
    
    
  }
  }
  thirdscan = lastscan;
  lastscan = content.substring(1);
  content.substring(1) ="";

In general to get data into a database you need to have a program "listening" for the Arduino which can receive the data and then save it to the database.

You could set up the database on a laptop (or a Raspberry Pi). You could connect to it using WiFi if you want a wireless connection (or using Bluetooth) or you could simply connect with the regular USB cable if wireless is not needed.

Do you you know how to write a program to save data in and retrieve data from an SQL database?

If you just want a simple database I suggest using SQLite which contains everything in a single database file

...R

yes, you can. You can connect directly ESP8266 to the database. (there is library for MySQL)
However, that is not a good way because of security. Exposing database to the outside is dangerous.
There is a better way . Arduino makes HTTPS request to the webserver, the Web server will interact with the database

Robin2:
In general to get data into a database you need to have a program “listening” for the Arduino which can receive the data and then save it to the database.

You could set up the database on a laptop (or a Raspberry Pi). You could connect to it using WiFi if you want a wireless connection (or using Bluetooth) or you could simply connect with the regular USB cable if wireless is not needed.

Do you you know how to write a program to save data in and retrieve data from an SQL database?

If you just want a simple database I suggest using SQLite which contains everything in a single database file

…R

Thank you for your response, Robin. What program should I use to ‘listen’ to the Arduino?

I am still learning how to use SQL databases, but I was wondering whether the database could be stored on the web…

IoT_hobbyist:
yes, you can. You can connect directly ESP8266 to the database. (there is library for MySQL)
However, that is not a good way because of security. Exposing database to the outside is dangerous.
There is a better way . Arduino makes HTTPS request to the web server, the Web server will interact with the database

How do you make an HTTPS request?? Do you have to use PHP??

Also, I was wondering how I would then send data from the webserver to the database.

IoT_hobbyist:
yes, you can. You can connect directly ESP8266 to the database. (there is library for MySQL)

Can you provide more details please?

...R

alexoort:
What program should I use to 'listen' to the Arduino?

I am still learning how to use SQL databases, but I was wondering whether the database could be stored on the web...

The answer to the first part of the question depends (among other things) on where you want to have the database. I was assuming you would have it on a laptop or Raspberry Pi. If I was building it on this laptop I would write the program in Python.

Many (most?) of the internet hosting companies provide an SQL database as part of their package. I am not familiar enough with how that works to offer advice.

...R

alexoort:
How do you make an HTTPS request?? Do you have to use PHP??

Also, I was wondering how I would then send data from the webserver to the database.

From Arduino ESP8266, you can use a similar function of WiFi101SSLClient to make HTTPS request. On Webserver, you use PHP to handle the request from Arduino/ESP8266 and interact with the database. you can google "PHP MySQL" to see how

Robin2:
Can you provide more details please?

...R

A connection is created between Arduino/ESP8266 and MySQL. There are some library that you can find on the internet. For example, GitHub - ChuckBell/MySQL_Connector_Arduino: Database connector library for using MySQL with your Arduino projects.

IoT_hobbyist:
For example, GitHub - ChuckBell/MySQL_Connector_Arduino: Database connector library for using MySQL with your Arduino projects.

Thanks for the link. Interesting.

I guess if one is accessing an existing MySQL that someone else has set up for this it should be straightforward. However I would not want to have to create and debug SQL commands on an Arduino.

But as far as I an see the link does not explain how to install and set up MySQL. I did that in the past and I did not consider it simple - hence my preference for SQLite if all the features of MySQL are not required.

…R