Pages: 1 [2] 3 4   Go Down
Author Topic: Arduino ethernet webclient php script and mysql  (Read 56386 times)
0 Members and 1 Guest are viewing this topic.
South Coast UK
Offline Offline
Newbie
*
Karma: 0
Posts: 41
Arduino rocks
View Profile
 Bigger Bigger  Smaller Smaller  Reset Reset

zoomkat.

Thanks for getting back. I have Xubuntu with LAMP.

If I type the value into my PHP script the database gets filled, what I don't understand is how or through what the Arduino passes info to the PHP.

If you have any ideas where I should look I'd much appreciate it.

Thanks again

Tony
Logged

0
Offline Offline
Tesla Member
***
Karma: 143
Posts: 9600
Arduino rocks
View Profile
 Bigger Bigger  Smaller Smaller  Reset Reset

You might post your PHP code that works to see what you have. Below shows how to append a GET request (query_string, the string after the ?) to a URL. In windows, apache sets the query_string as an environmental varable, from which an application started by apache collects the data. Note that some characters have special meaning and can't be put in a query_string.  

client.println("GET /arduino.txt HTTP/1.0");

With query_string added:

client.println("GET /arduino.php?put-your-data-here HTTP/1.0");
« Last Edit: December 18, 2010, 12:21:32 pm by zoomkat » Logged

Consider the daffodil. And while you're doing that, I'll be over here, looking through your stuff.   smiley-cool

South Coast UK
Offline Offline
Newbie
*
Karma: 0
Posts: 41
Arduino rocks
View Profile
 Bigger Bigger  Smaller Smaller  Reset Reset

Well this is what I'm trying to run. The Arduino code is yours, modified to update the database with a value.
Code:
//zoomkat 11-13-10
//simple ethernet client test code
//for use with IDE 0021 and W5100 ethernet shield
//modify the arduino lan ip address as needed
//open serial monitor to see what the arduino receives
//push the shield reset button to run client again

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

byte mac[] = {0x90, 0xA2, 0xDA, 0x00, 0x11, 0xAC };
byte ip[] = { 192, 168, 1, 19 };
byte server[] = { 192,168,1,20 }; // zoomkat

Client client(server, 80);

void setup()
{
  Ethernet.begin(mac, ip);
  Serial.begin(9600);
  Serial.println("starting simple arduino client test");
  Serial.println();

  delay(1000);

  Serial.println("connecting...");

  if (client.connect()) {
    Serial.println("connected");
    //client.println("GET /arduino.txt HTTP/1.0");
    client.println("GET /update_db.php?celsius=18 HTTP/1.0");
    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("disconnecting.");
    Serial.println("==================================");
    Serial.println("");
    client.stop();
    for(;;);
  }
}
  


And the PHP script
Code:
<?php
//Connect to database 
$con mysql_connect("localhost""xxxx""xxxx");
if(!
$con)
      &
#123;
      
die('Could not connect: ' .mysql_error());
      &
#125;
mysql_select_db("1stClassElec"$con);

mysql_query("INSERT INTO device_1(temp) VALUES (celsius)");
mysql_close($con);
?>

NOTE:
If I change
Code:
mysql_query("INSERT INTO device_1(temp) VALUES (celsius)");
to
Code:
mysql_query("INSERT INTO device_1(temp) VALUES (19)");
and then run Arduino the value of 19 gets written into the database.
Why
Code:
mysql_query("INSERT INTO device_1(temp) VALUES (celsius)");
doesn't pass its value of celsius=18 from the Arduino script is a mystery to me at the moment.
« Last Edit: December 18, 2010, 01:12:38 pm by xsilvergs » Logged

Seattle, WA USA
Offline Offline
Brattain Member
*****
Karma: 614
Posts: 49343
Seattle, WA USA
View Profile
 Bigger Bigger  Smaller Smaller  Reset Reset

In the PHP script, there are $_GET and $_POST variables that contain the data passed to the script, depending on the method used to to activate the script.

The $_GET variables are name/value pairs. The value is obtained from the name:
Code:
$val = $_GET['celcius'];
It is the value ($val) that you want to store in the database.
Logged

South Coast UK
Offline Offline
Newbie
*
Karma: 0
Posts: 41
Arduino rocks
View Profile
 Bigger Bigger  Smaller Smaller  Reset Reset

@ PaulS I was just reading a web page which is similar to your explanation. My PHP script is working and now looks like this:
Code:
<?php
//echo ("update_db.php indicator");
$celsius $_GET['celsius'];
//Connect to database 
$con mysql_connect("localhost""xxxx""xxxx");
if(!
$con)
      &
#123;
      
die('Could not connect: ' .mysql_error());
      &
#125;
mysql_select_db("1stClassElec"$con);

mysql_query("INSERT INTO device_1(temp) VALUES ('$celsius')");
mysql_close($con);
?>

Thanks go to all of you for your help, and I expect I'll be back soon with another question  smiley
Logged

South Coast UK
Offline Offline
Newbie
*
Karma: 0
Posts: 41
Arduino rocks
View Profile
 Bigger Bigger  Smaller Smaller  Reset Reset

Ok back again. I would like my Arduino web-client to write data to the mysql database every 15 minutes.

What would be the best way to do the time interval counting?

As I see it, it could be done by the Arduino but could my web server poll the Arduino or what about the timer_ntp. I'd need some examples with the last two suggestions if they are good ideas.
« Last Edit: December 19, 2010, 03:53:49 pm by xsilvergs » Logged

Seattle, WA USA
Offline Offline
Brattain Member
*****
Karma: 614
Posts: 49343
Seattle, WA USA
View Profile
 Bigger Bigger  Smaller Smaller  Reset Reset

Quote
As I see it, it could be done by the Arduino but could my web server poll the Arduino
Yes. The Arduino could use millis() to determine the "time", and send data only once every 15 minutes. Or the Arduino could do nothing except wait for a client connection, and, in response to the client connection, return the temperature.

Which approach to use depends on what you are comfortable programming.
Logged

South Coast UK
Offline Offline
Newbie
*
Karma: 0
Posts: 41
Arduino rocks
View Profile
 Bigger Bigger  Smaller Smaller  Reset Reset

Well in my endeavour to learn Arduino Ethernet I'm still unable to get the Arduino web-client to accept commands from a server PC. Also I'm unable to get web-server to send data to a mysql database.

So I've written (probably poorly) code which I 95% understand that accepts commands from a browser.
http://192.168.1.19/?7=1 turns digital 7 on
http://192.168.1.19/?7=0 turns digital 7 off
http://192.168.1.19/?A=0 gets the value from analogue 0, prints what looks like the correct string in the web-browser but it won't write to a mysql.
Code:
#include <SPI.h>
//#include <WString.h>
#include <Ethernet.h>

byte mac[] = { 0x90, 0xA2, 0xDA, 0x00, 0x11, 0xAC }; //physical mac address
byte ip[] = { 192, 168, 1, 19 };                  // ip in lan
byte gateway[] = { 192, 168, 1, 1 };                  // internet access via router
byte subnet[] = { 255, 255, 255, 0 };                   //subnet mask
//byte server[] = { 192,168,1,20 };
Server server(80);                                      //server port
byte sampledata=50;            //some sample data - outputs 2 (ascii = 50 DEC)            
int ledPin = 4;  // LED pin
char link[]="http://www.scienceprog.com/"; //link data
String readString = String(30); //string for fetching data from address
boolean LEDON = false; //LED status flag
//#######################################
   #define READING_PIN 0
    double R1 = 10000.0; //resistance put in parallel
    double V_IN = 5.0;
    double A = 1.129148e-3;
    double B = 2.34125e-4;
    double C = 8.76741e-8;
    double K = 9.5; // mW/dec C – dissipation factor
    double SteinhartHart(double R)
    {
    // calculate temperature
    double logR  = log(R);
    double logR3 = logR * logR * logR;
    return 1.0 / (A + B * logR + C * logR3 );
    }
    int celsius;//Global declare celsius
//#######################################
void setup(){
//start Ethernet
  Ethernet.begin(mac, ip, gateway, subnet);
//pinMode;
pinMode(7,OUTPUT); digitalWrite(7,LOW);  // I use this pin as GND for the LED.
pinMode(8,OUTPUT); // Sample output, unable to use built-in LED at pin 13 because Ethernet Shield uses pins 10,11,12,13.
  
//enable serial datada print  
    Serial.begin(9600);
}
void loop(){
    readString="";//Clear readString before filling
    // Create a client connection
    Client client = server.available();
      if (client) {
      while (client.connected()) {
        if (client.available()) {
          char c = client.read();
          //read char by char HTTP request
          if (readString.length() < 30)  {
          //store characters to string
          readString.concat(c);
          Serial.println(readString);
          }  
          //output chars to serial port
          Serial.print(c);
            //if HTTP request has ended
            if (c == '\n') {
            //lets check if LED should be lighted
            Serial.println(readString.substring(6,9));//for test to allow readString to be viewed
              if(readString.substring(6,9)=="7=1") {//read the last 3 information characters from URL
              // Serial.println(readString);
              //set digital 7 HIGH
              Serial.println("HIGH");
             digitalWrite(7, HIGH);    // set the LED on
              readString="";//clear readString
              }
                if(readString.substring(6,9)=="7=0") {
                //set digital 7 LOW
                Serial.println("LOW");
                digitalWrite(7, LOW);    // set the LED OFF
                readString="";//clear readString          
                }
              if(readString.substring(6,9)=="A=0")  {
              //readAnal 0 temp
              ReadTemp();
              Serial.println("for test");
              client.print("GET http://192.168.1.20/update_db.php?celsius=");
              client.print(celsius);
              client.println(" HTTP/1.0");
              client.println("Host: http://192.168.1.20");
              client.println();
              readString="";//clear readString            
              }
          
          //clearing string for next read
          readString="";
          //stopping client
          client.stop();
            }
          }
        }
      }
 }
//########################################################################
void ReadTemp() {
//Serial.print("I'm at ReadTemp ");
    double adc_raw = analogRead(READING_PIN);
    double V =  adc_raw / 1024 * V_IN;//Serial.println(adc_raw);
    double R_th = (R1 * V ) / (V_IN - V);//calculate resistance
    double kelvin = SteinhartHart(R_th) - V*V/(K * R_th);
    celsius = kelvin - 273.15;//was double but changed to int to remove decimal places
    Serial.println();
    delay(1000);
    
    return;
}
//#######################################################################
Can anyone see and explain what's wrong as I've spent all day on this with no answer.

All I want is an Arduino with Ethernet Shield that can accept commands from a web-server PC and send values to a mysql database on the same to the web-server PC.

Thanks for any help.

Tony
« Last Edit: December 20, 2010, 03:20:41 pm by xsilvergs » Logged

Seattle, WA USA
Offline Offline
Brattain Member
*****
Karma: 614
Posts: 49343
Seattle, WA USA
View Profile
 Bigger Bigger  Smaller Smaller  Reset Reset

Do you have error handling turned on in PHP?
Logged

0
Offline Offline
Tesla Member
***
Karma: 143
Posts: 9600
Arduino rocks
View Profile
 Bigger Bigger  Smaller Smaller  Reset Reset

Quote
I'm still unable to get the Arduino web-client to accept commands from a server PC.

Well, it looks like you have a mix of client and server in your code which I haven't tried. I suggest you work on the client part first. A client sends a request to a server, and the server sends info back to the client. For the client to do something like turn on lights and such, the client needs to evaluate the info returned from the server for what to do. The below simple client code shows what is received by the client from a server. The actual requested info contained in the returned material is the "woohoo...zoomkat" part. The rest is standard HTTP stuff. For the client to act on the returned info, the "woohoo...zoomkat" part would need to be extracted and evaluated for commands.  


Code:
//zoomkat 11-13-10
//simple ethernet client test code
//for use with IDE 0021 and W5100 ethernet shield
//modify the arduino lan ip address as needed
//open serial monitor to see what the arduino receives
//push the shield reset button to run client again

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

byte mac[] = { 0xDE, 0xAD, 0xBE, 0xEF, 0xFE, 0xED };
byte ip[] = { 192, 168, 1, 102 };
byte server[] = { 208, 104, 2, 86 }; // zoomkat

Client client(server, 80);

void setup()
{
  Ethernet.begin(mac, ip);
  Serial.begin(9600);
  Serial.println("starting simple arduino client test");
  Serial.println();

  delay(1000);

  Serial.println("connecting...");

  if (client.connect()) {
    Serial.println("connected");
    client.println("GET /~shb/arduino.txt HTTP/1.0");
    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("disconnecting.");
    Serial.println("==================================");
    Serial.println("");
    client.stop();
    for(;;);
  }
}
Logged

Consider the daffodil. And while you're doing that, I'll be over here, looking through your stuff.   smiley-cool

South Coast UK
Offline Offline
Newbie
*
Karma: 0
Posts: 41
Arduino rocks
View Profile
 Bigger Bigger  Smaller Smaller  Reset Reset

according to the .ini Yes.

And this is what is sent to the php file:
Code:
GET /update_db.php?celsius=45 HTTP/1.1
by this line
Code:
client.println("GET /update_db.php?celsius=45 HTTP/1.1");
If this arduino is set up as a web-server why can I use this
Code:
client.println("GET /update_db.php?celsius=45 HTTP/1.1");
Should it not be Server.print which gives errors when I try to Compile?
« Last Edit: December 20, 2010, 04:46:41 pm by xsilvergs » Logged

0
Offline Offline
Tesla Member
***
Karma: 143
Posts: 9600
Arduino rocks
View Profile
 Bigger Bigger  Smaller Smaller  Reset Reset

Quote
Should it not be Server.print which gives errors when I try to Compile?

I have no idea. Your code is such a jumble of stuff that I don't think I can be of any help. Sorry.
Logged

Consider the daffodil. And while you're doing that, I'll be over here, looking through your stuff.   smiley-cool

South Coast UK
Offline Offline
Newbie
*
Karma: 0
Posts: 41
Arduino rocks
View Profile
 Bigger Bigger  Smaller Smaller  Reset Reset

zoomkat. Thanks for your help but I'm getting nowhere. I've tried putting in just the code to turn the LED ON/OFF but it doesn't work and the serial monitor chucks out tons of stuff. I've been on this since 7am and it's now 10:45pm so here's my code:
Code:
//zoomkat 11-13-10
//simple ethernet client test code
//for use with IDE 0021 and W5100 ethernet shield
//modify the arduino lan ip address as needed
//open serial monitor to see what the arduino receives
//push the shield reset button to run client again

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

byte mac[] = { 0x90, 0xA2, 0xDA, 0x00, 0x11, 0xAC };
byte ip[] = { 192, 168, 1, 19 };
byte server[] = { 192,168,1,20 };

Client client(server, 80);
String readString = String(30); //string for fetching data from address

void setup()
{
  Ethernet.begin(mac, ip);
  Serial.begin(9600);
  //pin mode
  pinMode(7,OUTPUT); digitalWrite(7,LOW);  // I use this pin as GND for the LED.
  pinMode(8,OUTPUT); // Sample output, unable to use built-in LED at pin 13 because Ethernet Shield uses pins 10,11,12,13.

  Serial.println("starting simple arduino client test");
  Serial.println();

  delay(1000);

  Serial.println("connecting...");

  if (client.connect()) {
    Serial.println("connected");
    client.println("GET /~shb/arduino.txt HTTP/1.0");
    client.println();
  } else {
    Serial.println("connection failed");
  }
}

void loop()
{
  if (client.available()) {
//###########################################################
          char c = client.read();
          //read char by char HTTP request
          if (readString.length() < 30)  {
          //store characters to string
          readString.concat(c);
          Serial.println(readString);
          }  
          //output chars to serial port
          Serial.print(c);
            //if HTTP request has ended
            if (c == '\n') {
            //Turn on LED
            Serial.println(readString.substring(6,9));//for test to allow readString to be viewed
              if(readString.substring(6,9)=="7=1") {//read the last 3 information characters from URL
              // Serial.println(readString);
              //set digital 7 HIGH
              Serial.println("HIGH");
             digitalWrite(7, HIGH);    // set the LED on
              readString="";//clear readString
              }
                if(readString.substring(6,9)=="7=0") {
                //set digital 7 LOW
                Serial.println("LOW");
                digitalWrite(7, LOW);    // set the LED OFF
                readString="";//clear readString          
                }
              
    
              }
//###########################################################


  if (!client.connected()) {
    Serial.println();
    Serial.println("disconnecting.");
    Serial.println("==================================");
    Serial.println("");
    client.stop();
   // for(;;);
  }
}
}

I'll have another go tomorrow perhaps! smiley-sad
Logged

Seattle, WA USA
Offline Offline
Brattain Member
*****
Karma: 614
Posts: 49343
Seattle, WA USA
View Profile
 Bigger Bigger  Smaller Smaller  Reset Reset

Think about what a web browser is doing when it executes a GET request. It is asking the server for some information. Typically, the server is expected to return a web page. For the 7=1 and 7=0 requests, the server should send back a page that says that the light was turned on, or off.

For the A=0 request, the client is expecting a value in return. The response from the server can be a page that redirects the browser to another page, with arguments in the redirect string.

What you were sending back was a GET request, which the client is not prepared to accept.

Tomorrow, I'll see if I can find some html/PHP code that that does a re-direct.
Logged

0
Offline Offline
Tesla Member
***
Karma: 143
Posts: 9600
Arduino rocks
View Profile
 Bigger Bigger  Smaller Smaller  Reset Reset

I suggest trouble shooing your server/php/mysql by using your get request below in a brouser URL box, hit enter, and see if the desired result is returned. Bottom is some client code that sends a request to a server, and then extracts the desired data from what is returned.

http://192.168.1.20/update_db.php?celsius=45

Code:
//zoomkat 12-21-10
//simple ethernet client test code
//for use with IDE 0021 and W5100 ethernet shield
//modify the arduino lan ip address as needed
//open serial monitor to see what the arduino receives
//push the shield reset button to run client again

#include <SPI.h>
#include <Ethernet.h>
String readString, readString1;

byte mac[] = { 0xDE, 0xAD, 0xBE, 0xEF, 0xFE, 0xED };
byte ip[] = { 192, 168, 1, 102 };
byte server[] = { 208, 104, 2, 86 }; // zoomkat

Client client(server, 80);

void setup()
{
  Ethernet.begin(mac, ip);
  Serial.begin(9600);
  Serial.println("starting simple arduino client test");
  Serial.println();
  Serial.println("connecting...");

  if (client.connect()) {
    Serial.println("connected");
    client.println("GET /~shb/arduino1.txt HTTP/1.0");
    client.println();
  } else {
    Serial.println("connection failed");
  }
}

void loop()
{
  if (client.available()) {
    char c = client.read();
    readString += c;
  }
        
  if (!client.connected()) {
     client.stop();
    
    Serial.print(readString);
    Serial.println();
    Serial.println();
    
    int d1 = readString.indexOf('<');
    int d2 = readString.indexOf('>');
    readString1 = (readString.substring(d1+1,d2));
    
    Serial.println("==================================");
    Serial.println("data from server:");
    Serial.println();
    Serial.println(readString1);
    Serial.println();
    Serial.println("==================================");
    Serial.println();
        
    for(;;);
    
    }
 }
Logged

Consider the daffodil. And while you're doing that, I'll be over here, looking through your stuff.   smiley-cool

Pages: 1 [2] 3 4   Go Up
Jump to: