Wrong sensor data being sent to msql database from arduino

Status
Not open for further replies.

Thread Starter

macman23

Joined Jan 6, 2023
13
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");
 }
}
Php.png
 

tsan

Joined Sep 6, 2014
138
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.
 

Thread Starter

macman23

Joined Jan 6, 2023
13
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.
Arduino data.png
 

tsan

Joined Sep 6, 2014
138
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
 

Thread Starter

macman23

Joined Jan 6, 2023
13
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.
 

Thread Starter

macman23

Joined Jan 6, 2023
13
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

Joined Sep 6, 2014
138
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.
 

Thread Starter

macman23

Joined Jan 6, 2023
13
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
 
Status
Not open for further replies.
Top