Connect Arduino to MySQL database online problem

Question

I'm trying to connect Arduino with MySQL database on my online server.

I manage my project to work on WampServer locally on localhost, but when I try to move everything online I have problem... on the serial monitor I get the message that the server is connected and my sensor readings, but I can't put them in to my database on my online sever.

Here is my code:

---

dbconnect.php

<?php
$MyUsername = "*******";  // enter your username for mysql
$MyPassword = "*******%";  // enter your password for mysql
$MyHostname = "*************";      // this is usually "localhost" unless your database resides on a different server

$dbh = mysql_pconnect($MyHostname , $MyUsername, $MyPassword);
$selected = mysql_select_db("tanjamayaarduino",$dbh);
?>

---

add_data.php

<?php
    // Connect to MySQL
    include("dbconnect.php");

    // Prepare the SQL statement
    $SQL = "INSERT INTO tanjamayaarduino.temperature (sensor ,celsius, light, moisture1, moisture2) VALUES ('".$_GET["serial"]."', '".$_GET["temperature"]."','".$_GET["light"]."','".$_GET["moisture1"]."','".$_GET["moisture2"]."')";     

    // Execute SQL statement
    mysql_query($SQL);

    // Go to the review_data.php (optional)
    header("Location: review_data.php");
?>

---

Arduino code

#include<Ethernet.h>
#include<SPI.h>
const int temperaturePin = A3;    //LM35 Temperature sensor
const int lightPin = A2;          //LDR photoresistor sensor
const int moisture1Pin = A4;          //Moisture1 sensor
const int moisture2Pin = A5;          //Moisture2 sensor

// **** 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[] = { 0x90, 0xA2, 0xDA, 0x0F, 0x3A, 0xDC };
// For the rest we use DHCP (IP address and such)
IPAddress ip(192, 168, 2, 121);
EthernetClient client;
//IPAddress server(192, 168, 2, 100); // IP Adres (or name) of server to dump data to  
// IP Adres (or name) of server to dump data to  (godaddy baza server 37.148.204.140) (User: tanjamayaarduino@188.121.42.33) (goddady glavna adresa na hostingot 188.121.46.1) 
//IPAddress server(188, 121, 46, 1); 
char server[] = "188.121.46.1";


int  interval = 5000; // Wait between dumps

void setup() {

  Serial.begin(9600);
  while (!Serial) {
    ; // wait for serial port to connect. Needed for Leonardo only
  }
  if (Ethernet.begin(mac) == 0) {
    Serial.println("Failed to configure Ethernet using DHCP");
    // no point in carrying on, so do nothing forevermore:
    // try to congifure using IP address instead of DHCP:
    Ethernet.begin(mac, ip);
  }


  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)==1) {
    float tem = getTemp();
    Serial.println( tem );
    float lig = getLight();
    Serial.println( lig );
    float mois1 = getMoisture1();
    Serial.println( mois1 );
    float mois2 = getMoisture2();
    Serial.println( mois2 );

    Serial.println("-> Connected");
    if (client.connected()) {
      // Make a HTTP request:
      client.print( "GET /advertacs/test/arproekt/add_data.php?");
      Serial.println("-> add_data");
      client.print("serial=");
      client.print( "TempSensor" );
      client.print("&&");
      client.print("temperature=");
      client.print( tem);
      Serial.println("-> add_temp");
      client.print("&&");
      client.print("light=");
      client.print( lig);
      Serial.println("-> add_light");
      client.print("&&");
      client.print("moisture1=");
      client.print( mois1);
      Serial.println("-> add_mois1");
      client.print("&&");
      client.print("moisture2=");
      client.print( mois2);
      Serial.println("-> add_moi2");

      client.println( " HTTP/1.1");
      client.println( "Host:" );
      client.println(server);
      client.println( "Connection: close" );
      client.println();
      client.println();
      client.stop();
    }
  }
  else {
    // you didn't get a connection to the server:
    Serial.println("--> connection failed/n");
  }

  delay(interval);
}
float getTemp() {
  float temperatureC = (5.0 * analogRead(temperaturePin) * 100.0) / 1024; //converting from a 0 to 1023 digital range to 0 to 5 volts (each 1 reading equals ~ 5 millivolts
  return temperatureC;
}
float getLight() {
  float light;
  light = analogRead(lightPin) ;
  return light;
}

float getMoisture1() {
  float moisture1;
  moisture1 = analogRead(moisture1Pin) ;
  return moisture1;
}
float getMoisture2() {
  float moisture2;
  moisture2 = analogRead(moisture2Pin) ;
  return moisture2;
}

Answer 1

There are two basic things wrong with your code. First is the formatting of your headers - you have mistakenly used client.println for sending the first part of the Host: header, so it becomes split onto two lines:

client.println( "Host:" );
client.println(server);

Secondly you are using the server's IP address instead of the actual host name in the Host: header. That means the server can't work out which website you are wanting to talk to and just rejects your request.

You need to have something more like:

client.print("Host: ");
client.println(servername);

where servername is the name of the server (www.website.com) not the IP address. You can put it all in one line if you don't ever want to change the website name, since it makes it slightly harder to find and edit:

client.println("Host: www.mywebsite.com");

Answer 2

First of all, are you now connected to SQL? If so, and it still doesn't work... Have you tried to escape your ", so instead of:

$SQL = "... VALUES ('".$_GET["serial"]."', '".$_GET["temperature"]."',...')";

doing something like:

$SQL = "... VALUES ('\".$_GET[\"serial\"].\"', '\".$_GET[\"temperature\"].\"',...')";

If it still doesn't work, once you send to SQL with your code, print on screen what you are effectively sending, copy it with your mouse, paste it in your phpadmin-sql console and execute it. There you should see any helpful tips on what is really happening. Hope this helps!