[Solved] Posting sensor reading to MySQL via PHP GET - Not working

I have a project where I am taking readings from an IR sensor and trying to get it to periodically post the readings to a MySQL database via PHP using GET.

The problem I’m having is that when I first start up the sketch, everything works fine through the first database update. It will update once, then it won’t ever do it again.

I’m trying to monitor some material in a container that is nowhere near a computer, so I don’t have the luxury of the serial monitor, but I do have an LCD screen where I can display debugging readings. It’s telling me that the connection to the website is failing on the second and subsequent attempts.

I’ve got ulcers trying to figure out what the problem is. Can anyone come up with suggestions why it wouldn’t work more than once?

I have no reason to believe the problem is server-side. I can update as often as I want if I restart the sketch or use a web browser.

/* 
 * This sketch is intended to do a few things:
 * 1- connect to the internet to get the current time (UTC, will be converted to local using the Timezone library)
 * 2- Take measurements using an IR sensor
 * 3- Display the measurements, as well as other information, on the LCD screen
 * 4- Submit the measurements to a mysql database by way of a PHP web page GET request
 */
 
#include <LiquidCrystal.h>
#include <Streaming.h>           //http://arduiniana.org/libraries/streaming/
#include <SPI.h>
#include <Ethernet.h>
#include <Time.h>
#include <Timezone.h>            //https://github.com/JChristensen/Timezone
#include <EthernetUdp.h>

LiquidCrystal lcd(5, A1, A2, A3, A4, A5);

// ********Sensor Reading Variables
const int sensorPin = 0;            // the analog pin connected to the sensor
const long referenceMv = 8800;      // long int to prevent overflow when multiplied

int mV, cm, cmMaterial;             // mV is the sensor value, cm is the distance to the material measured by the sensor, cmMaterial is the amount of material in the container
float in, inMaterial;               // in is the distance to the material measured by the sensor in inches, inMaterial is the amount of material in the container in inches
int material;                       // converts inMaterial to an integer for the remote database
boolean service_flag = false;       // sets wheather or not the material requires attention

// variables for averaging the sensor value - 
const int numReadings = 50;         // the number of readings to consider for averaging
int readings[numReadings];          // the readings from the analog input
int index = 0;                      // the index of the current reading
int total = 0;                      // the running total
int average = 0;                    // the average


// ********Network & Time Variables
byte mac[] = { 0x00, 0xD2, 0xBB, 0x1C, 0x23, 0x62 };  // the device MAC address. Must be unique on the network. Extras below for multiple sensors or other ethernet devices (Just in case)

unsigned int localPort = 8888;                        // local port to listen for UDP packets
IPAddress timeServer(132, 163, 4, 101);               // time-a.timefreq.bldrdoc.gov NTP server
const int NTP_PACKET_SIZE= 48;                        // NTP time stamp is in the first 48 bytes of the message
byte packetBuffer[ NTP_PACKET_SIZE];                  // buffer to hold incoming and outgoing packets 
EthernetUDP Udp;                                      // A UDP instance to let us send and receive packets over UDP
unsigned long epoch;                                  // current UTC time obdained from 'timeserver' above
// char server[] = "myserver.com";                    // name address for the PHP/MySQL server (using DNS)
IPAddress server(25,30,35,40);                        // IP address for the PHP/MySQL server (not the real address, just as myserver.com is not real)
EthernetClient client;                                // create a client instance

boolean dbUpdated;                                    // ensure that the DB is only updated once per cycle
int updateStatus = 0;                                 // used to display if the last connection was successful or failed (displays "unknown" prior to first attempt) 
int dbAttempts = 0;                                   // number of failed database connection attampts since last successful attempt
long int deviceID = 100001;                           // set the unique device ID used to update the remote database

const unsigned long seventyYears = 2208988800UL;      // Unix time starts on Jan 1 1970. In seconds, that's 2208988800
time_t utcNow, locNow;                                // time variables/values for UTC and local time.
time_t previousUpdate;                                // last time database was updated

TimeChangeRule usCDT = {"CDT", Second, dowSunday, Mar, 2, -300};
TimeChangeRule usCST = {"CST", First, dowSunday, Nov, 2, -360};
Timezone usCT(usCDT, usCST);
TimeChangeRule *tcr;        //pointer to the time change rule, use to get the TZ abbrev


// ********Timing Variables
unsigned long currentTime = millis();
unsigned long previousDisplayTime = currentTime;       // Used for timing when to change the LCD display
int displayInterval = 3000;                            // how often the LCD display changes
int displayScreen = 1;                                 // used to switch between multiple screens on the LCD


void setup()    {
  
  Serial.begin(9600);
  lcd.begin(16, 2);          // 16 char by 2 lines
  lcd.noCursor();            // Hides the LCD cursor.
  
  pinMode(4, OUTPUT);        // for the SD Card slot
  digitalWrite(4, HIGH);     // HIGH means the SD card slot is off
  
  for (int thisReading = 0; thisReading < numReadings; thisReading++)    // fill the array used to average the sensor readings
    readings[thisReading] = 0;
    
  if (Ethernet.begin(mac) == 0) {      //If the ethernet port doesn't connect tot he internet...
    lcd.setCursor(0, 0);
    lcd.print("Failed to config");
    lcd.setCursor(0, 1);
    lcd.print("Network DHCP");
    Serial.println("Failed to configure Ethernet using DHCP");
    // no point in carrying on, so do nothing forever:
    for(;;);
  }
  Udp.begin(localPort);
  
  sendNTPpacket(timeServer);                              // request time packet from timeServer
  delay(1200);
  updateTime();                                           // run the time updater, get the time from online
  
  /* for (int i; i < numReadings; i++)  {
    IRMeasure();
  }
  updateDB(); */
  previousUpdate = 0;                                     // used to display the last time the remote DB was updated. Set to 0 until it is updated.
  // updateDB();
  // timeFuntion();
}

void loop()    {
  timeFunction();                                         // Get the curren time from the internet (under "TimeFunctions")
  IRMeasure();                                            // Take a measurement from the sensor (under "IRMeasure")
  displayInfo();                                          // Update the LCD display (under "Display")
  updateDB();                                             // Update the remote database with the measurement from the sensor
 
}

The code is too long for a single post, here is the rest:

// ***********************************>>>

void displayInfo()  {
 
  if (currentTime - previousDisplayTime >= displayInterval)  {
    lcd.clear();              // clear the LCD
    time_t t = locNow;        // create/update the time variable 't', set to current local time 
    
    switch (displayScreen)  {
      
      case 1:
        cm = getDistance(mV);    // Set the distance from the sensor to the material from the sensor reading
        in = cm / 2.54;          // convert the distance measured by the sensor to inches
        cmMaterial = 122 - cm;    // calculate the amount of material in cm by subtracting the sensor distance from the total possible
        inMaterial = 48 - in;  // calculate the amount of material in inches by subtracting the sensor distance from the total possible
        material = round(inMaterial);    // convert the amount of material from a float to an integer
        Serial << mV << "mV, " << cm << "cm" << endl;
        
        lcd.setCursor(0, 0);
        lcd << "Material: " << material << (char)0x22 << "/" << cmMaterial << "cm  ";
        lcd.setCursor(0, 1);
        lcd << "Sensor: " << mV << "mV";
        displayScreen++;
        break;
        
      case 2:
      
        // prevDisplay = utcNow;
        digitalClockDisplay(t, 0);        // pass the current time into the digitalClockDisplay method, as well as the row it is to be displayed on
        lcd.setCursor(0, 1);
        lcd << Ethernet.localIP()[0] << "." << Ethernet.localIP()[1] << "." << Ethernet.localIP()[2] << "." << Ethernet.localIP()[3];
        displayScreen++;
        break;
      
      case 3:
        lcd.setCursor(0, 0);
        lcd << "Last DB Update:";
        if (previousUpdate == 0)  {
          lcd.setCursor(0, 1);
          lcd << "0 since restart";
        }
        else digitalClockDisplay(previousUpdate, 1);    // pass the time the remote database was last updated into the digitalClockDisplay method, as well as the row it is to be displayed on
        displayScreen++;
        break;
        
      case 4:
        lcd.setCursor(0, 0);
        // lcd << "DB updates daily";
        lcd << "DB updates every";
        lcd.setCursor(0, 1);
        // lcd << "at 4:30PM";
        lcd << "15 minutes";
        displayScreen++;
        break;
        
      case 5:
        lcd.setCursor(0, 0);
        lcd << "Updated:" << dbUpdated;
        lcd.setCursor(0, 1);
        
        switch (updateStatus)  {
        
          case 0:
            lcd << "Status Unknown";
            break;
          
          case 1:
            lcd << "Update Success";
            break;
          
          case 2:
            lcd << "Update Failed";
            break;
        }

        displayScreen++;
        break;
      
      case 6:
        lcd.setCursor(0, 0);
        // lcd << "DB updates daily";
        lcd << "DB update";
        lcd.setCursor(0, 1);
        // lcd << "at 4:30PM";
        lcd << "attempts: " << dbAttempts;
        displayScreen = 1;
        break;
        
      default:
        lcd.setCursor(0, 0);
        lcd << "Display Code Error";
        lcd.setCursor(0, 1);
        lcd << "Please wait";
        displayScreen = 1;
        break;
       
    }
    
  previousDisplayTime = currentTime;
  
  }
}


void digitalClockDisplay(time_t t, int displayRow)  {
  // digital clock display of the time
  lcd.setCursor(0, displayRow);
  lcd.print(hourFormat12(t));
  printDigits(minute(t));

  if (hour(t) < 12) lcd.print("AM");
  else lcd.print("PM ");


  if (month(t) < 10)  {                  // to get the last digit of the month consistently in the same place, set the location depending on if the month is single or double digits
    lcd.setCursor(10, displayRow);
  }
  else  {
    lcd.setCursor(9, displayRow);
  }
  
  lcd << monthShortStr(month(t)) << " " << day(t);
}

void IRMeasure()  {
  
  total= total - readings[index];               // subtract the last reading
  readings[index] = analogRead(sensorPin);      // read from the sensor
  total= total + readings[index];               // add the reading to the total
  index = index + 1;                            // advance to the next position in the array

  
  if (index >= numReadings)                     // if we're at the end of the array...
    index = 0;                                  // ...wrap around to the beginning

  average = total / numReadings;                // calculate the average:
  mV = (average * referenceMv) / 1023;

}


// ********* Code to smooth the sensor readings - from: https://www.inkling.com/read/arduino-cookbook-michael-margolis-2nd/chapter-6/recipe-6-5 (or http://bit.ly/15QSgFA)

// the following is used to interpolate the distance from a table
// table entries are distances in steps of 250 millivolts
const int TABLE_ENTRIES = 12;
const int firstElement = 250; // first entry is 250 mV
const int INTERVAL  = 250; // millivolts between each element
static int distance[TABLE_ENTRIES] = {150,140,130,100,60,50,40,35,30,25,20,15};

int getDistance(int mV)
{
   if( mV >  INTERVAL * TABLE_ENTRIES-1 )
      return distance[TABLE_ENTRIES-1];
   else
   {
      int index = mV / INTERVAL;
      float frac = (mV % 250) / (float)INTERVAL;
      return distance[index] - ((distance[index] - distance[index+1]) * frac);
   }
}


void updateDB()  {
  // if (hour(locNow) == 12 && minute(locNow) == 0)  {      // update the remote DB daily at Noon local time.
  if (minute(locNow) % 1 == 0)  {
    if (!dbUpdated)  {
      dbClientConnect();
    }
  }
  else {
    dbUpdated = false;                                                            //resets the dbUpdated variable to ensure that the conditions are met next time the DB update needs to be done
  }
}

void timeFunction()  {
  // if ((currentTime - previousNetTime) >= 300000)  {
  if (hour(utcNow) % 4 == 0 && minute(utcNow) == 0 && 0 < second(utcNow) < 3)  {
     sendNTPpacket(timeServer);                                                                //request the packet with the time from the time server
     delay(1500);
     updateTime();                                                                             //convert the packet with the timestamp to a time variable & set the system time
  }
  currentTime = millis();
  utcNow = now();
  locNow = usCT.toLocal(utcNow, &tcr);                                                         //convert from utc to local time to set the 'locNow' variable
}



void updateTime()  {
 if ( Udp.parsePacket() ) {
        Udp.read(packetBuffer,NTP_PACKET_SIZE);
        unsigned long highWord = word(packetBuffer[40], packetBuffer[41]);
        unsigned long lowWord = word(packetBuffer[42], packetBuffer[43]);
        unsigned long secsSince1900 = highWord << 16 | lowWord;
        epoch = secsSince1900 - seventyYears;
        setTime(epoch);
     } 
}


unsigned long sendNTPpacket(IPAddress& address)
{
  // set all bytes in the buffer to 0
  memset(packetBuffer, 0, NTP_PACKET_SIZE); 
  // Initialize values needed to form NTP request
  // (see URL above for details on the packets)
  packetBuffer[0] = 0b11100011;   // LI, Version, Mode
  packetBuffer[1] = 0;     // Stratum, or type of clock
  packetBuffer[2] = 6;     // Polling Interval
  packetBuffer[3] = 0xEC;  // Peer Clock Precision
  // 8 bytes of zero for Root Delay & Root Dispersion
  packetBuffer[12]  = 49; 
  packetBuffer[13]  = 0x4E;
  packetBuffer[14]  = 49;
  packetBuffer[15]  = 52;

  // all NTP fields have been given values, now
  // you can send a packet requesting a timestamp: 		   
  Udp.beginPacket(address, 123); //NTP requests are to port 123
  Udp.write(packetBuffer,NTP_PACKET_SIZE);
  Udp.endPacket(); 
}

void printDigits(int digits){
  // utility function for digital clock display: prints preceding colon and leading 0
  lcd.print(":");
  if(digits < 10)
    lcd.print('0');
  lcd.print(digits);
}

void dbClientConnect()  {
  if (client.connect(server, 80)) {
    Serial.println("connected");
    // Make a HTTP request:
    client << "GET /submit.php?deviceID=" << deviceID << "&material_reading=" << "20" << "&service_flag=" << service_flag << " HTTP/1.1" << endl;
    client << "Host: myserver.com" << endl;
    client << "Connection: close" << endl;
    client.println();
      
    // lastConnection = true;
    dbUpdated = true;                      //sets to true to make sure the database isn't update multiple times in the 3 second window.
    updateStatus = 1;
    dbAttempts = 0;
    previousUpdate = locNow;
  }
  else {
    // kf you didn't get a connection to the server:
    Serial.println("connection failed");
    updateStatus = 2;
    dbUpdated = false;
    dbAttempts++;
    // lastConnection = false;
  }
  // delay(4000);
}

This could use further scrutiny:

void updateDB()  {
  // if (hour(locNow) == 12 && minute(locNow) == 0)  {      // update the remote DB daily at Noon local time.
  if (minute(locNow) % 1 == 0)  {
    if (!dbUpdated)  {
      dbClientConnect();
    }
  }
  else {
    dbUpdated = false;                                                            //resets the dbUpdated variable to ensure that the conditions are met next time the DB update needs to be done
  }
}

This:

  if (minute(locNow) % 1 == 0)  {

is effectively:

  if (true)  {

Which means that dbUpdated will never be set false, so no subsequent GET calls will be made. Try changing the %1 to %2 or bigger.

Thanks for catching that. That's not in my operational code.

You are right, it would just always be true. After posting, I changed it to:

if (minute(locNow) % 15 == 0)  {

so that it should be true every 15 minutes.

That's not my problem though, it still updates once, but then it never does so again.

Once I get it working right, it will go back to daily at Noon.

I got it working by adding this to the else:

if (dbAttempts > 0) {
client.stop();
}

void dbClientConnect()  {
  if (client.connect(server, 80)) {
    Serial.println("connected");
    // Make a HTTP request:
    client << "GET /submit.php?deviceID=" << deviceID << "&material_reading=" << "20" << "&service_flag=" << service_flag << " HTTP/1.1" << endl;
    client << "Host: myserver.com" << endl;
    client << "Connection: close" << endl;
    client.println();
      
    // lastConnection = true;
    dbUpdated = true;                      //sets to true to make sure the database isn't update multiple times in the 3 second window.
    updateStatus = 1;
    dbAttempts = 0;
    previousUpdate = locNow;
  }
  else {
    // if you didn't get a connection to the server:
    Serial.println("connection failed");
    updateStatus = 2;
    dbUpdated = false;

    if (dbAttempts > 0)  {
     client.stop(); 
    }

    dbAttempts++;
  }
}

Now I’m wondering why it ever worked as I expected in the first place.