Wrong sensor data being sent to msql database from arduino

macman23 · Jan 6, 2023

Hi,

I am currently doing a smart home project which consists of an Arduino Mega which is connected to the internet with an ethernet shield. It has an lm35 temp sensor and an LDR both turning on and off separate relays. I have XAMPP installed and I am sending sensor data to phpmyadmin. I have data being saved in the database which is just nonsense data with really high numbers. I have spent a lot of time trying to get this to work correctly. Apologies for the long post. Any help would be greatly apprieciated.

This is the PHP script:

PHP script:

<?php
class lm35{
 public $link='';
 function __construct($temperature, $lightlevel ){
  $this->connect();
  $this->storeInDB($temperature, $lightlevel);
 }
 
 function connect(){
  $this->link = mysqli_connect('localhost','root','') or die('Cannot connect to the DB');
  mysqli_select_db($this->link,'arduino') or die('Cannot select the DB');
 }
 
 function storeInDB($temperature, $lightlevel){
  $query = "insert into lm35 set temperature='".$temperature."', lightlevel='".$lightlevel."'";
  $result = mysqli_query($this->link,$query) or die('Errant query:  '.$query);
 }
 
}
if($_GET['temperature'] != '' and $_GET['lightlevel'] != '');{
 $lm35=new lm35($_GET['temperature'], $_GET['lightlevel'] );
}

?>

This is the Arduino sketch

Arduino code:

// Initialize libraries
#include <SPI.h> // for communication with Ethernet Shield
#include <Ethernet.h> // for communication with NTP Server via UDP
#include <TimeLib.h> // for update/display of time
#include <LiquidCrystal_I2C.h> // for the I2C LCD
#include <Wire.h> // allows communication with I2C devices
#define I2C_ADDR    0x27;
#define BACKLIGHT_PIN 3;
LiquidCrystal_I2C lcd(0x27, 16, 2);


//---------------------------------------------------------------------------------------------------------------------------------------
byte mac[] = { 0xA8, 0x61, 0x0A, 0xAE, 0x89, 0x00 }; // Arduino MAC address
byte ip[] = { 192, 168, 178, 17 }; // IP address for my home network. 193.203.134.217 for outside home network. 192, 168, 178, 17(copy of)

// define IPAddress object that will contain the NTP server IP address
// useing an NTP server from https://tf.nist.gov/tf-cgi/servers.cgi
IPAddress timeSrvr(129,6,15,28);

// define Ethernet UDP object and local port 8888 (User Datagram Protocol- communications protocol)
EthernetUDP ethernet_UDP;
unsigned int localPort = 8888;

// variable to store previous displayed time
time_t prevDisplay = 0;

// array to hold incoming/outgoing NTP messages.(NTP-Network time protocol server)
// NTP time message is 48 bytes long
byte messageBuffer[48];

EthernetServer server(80); // Assigning the port forwarded number. Port 80 on my home router

String readString; // We will be using strings to keep track of things.
int val;    // Using val as a variable for the PIR status.
const int Temp = A0;
const int LDR = A1;
const int button = 4;
const int relay = 5;
const int relay2 = 6;

float temperaturedata;
float lightleveldata;

EthernetClient client;
///////////////////////////////////////////////////////////////////////////////////////

void setup(){

  lcd.init();
  lcd.begin (16,2);
  lcd.setBacklight(HIGH);
  Serial.begin(9600);
  delay(500);
  Serial.println("RTC to get accurate date and time from NTP server");
  // get ethernet shield IP via DHCP display error if it fails to get IP from server
  while (Ethernet.begin(mac) == 0) {
    Serial.println("Failed to configure Ethernet using DHCP"); // display error
    delay(1000); // retry after 1 sec
  }
  // DHCP assignment successful, display ethernet shield IP to serial
  Serial.print("Ethernet Shield IP (DHCP): ");
  Serial.println(Ethernet.localIP());

  // start UDP
  ethernet_UDP.begin(localPort);
  Serial.println("Ethernet UDP Start....");

  // pass function getTime() to Time Library to update current time
  setSyncProvider(getTime);

  pinMode(2, OUTPUT);
  pinMode(relay, OUTPUT);
  pinMode(relay2, OUTPUT);
  Ethernet.begin(mac, ip);
  client.connect("192.168.178.39", 80);
 
}

void loop(){
  {
  lightleveldata = analogRead(LDR);
  temperaturedata = analogRead(Temp);
  Sending_To_phpmyadmindatabase();
  delay(30000); // interval
    
  }
      // Temperature Circuit
  int celcius = map(analogRead(A0), 0, 1023, -40, 80);
       Serial.print("Temperature in Celcius is = ");
       Serial.print(celcius);
       Serial.print("C");
       Serial.println();
       delay(500);

       // LDR circuit
  int lightlevel = map(analogRead(A1), 0, 1023, 100, 0);
       Serial.print("The light level is = ");
       Serial.print(lightlevel);
       Serial.print("%");
       Serial.println();
       delay(500);

  if (timeStatus() != timeNotSet) {   // check if the time is successfully updated
    if (now() != prevDisplay) {       // update the display only if time has changed
      prevDisplay = now();
      digitalClockDisplay();          // function to display the current date and time
    }
  }

 EthernetClient client = server.available();  // wait for a client (web browser) to connect
if (client) {                              // if the client is connected
  while (client.connected()) {              // while the client is connected
    if (client.available()) {               // if there is data available to read
      char c = client.read();                // read a single character
      if (readString.length() < 100) {       // if the read string is less than 100 characters
        readString += c;                     // add the character to the readString
      }
      if (c == '\n') {                      // if the character is a newline
        Serial.println(readString);          // print the readString to the serial monitor
        // send an HTTP response back to the client
        client.println("HTTP/1.1 200 OK");
        client.println("Content-Type: text/html");
        client.println();
        // send the HTML page back to the client
        client.println("<hmtl>"); // Initiates the html page
client.println("<head>");
client.println("Darren McKee-C00100946");
client.println("</head>");
client.println("<title>");
client.println("SmartHome Project");
client.println("</title>");
client.println("<body bgcolor=white>");
client.println("<font color=black>");
client.println("<meta http-equiv=\"refresh\" content=\"4\">"); // This is used to refresh the page every 4 secs
client.println("<center>");                                     
client.println("<b>");
client.println("Welcome to your Smarthome");
client.println("<td align=center>");
client.println("</br>");
client.println("Choose an option to control your home");
client.println("<font color = black size=15>");
client.println("</br>");
client.println("");
client.println("</b>");
client.println("<p>");
client.println("<table border=0 width=200>");

client.println("<tr>");
client.println("<td align=center>");
client.println("<font color=black>");
client.println("</br>");
client.println("Current LightLevel is: ");
client.println("</td>");
client.println("</tr>");
client.println("<tr>");
client.println("<td align=center>");
client.println("<font color = black size=15>");
client.println(lightlevel);
client.println(" %");

client.println("</tr>");
client.println("<td align=center>");
client.println("<font color=black>");
client.println("</br>");
client.println("</br>");
client.println("Current Temperature is: ");
client.println("</td>");
client.println("</tr>");
client.println("<tr>");
client.println("<td align=center>");
client.println("<font color = black size=15>");
client.println(celcius);
client.println("&#176 C");
client.println("<br>");

client.println("</td>");
client.println("</tr>");
client.println("</table>");
client.println("<p>");
client.print("<font color=white size=5>");
client.println("<FORM>");
client.println("<INPUT type=button value=Autonomus-ON onClick=window.location='/?Auto_On_1\'>");
client.println("<INPUT type=button value=Autonomus-OFF onClick=window.location='/?Auto_Off_1\'>");
client.println("</FORM>");   
client.println("<FORM>");   
client.println("<INPUT type=button value=Heater-On onClick=window.location='/?Relayon1\'>");
client.println("<INPUT type=button value=Heater-Off onClick=window.location='/?Relayoff1\'>");
client.println("</FORM>");
client.println("<FORM>");
client.println("<INPUT type=button value=Light-On onClick=window.location='/?Relayon2\'>");
client.println("<INPUT type=button value=Light-Off onClick=window.location='/?Relayoff2\'>");
client.println("</FORM>");   // Above and below you'll see that when we click on a button, it adds a little snippet

client.print("<table border=1 width=200>");
client.print("<tr>");
client.print("<td align=center>");
client.print("<font color=black size=3>");
client.print("Heater is ");
client.print("</td>");
client.print("</tr>");
client.print("<tr>");
client.print("<td align=center>");
client.print("<font color=black size=3>");  // And below we will print Motion if there is, and No Motion if there's not.
val = digitalRead(relay);
    if(val == HIGH){
      client.print("ON");
    }
    else{
      client.print("OFF");
    }

client.print("<table border=1 width=200>");
client.print("<tr>");
client.print("<td align=center>");
client.print("<font color=black size=3>");
client.print("The light is ");
client.print("</td>");
client.print("</tr>");
client.print("<tr>");
client.print("<td align=center>");
client.print("<font color=black size=3>");  // And below we will print Motion if there is, and No Motion if there's not.
val = digitalRead(relay2);
    if(val == HIGH){
      client.print("ON");
    }
    else{
      client.print("OFF");
    }
//client.print("</td>");
//client.print("</tr>");
//client.print("</table>");
//client.println("</center>");
//client.println("</font>");
//client.println("</body>");
//client.println("</html>");

          delay(1);
            // these are the snippets the Arduino is watching for >0 waiting for the button press
          if(readString.indexOf("?Auto_On_1") >0 && celcius<28) // if the Temperature goes below 28 degrees turn on the relay which will be connected to a heater which will turn the heater on     
          {
            digitalWrite(relay, HIGH); // Turn on the relay
          }
          else if(readString.indexOf("?Auto_On_1") >0 && celcius>32) // if the Temperature goes above 32 degrees turn off the relay which will be connected to a heater which will turn the heater off
          {
            digitalWrite(relay, LOW); // Turn off the relay
          }
          if(readString.indexOf("?Auto_On_1") >0 && lightlevel<28) // if the light level goes below 35% turn on the relay which will be connected to a lightswitch will turn on   
          {
            digitalWrite(relay2, HIGH); // Turn on the relay
          }
          else if(readString.indexOf("?Auto_On_1") >0 && lightlevel>32) // if the light level goes above 40% turn on the relay which will be connected to a lightswitch will turn off
          {
            digitalWrite(relay2, LOW); // Turn off the relay
          }
          if(readString.indexOf("?Relayon1") >0) // If heater on button is pressed turns on relay
          {
            digitalWrite(relay, HIGH);
          }
          else if(readString.indexOf("?Relayoff1") >0) // If heater off button is pressed turns off relay
          {
            digitalWrite(relay, LOW);
          }
          if(readString.indexOf("?Relayon2") >0)  // If light on button is pressed turns on relay 
          {
            digitalWrite(relay2, HIGH);
          }
          else if(readString.indexOf("?Relayoff2") >0)
          {
            digitalWrite(relay2, LOW);
          }
      
          
          
          readString="";
          client.stop();            // End of session.

        }
      }
    }
  }

}
// function to display the date and time in the serial monitor and the LCD
void digitalClockDisplay() {

  Serial.print(hour());
  printDigits(minute());
  printDigits(second());
  Serial.print(" ");
  Serial.print(day());
  Serial.print(" ");
  Serial.print(month());
  Serial.print(" ");
  Serial.print(year());
  Serial.println();

  lcd.setCursor (0,0);
  if (hour() < 10){
    lcd.print("0"); }
  if (hour() > 12){
    lcd.print("0");
    lcd.print(hour()-12); } else {
    lcd.print(hour()); }
    lcd.print(":");
  if (minute() < 10){
    lcd.print("0"); }
    lcd.print(minute());
    lcd.print(":");
  if (second() < 10){
    lcd.print("0"); }
    lcd.print(second());
   if (hour() > 12){
    lcd.print(" PM"); }
    else {
    lcd.print(" AM"); }

  lcd.setCursor (0,1);
  if (month() < 10){
   lcd.print("0"); }
   lcd.print(month());
   lcd.print("/");
  if (day() < 10){
   lcd.print("0"); }
   lcd.print(day());
   lcd.print("/");
   lcd.print(year());
}

void printDigits(int digits) {
  // add colon character and a leading zero if number < 10
  Serial.print(":");
  if (digits < 10)
    Serial.print('0');
  Serial.print(digits);
}

time_t getTime()
{
  while (ethernet_UDP.parsePacket() > 0) ; // discard packets remaining to be parsed

  Serial.println("Transmit NTP Request message");

  // send packet to request time from NTP server
  sendRequest(timeSrvr);

  // wait for response
  uint32_t beginWait = millis();

  while (millis() - beginWait < 1500) {

    int size = ethernet_UDP.parsePacket();

    if (size >= 48) {
      Serial.println("Receiving NTP Response");

      // read data and save to messageBuffer
      ethernet_UDP.read(messageBuffer, 48);

      // NTP time received will be the seconds elapsed since 1 January 1900
      unsigned long secsSince1900;

      // convert to an unsigned long integer the reference timestamp found at byte 40 to 43
      secsSince1900 =  (unsigned long)messageBuffer[40] << 24;
      secsSince1900 |= (unsigned long)messageBuffer[41] << 16;
      secsSince1900 |= (unsigned long)messageBuffer[42] << 8;
      secsSince1900 |= (unsigned long)messageBuffer[43];

      // returns UTC time
      return secsSince1900 - 2208988800UL;
    }
  }

  // error if no response
  Serial.println("Error: No Response.");
  return 0;
}

void sendRequest(IPAddress &address)
{
  // set all bytes in messageBuffer to 0
  memset(messageBuffer, 0, 48);

  // create the NTP request message

  messageBuffer[0] = 0b11100011;  // LI, Version, Mode
  messageBuffer[1] = 0;           // Stratum, or type of clock
  messageBuffer[2] = 6;           // Polling Interval
  messageBuffer[3] = 0xEC;        // Peer Clock Precision
  // array index 4 to 11 is left unchanged - 8 bytes of zero for Root Delay & Root Dispersion
  messageBuffer[12]  = 49;
  messageBuffer[13]  = 0x4E;
  messageBuffer[14]  = 49;
  messageBuffer[15]  = 52;

  // send messageBuffer to NTP server via UDP at port 123
  ethernet_UDP.beginPacket(address, 123);
  ethernet_UDP.write(messageBuffer, 48);
  ethernet_UDP.endPacket();
}

void Sending_To_phpmyadmindatabase()   //CONNECTING WITH MYSQL
{
  if (client.connect("192.168.178.39", 80)) { // copy of local ip address "192.168.178.39"
   Serial.println("connected");
   // Make a HTTP request:
   Serial.print("GET /testcode/dht.php?temperature=");
   client.print("GET /testcode/dht.php?temperature=");     //YOUR URL
   Serial.println(temperaturedata);
   client.print(temperaturedata);
   client.print("&lightlevel=");
   Serial.println("&lightlevel=");
   client.print(lightleveldata);
   Serial.println(lightleveldata);
   client.print(" ");      //SPACE BEFORE HTTP/1.1
   client.print("HTTP/1.1");
   client.println();
   client.println("Host: 192.168.178.17");
   client.println("Connection: close");
   client.println();
 } else {
   // if you didn't get a connection to the server:
   Serial.println("connection failed");
 }
}

tsan · Jan 6, 2023

On a quick look I see a possible problem. Variables are float:
float temperaturedata;
float lightleveldata;

and read with analogread but analogread returns an int,
lightleveldata = analogRead(LDR);
temperaturedata = analogRead(Temp);

Then those float types are sent
client.print(temperaturedata);
client.print(lightleveldata);
but float is not a supported parameter type.

macman23 · Jan 6, 2023

Hi thank you for the reply. I changed them to int in the code and also changed them to int in the phpmyadmin database. Still getting the same high sensor values. They are correct printing to the serial monitor but wrong being put into the database. I will attach a picture.

KeithWalker · Jan 6, 2023

What format does the database expect? Is the byte order reversed?

tsan · Jan 6, 2023

The values in the database are closely the same than in the serial monitor. Temperature is 568 and lightlevel is 734 and these integer values are sent to the server, not scaled values. It's unlikely that the value sent from Arduino goes upto database but the value would change only slightly like 568 -> 622. It's possible that the code works correctly.

You can send the same query that is on serial monitor to XAMPP using a browser. This way you can see if the values stored in the DB are correct.

It's long time I have used php but your query seems to be vulnerable to SQL injection. No problem when testing on home computer/network if it's behind NAT but a parameterized query should be used in a public server,
info about mysqli and SQL injection

macman23 · Jan 7, 2023

KeithWalker said:
What format does the database expect? Is the byte order reversed?

I have attached a picture of the database.

macman23 · Jan 7, 2023

tsan said:
The values in the database are closely the same than in the serial monitor. Temperature is 568 and lightlevel is 734 and these integer values are sent to the server, not scaled values. It's unlikely that the value sent from Arduino goes upto database but the value would change only slightly like 568 -> 622. It's possible that the code works correctly.

You can send the same query that is on serial monitor to XAMPP using a browser. This way you can see if the values stored in the DB are correct.

It's long time I have used php but your query seems to be vulnerable to SQL injection. No problem when testing on home computer/network if it's behind NAT but a parameterized query should be used in a public server,
info about mysqli and SQL injection

I want to send the exact values from the temperature sensor and the LDR sensor to the database. Which would be 26C and 29% instead of 568 and 734. I am aware of the SQL injection but I would like to get the correct values stored in the database first.

macman23 · Jan 7, 2023

I have changed the code around and i am now getting the correct values in the serial monitor. But now no data is being sent to the database.

Main code:

//

// Initialize libraries
#include <SPI.h> // for communication with Ethernet Shield
#include <Ethernet.h> // for communication with NTP Server via UDP
#include <TimeLib.h> // for update/display of time
#include <LiquidCrystal_I2C.h> // for the I2C LCD
#include <Wire.h> // allows communication with I2C devices
#define I2C_ADDR    0x27
#define BACKLIGHT_PIN 3
LiquidCrystal_I2C lcd(0x27, 16, 2);


//---------------------------------------------------------------------------------------------------------------------------------------
byte mac[] = { 0xA8, 0x61, 0x0A, 0xAE, 0x89, 0x00 }; // Arduino MAC address
byte ip[] = { 192, 168, 178, 17 }; // IP address for my home network.  for outside home network. 192, 168, 178, 17(copy of)

// define IPAddress object that will contain the NTP server IP address
// useing an NTP server from https://tf.nist.gov/tf-cgi/servers.cgi
IPAddress timeSrvr(129,6,15,28);

// define Ethernet UDP object and local port 8888 (User Datagram Protocol- communications protocol)
EthernetUDP ethernet_UDP;
unsigned int localPort = 8888;

// variable to store previous displayed time
time_t prevDisplay = 0;

// array to hold incoming/outgoing NTP messages.(NTP-Network time protocol server)
// NTP time message is 48 bytes long
byte messageBuffer[48];

EthernetServer server(80); // Assigning the port forwarded number. Port 80 on my home router

String readString; // We will be using strings to keep track of things.
int val;  
const int Temp = A0;
const int LDR = A1;
const int button = 4;
const int relay = 5;
const int relay2 = 6;
int lightlevel = 0;
int celcius = 0;


//int temperaturedata;
//int lightleveldata;

//char server[] = "192.168.178.39";
//IPAddress ip(192, 168, 178, 17);
EthernetClient client;
///////////////////////////////////////////////////////////////////////////////////////

void databaseLoop() {
  // Call Sending_To_phpmyadmindatabases() every 30 seconds
  Sending_To_phpmyadmindatabase(lightlevel, celcius);
  delay(3000);
}

void setup(){

  lcd.init();
  lcd.begin (16,2);
  lcd.setBacklight(HIGH);
  Serial.begin(9600);
  delay(500);
  Serial.println("RTC to get accurate date and time from NTP server");
  // get ethernet shield IP via DHCP display error if it fails to get IP from server
  while (Ethernet.begin(mac) == 0) {
    Serial.println("Failed to configure Ethernet using DHCP"); // display error
    delay(1000); // retry after 1 sec
  }
  // DHCP assignment successful, display ethernet shield IP to serial
  Serial.print("Ethernet Shield IP (DHCP): ");
  Serial.println(Ethernet.localIP());

  // start UDP
  ethernet_UDP.begin(localPort);
  Serial.println("Ethernet UDP Start....");

  // pass function getTime() to Time Library to update current time
  setSyncProvider(getTime);

  pinMode(2, OUTPUT);
  pinMode(relay, OUTPUT);
  pinMode(relay2, OUTPUT);
  Ethernet.begin(mac, ip);
  client.connect("192.168.178.39", 80);


}

void loop(){
      //lightleveldata = analogRead(LDR);
      //temperaturedata = analogRead(Temp);
   int temperature = analogRead(Temp);
  // Convert temperature sensor value to Celcius
  celcius = map(temperature, 0, 1023, -40, 80);
       Serial.print("Temperature in Celcius is = ");
       Serial.print(celcius);
       Serial.print("C");
       Serial.println();
       delay(500);

       // LDR circuit
int light = analogRead(LDR);      
  lightlevel = map(light, 0, 1023, 100, 0);
       Serial.print("The light level is = ");
       Serial.print(lightlevel);
       Serial.print("%");
       Serial.println();
       delay(500);

       databaseLoop();

  if (timeStatus() != timeNotSet) {   // check if the time is successfully updated
    if (now() != prevDisplay) {       // update the display only if time has changed
      prevDisplay = now();
      digitalClockDisplay();          // function to display the current date and time
    }
  }
EthernetClient client = server.available();  // wait for a client (web browser) to connect
if (client) {                              // if the client is connected
  while (client.connected()) {              // while the client is connected
    if (client.available()) {               // if there is data available to read
      char c = client.read();                // read a single character
      if (readString.length() < 100) {       // if the read string is less than 100 characters
        readString += c;                     // add the character to the readString
      }
      if (c == '\n') {                      // if the character is a newline
        Serial.println(readString);          // print the readString to the serial monitor
        // send an HTTP response back to the client
        client.println("HTTP/1.1 200 OK");
        client.println("Content-Type: text/html");
        client.println();
        // send the HTML page back to the client
        client.println("<hmtl>"); // Initiates the html page
client.println("<head>");

client.println("</head>");
client.println("<title>");
client.println("SmartHome Project");
client.println("</title>");
client.println("<body bgcolor=white>");
client.println("<font color=black>");
client.println("<meta http-equiv=\"refresh\" content=\"4\">"); // This is used to refresh the page every 4 secs
client.println("<center>");                                    
client.println("<b>");
client.println("Welcome to your Smarthome");
client.println("<td align=center>");
client.println("</br>");
client.println("Choose an option to control your home");
client.println("<font color = black size=15>");
client.println("</br>");
client.println("");
client.println("</b>");
client.println("<p>");
client.println("<table border=0 width=200>");

client.println("<tr>");
client.println("<td align=center>");
client.println("<font color=black>");
client.println("</br>");
client.println("Current LightLevel is: ");
client.println("</td>");
client.println("</tr>");
client.println("<tr>");
client.println("<td align=center>");
client.println("<font color = black size=15>");
client.println(lightlevel);
client.println(" %");

client.println("</tr>");
client.println("<td align=center>");
client.println("<font color=black>");
client.println("</br>");
client.println("</br>");
client.println("Current Temperature is: ");
client.println("</td>");
client.println("</tr>");
client.println("<tr>");
client.println("<td align=center>");
client.println("<font color = black size=15>");
client.println(celcius);
client.println("&#176 C");
client.println("<br>");

client.println("</td>");
client.println("</tr>");
client.println("</table>");
client.println("<p>");
client.print("<font color=white size=5>");
client.println("<FORM>");
client.println("<INPUT type=button value=Autonomus-ON onClick=window.location='/?Auto_On_1\'>");
client.println("<INPUT type=button value=Autonomus-OFF onClick=window.location='/?Auto_Off_1\'>");
client.println("</FORM>");  
client.println("<FORM>");  
client.println("<INPUT type=button value=Heater-On onClick=window.location='/?Relayon1\'>");
client.println("<INPUT type=button value=Heater-Off onClick=window.location='/?Relayoff1\'>");
client.println("</FORM>");
client.println("<FORM>");
client.println("<INPUT type=button value=Light-On onClick=window.location='/?Relayon2\'>");
client.println("<INPUT type=button value=Light-Off onClick=window.location='/?Relayoff2\'>");
client.println("</FORM>");   // Above and below you'll see that when we click on a button, it adds a little snippet

client.print("<table border=1 width=200>");
client.print("<tr>");
client.print("<td align=center>");
client.print("<font color=black size=3>");
client.print("Heater is ");
client.print("</td>");
client.print("</tr>");
client.print("<tr>");
client.print("<td align=center>");
client.print("<font color=black size=3>");  // And below we will print Motion if there is, and No Motion if there's not.
val = digitalRead(relay);
    if(val == HIGH){
      client.print("ON");
    }
    else{
      client.print("OFF");
    }

client.print("<table border=1 width=200>");
client.print("<tr>");
client.print("<td align=center>");
client.print("<font color=black size=3>");
client.print("The light is ");
client.print("</td>");
client.print("</tr>");
client.print("<tr>");
client.print("<td align=center>");
client.print("<font color=black size=3>");  // And below we will print Motion if there is, and No Motion if there's not.
val = digitalRead(relay2);
    if(val == HIGH){
      client.print("ON");
    }
    else{
      client.print("OFF");
    }
//client.print("</td>");
//client.print("</tr>");
//client.print("</table>");
//client.println("</center>");
//client.println("</font>");
//client.println("</body>");
//client.println("</html>");

          delay(1);
            // these are the snippets the Arduino is watching for >0 waiting for the button press
          if(readString.indexOf("?Auto_On_1") >0 && celcius<28) // if the Temperature goes below 28 degrees turn on the relay which will be connected to a heater which will turn the heater on    
          {
            digitalWrite(relay, HIGH); // Turn on the relay
          }
          else if(readString.indexOf("?Auto_On_1") >0 && celcius>32) // if the Temperature goes above 32 degrees turn off the relay which will be connected to a heater which will turn the heater off
          {
            digitalWrite(relay, LOW); // Turn off the relay
          }
          if(readString.indexOf("?Auto_On_1") >0 && lightlevel<28) // if the light level goes below 35% turn on the relay which will be connected to a lightswitch will turn on  
          {
            digitalWrite(relay2, HIGH); // Turn on the relay
          }
          else if(readString.indexOf("?Auto_On_1") >0 && lightlevel>32) // if the light level goes above 40% turn on the relay which will be connected to a lightswitch will turn off
          {
            digitalWrite(relay2, LOW); // Turn off the relay
          }
          if(readString.indexOf("?Relayon1") >0) // If heater on button is pressed turns on relay
          {
            digitalWrite(relay, HIGH);
          }
          else if(readString.indexOf("?Relayoff1") >0) // If heater off button is pressed turns off relay
          {
            digitalWrite(relay, LOW);
          }
          if(readString.indexOf("?Relayon2") >0)  // If light on button is pressed turns on relay
          {
            digitalWrite(relay2, HIGH);
          }
          else if(readString.indexOf("?Relayoff2") >0)
          {
            digitalWrite(relay2, LOW);
          }
     
         
         
          readString="";
          client.stop();            // End of session.

        }
      }
    }
  }

}
//databaseLoop();
// function to display the date and time in the serial monitor and the LCD
void digitalClockDisplay() {

  Serial.print(hour());
  printDigits(minute());
  printDigits(second());
  Serial.print(" ");
  Serial.print(day());
  Serial.print(" ");
  Serial.print(month());
  Serial.print(" ");
  Serial.print(year());
  Serial.println();

  lcd.setCursor (0,0);
  if (hour() < 10){
    lcd.print("0"); }
  if (hour() > 12){
    lcd.print("0");
    lcd.print(hour()-12); } else {
    lcd.print(hour()); }
    lcd.print(":");
  if (minute() < 10){
    lcd.print("0"); }
    lcd.print(minute());
    lcd.print(":");
  if (second() < 10){
    lcd.print("0"); }
    lcd.print(second());
   if (hour() > 12){
    lcd.print(" PM"); }
    else {
    lcd.print(" AM"); }

  lcd.setCursor (0,1);
  if (month() < 10){
   lcd.print("0"); }
   lcd.print(month());
   lcd.print("/");
  if (day() < 10){
   lcd.print("0"); }
   lcd.print(day());
   lcd.print("/");
   lcd.print(year());
}

void printDigits(int digits) {
  // add colon character and a leading zero if number < 10
  Serial.print(":");
  if (digits < 10)
    Serial.print('0');
  Serial.print(digits);
}

time_t getTime()
{
  while (ethernet_UDP.parsePacket() > 0) ; // discard packets remaining to be parsed

  Serial.println("Transmit NTP Request message");

  // send packet to request time from NTP server
  sendRequest(timeSrvr);

  // wait for response
  uint32_t beginWait = millis();

  while (millis() - beginWait < 1500) {

    int size = ethernet_UDP.parsePacket();

    if (size >= 48) {
      Serial.println("Receiving NTP Response");

      // read data and save to messageBuffer
      ethernet_UDP.read(messageBuffer, 48);

      // NTP time received will be the seconds elapsed since 1 January 1900
      unsigned long secsSince1900;

      // convert to an unsigned long integer the reference timestamp found at byte 40 to 43
      secsSince1900 =  (unsigned long)messageBuffer[40] << 24;
      secsSince1900 |= (unsigned long)messageBuffer[41] << 16;
      secsSince1900 |= (unsigned long)messageBuffer[42] << 8;
      secsSince1900 |= (unsigned long)messageBuffer[43];

      // returns UTC time
      return secsSince1900 - 2208988800UL;
    }
  }

  // error if no response
  Serial.println("Error: No Response.");
  return 0;
}

void sendRequest(IPAddress &address)
{
  // set all bytes in messageBuffer to 0
  memset(messageBuffer, 0, 48);

  // create the NTP request message

  messageBuffer[0] = 0b11100011;  // LI, Version, Mode
  messageBuffer[1] = 0;           // Stratum, or type of clock
  messageBuffer[2] = 6;           // Polling Interval
  messageBuffer[3] = 0xEC;        // Peer Clock Precision
  // array index 4 to 11 is left unchanged - 8 bytes of zero for Root Delay & Root Dispersion
  messageBuffer[12]  = 49;
  messageBuffer[13]  = 0x4E;
  messageBuffer[14]  = 49;
  messageBuffer[15]  = 52;

  // send messageBuffer to NTP server via UDP at port 123
  ethernet_UDP.beginPacket(address, 123);
  ethernet_UDP.write(messageBuffer, 48);
  ethernet_UDP.endPacket();
}

void Sending_To_phpmyadmindatabase(int lightlevel, int celcius)   //CONNECTING WITH MYSQL
{
  if (client.connect("192.168.178.39", 80)) { // copy of local ip address "192.168.178.39"
   Serial.println("connected");
   // Make a HTTP request:
   Serial.print("GET /testcode/mac.php?celcius=");
   client.print("GET /testcode/mac.php?celcius=");     //YOUR URL
   Serial.println(celcius);
   client.print(celcius);
   client.print("&lightlevel=");
   Serial.println("&lightlevel=");
   client.print(lightlevel);
   Serial.println(lightlevel);
   client.print(" ");      //SPACE BEFORE HTTP/1.1
   client.print("HTTP/1.1");
   client.println();
   client.println("Host: 192.168.178.17");
   client.println("Connection: close");
   client.println();
} else {
   // if you didn't get a connection to the server:
   Serial.println("connection failed");
}
}

tsan · Jan 7, 2023

macman23 said:
I want to send the exact values from the temperature sensor and the LDR sensor to the database. Which would be 26C and 29% instead of 568 and 734. I am aware of the SQL injection but I would like to get the correct values stored in the database first.

If the correct values -but in integer format- are sent to XAMPP server it's possible to do the scaling in PHP when receiving the data and write the exact values to the DB after scaling. If you control both Arduino and PHP side that seems to be easiest way to solve the problem.

macman23 · Jan 7, 2023

tsan said:
If the correct values -but in integer format- are sent to XAMPP server it's possible to do the scaling in PHP when receiving the data and write the exact values to the DB after scaling. If you control both Arduino and PHP side that seems to be easiest way to solve the problem.

I have the correct values now. But no data is going to the database now atall. I have posted my updated code in my previous post

macman23 · Mar 4, 2023

I got this all sorted in the end. Took a lot of work but project fully built

macman23 · Mar 4, 2023

Does anyone know how to delete this thread? It is causing issues with my project report similarities.

Papabravo · Mar 4, 2023

Threads do not normally get deleted. It is up to the moderators, but they usually let threads fade into irrelevancy.

Thread starter	Similar threads	Forum	Replies	Date
	PCB etching gone wrong, please advice	PCB Layout , EDA & Simulations	3	Yesterday at 1:37 PM
P	Deep Reinforcement Learning for automated analog IC design sizing- Suggest me my path is right or wrong?	Jobs & Career Advising	0	Mar 13, 2023
	Have a trouble figuring whats wrong in the 220v inverter.	General Electronics Chat	29	Jan 13, 2023
D	[SOLVED] LED strip w/PIR sensor and MOSFET - LEDs are blinking. What am I doing wrong ?	General Electronics Chat	20	Mar 30, 2020
T	Microchip LM74 Temperature Sensor - Wrong Temp	Sensor Design & Implementation	12	May 5, 2010

Wrong sensor data being sent to msql database from arduino

Join our Engineering Community! Sign-in with:

Wrong sensor data being sent to msql database from arduino

macman23

tsan

macman23

KeithWalker

tsan

macman23

Attachments

macman23

macman23

tsan

macman23

macman23

macman23

Papabravo

You May Also Like

Fabless FPGA/eFPGA Players Ramp up Their Innovations—a Roundup

Hailo Spins Vision SoC Crafted for AI Analytics-capable Smart Cameras

IEEE RWW Keynotes: Future of RF Looks Rocky and That’s a Good Thing

Researchers Tap Origami as Basis for Reconfigurable Antenna Design