Location of add_data.php

Hi,

Hopefully this is a simple fix as I’m pretty sure I’m 99% there.

I’ve been trying to set up a simple “read temperature data and record on a mySQL database” style project. I’ve been following the tutorial here:

All seems to be working fine in terms of connecting to the database etc, but for some reason no additions are being made to the mySQL table.

My php files are fine I believe, as if I write a manual “add_data.php?serial=xxxxxxxx…” etc string to the browser I can then see the record appear in the mySQL table.

My arduino code is as follows:

#include <UIPEthernet.h> // Used for Ethernet

// **** ETHERNET SETTING ****
// Arduino Uno pins: 10 = CS, 11 = MOSI, 12 = MISO, 13 = SCK
// Ethernet MAC address - must be unique on your network - MAC Reads T4A001 in hex (unique in your network)
byte mac[] = { 0x54, 0x34, 0x41, 0x30, 0x30, 0x31 };                                       
// For the rest we use DHCP (IP address and such)

EthernetClient client;
char server[] = "audiovisualelectroni.ipagemysql.com"; // IP Adres (or name) of server to dump data to
int  interval = 5000; // Wait between dumps

void setup() {

  Serial.begin(9600);
  Ethernet.begin(mac);

  Serial.println("Tweaking4All.com - Temperature Drone - v2.0");
  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() {
  // if you get a connection, report back via serial:
  if (client.connect(server, 80)) {
    Serial.println("-> Connected");
    // Make a HTTP request:
    client.print( "GET /add_data.php?");
    client.print("serial=");
    client.print( "288884820500006X" );
    client.print("&&");
    client.print("temperature=");
    client.print( "12.3" );
    client.println( " HTTP/1.1");
    client.print( "Host: " );
    client.println(server);
    client.println( "Connection: close" );
    client.println();
    client.println();
    client.stop();
    
    
    Serial.println( "GET /add_data.php?");
    Serial.println("serial=");
    Serial.println( "288884820500006X" );
    Serial.println("&&");
    Serial.println("temperature=");
    Serial.println( "12.3" );
    Serial.println( " HTTP/1.1");
    Serial.println( "Host: " );
    Serial.println(server);
    Serial.println( "Connection: close" );
    Serial.println();
    Serial.println();
    client.stop();
    
  }
  else {
    // you didn't get a connection to the server:
    Serial.println("--> connection failed/n");
  }

  delay(interval);
}

I’ve modified the standard code slightly as I was having issues, so added in some serial.println lines to see if I could debug. This has shown me that the connection is being made to the server successfully as I can see “Connected” appear in the serial monitor.

BUT - I think the reason no records are being added are because of the following line, in that it can’t find the “add_data.php” file on my webspace.

I have put the add_data.php file the root of the webspace but to no avail.

Is anybody able to help please?

For reference, example add-data command as below (copy and paste into browser):

http://aveshop.co.uk/add_data.php?serial=123456789012345A

And once added, the added data can be seen at:

http://aveshop.co.uk/review_data.php

Thanks in advance!!! :smiley: :wink:

Your code says:

char server[] = "audiovisualelectroni.ipagemysql.com";

Is that correct?

I believe so, I have hosting with ipage, and there was an online wizard which generated the code for the dbconnect.php file referenced within the code above.

That auto generated dbconnect.php stated my mysql server address to be the ipage address referenced here, and since connecting/adding records is successful if I perform the add_data task manually by typing into the browser (ie this gives a successful connection into the database), I'd be inclined to believe this bit is ok.

I wonder whether it's the line with:

client.print( "GET /add_data.php?");

...that is the one causing the problem. The add_data.php file is on the root of my webspace so I thought I wouldn't have to put any further navigating parameters before the "/" on this line, but I wonder if this is right?

Debugging with serial.println suggests a connection is being achieved ok, and I can perform a manual add fine, it's just the automated one that doesn't work. That would say to me that the script can't find the add_data.php file properly otherwise it would run it. If it can't find the file, my address parameters might be wrong?

You might be right that it's the ipage address that's wrong though; if so any idea what it should be?? (I've deliberately left security on the system low for the time being so feel free to go in and add records/delete records from the mysql tables for trial and error if that helps).

root@zippo:~# telnet audiovisualelectroni.ipagemysql.com 80
Trying 103.224.212.250...
Connected to audiovisualelectroni.ipagemysql.com.
Escape character is '^]'.
GET /add_data.php HTTP/1.0
Host: audiovisualelectroni.ipagemysql.com

HTTP/1.1 302 Found
Date: Fri, 08 May 2015 04:15:47 GMT
Server: Apache
X-Powered-By: PHP/5.4.39-0+deb7u2
Location: http://ww1.audiovisualelectroni.ipagemysql.com/add_data.php
Content-Length: 0
Connection: close
Content-Type: text/html; charset=UTF-8
X-Pad: avoid browser bug

Connection closed by foreign host.

"audiovisualelectroni.ipagemysql.com" resolves to a Trellian network (I assume that's your ISP). When a request to that domain is submitted it results in a redirect to "ww1.audiovisualelectroni.ipagemysql.com" which resolves to a Sedo Parking network, however. Sedo is a company that hosts orphan domains for resale and advertising purposes. Any thoughts on why that is occurring? And by "thoughts" I mean this isn't an Arduino or code problem but something is fishy with that host.

As an aside, if you use "HTTP/1.0" instead of "HTTP/1.1" then you can skip the "Connection: close" part.

I'm pretty sure if you just change your server to "aveshop.co.uk" that all of your problems will go away.

Thanks Chagrin for both replies, I'll make changes with regards to both points this evening and hopefully all goes well.

To confirm, when you say "change your server to "aveshop.co.uk" ", I'm guessing you mean:

char server[] = "audiovisualelectroni.ipagemysql.com"

Yes, that's what I meant.

Hi Chagrin, thanks that helped, so I moved on to the next level (ie trying to feed in real data from the sensor, DHT11, rather than dummy data as above).......and it's fallen over again!

My revised code to take readings for the sensor and post to a different mySQL table is follows (I've essentially mashed together various examples available on the internet, whilst trying to understand the previous example you helped me with - trying to take it apart and understand each line, before modifying to get what you see below:

#include <UIPEthernet.h> // Used for Ethernet
#include <DHT.h>
#include <SPI.h>

byte mac[] = { 0x54, 0x34, 0x41, 0x30, 0x30, 0x31 };   // RESERVED MAC ADDRESS
EthernetClient client;

#define DHTPIN 2 // SENSOR PIN
#define DHTTYPE DHT11 // SENSOR TYPE - THE ADAFRUIT LIBRARY OFFERS SUPPORT FOR MORE MODELS
DHT dht(DHTPIN, DHTTYPE);

long previousMillis = 0;
unsigned long currentMillis = 0;
long interval = 10000; // READING INTERVAL

int t = 0; // TEMPERATURE VAR
int h = 0; // HUMIDITY VAR
String data;


char server[] = "aveshop.co.uk"; // IP Adres (or name) of server to dump data to
int  interval1 = 15000; // Wait between dumps

void setup() { 
 Serial.begin(9600);
        Ethernet.begin(mac);
        
        dht.begin(); 
 delay(10000); // GIVE THE SENSOR SOME TIME TO START

 h = (int) dht.readHumidity(); 
 t = (int) dht.readTemperature(); 

 data = "";
        
        
        
        Serial.println("Temperature and Humidity reading");
        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());

 if (Ethernet.begin(mac) == 0) {
 Serial.println("Failed to configure Ethernet using DHCP"); 
 }


}

void loop(){

 currentMillis = millis();
 if(currentMillis - previousMillis > interval) { // READ ONLY ONCE PER INTERVAL
 previousMillis = currentMillis;
 h = (int) dht.readHumidity();
 t = (int) dht.readTemperature();
 }

 //data = "temp1=" + t + "&hum1=" + h;  // [THIS WASN'T WORKING SO EDITED OUT, INSTEAD THE BELOW FIVE LINES WERE USED]

        data+="";   //IE THESE FIVES LINES STARTING HERE
        data+="temp1=";
        data+=(int) dht.readTemperature();
        data+="&hum1=";
        data+=(int) dht.readHumidity(); //AND FINISHING HERE.

        
        Serial.print("Temperature: ");
        Serial.println(t);
        Serial.print("Humidity: ");
        Serial.println(h);
        Serial.print("Therefore, combined 'data' value of: ");
        Serial.println(data);



        Enc28J60.init(mac);

 if (client.connect(server, 80)) { // REPLACE WITH YOUR SERVER ADDRESS
 Serial.println("-> Connected");

                //Make a HTTP request
                client.print( "GET /add_data1.php?"); 
 client.print("temp1=");
                client.print(t);
                client.print("&");
                client.print("hum1=");
                client.print(h);
                client.println( " HTTP/1.1");
                client.print( "Host: "); // SERVER ADDRESS HERE TOO
                client.println(server);
                client.println( "Connection: close" );
                client.println();
                client.println();
 //client.println("Content-Type: application/x-www-form-urlencoded"); 
 //client.print("Content-Length: "); 
 //client.println(data.length()); 
 //client.println(); 
 //client.print(data); 
                
                //PRINT TO SERIAL FOR DEBUGGING (MAKE SURE SAME AS ABOVE)
                Serial.print( "GET /add_data1.php?"); 
 Serial.print("temp1=");
                Serial.print(t);
                Serial.print("&");
                Serial.print("hum1=");
                Serial.println(h);
                Serial.println( " HTTP/1.1");
                Serial.print("Host: "); // SERVER ADDRESS HERE TOO
                Serial.println(server);
                Serial.println( "Connection: close" );
                Serial.println();
                Serial.println();
                
                
                data = "";  //clear data value for next loop
                Serial.print("Data value cleared for next loop to value of:");
                Serial.println(data);
                Serial.println("Loop ended");
                Serial.println("..............................");
                
 } 

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

 }

 delay(interval); // WAIT INTERVAL TIME BEFORE SENDING AGAIN
}

Once again, if I do a manual post, ie:

http://aveshop.co.uk/add_data1.php?temp1=200&hum1=300

....that will work fine and add a record.

When I try to automate it however, it doesn't work.

Trying to take apart this revised code, I would have thought that the correct string is now being passed and therefore should have the same effect as if I sat there any wrote it into the browser manually as above...but unfortunately that's not the case.

In order to try to debug myself as much as possible, I've included lots of Serial.print lines so that I can see what is being generated, and whether there are any failures like not being able to connect to the internet etc, but as can be seen from the Serial Monitor log when running this sketch below, it certainly looks like I've formatted things to replicate the manual request (and therefore it should post but it hasn't):

Temperature and Humidity reading
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

IP Address        : 192.168.0.14
Subnet Mask       : 255.255.255.0
Default Gateway IP: 192.168.0.1
DNS Server IP     : 192.168.0.1
Temperature: 24
Humidity: 40
Therefore, combined 'data' value of: temp1=24&hum1=40
-> Connected
GET /add_data1.php?temp1=24&hum1=40
Data value cleared for next loop to value of:
Loop ended
..............................
Temperature: 24
Humidity: 40
Therefore, combined 'data' value of: temp1=24&hum1=40
-> Connected
GET /add_data1.php?temp1=24&hum1=40
Data value cleared for next loop to value of:
Loop ended
..............................
Temperature: 24
Humidity: 40
Therefore, combined 'data' value of: temp1=24&hum1=40
-> Connected
GET /add_data1.php?temp1=24&hum1=40
Data value cleared for next loop to value of:
Loop ended
..............................

Thanks for all your help so far, once again I'm sure it's just something silly but despite staring at the screen for a couple of hours I can't see where the process is failing.

Any ideas?

All I see is one too many println()s:

               client.println( "Connection: close" );
                client.println();
                client.println();

I wouldn't expect that to cause a problem but y'know.

It's times like these when access to the web server's error log would be extremely useful.

Thanks Chagrin, that was sending me mad - just spent another 10 minutes looking at it and saw that the only thing that I didn’t see a reason for was the:

Enc28J60.init(mac);

line.

I got rid of that and everything works. I’ve later googled that line and realised it resets the network board, so I’ve got a feeling I’d get a nice connection to the database, only to go and reset the network adapter again before trying to send data!