[SOLVED] Store sensor value into MySql using WiServer?

Projects Programming
1

Hi, here is my project :
I want to read sensor value and show the value on web browser, and then get the sensor value (with php?) and store it into mysql and then show last 20 logs of measuring into php again..

I am connecting Grove Light Sensor to XBee Carrier + Wifi Bee and using WiShield library with WiServer header. XBee Carrier reading sensor value and sending it to browser using Wifi Bee.

The code :

/*
 * A simple sketch that uses WiServer to serve a web page
 */


#include <WiServer.h>
#include <math.h>

// Wireless configuration parameters ----------------------------------------
unsigned char local_ip[]    = {192,168,234,55};   // IP address of WiShield
unsigned char gateway_ip[]  = {192,168,234,1};   // router or gateway IP address
unsigned char subnet_mask[] = {255,255,255,0}; // subnet mask for the local network
char ssid[]                 = {"Connectify-me"};   // max 32 bytes
unsigned char security_type = 3;               // 0 - open; 1 - WEP; 2 - WPA; 3 - WPA2

// WPA/WPA2 passphrase
const prog_char security_passphrase[] PROGMEM = {"0987654321"};	// max 64 characters

// WEP 128-bit keys
prog_uchar wep_keys[] PROGMEM = { 
	0x01, 0x02, 0x03, 0x04, 0x05, 0x06, 0x07, 0x08, 0x09, 0x0a, 0x0b, 0x0c, 0x0d,	// Key 0
	0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00,	// Key 1
	0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00,	// Key 2
	0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00	// Key 3
};

// setup the wireless mode; infrastructure - connect to AP; adhoc - connect to another WiFi device
#define WIRELESS_MODE_INFRA	1
#define WIRELESS_MODE_ADHOC	2
unsigned char wireless_mode = WIRELESS_MODE_INFRA;
unsigned char sensorValue;
unsigned char ssid_len;
unsigned char security_passphrase_len;
// End of wireless configuration parameters ----------------------------------------

int counter;
// This is our page serving function that generates web pages
boolean sendMyPage(char* URL) {
  
    // Check if the requested URL matches "/"
    if (strcmp(URL, "/") == 0) {
        // Use WiServer's print and println functions to write out the page content
        WiServer.print("<html>");
        // Instruct the web broweser to refersh every 10 seconds 
        WiServer.print("<head><meta http-equiv=\"refresh\" content=\"10\"> </head>"); 
        WiServer.println("<H1>WifiBee Connected...<H1>");
        WiServer.print("<H2>Sensor Value = ");
        WiServer.print(sensorValue);  //pin 5 is connected to an exernal sensor. The RAW value is send to the client.
        WiServer.print("</H2>");
        WiServer.print("</html>");
        // URL was recognized
        return true;
    }
    // URL not found
    return false;
}


void setup() {
  // Initialize WiServer and have it use the sendMyPage function to serve pages
  WiServer.init(sendMyPage);
  
  // Enable Serial output and ask WiServer to generate log messages (optional)
  Serial.begin(57600);
  WiServer.enableVerboseMode(true);
}

void loop(){
  sensorValue = analogRead(5);  //pin 5 is connected to an exernal sensor via Grove interface in Grove - XBee Carrier .
  Rsensor=(float)(1023-sensorValue)*10/sensorValue;
  // Run WiServer
  WiServer.server_task();
 
  delay(10);
}

When I access the IP of Wifi Bee it worked fine, the page showed :

WifiBee Connected...
Sensor Value = xxx

My question is how to get the "xxx" in order to store it into mysql??
I have searched over the internet and got some idea if it is possible :

  1. use GET in php, the problem is how to send the value to URL?
    I found this code :
void getTemp() {
  Serial.println("connected, writing ...");
  int temp = analogRead(5);
  delay(50);
  sprintf(strURL,"GET /temp.php?t=%d",temp);
  client.println(strURL);
  client.println();
  Serial.println(strURL);

but the code is in ethernet shield tutorial and use Ethernet.h header, I wonder if it works in WiShield library/WiServer header?

  1. use POST in php, the problem is how to send the value automatically (without submit button or something like that since I only know that sending strings is manually and then php call the strings)

  2. assume the sensor value is a string in html, is it possible to get a certain string (the sensor value in this case) from html page/html code?

(sorry for my bad english)

2

monyet_galau:
I want to read sensor value and show the value on web browser, and then get the sensor value (with php?) and store it into mysql and then show last 20 logs of measuring into php again..

I am connecting Grove Light Sensor to XBee Carrier + Wifi Bee and using WiShield library with WiServer header. XBee Carrier reading sensor value and sending it to browser using Wifi Bee.

I really struggle to make sense of that. If you want to store historical values in MySQL then you need a MySQL database installation somewhere - and obviously, that is not on the Arduino. Presumably you will have a PC to host that. You also need some way for the Arduino to send data to it. Since there is no MySQL client for the Arduino (as far as I'm aware) and since you want to display the database content in a web browser, the most sensible architecture would be to have a web server on the same host as the database; provide one web service which accepts HPPT POST requests from the Arduino containing data to be inserted into the database; provide another web service which provides a user interface, using data read from the database however you see fit. On the Arduino, all you'd need would be a TCP/IP network interface and an HTTP client to execute the HTTP Post request via TCP/IP. If you have an Ethernet or WiFi interface for the Arduino, that would work fine.

If the Arduino has a USB connection to the host that the MySQL database and web server is on then a possioble variation would be to send the data to the web app via the serial link instead of via an HTTP POST. In that case your web app would have to know which serial port to connect to, connect to it, receive messages from it in some format you will define, and write the received data to the database. It's functionally very similar to the previous approach except using a serial connection instead of the TCP/IP connection.

3

Yes I already knew that I have to get a PC to host MySQL database, and as you can read on my previous post that I'm using Wifi Bee to show the sensor value in web browser and it was done. My question is how to passing the sensor value to php?

for example on a code I found for Ethernet Shield it is possible to store the value into URL using GET in php :

void getTemp() {
  Serial.println("connected, writing ...");
  int temp = analogRead(5);
  delay(50);
  sprintf(strURL,"GET /temp.php?t=%d",temp);
  client.println(strURL);
  client.println();
  Serial.println(strURL);

let's say I'm using ethernet shield and I'm accessing the IP Addrress and the value I got is 50 then the value should stored in URL like this :
http://localhost/temp.php?t=50
and in my php code I can call the value to be seen, and if I'm able to do that then it would be easy to sending the value to mysql.
but I'm using WiShield library and WiServer header, not Ethernet header, so how to make it works?

4

Ok I have got it works, so here is to solver :

boolean sendMyPage(char* URL) {
  
    // Check if the requested URL matches "/"
    if (strcmp(URL, "/") == 0) {
        // Use WiServer's print and println functions to write out the page content
        WiServer.print(sensorValue); 
        // URL was recognized
        return true;
    }
    // URL not found
    return false;
}

that code will return only the sensor value if I open the Wifi Bee IP Address, like this :

for the PHP code :

<?php
$html = file_get_contents('http://192.168.234.55'); //get contents from Wifi Bee IP Address

$url='http://localhost/ambil.php'; //set refresh for this php page
header("Refresh: 5; URL=$url");

mysql_connect('localhost','root','') or die("Tidak dapat terhubung MySQL").mysql_error(); 
@mysql_select_db('cahaya') or die('Tidak dapat memilih database');
$query = "INSERT INTO light VALUES(null, now(),'$html')"; //I have set columns like this : ID (auto increment), TIME (timestamp), LUM (for storing the sensor values)
$result = mysql_query($query) or die("error".mysql_error());
mysql_close();
?>