PHP, MySQL and Pushing Data?

Projects Networking, Protocols, and Devices
1

I have a simple device that reads a RFID card and I want to put that information to the web. I am having trouble getting the arduino to connect/push the data.
I know the server/php/mysql is working because I can manually add data from the browser e.g.
http://www.storemyRFIDcard.com//add.php?cardUID=05 5D 72 D4&&cardPresent=1&&macAddress=11:11:11:11:11:11&&dateTime=2014-03-1 01:01:01
When I add from the browser, everything shows up on the page as expected.

Additionally, the Ethernet is working because I can get NTP time and run various sketches. The below code is pulled from the WebClient example. I just changed the server and GET lines.

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

byte mac[] = { 0x11, 0x11, 0x11 0x11, 0x11, 0x11 };
char server[] = "http://www.storemyRFIDcard.com/"; 

IPAddress ip(192,168,0,177);

EthernetClient client;

void setup() {
  Serial.begin(9600);
   while (!Serial) {
    ; // wait for serial port to connect. Needed for Leonardo only
  }

  // start the Ethernet connection:
  if (Ethernet.begin(mac) == 0) {
    Serial.println("Failed to configure Ethernet using DHCP");
    Ethernet.begin(mac, ip);
  }
  // give the Ethernet shield a second to initialize:
  delay(1000);
  Serial.println("connecting...");

  // if you get a connection, report back via serial:
  if (client.connect(server, 80)) {
    Serial.println("connected");
    client.println("GET /add.php?&cardUID=05 5D 72 D4&&cardPresent=1&&macAddress=11:11:11:11:11:11&&dateTime=2014-03-1 03:04:05");
    client.println("Host: www.storemyRFIDcard.com");
    client.println("Connection: close");
    client.println();
  } 
  else {
    Serial.println("connection failed");
  }
}

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

  if (!client.connected()) {
    Serial.println();
    Serial.println("Done");
    client.stop();

    // do nothing forevermore:
    while(true);
  }
}

I know its not secure/best practice but if I could simply get the arduino to do the same thing as the manually adding through the webpage I'd be happy. Any observations/thoughts/ideas?

2

When I add from the browser, everything shows up on the page as expected.

Because your web browser url encodes that string for you. It becomes something like this. Note the spaces have become %20. This is called "url encoding". I removed the double ampersands.

http://www.storemyrfidcard.com//add.php?cardUID=05%205D%2072%20D4&cardPresent=1&macAddress=11:11:11:11:11:11&dateTime=2014-03-1%2001:01:01
3

PBear:
if (client.connect(server, 80)) {
Serial.println("connected");
client.println("GET /add.php?&cardUID=05 5D 72 D4&&cardPresent=1&&macAddress=11:11:11:11:11:11&&dateTime=2014-03-1 03:04:05 HTTP/1.1");
client.println("Host: www.storemyRFIDcard.com");
client.println("Connection: close");
client.println();
}

You forgot the "HTTP/1.1" above; I've added it. But I suggest you do:

PBear:
if (client.connect(server, 80)) {
Serial.println("connected");
client.println("GET /add.php?&cardUID=05 5D 72 D4&&cardPresent=1&&macAddress=11:11:11:11:11:11&&dateTime=2014-03-1 03:04:05 HTTP/1.0");
client.println("Host: www.storemyRFIDcard.com");
client.println();
}

Switch to HTTP/1.0 so you don't have to worry about persistent connections. Should probably keep the "%20" space encoding suggested by SurferTim or (even better) eliminate the need for those spaces in your add.php script.

4

Thanks for the suggestions, but I still can't get anything to push. This is what I have:

client.println("GET /add.php?&cardUID=05&205D&2072&20D4&cardPresent=1&macAddress=56:34:AC:45:C7:67&dateTime=2014-03-1&2003:04:06 HTTP/1.0");

Nothing shows up in the Sql, not even partial fields. Any other suggestions?

5

the URL addition works in Chrome (in Privacy Mode)/IE so I don't think its an HTMLaccess/permission issue.

Here is the add.php code

<?php
   include("conec.php");
   $link=Conection();
$Sql="INSERT INTO  `a7557477_tmp2`.`fileFlow` (`cardUID` ,`cardPresent`, `macAddress` ,`dateTime`)
VALUES ('".$_GET["cardUID"]."', '".$_GET["cardPresent"]."', '".$_GET["macAddress"]."', '".$_GET["dateTime"]."')";     
   mysql_query($Sql,$link);
   header("Location: insertareg.php");
?>
6

Is this your LAMP server or a contracted virtual hosting server?
What does the server return as a response?

7

I'm just using a free/virtual one (000webhost.com) for testing. I'm not seeing any response from the server. How can I check this? Thanks.

8

First, change the request to a simple page on that server that does return a response. Insure you are going to the correct website.

Second, I don't see on that add.php page where it gets the values for the variables you sent. I can't tell what is on conect.php, and you are sending a Location value of insertareg.php. ??

<?php
  $cardUID = $_GET['cardUID'];
?>
9

I'm just pulling code from examples and removed the reference to insertareg.php. Now it seems to be working and I'm getting a response from the server. Thanks for the help, I really appreciate it.

10

Here is my URI encoder you might find useful ( also called percent encoding ). If there is binary data to send I have a Base64 encoder too. Will make it easy to create a URL.
http://forum.arduino.cc/index.php?topic=202264.0