Problem save sensor data on server with Arduino Uni and Ethernet shield

Hi,

I'm quite new to Arduinos and have problem storing sensor values to MySQL database. I have read a lot in different forums yesterday and today and found that other have had the same problem but their solutions havn't helped me (maybe because of my lack of knowledge) so i hope you can get me on track again.

On server I have this code:

<?php

if ($_GET["key"] == "7RrlaA2tubH2") { 
 
$servername = "xxx";
$username = "yyy";
$password = "zzz";
$dbname = "123";

// Create connection
$conn = mysqli_connect($servername, $username, $password, $dbname);
// Check connection
if (!$conn) {
    die("Connection failed: " . mysqli_connect_error());
}

$val_ktSensorTemp = $_GET["SensorTemp"];
$val_ktSensorHum = $_GET["SensorHum"];
$val_ktSetTemp = $_GET["SetTemp"];
$val_ktSetHum = $_GET["SetHum"];
$val_ktFridge = $_GET["Fridge"];
$val_ktHeat = $_GET["Heat"];
$val_ktHum = $_GET["Hum"];
$val_ktDehum = $_GET["Dehum"];
$val_ktLog = $_GET["Log"];

$sql = "INSERT INTO kt_data (ktSensorTemp, ktSensorHum, ktSetTemp, ktSetHum, ktFridge, ktHeat, ktHum, ktDehum, ktLog)
VALUES (" . $val_ktSensorTemp .", " . $val_ktSensorHum .", " . $val_ktSetTemp .", " . $val_ktSetHum .", '" . $val_ktFridge ."', '" . $val_ktHeat ."', '" . $val_ktHum ."', '" . $val_ktDehum ."', '" . $val_ktLog . "')";

if (mysqli_query($conn, $sql)) {
    echo "Posten sparad";
} else {
    echo "Fel: " . $sql . "
" . mysqli_error($conn);
}

mysqli_close($conn);

} else {
	echo "Behörighet saknas";
}
?>

If I browse to
"http://api.gotfarm.se/korvtork/kt_save.php?key=7RrlaA2tubH2&SensorTemp=22.20&SensorHum=82.21&SetTemp=20&SetHum=80&Fridge=J&Heat=N&Hum=J&Dehum=N&Log=This is a test" the values are saved in MySQL so I think the server part works as expected.

In the Arduino I have this code for testing:

#include <SPI.h>
#include <Ethernet.h>

byte mac[] = { 0xDE, 0xAD, 0xBE, 0xEF, 0xFE, 0xED };
char server[] = "api.gotfarm.se";    

// Set the static IP address to use if the DHCP fails to assign
IPAddress ip(192, 168, 0, 65);

// Initialize the Ethernet client library
// with the IP address and port of the server
// that you want to connect to (port 80 is default for HTTP):
EthernetClient client;


void setup() {
  // Open serial communications and wait for port to open:
  Serial.begin(9600);
  while (!Serial) {
    ; // wait for serial port to connect. Needed for native USB port only
  }

  // start the Ethernet connection:
  if (Ethernet.begin(mac) == 0) {
    Serial.println("Failed to configure Ethernet using DHCP");
    // try to congifure using IP address instead of DHCP:
    Ethernet.begin(mac, ip);
  }
  // give the Ethernet shield a second to initialize:
  delay(1000);
  //Serial.println("connecting...");
  Serial.println("Test");
  Serial.println("-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-\n");
  Serial.print("IP Address        : ");
  Serial.println(Ethernet.localIP());
  Serial.print("Subnet Mask       : ");
  Serial.println(Ethernet.subnetMask());
  Serial.print("Default Gateway IP: ");
  Serial.println(Ethernet.gatewayIP());
  Serial.print("DNS Server IP     : ");
  Serial.println(Ethernet.dnsServerIP());

  
}

void loop() {


  Serial.println("connecting...");
  
    if (client.connect(server,80)) { // REPLACE WITH YOUR SERVER ADDRESS
      Serial.println("Connected");
      
      client.print("GET /korvtork/kt_save.php?key=7RrlaA2tubH2&SensorTemp=22.20&SensorHum=82.21&SetTemp=20&SetHum=80&Fridge=J&Heat=N&Hum=J&Dehum=N&Log=From Arduino"); 
      client.println(" HTTP/1.1");
      client.print("Host: "); // SERVER ADDRESS HERE TOO
      client.println(server);
      client.println("Connection: close");
      client.println(); 
      client.stop();
      } 
    else {

    // you didn't get a connection to the server:

    Serial.println("--> connection failed/n");

  }

  if (client.connected()) { 
    Serial.println("connected");
    client.stop();  // DISCONNECT FROM THE SERVER
    Serial.println("Client stopped");
  }

  delay(5000);
}

The output in Serial Monitor is:

Test
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

IP Address        : 192.168.1.141
Subnet Mask       : 255.255.255.0
Default Gateway IP: 192.168.1.1
DNS Server IP     : 192.168.1.1
connecting...
Connected

So it seems it get IP and connects to the server.

What do you think cause the problem that the data isn't saved on the server?

      client.println();
      client.stop();

You don't wait for a server response. This way the connection is terminated before the server processed the request. Read the server response. It might contain an error message. Did the server log your request?

pylon:
You don't wait for a server response.

Thanks Pylon for your reply. I removed

client.stop();

and added

  if (client.available()) {
    char c = client.read();
    Serial.print(c);
  }

Now the data is saved to the DB. :slight_smile:

However, the response from server (HTTP/l.l 200 OK etc) is written letter by letter in Serial monitor like this:

connecting...
Connected
connecting...
--> connection failed/n
Hconnecting...
--> connection failed/n
Tconnecting...
--> connection failed/n
Tconnecting...
--> connection failed/n
Pconnecting...
--> connection failed/n
/connecting...
--> connection failed/n
1connecting...
--> connection failed/n
.connecting...
--> connection failed/n
1connecting...

Data is only saved first time. When server respons is written letter by letter together with connection failed nothing is (of course) saved. Why don't I get the server response as a complete message? Guess I need a delay and/or client.stop or something else somewhere because it tries to connect when there already is a connection.

For reference, my updated full loop:

void loop() {

  Serial.println("connecting...");
  
    if (client.connect(server,80)) { // REPLACE WITH YOUR SERVER ADDRESS
      Serial.println("Connected");
      client.print("GET /korvtork/kt_save.php?key=7RrlaA2tubH2&SensorTemp=22.20&SensorHum=82.21&SetTemp=20&SetHum=80&Fridge=J&Heat=N&Hum=J&Dehum=N&Log=Arduino"); 
      client.println(" HTTP/1.1");
      client.print("Host: "); // SERVER ADDRESS HERE TOO
      client.println(server);
      client.println("Connection: close");
      client.println(); 
      
      } 
    else {
      // you didn't get a connection to the server:
      Serial.println("--> connection failed/n");
      }

  if (client.available()) {
    char c = client.read();
    Serial.print(c);
  }

  // if the server's disconnected, stop the client:
  if (!client.connected()) {
    Serial.println();
    Serial.println("disconnecting.");
    client.stop();
  }
  
  delay(3000);
}

if (client.available()) {
char c = client.read();
Serial.print(c);
}

Change your "if" to a "while", that probably helps most time if your server is fast enough.

pylon:
Change your "if" to a "while"

Excellent - it worked. I'm really happy for your help. :slight_smile: