Problem: Storing data on online database using EtherCard lib

Projects Programming
1

Greetings all!

After reading through examples and projects, it has come to my notice that I can use the WebClient example in the EtherCard lib to store data in my MySQL database. But I am still not able to store the data. I am able to display the data on a webpage.

How do I store my data?

I have edited the example code for my project work to display the distance from an ultrasonic sensor to my database.

Could you please help?

Below is the Arduino Code

#include <EtherCard.h>

#define trigPin 23
#define echoPin 22

long duration;
int distance;

static byte mymac[] = { 0x74,0x69,0x69,0x2D,0x30,0x31 };

byte Ethernet::buffer[700];
static uint32_t timer;

static byte session_id;

const char website[] PROGMEM = "localhost";

static void my_callback (byte status, word off, word len) {
  Serial.println(">>>");
  Ethernet::buffer[off+300] = 0;
  Serial.print((const char*) Ethernet::buffer + off);
  Serial.println("...");
}

void setup () {
  Serial.begin(57600);
  Serial.println(F("\n[webClient]"));

  pinMode(trigPin, OUTPUT);
  pinMode(echoPin, INPUT);
  
  if (ether.begin(sizeof Ethernet::buffer, mymac, 53) == 0) 
    Serial.println(F("Failed to access Ethernet controller"));
  if (!ether.dhcpSetup())
    Serial.println(F("DHCP failed"));

  ether.printIp("IP:  ", ether.myip);
  ether.printIp("GW:  ", ether.gwip);  
  ether.printIp("DNS: ", ether.dnsip);  

#if 1
  if (!ether.dnsLookup(website))
    Serial.println("DNS failed");
#elif 2
  // then use it directly. Note: the string can not be in PROGMEM.
  char websiteIP[] = "192.168.1.2";
  ether.parseIp(ether.hisip, websiteIP);
#else
  // or provide a numeric IP address instead of a string
  byte hisip[] = { 192,168,1,2 };
  ether.copyIp(ether.hisip, hisip);
#endif
    
  ether.printIp("SRV: ", ether.hisip);
}

void loop () {
  ether.packetLoop(ether.packetReceive());
  
  if (millis() > timer) {
    timer = millis() + 5000;
    Serial.println();
    Serial.print("<<< REQ ");
    ether.browseUrl(PSTR("/writedata.php/"), distance, website, my_callback);
  }
}

Below is my php code

<?php

   $link = mysqli_connect("localhost", "root", "microgo777", "test");
if($link === false){
    die("ERROR: Could not connect. " . mysqli_connect_error());
}

$sql = "INSERT INTO sensor (value) VALUES ('".$_GET["distance"]."')";

if(mysqli_query($link, $sql)){
    echo "<center> Records inserted successfully</center>";
} else{
    echo "ERROR: Could not able to execute $sql. " . mysqli_error($link);
}
mysqli_close($link);
?>
2

I am able to display the data on a webpage.

That means that your Arduino is acting as a server, and the browser is acting as a client (the only role it can have).

You store data in a database by making a GET request, which is something only a client does.

With a W5x00 chip, which handles a lot more of the work, you can have the Arduino act as server and still be able to act as a client when needed. I do not know the ENCJ ethernet shield can act as client and server, though I have my doubts.

Not that any of this matters, because your code proves that your Arduino is NOT acting as a server, so that first statement is irrelevant.

const char website[] PROGMEM = "localhost";

That is so dumb. localhost is a name that has special meaning. It means that the client and the server are running on the same machine. The Arduino is NOT running a MySQL database, php, and acting as a client.

Your client is NOT trying to connect to itself. So, you need to use a different name.

3

WAIT A MIN!

THIS IS WHAT I HAVE UNDERSTOOD SO FAR.

THE ARDUINO HAS TO BEHAVE AS THE SERVER. Please tell me I have understood right.

I am admiring the way you guide.

4

Please tell me I have understood right.

If you want the Arduino to request that a server store some data, it does that by making a GET request, which is a client role. So, no, the Arduino does not need to act as a server, so, no, you did not understand correctly.

5

Okay, so the Arduino has to be a client and send data to the server. This has to be done by making the Arduino do a Get request.

My server is 192.168.1.3.

So the Arduino has to make a GET request to send the data to the server.

Now am I right?

6

Now am I right?

Yes.

7

Awesome awesome awesome.

Thank you again.

Now the real question, how do I make the GET request and send the data to the server?

There are example codes but I have no idea how to use them.

// This demo does web requests to a fixed IP address, using a fixed gateway.
// 2010-11-27 <jc@wippler.nl> http://opensource.org/licenses/mit-license.php

#include <EtherCard.h>

#define REQUEST_RATE 5000 // milliseconds

// ethernet interface mac address
static byte mymac[] = { 0x74,0x69,0x69,0x2D,0x30,0x31 };
// ethernet interface ip address
static byte myip[] = { 192,168,1,2 };
// gateway ip address
static byte gwip[] = { 192,168,1,1 };
// remote website ip address and port
static byte hisip[] = { 192, 168, 1, 3 };
// remote website name
const char website[] PROGMEM = "google.com";

byte Ethernet::buffer[300];   // a very small tcp/ip buffer is enough here
static long timer;

// called when the client request is complete
static void my_result_cb (byte status, word off, word len) {
  Serial.print("<<< reply ");
  Serial.print(millis() - timer);
  Serial.println(" ms");
  Serial.println((const char*) Ethernet::buffer + off);
}

void setup () {
  Serial.begin(57600);
  Serial.println("\n[getStaticIP]");
  
  if (ether.begin(sizeof Ethernet::buffer, mymac, 53) == 0) 
    Serial.println( "Failed to access Ethernet controller");

  ether.staticSetup(myip, gwip);

  ether.copyIp(ether.hisip, hisip);
  ether.printIp("Server: ", ether.hisip);

  while (ether.clientWaitingGw())
    ether.packetLoop(ether.packetReceive());
  Serial.println("Gateway found");
  
  timer = - REQUEST_RATE; // start timing out right away
}

void loop () {
  ether.packetLoop(ether.packetReceive());
  
  if (millis() > timer + REQUEST_RATE) {
    timer = millis();
    Serial.println("\n>>> REQ");
    ether.browseUrl(PSTR("/foo/"), "bar", website, my_result_cb);
  }
}
8

Now the real question, how do I make the GET request and send the data to the server?

You need to ask yourself some questions. Is google.com a client or a server? Do you ask it for information, or does it ask for information? Which role does a client play? Which roles does a server play?

If you agree that google.com is a server, then you should be able to recognize that the code you posted is having the Arduino act as a client. And, you should be able to recognize that you need to replace that server's name with your server's name.

The ether.browseUrl() method call's first two arguments contain the name of the script to execute and the data to pass to the script, though the example does a piss-poor job of illustrating or explaining that.

The documentation that I found for the method makes it even worse, by referring to the arguments as path and file.

The first argument should be the name of the script to execute. The second argument should be the data to pass to the script.

If you were to enter http://my.server.com/saveData.php?temp=26&hum=81 in the address bar of a browser, to pass temp and hum, with values of 26 and 81, to the saveData.php scrip on my.server.com, making the Arduino make the same request would involve having the first argument to the browseUrl() method be "/saveData.php" and the second argument would be "temp=26&hum=81".

If you wanted to pass variable data to the script, you could have

   int temp=26;
   int hum = 81;

   char scriptData[80];

   sprintf(scriptData, "temp=%d&hum=%d, temp, hum);

and the second argument would be scriptData, rather than a hardcoded string.

9

Thank you again, for your time and patience. I am super grateful.

I do have more questions.

As for the server, I am using the Wamp Server for the database and not any website. When I type "Localhost" or the computer's IP "192.168.1.3" on the address bar which opens the Wamp Server Homepage.

In such a case, what should be the value of :

const char website[] PROGMEM = "";

192.168.1.3/writedata.php?distance=10 stores 10 into the database

My script is writedata.php.

I want to print distance values measured by the ultrasonic sensor HC-SR04.

Instead of setting distance values, I would like to print whatever value it senses at that moment.

Would the following code be correct?

int distance;
char scriptData[80];
sprintf(scriptData, "distance = %d", distance);
ether.browseUrl(PSTR("/writedata.php"), scriptData, website, my_result_cb);
10

In such a case, what should be the value of

The website name is a bit confusing. It is supposed to be the server name along with whatever path is required to locate the script.

In your case, the value should be "192.168.1.3", since the script is located at the root of the server directory.

Instead of setting distance values, I would like to print whatever value it senses at that moment.

Isn't what it "senses" a distance?

Would the following code be correct?

Almost. That can work, but look at typical URL when googling or posting on the forum. No spaces in the URLs...

Spaces need to be handled correctly. Far simpler to just not have spaces.

sprintf(scriptData, "distance=%d", distance);

The php script doesn't need the spaces. The server doesn't need the spaces.

Try that, and see what happens.

11

Hello Paul,

I tried them out, but nothing yet.

I feel like the server is not listening to the Arduino or the Arduino is not connecting properly to the server. I also feel like my PHP code could be wrong. I am eating my fingers, ripping my face and breaking my head over this.

12

Clearly, you control the entire process - the client side, making GET requests, and the server side, where the PHP scripts run and the database lives.

Post the code you have now, and the serial monitor output.

Look at the web server logs. Does the Arduino make a connection attempt? What did the server do with the GET request?

You can debug the PHP script, with echo statements.

I presume that you have tried executing the PHP script by making a web browser make the equivalent call to the script. If not, THAT is the next thing to do.