Go Down

Topic: PHP+Mysql+Ethernet stations (Read 3195 times) previous topic - next topic

abrand

I want create some stations to measure any diferent things, each station need send information to mysql on my server by a php file. It is working fine.
But i want to access a page, were i can see the last state of all sensor from all station.
I thinking about two ways.

1- get the last value from mysql, but i think this is stress my hard disk, i'm using a old pc, and this page will be open for ever.
2- My page receive data directly from stations, but i have no idea how. Somebody have a sugestion?

Thanks!

My Arduino code to test is:

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



int bstate = 0;                                                                // bstate precisa ser convertida p/ string antes de enviada
int x = 0;
String txData ="";
String teste1 ="";
String teste2 ="";// Esta sera a string contendo as variaveis e nomes
byte mac[] = { 0xDE, 0xAD, 0xBE, 0xEF, 0xFE, 0xED };                           //MAC ADDRESS "inventado" para a minha ethernet
byte ip[]  = { 192,168,0,195 };                                                // IP do Arduino
byte server[] = {192,168,0,197};                                               //IP do SERVER

void setup(){
     Ethernet.begin(mac, ip);                                                  // Inicia Ethernet
     Serial.begin(9600);                                                       // Inicia serial para diagnostico
     delay(1000);                                                              // 1 seg para a ethernet entrar no ar.
}

void loop(){
     bstate++;                                                                 //incrementando a variavelte
     x=bstate+2;
     teste1 = "BUTTON_STATE="+(String (bstate));             //concatena a variavel com um texto
     teste2 = "X="+(String(x));
     txData = teste1+"&"+teste2;
     EthernetClient client;

     if (client.connect(server,80))  {                                         //Se conectar
        Serial.println("Connected to yourwebsite...");                         // diagnostico...
        Serial.println(bstate);
        Serial.println(x);
        client.print("POST /update.php HTTP/1.1\n");
        client.print("Host: cpd\n");                         
        client.print("Connection: close\n");
        client.print("Content-Type: application/x-www-form-urlencoded\n");
        client.print("Content-Length: ");                                      //O PHP precisa saber quantos caracteres sero enviados
        client.print(txData.length());                                          // Retorna o tamanho do dado a ser enviado 
        client.print("\n\n");
        client.print(txData);                                                   // after all of the required junk we send the data
    }else{
          Serial.println("Connection Failed.");                                // Apenas para saber se deu errado   
          Serial.println();
    }
    delay(60000); //this delay was for my test to send a number every 60 sec
}//loop

My PHP code at Server:

<?php
require("connect.php");

$query = "INSERT INTO teste(BUTTON_STATE,X)
                  VALUES('$_POST[BUTTON_STATE]','$_POST[X]')";


if(!@mysql_query($query))
{
echo "&Answer; SQL Error - ".mysql_error();
return;
}
?>



lafontas

I have built a similar network of sensor nodes or "stations" and capture data in the same method you describe. I opted to use PHP to report the latest node data in a tabular format using a SQL request like this:

Code: [Select]
$node1Result = mysql_query("SELECT * FROM nodes WHERE Node='1' ORDER BY TimeStamp DESC LIMIT 1") or die(mysql_error());

I also use pChart2 to produce graphs of temperature, humidity, light, etc.

IMHO and not withstanding your concern about the capabilities of your server, I think it's important to use "the right tool for the right job" as the saying goes. Microcontrollers (MCUs) like the Arduino Uno or Mega are great for collecting sensor data and other inputs, and then actuating things like LEDs and servos, etc., while LAMPP and WAMPP servers using tools like python and PHP are well suited to data reporting and analysis. That said, new boards like the Yun provide both a MCU and a LAMPP server is a single board. 

abrand

Thanks lafontas! I will try it!

Chagrin


Code: [Select]
        client.print("POST /update.php HTTP/1.1\n");
        client.print("Host: cpd\n");                         
        client.print("Connection: close\n");
        client.print("Content-Type: application/x-www-form-urlencoded\n");
        client.print("Content-Length: ");                                      //O PHP precisa saber quantos caracteres sero enviados
        client.print(txData.length());                                          // Retorna o tamanho do dado a ser enviado 
        client.print("\n\n");
        client.print(txData);                                                   // after all of the required junk we send the data



If you use GET instead of POST it's a bit simpler. Example:

Code: [Select]
        client.print("GET /update.php?");
        client.print(txData);
        client.print(" HTTP/1.0\n");
        client.print("Host: cpd\n\n");                         


The GET eliminates the need for the Content-Length and Content-Type stuff. Switching from HTTP/1.1 to HTTP/1.0 means you can skip the "Connection: Close" part. Make sure that the value of the "Host" matches the hostname configured in the webserver configuration if you're doing name-based virtualhosts; if you're not then you can just skip it altogether.

Saves a few bytes of memory and a lot easier to troubleshoot too because you can see the request in the webserver's logs.

Go Up