Variable update in SQL database

ingenieriaquasar · June 18, 2021, 10:41am

Hi there,

I amtrying to upload data into an SQL database. The database is located in a domain server and I my arduino establishes well communication trough GET command and php seems to work ok also. The problem I have is that variables show 0 as result in the column. I can only write a value if I put it directly in the server .php document, as shown in the example... What I am doing wrong? I tried several variations to the .php, where I think the problem is...

PHP file:

<?php

    // PhpConsulta.php

    // Importamos la configuración

$servername = "localhost";

$database = "databasename";

$username = "username";

$password = "password";

// Create connection

$con = mysqli_connect($servername, $username, $password, $database);

// Check connection

if (!$con) {

      die("Connection failed: " . mysqli_connect_error());

}

 

echo "Connected successfully";

    // Leemos los valores que nos llegan por GET

    

    $x=$_GET['ID'];

    $y=$_GET['TEMP'];

    $Z=$_GET['HR'];

 

    // Esta es la instrucción para insertar los valores

    $query = "INSERT INTO tablename (ID, TEMP, HR) VALUES (ID, '22.5','".$z."')";

    // Ejecutamos la instrucción

    mysqli_query($con, $query);

    mysqli_close($con);

?>

Arduino code:
include "DHT.h"
#include <SPI.h>
#include <WiFiNINA.h>
#include "arduino_secrets.h"


#define DHTPIN 2     // what digital pin we're connected to
#define DHTTYPE DHT11   // DHT 11
DHT dht(DHTPIN, DHTTYPE);



//conexión WIFI
char ssid[] = SECRET_SSID;             //  your network SSID (name) between the " "
char pass[] = SECRET_PASS;             // your network password between the " "

int status = WL_IDLE_STATUS;
WiFiClient client;

// server address:
char server[] = "servername.com";


unsigned long lastConnectionTime = 0;            // last time you connected to the server, in milliseconds
const unsigned long postingInterval = 10L * 1000L; // delay between updates, in milliseconds


/*//////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////*/

void setup() {

dht.begin();
Serial.begin(9600);

while (!Serial) {

    ; // wait for serial port to connect. Needed for native USB port only

  }

  // check for the WiFi module:

  if (WiFi.status() == WL_NO_MODULE) {

    Serial.println("Communication with WiFi module failed!");

    // don't continue

    while (true);

  }

  String fv = WiFi.firmwareVersion();

  if (fv < WIFI_FIRMWARE_LATEST_VERSION) {

    Serial.println("Please upgrade the firmware");

  }

  // attempt to connect to Wifi network:

  while (status != WL_CONNECTED) {

    Serial.print("Attempting to connect to SSID: ");

    Serial.println(ssid);

    // Connect to WPA/WPA2 network. Change this line if using open or WEP network:

    status = WiFi.begin(ssid, pass);

    // wait 10 seconds for connection:

    delay(1000);

  }

// you're connected now, so print out the status:

  printWifiStatus();

}







/*//////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////*/
void loop() 


{

  

delay(500);

  
// Check if any reads failed and exit early (to try again).
if (isnan(h) || isnan(t) || isnan(f)) {
    Serial.println("Failed to read from DHT sensor!");
    return;
  }

Serial.print("Temperatura: ");
Serial.print(t);
Serial.println("ºC");
Serial.print("Humedad Relativa: ");
Serial.print(h);
Serial.println("%");




  delay(1000);




  // if there's incoming data from the net connection.

  // send it out the serial port.  This is for debugging

  // purposes only:

  // while (client.available()) {

  //  char c = client.read();

  //  Serial.write(c);


// if ten seconds have passed since your last connection,

  // then connect again and send data:

  if (millis() - lastConnectionTime > postingInterval) {

    httpRequest();

  }

  }


// this method makes a HTTP connection to the server:
void httpRequest() {

  // close any connection before send a new request.

  // This will free the socket on the Nina module

  //client.stop();

  // if there's a successful connection:

  if (client.connect(server, 80)) {



    Serial.println("connecting...");

    // send the HTTP PUT request:

float ID='EJEMPLO';
float HR;
float TEMP;
int $Z;
int $y;


    client.print(HR);
    client.print(TEMP);
    client.println("GET /PhpConsulta.php?TEMP=$y HTTP/1.1");
    client.println("Host: www.servername.com");
    client.println("User-Agent: ArduinoWiFi/1.1");
    client.println();



    client.println("Connection: close");

    client.println();
    delay(1000);

    // note the time that the connection was made:

    lastConnectionTime = millis();

  } else {

    // if you couldn't make a connection:

    Serial.println("connection failed");



  }
client.stop();
}


void printWifiStatus() {

  // print the SSID of the network you're attached to:

  Serial.print("SSID: ");

  Serial.println(WiFi.SSID());

  // print your board's IP address:

  IPAddress ip = WiFi.localIP();

  Serial.print("IP Address: ");

  Serial.println(ip);

  // print the received signal strength:

  long rssi = WiFi.RSSI();

  Serial.print("signal strength (RSSI):");

  Serial.print(rssi);

  Serial.println(" dBm");
}

Database looks like this:

type or paste code here

wildbill · June 18, 2021, 12:14pm

Your Arduino code doesn't look valid enough to compile.

PaulRB · June 18, 2021, 12:33pm

@wildbill is correct, that cannot be the code you uploaded to Arduino. Please find the code you uploaded, click Auto Format, then post that.

Also please post what you see in serial monitor. Use code tags for that also.

ingenieriaquasar · June 18, 2021, 12:49pm

This is the compiling result:
C:\LOCAL\ARDUINO\SQLQUASAR\Sensor_DHT11_SQLQUASAR3\Sensor_DHT11_SQLQUASAR2\Sensor_DHT11_SQLQUASAR2.ino:187:10: warning: character constant too long for its type
float ID='EJEMPLO';
^~~~~~~~~
C:\LOCAL\ARDUINO\SQLQUASAR\Sensor_DHT11_SQLQUASAR3\Sensor_DHT11_SQLQUASAR2\Sensor_DHT11_SQLQUASAR2.ino:188:15: warning: character constant too long for its type
float CLIENTE='CLIENTE';
^~~~~~~~~
C:\LOCAL\ARDUINO\SQLQUASAR\Sensor_DHT11_SQLQUASAR3\Sensor_DHT11_SQLQUASAR2\Sensor_DHT11_SQLQUASAR2.ino:189:18: warning: character constant too long for its type
float GENERACION='GENERACION';
^~~~~~~~~~~~
Sketch uses 30736 bytes (11%) of program storage space. Maximum is 262144 bytes.
Global variables use 2580 bytes (7%) of dynamic memory, leaving 30188 bytes for local variables. Maximum is 32768 bytes.

wildbill · June 18, 2021, 12:53pm

Similarly, those errors don't tie back to the code.

ingenieriaquasar · June 18, 2021, 12:56pm

You are right, I confused projects. Will look for the good one (without errors) and upload them back. Sorry about that.

ingenieriaquasar · June 18, 2021, 11:14pm

Here we go. After revising the correct files, I truly believe the problem is in the php file..

php:

<?php
$servername = "localhost";
$database = "myudatabase";
$username = "myusername";
$password = "mypwd";
// Create connection
$conn = mysqli_connect($servername, $username, $password, $database);
// Check connection
if (!$conn) {
      die("Connection failed: " . mysqli_connect_error());
}
 
echo "Connected successfully";


$sql = "INSERT INTO MY_TABLE VALUES ($_GET["ID"],$_GET["TEMP"],$_GET["HR"])";
$ID=$_get['ID'];
$TEMP=$_get['TEMP'];
$HR=$_get['HR'];




if (mysqli_query($conn, $sql)) {
      echo "New record created successfully";
} else {
      echo "Error: " . $sql . "<br>" . mysqli_error($conn);
}
mysqli_close($conn);
?>

Arduino:

#include "DHT.h"
#include <SPI.h>
#include <WiFiNINA.h>
#include "arduino_secrets.h"


#define DHTPIN 2     // what digital pin we're connected to
#define DHTTYPE DHT11   // DHT 11
DHT dht(DHTPIN, DHTTYPE);


//conexión WIFI
char ssid[] = SECRET_SSID;             //  your network SSID (name) between the " "
char pass[] = SECRET_PASS;             // your network password between the " "

int status = WL_IDLE_STATUS;
WiFiClient client;

// server address:
char server[] = "mydomain.com";

unsigned long lastConnectionTime = 0;            // last time you connected to the server, in milliseconds
const unsigned long postingInterval = 10L * 1000L; // delay between updates, in milliseconds


/*//////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////*/

void setup() {

dht.begin();
Serial.begin(9600);

while (!Serial) {   // wait for serial port to connect. Needed for native USB port only

    Serial.println("PUERTO SERIAL OK"); 
  }
  if (WiFi.status() == WL_NO_MODULE) {    // check for the WiFi module:
    Serial.println("No hay comunicación con el módulo WiFi");
   while (true); // don't continue
  }

  String fv = WiFi.firmwareVersion();
  if (fv < WIFI_FIRMWARE_LATEST_VERSION) {
  Serial.println("Por favor actualice firmware");
  }
  
  while (status != WL_CONNECTED) {  // attempt to connect to Wifi network:
    Serial.print("Conectando con SSID: ");
    Serial.println(ssid);  // Connect to WPA/WPA2 network. Change this line if using open or WEP network:
    status = WiFi.begin(ssid, pass);
    delay(1000);  // wait for connection
  }

// you're connected now, so print out the status:

printWifiStatus();

}

/*//////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////*/
void loop() {



  // Reading temperature or humidity takes about 250 milliseconds!
  // Sensor readings may also be up to 2 seconds 'old' (its a very slow sensor)
  float h = dht.readHumidity();
  // Read temperature as Celsius (the default)
  float t = dht.readTemperature();

delay(500);

// Check if any reads failed and exit early (to try again).
if (isnan(h) || isnan(t)) {
    Serial.println("Failed to read from DHT sensor!");
    return;
  }

Serial.print("Temperatura: ");
Serial.print(t);
Serial.println("ºC");
Serial.print("Humedad Relativa: ");
Serial.print(h);
Serial.println("%");

delay(1000);

  if (millis() - lastConnectionTime > postingInterval) {

    httpRequest();
  }

  // if there's incoming data from the net connection.
  // send it out the serial port.  This is for debugging
  // purposes only:
   while (client.available()) {
   char c = client.read();
   Serial.write(c);
  }
}

// this method makes a HTTP connection to the server:
void httpRequest() {

  // close any connection before send a new request.
  // This will free the socket on the Nina module
  client.stop();
  // if there's a successful connection:
  if (client.connect(server, 80)) {
  Serial.println("connecting...");

// send the HTTP GET request:
float h = dht.readHumidity();
float t = dht.readTemperature();
float TEMP = t;
float HR = h;

    client.println("GET /PhpConsulta.php?TEMP= HTTP/1.1");
    client.println("Host: www.mydomain.com");
    client.println("User-Agent: ArduinoWiFi/1.1");
    client.println("Connection: close");
    client.println();
    delay(300);
    // note the time that the connection was made:
    lastConnectionTime = millis();
  } 
    else {
    // if you couldn't make a connection:
    Serial.println("connection failed");
    }
    printWifiStatus();
}


void printWifiStatus() {

  // print the SSID of the network you're attached to:
  Serial.print("SSID: ");
  Serial.println(WiFi.SSID());
  IPAddress ip = WiFi.localIP();
  Serial.print("IP Address: ");
  Serial.println(ip);
  long rssi = WiFi.RSSI();
  Serial.print("signal strength (RSSI):");
  Serial.print(rssi);
  Serial.println(" dBm");
}

PaulRB · June 19, 2021, 6:18am

Why not test that theory? You can easily do that from your browser by entering an address & parameters:

http://www.mydomain.com/PhpConsulta.php?ID=123&TEMP=456&HR=789

PaulRB · June 19, 2021, 6:23am

This part looks incorrect:

$sql = "INSERT INTO MY_TABLE VALUES ($_GET["ID"],$_GET["TEMP"],$_GET["HR"])";
$ID=$_get['ID'];
$TEMP=$_get['TEMP'];
$HR=$_get['HR'];

I am not sure if the first line will format the SQL string you wanted. But I am certain that it is pointless to get the 3 parameters and assign them to variables and then never use them!

Try this:

$ID=$_get['ID'];
$TEMP=$_get['TEMP'];
$HR=$_get['HR'];
$sql = "INSERT INTO MY_TABLE VALUES ($ID,$TEMP,$HR)";

PaulRB · June 19, 2021, 6:33am

Once you have your php corrected and tested from your browser, you can then look at the errors in your Arduino code. For example:

// send the HTTP GET request:
float h = dht.readHumidity();
float t = dht.readTemperature();
float TEMP = t;
float HR = h;

    client.println("GET /PhpConsulta.php?TEMP= HTTP/1.1");
    client.println("Host: www.mydomain.com");
    client.println("User-Agent: ArduinoWiFi/1.1");
    client.println("Connection: close");
    client.println();

In this code, you read the temperature and humidity but then you do not include those values in the get request that is sent.

It should be something like:

// send the HTTP GET request:
float h = dht.readHumidity();
float t = dht.readTemperature();

    client.print("GET /PhpConsulta.php?ID=123&TEMP=");
    client.print(t);
    client.print("&HR=");
    client.print(h);
    client.println(" HTTP/1.1");
    client.println("Host: www.mydomain.com");
    client.println("User-Agent: ArduinoWiFi/1.1");
    client.println("Connection: close");
    client.println();

Please click Auto Format in the IDE before you post your code each time, to make it easier to read.

ingenieriaquasar · June 19, 2021, 1:55pm

Thank you!
My problem still with php file..
This code works but column TEMP results are always zero
ID increases correctly.
I have reduced to one column for simplicity purposes

<?php
$servername = "localhost";
$database = "mydatabase";
$username = "myusername";
$password = "mypwd";
// Create connection
$conn = mysqli_connect($servername, $username, $password, $database);
// Check connection
if (!$conn) {"Conexion OK";
      die("Connection failed: " . mysqli_connect_error());
}
 
echo "Connected successfully";


$_GET['ID'];
$_GET['TEMP'];
$sql = "INSERT INTO mytable (ID, TEMP) VALUES(ID, TEMP)";
if (mysqli_query($conn, $sql)) {
      echo "New record created successfully";
} else {
      echo "Error: " . $sql . "<br>" . mysqli_error($conn);
}
mysqli_close($conn);
?>

PaulRB · June 19, 2021, 5:34pm

$sql = "INSERT INTO mytable (ID, TEMP) VALUES(ID, TEMP)";

Pay close attention to what you put in your code compared to the examples of code given to you. With PHP, like C++ and every other language, small things are important. Compare that line from your code to my suggestion from the earlier post. There is a small but important difference.

How is the table mytable defined? Is the ID column defined to be automatically populated with an increasing row identifier or something?

ingenieriaquasar · June 19, 2021, 7:57pm

Yes, ID column is defined to be automatically populated with an increasing row identifier.

I also tried

$sql = "INSERT INTO PV_QUASAR1 VALUES ($ID,$t)";

but then the message return is:

?> Connected successfullyError: INSERT MY_TABLE VALUES (,)
You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ')' at line 1

Then tried:

$sql = "INSERT INTO PV_QUASAR1 VALUES ('$ID','$t')";

no error message when:

put into the browser:
'https://www.mydomain.com/PhpConsulta.php?TEMP=456'

But still TEMP value does not update

ingenieriaquasar · June 19, 2021, 7:58pm

Sorry I put:

$sql = "INSERT INTO PV_QUASAR1 VALUES ('$ID','$TEMP')";

PaulRB · June 20, 2021, 7:40am

In that case, should your INSERT statement be inserting any value for ID?

$sql = "INSERT INTO PV_QUASAR1 VALUES ('$ID','$TEMP')";

You have lost the column names of the columns you are giving values for. They were present in your earlier posts.

Post the definition of your table.

Maybe my code here will give you some ideas.

ingenieriaquasar · June 20, 2021, 10:51am

Finally, after all your help and many 'night' hours... It WORKS!! Thank you! Had problems with variables definition and some syntax... bellow php and arduino:

php: PhpConsulta.php

?>

<?php

$servername = "localhost";

$database = "mydatabase";

$username = "myusername";

$password = "mypassword";

// Create connection

$conn = mysqli_connect($servername, $username, $password, $database);

// Check connection

if (!$conn) {

      die("Connection failed: " . mysqli_connect_error());

}

 

echo "Connected successfully";

$ID=$_GET['ID'];

$t=$_GET['t'];

$h=$_GET['h'];

$sql = "INSERT INTO mytable (ID, t, h) VALUES ('$ID','$t','$h')";

if (mysqli_query($conn, $sql)) {

      echo "New record created successfully";

} else {

      echo "Error: " . $sql . "<br>" . mysqli_error($conn);

}

mysqli_close($conn);

?>

arduino:

#include "DHT.h"
#include <SPI.h>
#include <WiFiNINA.h>
#include "arduino_secrets.h"


#define DHTPIN 2  // what digital pin we're connected to
#define DHTTYPE DHT11 // DHT 11
DHT dht(DHTPIN, DHTTYPE);

//conexión WIFI
char ssid[] = SECRET_SSID;  //  your network SSID (name) between the " "
char pass[] = SECRET_PASS;  // your network password between the " "

int status = WL_IDLE_STATUS;
WiFiClient client;


char server[] = "mydomain.com"; // server address:

unsigned long lastConnectionTime = 0; // last time you connected to the server, in milliseconds
const unsigned long postingInterval = 10L * 1000L; // delay between updates, in milliseconds

void setup() {

  dht.begin();
  Serial.begin(9600);

  // wait for serial port to connect. Needed for native USB port only
  while (!Serial) {
    ;

  }

  // check for the WiFi module:
  if (WiFi.status() == WL_NO_MODULE) {

    Serial.println("Communication with WiFi module failed!"); // don't continue
    while (true);
  }

  String fv = WiFi.firmwareVersion();
  if (fv < WIFI_FIRMWARE_LATEST_VERSION) {
    Serial.println("Please upgrade the firmware");
  }


  // attempt to connect to Wifi network:
  while (status != WL_CONNECTED) {

    Serial.print("Attempting to connect to SSID: ");

    Serial.println(ssid);

    status = WiFi.begin(ssid, pass);

    // wait for connection:
    delay(1000);

  }

  // you're connected now, so print out the status:
  printWifiStatus();

}



void loop() {

  delay(500);

  // Reading temperature or humidity takes about 250 milliseconds!
  // Sensor readings may also be up to 2 seconds 'old' (its a very slow sensor)
  float t = dht.readTemperature();
  float h = dht.readHumidity();
  float HR = h;

  // Check if any reads failed and exit early (to try again).
  if (isnan(h) || isnan(t)) {
    Serial.println("Failed to read from DHT sensor!");
    return;
  }

  Serial.print("Temperatura: ");
  Serial.print(t);
  Serial.println("ºC");
  Serial.print("Humedad Relativa: ");
  Serial.print(h);
  Serial.println("%");
  Serial.print("Temperatura: ");

  delay(1000); //wait

 
  // if 10 seconds have passed since your last connection,
  // then connect again and send data:
  if (millis() - lastConnectionTime > postingInterval) {

    httpRequest();

  }

  // if there's incoming data from the net connection.
  // send it out the serial port.  This is for debugging
  // purposes only:
  while (client.available()) {
    char c = client.read();
    Serial.write(c);
  }
}




// this method makes a HTTP connection to the server:
void httpRequest() {

  // close any connection before send a new request.
  // This will free the socket on the Nina module
  client.stop();

  // if there's a successful connection:
  if (client.connect(server, 80)) {
    Serial.println("connecting...");

    // send the HTTP PUT request:
    float temp = dht.readTemperature();
    float hum = dht.readHumidity();
    delay(500);
    
    int ID = (0);
    float t = (temp);
    float h = (hum);

    client.print("GET /PhpConsulta.php?ID=");
    client.print(ID);
    client.print("&t=");
    client.print(t);
    client.print("&h=");
    client.print(h);
    client.println(" HTTP/1.1");
    client.println("Host: www.mydomain.com");
    client.println("User-Agent: ArduinoWiFi/1.1");
    client.println("Connection: close");
    client.println();

    client.println();
    delay(1000);

    // note the time that the connection was made:
    lastConnectionTime = millis();

  } else {

    // if you couldn't make a connection:
    Serial.println("connection failed");

  }
}


void printWifiStatus() {

  // print the SSID of the network you're attached to:
  Serial.print("SSID: ");
  Serial.println(WiFi.SSID());

  // print your board's IP address:

  IPAddress ip = WiFi.localIP();
  Serial.print("IP Address: ");
  Serial.println(ip);

  // print the received signal strength:

  long rssi = WiFi.RSSI();

  Serial.print("signal strength (RSSI):");
  Serial.print(rssi);
  Serial.println(" dBm");
}

system · October 18, 2021, 10:52am

This topic was automatically closed 120 days after the last reply. New replies are no longer allowed.

Topic		Replies	Views
Updating data in an online database Programming	5	2695	May 5, 2021
Sending data from phpMyAdmin database to Arduino General Guidance	6	3229	May 5, 2021
Problem parsing values from Arduino to PHP/SQL Programming	9	2396	May 5, 2021
arduino mysql/connector help Home Automation	4	3394	May 6, 2021
database communication internet Networking, Protocols, and Devices	3	1730	May 6, 2021

Variable update in SQL database

Related topics