1

I'm having trouble to send data to my database with the ESP8266-01.

I'm getting the correct data from the sensor in the Console but nothing in my Database. The PHP Script is correct, I know that, just to be sure, I'm gonna add it in here too.

My Code:

 // http://playground.arduino.cc/Main/Average
#include <Average.h>
#include <SoftwareSerial.h>

char serialbuffer[100];//serial buffer for request url
SoftwareSerial mySerial(10, 11);

const char* ssid = "Master";
const char* password = "#Bennet99*";

const char* host = "server";

void setup() {
  Serial.begin(9600); // Connection to PC
  mySerial.begin(9600); // Connection to ESP8266
  pinMode(13, OUTPUT);
  digitalWrite(13, LOW);

  pinMode(7, OUTPUT);
  digitalWrite(7, LOW);
}

void loop() {
  float temp = getTemperatureAverage();
  Serial.println("Temperature: " + String(temp));
  sendTemperature(temp);

  delay(10000);
}

void sendTemperature(float temperature) {
  digitalWrite(7, HIGH);
  delay(2000);

  mySerial.println("AT+RST");
  WaitForReady(2000);
  mySerial.println("AT+CWMODE=1");
  WaitForOK(2000);
  mySerial.println("AT+RST");
  WaitForReady(2000);

  mySerial.println("AT+CWJAP=\"Master\",\"#Bennet99*\"");
  if (WaitForOK(5000)) {
    digitalWrite(13, HIGH); // Connection succesful
  }

  mySerial.println("AT+CIPSTART=\"TCP\",\"server\",80");
  WaitForOK(5000);
  mySerial.println("AT+CIPSEND=123");
  WaitForOK(5000);
  mySerial.print("GET /Intranet/Interface/modules/php/temp/temp.php?sensorid=\"1\"?humidity=\"1\"&temp=" + String(temperature) + " HTTP/1.0\r\n");
  mySerial.print("Host: server");
  WaitForOK(5000);
  mySerial.println("AT+CIPCLOSE");
  WaitForOK(5000);

  digitalWrite(13, LOW);
  digitalWrite(7, LOW);
}

float getTemperatureAverage() {
  Average<float> ave(10);
  for (int i = 0; i < 10; i++) {
    ave.push(getTemperature());
    delay(500);
  }

  float total = 0.0;
  delay(50);

  float temperature = ave.mean();

  return temperature;
}

float getTemperature() {
  int sensorVal = analogRead(A0);
  float voltage = (sensorVal / 1024.0) * 5.0;
  float temperature = (voltage - .5) * 100;

  return temperature;
}

boolean WaitForOK(long timeoutamount) {
  return WaitForResponse("OK", timeoutamount);
}

boolean WaitForReady(long timeoutamount) {
  return WaitForResponse("ready", timeoutamount);
}

// Parts used from https://github.com/contractorwolf/ESP8266
boolean WaitForResponse(String response, long timeoutamount) {
  unsigned long timeout = millis() + timeoutamount;

  while (millis() <= timeout) {
    while (mySerial.available() > 0) {
      int len = mySerial.readBytesUntil('\n', serialbuffer, sizeof(serialbuffer));

      String message = String(serialbuffer).substring(0, len - 1);

      if (message == response) {
        return true;
      }
    }
  }

  return false;
}

PHP:

<?php
$servername = "server";
$username = "root";
$password = "root";
$dbname = "Intranet";
$now = new DateTime();

$field = $_GET['sensorid'];
$value = $_GET['temp'];

$conn = mysql_connect("server","root","root");
if (!$conn)
{
    die('Could not connect: ' . mysql_error());
}
$con_result = mysql_select_db("some_database", $conn);
if(!$con_result)
{
    die('Could not connect to specific database: ' . mysql_error());    
}

    $datenow = $now->format("Y-m-d H:i:s");
    $hvalue = $value;

    $sql = "INSERT INTO `DataTable`(`logdata`, `field`, `value`) VALUES (\"$datenow\",\"$field\",$value)";
    $result = mysql_query($sql);
    if (!$result) {
        die('Invalid query: ' . mysql_error());
    }
    //echo "<h1>THE DATA HAS BEEN SENT!!</h1>";
    mysql_close($conn);
?>

Best Regards.

1
  • 2
    Script is correct no. Everywhere I go on these Arduino/ESP selfmade PHP pages they are SQL-injection vulnerable. Every single one. Please fix the security issues. To the question: You do not terminate your HTTP request correctly. mySerial.print("Host: server"); should be mySerial.print("Host: <actual IP address/domain>\r\n\r\n"). Then also adjust the value in CIPSEND. Also learn to use wireshark to dump the HTTP traffic sent, this way we can debug network issues much easier than going through a few hundred lines of your code. Commented Mar 9, 2017 at 20:13

3 Answers 3

1

The problem is in your algorithm. ESP module sometimes respond quickly and and sometimes respond late depends on your internet connection. for example you sent the command AT+CIPSTART="TCP","server",80 and then you WaitForOK(5000);. The ESP didn't reply OK yet and it is still connecting to the server, meanwhile your WaitForOK(5000); timed out and proceed for the next command.

I will suggest you to manually enter all those commands using SERIAL and check for the response.

Thanks. :)

Sign up to request clarification or add additional context in comments.

2 Comments

I probably have to set up many ESP's, so that would be the last alternative? Is there some way of getting a callback and them proceed?
The above mentioned suggestion is to check either your ESP is working fine with with those sequence of commands or not. once you find out how much time it is taking to process those commands, you can change WaitForOK(5000); and WaitForResponse(5000); functions. accordingly. You don't have to check all the ESP's on SERIAL. I my case, I was getting Bussy p... on the serial when EPS was trying to AT+CIPSTART="TCP","server",80
0

i have designed a channel over the thingspeak, where i am posting my data using the ESP8266 using the commands,

AT+CIPSTART="TCP","184.106.153.149",80

where AT+CIPSTART starts a TCP connection to the thingspeak having the IP (184.106.153.149), with the port number (80). Next to send the HTTP command

AT+CIPSEND=93

where 93 is the length of the command. and the command is

GET /update?api_key=key_of_my_account&field1=1.34&field2=2.89&field3=3.45&field4=4.67\r\n

this is successfully posting my data on the thingspeak channel.

2 Comments

Can you let us know how did you calculate the content length as 93?
The question is, how do you calculate 93? because it is 87!
0

So i am having same issue.But finally found the solution

  • First Connect to Server using following command :

      AT+CIPSTART="TCP","<IpAddress>",<port>
      AT+CIPSTART="TCP","127.0.0.0.1",80
    
  • Next we have to set the buffer size using following Command :

      AT+CIPSEND=0,<buffersize>
      AT+CIPSEND=0,29
    
  • Next we have to send GET command which is:

      e.g GET /sensor/temp1?temp=25
    

    The length for above is 25. We have to always add 4. So size will be 29.

  • Now, if you are using Arduino Serial, just press enter & it will hit the API endpoint.

Output

  • ESP01 Firmware Version Info:

    AT version:1.7.5.0(Oct 9 2021 09:26:04) SDK version:3.0.5(b29dcd3) compile time:Oct 15 2021 18:05:30 Bin version(Wroom 02):1.7.5

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.