Send data to mysql through php using ethernet shield (HR911105A)

Projects Networking, Protocols, and Devices
1

I have been trying to send values to a php file stored in a free hosting to save those in a database using the GET protocol.
I have tried many arduino aproachs with no good results.

Here is my arduino code:

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

// **** ETHERNET SETTING ****
// Ethernet MAC address - must be unique on your network - MAC Reads T4A001 in hex (unique in your network)
byte mac[] = { 0xDE, 0xAD, 0xBE, 0xEF, 0xFE, 0xED };
// For the rest we use DHCP (IP address and such)

EthernetClient client;
char server[] = "trobadagegantscat-redirect.epizy.com"; // IP Adres (or name) of server to dump data to

int user = 999;

void setup() {
  Serial.begin(9600);
  Serial.println("Starting Arduino");
  if(Ethernet.begin(mac) == 0){
    Serial.println("Failed to configure Ethernet using DHCP");
    while(true){}
  }

  delay(2000);
 
  Serial.println("Conecting...");
  if (client.connect(server, 80)) {
      Serial.println("-> Connected");
      // Make a HTTP request:
      client.print( "GET /DBTest/phpform.php?user=");
      client.print(user);
      client.println(" HTTP/1.0");
      client.println();

      Serial.println("Stuff done");
    }
    else {
      // you didn't get a connection to the server:
      Serial.println("--> connection failed/n");
    }
}

void loop() {
}

And here is my php code:

<?php
        $idToStore = $_GET["user"];
        
        $servername = "sql210.epizy.com";
        $username = "********";
        $password = "******";
        $dbName = "epiz_25053248_ControlHorario";

        // Create connection
        $conn = new mysqli($servername, $username, $password, $dbName);

        // Check connection
        if ($conn->connect_error) {
            die("Connection failed: " . $conn->connect_error);
        }
		
		$currentDate = date("Y-m-d H:i:s");
		$query = "INSERT INTO `Check in` (User, Time, Start) VALUES ($idToStore, \"$currentDate\", true)";
        $conn->query("$query");
		
        
        $conn->close();
?>

If i execute the php through my internet browser adding the value user in the url the value is properly stored in the database but the arduino is not working.

I can't figure out which is the difference since the php code is the same and im sending the variable the same way.

Thanks!

2

Maybe your Ethernet shield is not working right.

Have you tried example sketch to test it?

.

3

ieee488:
Maybe your Ethernet shield is not working right.

Have you tried example sketch to test it?

Yeah, it connects properly to webs. Even in my code the "Connected" print is done.

4

Try creating a single client.print( ) with the user hardcoded.

5

"and im sending the variable the same way."

So how do you know that? Is perhaps the host site you are connecting to HTTPS?

6

ieee488:
Try creating a single client.print( ) with the user hardcoded.

I wrote this

client.println( "GET /DBTest/phpform.php?user=999 HTTP/1.0");

Nothing.

7

rattaspi:
I wrote this

client.println( "GET /DBTest/phpform.php?user=999 HTTP/1.0");

Nothing.

How do you know that matches what is being sent when you use a web browser ?

8

ieee488:
How do you know that matches what is being sent when you use a web browser ?

Well, im supposing it. Is there any way to debug what is happening? This is kinda frustrating.

9

There probably is, but it is not something I know well.
When in doubt I Google.
Good luck.

.

10

Another working implementation...

//Revision: 7th March
//Revision by: Martin Chlebovec (martinius96)
//Web: https://arduino.php5.sk?lang=en
#include <SPI.h>
#include <Ethernet.h>

// **** ETHERNET SETTING ****
// Ethernet MAC address - must be unique on your network - MAC Reads T4A001 in hex (unique in your network)
byte mac[] = { 0xDE, 0xAD, 0xBE, 0xEF, 0xFE, 0xED };
// For the rest we use DHCP (IP address and such)

EthernetClient client;
char* host = "trobadagegantscat-redirect.epizy.com"; // IP Adres (or name) of server to dump data to

int user = 999;

void setup() {
  Serial.begin(9600);
  Serial.println("Starting Arduino");
  if (Ethernet.begin(mac) == 0) {
    Serial.println("Failed to configure Ethernet using DHCP");
    while (true) {}
  }

  delay(2000);

  Serial.println("Conecting...");
  client.stop();
  if (client.connect(host, 80)) {
    Serial.println("-> Connected");
    String url = "/DBTest/phpform.php?user=" + String(user);
    client.print(String("GET ") + url + " HTTP/1.0\r\n" + "Host: " + host + "\r\n" + "User-Agent: ArduinoEthernet\r\n" + "Connection: close\r\n\r\n");
    Serial.println("Stuff done");
    while (client.connected()) {
      String line = client.readStringUntil('\n');
      Serial.println(line); //parse HTTP header
      if (line == "\r") {
        break;
      }
    }
  }
  else {
    // you didn't get a connection to the server:
    Serial.println("--> connection failed/n");
  }
}

void loop() {
}

And also... Sometimes i had problem with Ethernet shield, when PHP didn't print anything...
By using only echo "OK"; in PHP it was solved... Arduino shows before it is connected, but request wasn't made.

11

martinius96:
Another working implementation...

//Revision: 7th March

//Revision by: Martin Chlebovec (martinius96)
//Web: https://arduino.php5.sk?lang=en
#include <SPI.h>
#include <Ethernet.h>

// **** ETHERNET SETTING ****
// Ethernet MAC address - must be unique on your network - MAC Reads T4A001 in hex (unique in your network)
byte mac[] = { 0xDE, 0xAD, 0xBE, 0xEF, 0xFE, 0xED };
// For the rest we use DHCP (IP address and such)

EthernetClient client;
char* host = "trobadagegantscat-redirect.epizy.com"; // IP Adres (or name) of server to dump data to

int user = 999;

void setup() {
  Serial.begin(9600);
  Serial.println("Starting Arduino");
  if (Ethernet.begin(mac) == 0) {
    Serial.println("Failed to configure Ethernet using DHCP");
    while (true) {}
  }

delay(2000);

Serial.println("Conecting...");
  client.stop();
  if (client.connect(host, 80)) {
    Serial.println("-> Connected");
    String url = "/DBTest/phpform.php?user=" + String(user);
    client.print(String("GET ") + url + " HTTP/1.0\r\n" + "Host: " + host + "\r\n" + "User-Agent: ArduinoEthernet\r\n" + "Connection: close\r\n\r\n");
    Serial.println("Stuff done");
    while (client.connected()) {
      String line = client.readStringUntil('\n');
      Serial.println(line); //parse HTTP header
      if (line == "\r") {
        break;
      }
    }
  }
  else {
    // you didn't get a connection to the server:
    Serial.println("--> connection failed/n");
  }
}

void loop() {
}



And also... Sometimes i had problem with Ethernet shield, when PHP didn't print anything... 
By using only echo "OK"; in PHP it was solved... Arduino shows before it is connected, but request wasn't made.

I tried your approach and it does not work for me. I have removed all the "echo" from my php file adding at the end an "echo "FINISH"".
The php keeps working on my web browser but my arduino have the same issue.

The HTTP parser you added to the code displays that:

15:10:47.081 -> Starting Arduino
15:10:52.608 -> Conecting...
15:10:52.709 -> -> Connected
15:10:52.709 -> Stuff done
15:10:52.743 -> HTTP/1.1 200 OK

15:10:52.743 -> Server: nginx

15:10:52.776 -> Date: Mon, 09 Mar 2020 14:10:31 GMT

15:10:52.809 -> Content-Type: text/html

15:10:52.844 -> Content-Length: 874

15:10:52.878 -> Connection: close

15:10:52.878 -> Vary: Accept-Encoding

15:10:52.911 -> Expires: Thu, 01 Jan 1970 00:00:01 GMT

15:10:52.944 -> Cache-Control: no-cache

15:10:52.978 ->
12

so it is working, you connected to server. lol

13

martinius96:
so it is working, you connected to server. lol

I know the connection is successful but the info is not being stored into the database.

14

rattaspi:
I know the connection is successful but the info is not being stored into the database.

put some echo "OK"; to your PHP code at end and it will work. I had similar problem. I get HTTP header, but datas were not saved...