Problems with PHP,MySQL and Ethernet

Projects Discussion and Showcase Home Automation
1

Hi, i'm italian and that's my first post on the international forum.
I'm trying to use Arduino Ethernet Shield + PHP + MySQL but i have some problems.
That's my Arduino code:

#include <SPI.h>
#include <Ethernet.h>
 
byte mac[] = { 0xDE, 0xAD, 0xBE, 0xEF, 0xFE, 0xED };

//indirizzo server web (locale)
IPAddress server(192, 168, 0, 4);
 //indirizzo ip dell'Arduino
IPAddress ip(192, 168, 0, 10);
String strURL = "";
float temp = 0;
char c; 
EthernetClient client;
void setup()
{
 
 Serial.begin(9600);
 Ethernet.begin(mac,ip);
 Serial.println("Ethernet Shield Arduino : Temperatura");
 Serial.println(Ethernet.localIP());
}
 
void loop()
{
   if(client.connect(server,80)){
   temp = analogRead(A5);
   temp = temp * 0.48828125;
 
    //creo l'url utilizzanso una stringa
    strURL = "GET /Arduino/indice.php?valore=";
    strURL +=  (int)temp;
    strURL += "&localita=Studio HTTP/1.1";
 
    //invio la richiesta al server
    client.println(strURL);
    client.print("Host:");
    client.println(server);
    client.println("Connection: close");
    client.println();  
}
  while(client.available())
  {
    char c = client.read(); Serial.print(c);
}

That's my PHP code:

<?php

//controllo se sono presenti i parametri valore e localita
if(isset($_GET['valore']) && isset($_GET['localita']))
{
  //Recupero il valore del parametro "valore"
  $valore = $_GET['valore'];
 
  //Recupero il valore del parametro "localita"
  $localita = $_GET['localita'];
 
  //eseguo la connessione al database sul server locale
  //inserendo nome utente e password
  $link = mysql_connect('localhost', '...', '...');
 
  //gestione degli errori
  if (!$link) {die('Impossibile connettersi: ' . mysql_error());}
 
  //seleziono il databse di nome arduino
  mysql_select_db("Arduino") or die( "Impossibile selezionare il database.");
 
  //creo una stringa sql di inserimento con i valori
  //recuperati dall'url
  $sql = "INSERT INTO Temperature (Valore,Localita)";
  $sql .= "VALUES ('" . $valore . "', '" . $localita . "')";
         
  //eseguo la query
  $retval = mysql_query( $sql, $link );
 
  //gestione degli errori
  if(! $retval ){die('Impossibile eseguire la query: ' . mysql_error());}
 
  //chiudo la connessione al db
  mysql_close($link);
}
?>

The PHP code work because if i write in the url bar http://localhost/Arduino/indice.php?valore=20&localita=Home i can found these data in my DB. But if i pulg in my arduino , i can't access to it due to its IP. I tried to do anything i can and i hope someone could help me! Thanks! :slight_smile: :slight_smile:

2

If you can connect using this
http://localhost/Arduino/indice.php?valore=20&localita=Home
but can't connect with the Arduino (considering that the server IP is 192.168.0.4), then check the server firewall to insure you are allowing port 80 through the firewall.

Try pinging the Arduino from the server. Does it respond to a ping? If it does, then the network settings should be ok.

You code looks like it has a problem. You are not reading the server response correctly, nor are you closing the connection. My client code in the playground has all that stuff, plus some fault tolerance.
http://playground.arduino.cc/Code/WebClient

3

Hi, thanks for your reply. Yes ping works , from server and from other pc in the netwrok. So i will try to "upgrade" my code with the one you'have posted. I'll let you know as soon as i did this upgrade. Thanks :slight_smile:

EDIT: In the GET method where is stored the path of my .php script?

4

The path is passed to the getPage function using the pageAdd variable. If you uncomment the line below, it allows you to pass a dynamic variable "test" to your php code named "arduino.php".

 // sprintf(pageAdd,"/arduino.php?test=%u",totalCount);

    if(!getPage(server,serverPort,pageAdd)) Serial.print(F("Fail "));

edit: Can the other PC on the network access the server? That is the test for the firewall.

5

Yes, if i go to http://MYLAPTOPIP/arduino/indice.php?valore=20&localita=home it works.
edit: should i set my laptop ip static?

I modified your code but now i can't ping it and so neither access to the board.
Serial monitor says:

Starting ethernet...
192.168.2.3
Ready
connecting...failed
Fail 1
connecting...failed
Fail 2
connecting...failed
Fail 3
connecting...failed
Fail 4
connecting...failed
Fail 5

/*
   Web client sketch for IDE v1.0.1 and w5100/w5200
   Uses GET method.
   Posted October 2012 by SurferTim
   Last modified September 15, 2013
*/

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

// this must be unique
byte mac[] = {  0xDE, 0xAD, 0xBE, 0xEF, 0xFE, 0xED };

// change to your network settings
IPAddress ip(192,168,2,3);
IPAddress gateway(192, 168, 0, 1);
IPAddress subnet(255, 255, 255, 0);

// change to your server
IPAddress server(192,168,0,6); // Laptop

//Change to your domain name for virtual servers
char serverName[] = "192.168.0.6";
// If no domain name, use the ip address above
// char serverName[] = "74.125.227.16";

// change to your server's port
int serverPort = 80;

EthernetClient client;
int totalCount = 0;
char pageAdd[64];


//My sensor
int temp = A0;
int val = 0;
// set this to the number of milliseconds delay
// this is 30 seconds
#define delayMillis 30000UL

unsigned long thisMillis = 0;
unsigned long lastMillis = 0;

void setup() {
  Serial.begin(9600);
  pinMode(temp,INPUT);
  // disable SD SPI
  pinMode(4,OUTPUT);
  digitalWrite(4,HIGH);

  // Start ethernet
  Serial.println(F("Starting ethernet..."));
  Ethernet.begin(mac, ip);

  // If using dhcp, comment out the line above 
  // and uncomment the next 2 lines plus the Ethernet.maintain call in loop

  //if(!Ethernet.begin(mac)) Serial.println(F("failed"));
  //else Serial.println(F("ok"));

  Serial.println(Ethernet.localIP());

  delay(2000);
  Serial.println(F("Ready"));
}

void loop()
{
  // If using dhcp to get an IP, uncomment the next line
  // Ethernet.maintain();
  
  thisMillis = millis();
  val = (5/1024)*(analogRead(temp));
  if(thisMillis - lastMillis > delayMillis)
  {
    lastMillis = thisMillis;

    // Modify next line to load different page
    // or pass values to server
    sprintf(pageAdd,"/arduino/indice.php?valore="+val,totalCount);

    // sprintf(pageAdd,"/arduino.php?test=%u",totalCount);

    if(!getPage(server,serverPort,pageAdd)) Serial.print(F("Fail "));
    else Serial.print(F("Pass "));
    totalCount++;
    Serial.println(totalCount,DEC);
  }    
}

byte getPage(IPAddress ipBuf,int thisPort, char *page)
{
  int inChar;
  char outBuf[128];

  Serial.print(F("connecting..."));

  if(client.connect(ipBuf,thisPort) == 1)
  {
    Serial.println(F("connected"));

    sprintf(outBuf,"GET %s HTTP/1.1",page);
    client.println(outBuf);
    sprintf(outBuf,"Host: %s",serverName);
    client.println(outBuf);
    client.println(F("Connection: close\r\n"));
  } 
  else
  {
    Serial.println(F("failed"));
    return 0;
  }

  // connectLoop controls the hardware fail timeout
  int connectLoop = 0;

  while(client.connected())
  {
    while(client.available())
    {
      inChar = client.read();
      Serial.write(inChar);
      // set connectLoop to zero if a packet arrives
      connectLoop = 0;
    }

    connectLoop++;

    // if more than 10000 milliseconds since the last packet
    if(connectLoop > 10000)
    {
      // then close the connection from this end.
      Serial.println();
      Serial.println(F("Timeout"));
      client.stop();
    }
    // this is a delay for the connectLoop timing
    delay(1);
  }

  Serial.println();

  Serial.println(F("disconnecting."));
  // close client end
  client.stop();

  return 1;
}
6

Your network settings are not changed correctly. The gateway and subnet are good, but the IP is not in the network range. It must be 192.168.0.10 as in your first sketch.

7

On pc you run the PHP Server you must have a static ip address and make sure you run the services ...

Try to run the command from another pc to check if accept it (do not try from the same pc you run the php server there it will run for sure ...)

Do you use xampp ? if yes try to search what settings you must make to accept other local ip address. (I will check on my papers if i have keep it somewhere, because i had the same issue, and i will post it here in case i have write it somewhere.)

Goodluck :wink:

8

I cant' find where i was keep it but i do a search on youtube and i find this - YouTube it is not in English but the video it will show you the steps.

I hope can help you to solve you're problem :slight_smile:

9

SurferTim:
Your network settings are not changed correctly. The gateway and subnet are good, but the IP is not in the network range. It must be 192.168.0.10 as in your first sketch.

yeah , you are right , sorry it was a mistake.
Now works! Thank you so much!
there is a small problem, in my DB the value of my temp 's reading is null can anyone help me ? Please.
Thank you! Here my scketch and my Serial monitor where FINALLY i can read it works!!

Pass 7
connecting...connected
HTTP/1.1 200 OK
Date: Wed, 21 Jan 2015 17:38:08 GMT
Server: Apache/2.4.7 (Ubuntu)
X-Powered-By: PHP/5.5.9-1ubuntu4.5
Content-Length: 38
Connection: close
Content-Type: text/html

Query done! It works!!!! :D
disconnecting.
Pass 8

/*
   Web client sketch for IDE v1.0.1 and w5100/w5200
   Uses GET method.
   Posted October 2012 by SurferTim
   Last modified September 15, 2013
*/

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

// this must be unique
byte mac[] = {  0xDE, 0xAD, 0xBE, 0xEF, 0xFE, 0xED };

// change to your network settings
IPAddress ip(192,168,0,254);
IPAddress gateway(192, 168, 0, 1);
IPAddress subnet(255, 255, 255, 0);

// change to your server
IPAddress server(192,168,0,6); // Laptop

//Change to your domain name for virtual servers
char serverName[] = "192.168.0.6";
// If no domain name, use the ip address above
// char serverName[] = "74.125.227.16";

// change to your server's port
int serverPort = 80;

EthernetClient client;
int totalCount = 0;
char pageAdd[64];


//My sensor
int temp = A0;
int val = 0;
// set this to the number of milliseconds delay
// this is 30 seconds
#define delayMillis 30000UL

unsigned long thisMillis = 0;
unsigned long lastMillis = 0;

void setup() {
  Serial.begin(9600);
  pinMode(temp,INPUT);
  // disable SD SPI
  pinMode(4,OUTPUT);
  digitalWrite(4,HIGH);

  // Start ethernet
  Serial.println(F("Starting ethernet..."));
  Ethernet.begin(mac, ip);

  // If using dhcp, comment out the line above 
  // and uncomment the next 2 lines plus the Ethernet.maintain call in loop

  //if(!Ethernet.begin(mac)) Serial.println(F("failed"));
  //else Serial.println(F("ok"));

  Serial.println(Ethernet.localIP());

  delay(2000);
  Serial.println(F("Ready"));
}

void loop()
{
  // If using dhcp to get an IP, uncomment the next line
  // Ethernet.maintain();
  
  thisMillis = millis();
  val = (5/1024)*(analogRead(temp));
  if(thisMillis - lastMillis > delayMillis)
  {
    lastMillis = thisMillis;

    // Modify next line to load different page
    // or pass values to server
    sprintf(pageAdd,"/arduino/indice.php?valore="+val,totalCount);

    // sprintf(pageAdd,"/arduino.php?test=%u",totalCount);

    if(!getPage(server,serverPort,pageAdd)) Serial.print(F("Fail "));
    else Serial.print(F("Pass "));
    totalCount++;
    Serial.println(totalCount,DEC);
  }    
}

byte getPage(IPAddress ipBuf,int thisPort, char *page)
{
  int inChar;
  char outBuf[128];

  Serial.print(F("connecting..."));

  if(client.connect(ipBuf,thisPort) == 1)
  {
    Serial.println(F("connected"));

    sprintf(outBuf,"GET %s HTTP/1.1",page);
    client.println(outBuf);
    sprintf(outBuf,"Host: %s",serverName);
    client.println(outBuf);
    client.println(F("Connection: close\r\n"));
  } 
  else
  {
    Serial.println(F("failed"));
    return 0;
  }

  // connectLoop controls the hardware fail timeout
  int connectLoop = 0;

  while(client.connected())
  {
    while(client.available())
    {
      inChar = client.read();
      Serial.write(inChar);
      // set connectLoop to zero if a packet arrives
      connectLoop = 0;
    }

    connectLoop++;

    // if more than 10000 milliseconds since the last packet
    if(connectLoop > 10000)
    {
      // then close the connection from this end.
      Serial.println();
      Serial.println(F("Timeout"));
      client.stop();
    }
    // this is a delay for the connectLoop timing
    delay(1);
  }

  Serial.println();

  Serial.println(F("disconnecting."));
  // close client end
  client.stop();

  return 1;
}
10

EDIT: I added one line of code where i do the cast of the var "val" becase in my DB's table i setted it as integer.

val = (5/1024)*(analogRead(temp));
  val = int(val);
11

This is the way you insert that value into your request.

    sprintf(pageAdd,"/arduino/indice.php?valore=%u",val);
12

I tried but nothing change. Thanks for your reply.

13

I don't know if you changed your php code, but it expects two variables (valore and localita) or it won't accept anything. Try this.

    sprintf(pageAdd,"/arduino/indice.php?valore=%u&localita=qui",val);
14

To semplify the DB i modified the table and now it had only the ID (INT AUTO_INCREMENT) and Valore(INT(11)). The php is this:

<?php
//controllo se sono presenti i parametri valore e localita
if(isset($_GET['valore']))
{ 
//Recupero il valore del parametro "valore"
$valore = $_GET['valore'];
//eseguo la connessione al database sul server locale
//inserendo nome utente e password
$link = mysql_connect('localhost', '****', '****');
//gestione degli errori
if (!$link) 
{
die('Impossibile connettersi: ' . mysql_error());
}
//seleziono il databse di nome arduino
mysql_select_db("domotica") or die( "Impossibile selezionare il database.");
//creo una stringa sql di inserimento con i valori
//recuperati dall'url 
echo("Sono qui2");
$sql = "INSERT INTO t1 (valore) VALUES ('$valore') "; 
//eseguo la query
$retval = mysql_query( $sql, $link ); 
//gestione degli errori
if(!$retval )
{die('Impossibile eseguire la query: '. mysql_error());}
else{
	echo("Query done, it works! :D");}
//chiudo la connessione al db
mysql_close($link);
}
?>

And this is the void loop() (the other function are the same)

void loop()
{
  // If using dhcp to get an IP, uncomment the next line
  // Ethernet.maintain();
  
  thisMillis = millis();
  val = (5/1024)*(analogRead(temp));
  val = int(val);
  if(thisMillis - lastMillis > delayMillis)
  {
    lastMillis = thisMillis;

    // Modify next line to load different page
    // or pass values to server
    sprintf(pageAdd,"/arduino/indice.php?valore=%u",val);

    // sprintf(pageAdd,"/arduino.php?test=%u",totalCount);

    if(!getPage(server,serverPort,pageAdd)) Serial.print(F("Fail "));
    else Serial.print(F("Pass "));
    totalCount++;
    Serial.println(totalCount,DEC);
  }    
}
15

I would check the pageAdd variable.

    sprintf(pageAdd,"/arduino/indice.php?valore=%u",val);
// add this
    Serial.println(pageAdd);

And the server still works with this?
http://localhost/Arduino/indice.php?valore=20

16

Yes it still works. i addes your line of code and that's the result:

Starting ethernet...
192.168.0.254
Ready
/arduino/indice.php?valore=0
connecting...connected
HTTP/1.1 200 OK
Date: Thu, 22 Jan 2015 14:54:49 GMT
Server: Apache/2.4.7 (Ubuntu)
X-Powered-By: PHP/5.5.9-1ubuntu4.5
Content-Length: 1
Connection: close
Content-Type: text/html
It works! 
PHPVAR = 0
disconnecting.
Pass 1

EDIT: I found my mistake!
It was in this line :

pinMode(temp,INPUT);

because i'm using a LM35 sensor and now it works!! Really thanks you man!! Last thing where can i find some documentation about the GET sketch? beacuse i think i'll use this project in an exam at school. Thanks!

17

There is sketchy documentation about any ethernet functions. You can take a look at my GET example sketch on the playground. I have included all error checking and fault tolerance stuff I could find, and commented it as well as I thought necessary. The timeout feature is the major improvement over other example sketches. Look for the loopCount variable and how it controls the timeout on the "while(client.connected))" loop.
http://playground.arduino.cc/Code/WebClient