Go Down

Topic: Send data to sql problem (SOLVED) (Read 18924 times) previous topic - next topic

Jason64

Feb 13, 2016, 10:36 pm Last Edit: Feb 14, 2016, 09:10 am by Jason64
Hello all.

I'm building a project where I want to send some sensor measurements to a database and present them in a site.

I'm using these steps.

I installed and configured XAMPP for the server and SQL database.

I then developed this short PHP code to add values to the DB

Code: [Select]
<?php

    
// Prepare variables for database connection
   
    
$dbusername "vicky";  // enter database username, I used "arduino" in step 2.2
    
$dbpassword "test";  // enter database password, I used "arduinotest" in step 2.2
    
$server "localhost"// IMPORTANT: if you are using XAMPP enter "localhost", but if you have an online website enter its address, ie."www.yourwebsite.com"

    // Connect to your database

    
$dbconnect mysql_connect($server$dbusername$dbpassword);
    
$dbselect mysql_select_db("test",$dbconnect);

    
// Prepare the SQL statement

    
$sql "INSERT INTO test.sensor (value) VALUES ('".$_GET["value"]."')";    

    
// Execute SQL statement

    
mysql_query($sql);

?>


When I paste "http://192.168.1.8:90/write_data.php?value=100" in my browser the DB gets a new value.

Then I edited my sketch to send a value to the DB.

Code: [Select]
#include <OneWire.h>
#include <DallasTemperature.h>
#include <SPI.h>
#include <WiFi.h>
#include <Ethernet.h>

// Data wire is plugged into pin 2 on the Arduino
#define ONE_WIRE_BUS 2
 
// Setup a oneWire instance to communicate with any OneWire devices
// (not just Maxim/Dallas temperature ICs)
OneWire oneWire(ONE_WIRE_BUS);
 
// Pass our oneWire reference to Dallas Temperature.
DallasTemperature sensors(&oneWire);

// LDR
const int analogInPin_LDR = A0;  // Analog input - light
int sensor_Value_LDR = 0;        // Light sensor value
int output_Value_LDR = 0;        // Light output value

// Humidity sensor
const int analogInPin_Humidity = A1;  // Analog input - humidity
int sensor_Value_Humidity = 0;        // Humidity sensor value
int output_Value_Humidity = 0;        // Humidity output value

// Tilt sensor
const int sensorPin_Tilt = 8;     // the number of the sensor pin
int output_Value_Tilt = 0;        // Tilt status value
const int ledPin =  13;           // Led pin

// Wifi Connection
char ssid[] = "HOMENET";      // your network SSID (name)
char pass[] = "86vikydi200619x5";   // your network password
int keyIndex = 0;                 // your network key Index number (needed only for WEP)

int status = WL_IDLE_STATUS;

//WiFiServer server(80);

char server[] = "192.168.1.8";
//char server[] = "www.google.com";

// Initialize the Wifi server library
WiFiClient client;
//static WiFiClient client;
  
int hp1 = 1;


void setup(void)
{
  // start serial port
  Serial.begin(9600);

  // Start up the library
  sensors.begin();

  // initialize the LED pin as an output:
  pinMode(ledPin, OUTPUT);
  // initialize the pushbutton pin as an input:
  pinMode(sensorPin_Tilt, INPUT);

// Wifi connection
 // attempt to connect using WPA2 encryption:
  Serial.println("Attempting to connect to WPA network...");
  status = WiFi.begin(ssid, pass);

  // if you're not connected, stop here:
  if ( status != WL_CONNECTED) {
    Serial.println("Couldn't get a wifi connection");
    while(true);
  }
  // if you are connected, print out info about the connection:
  else {
    Serial.println("Connected to network");
  }
  // print your WiFi shield's IP address:
    Serial.print("IP Address: ");
    Serial.println(WiFi.localIP());
    
  printWifiStatus();

   // Connect to the server (your computer or web page)  
  if (client.connect(server, 90)) {
    Serial.println("--> connection ok\n");
    client.print("GET /write_data.php?"); // This
    client.print("value="); // This
    client.print("100"); // And this is what we did in the testing section above. We are making a GET request just like we would from our browser but now with live data from the sensor
    client.println(" HTTP/1.1"); // Part of the GET request
    client.print( "Host: " );
    client.println(server);
    client.println("Connection: close"); // Part of the GET request telling the server that we are over transmitting the message
    client.println(); // Empty line
    client.println(); // Empty line
    client.stop();    // Closing connection to server
    Serial.println("--> finished transmission\n");  
  }
    else {
    // If Arduino can't connect to the server (your computer or web page)
    Serial.println("--> connection failed\n");
  }
}


 void loop() {
  // listen for incoming clients
//  WiFiClient client = server.available();
      
  //Temperature Sensor Code
    // call sensors.requestTemperatures() to issue a global temperature
    // request to all devices on the bus
    sensors.requestTemperatures(); // Send the command to get temperatures

}


void printWifiStatus() {
  // print the SSID of the network you're attached to:
  Serial.print("SSID: ");
  Serial.println(WiFi.SSID());

  // print your WiFi shield's IP address:
  IPAddress ip = WiFi.localIP();
  Serial.print("IP Address: ");
  Serial.println(ip);

  // print the received signal strength:
  long rssi = WiFi.RSSI();
  Serial.print("signal strength (RSSI):");
  Serial.print(rssi);
  Serial.println(" dBm");
}


It seems that it connects to the server but the Database in SQL is not getting the new value.
From that I infer that the "GET" is not implemented.  


Could anyone assist me please as I've looked it in any possible way I could?


In addition, how can I test what is written to the server?

Jason64

I just managed to solve it.


The IP address of the server was given by

Code: [Select]
char server[] = "192.168.1.8";

Then I changed it to:

Code: [Select]
IPAddress server(192,168,1,8);

and worked like a charm.

Don't really know why but I'm happy that it worked! :)

gatheringfan

Thank you for this code, it works like a charm.

I used the code before and that worked instantly. But today I couldn't get this to work. Turned out that I have several different ethernet shields and not all work with this code. I have a cheap chinese shield (HanRun HR911102A) that does not work. With the original Arduino ethernet shield is works perfect.



SurferTim

#3
Apr 17, 2016, 04:33 pm Last Edit: Apr 17, 2016, 04:34 pm by SurferTim
I just managed to solve it.


The IP address of the server was given by

Code: [Select]
char server[] = "192.168.1.8";

Then I changed it to:

Code: [Select]
IPAddress server(192,168,1,8);

and worked like a charm.

Don't really know why but I'm happy that it worked! :)
I know why. The char version of the "server" variable will use DNS to resolve the domain, and a localnet IP will not resolve.

chizom3

#4
May 26, 2016, 06:41 pm Last Edit: May 26, 2016, 06:55 pm by chizom3
HI, am having a slight issue with this. The code only works when you send numbers, in my case am trying to send letter but it does not work i have tried using strings, char, it still wont work. Does anyone have any idea on how to do this. Also it is possible to send 2 data to the database at the same time for different columns.
This is my code
Code: [Select]

 void sqlsavedata()
  {

    String IDNO, StudentName;
    //if (UIDs_No ==1){
      IDNO =UID_tagA;
      StudentName ="cane";
     
     // }
    //String student;
     
  if (client.connect(server, 80)) {
    client.print("GET /write_data.php?"); // This
    client.print("value="); // This
    client.print(IDNO); // And this is what we did in the testing section above. We are making a GET request just like we would from our browser but now with live data from the sensor
    client.print("StudentName=");
    client.print(StudentName);
    client.println(" HTTP/1.1"); // Part of the GET request
    client.println("Host: 169.254.12.83"); // IMPORTANT: If you are using XAMPP you will have to find out the IP address of your computer and put it here (it is explained in previous article). If you have a web page, enter its address (ie.Host: "www.yourwebpage.com")
    client.println("Connection: close"); // Part of the GET request telling the server that we are over transmitting the message
    client.println(); // Empty line
    client.println(); // Empty line
    client.stop();    // Closing connection to server

  }

  else {
    // If Arduino can't connect to the server (your computer or web page)
    Serial.println("--> connection failed\n");
  }
 
  // Give the server some time to recieve the data and store it. I used 10 seconds here. Be advised when delaying. If u use a short delay, the server might not capture data because of Arduino transmitting new data too soon.
  delay(10000);
  }

PaulS

If you are sending two name/value pairs, the name and value are separated by equal signs and the pairs are separated by &s.

Print the GET request, and you will see that it is wrong.

Code: [Select]
    client.print("StudentName=");
should be
Code: [Select]
    client.print("&StudentName=");

Assuming that UID_tagA can be converter to a String (which you really should not be using). The client class is perfectly capable of converting most types to strings, so it is not necessary for you to "help" it be performing the
conversion to a string and then wrapping the string in a String.
The art of getting good answers lies in asking good questions.

chrisom

If someone could please advise, my script is working fine when pluged in via broadband, its when i connect via 3 or 4g is when i get the problem, and as my device will be living outddors and not even close to a broadband conection i am in trouble :(..


so i am uploading to a database on a live server not localhost, when in testing i used my broadband connection, and all worked well, now i have moved on to the next stage of my project and using a 3g dongle.

Now i am only getting one or two updates within 100 tries, but have full signal on the 3g.

I have overcome this by adding a 2 second delay after the the two empty line, and before colsing the conection.
All is good apart from my script stops running for 2 seconds, and other signals could now be missed.

If anyone could help with a way round this i would be very happy.


gourneha

hi chrisom I was also facing same problem like yours that working fine with broadband and not with dongle ,and in my case  it is easily sending integer data but when I am trying to send data i.e a time from RTC  received serially from arduino then gateway error 400 is shown other integer data received serially is being transferred not that time data.Please suggest any solution in case you got it

deejayzhen

May I know if what parts did you used sir?

Go Up