Cant sent POST request to server with NodeMCU

Question

I have a NodeMCU programmed in the Arduino language. I'm trying to send some data from my sensors to a PHP script on my server which then writes the data to a MySQL server. When ran through the URL the script runs fine but when I try sending values from my code I get this error

200 INSERT INTO sensor (temp, light) VALUES ('', '')
SQLSTATE[HY000]: General error: 1366 Incorrect integer value: '' for column 'temp' at row 1

Now I understand that this is happening because there is no data being sent to the PHP script and an empty space isn't an integer.

Here's the code on the NodeMCU

#include <ESP8266HTTPClient.h>
#include <ESP8266WiFi.h>

void setup() {

  Serial.begin(115200);                                  //Serial connection
  WiFi.begin("ssid", "password");   //WiFi connection

  while (WiFi.status() != WL_CONNECTED) {  //Wait for the WiFI connection completion

    delay(500);
    Serial.println("Waiting for connection");

  }

}

void loop() {

 if(WiFi.status()== WL_CONNECTED){   //Check WiFi connection status

   HTTPClient http;    //Declare object of class HTTPClient

   http.begin("http://server_public_ip_address/test3.php");      //Specify request destination
   http.addHeader("Content-Type", "text/plain");  //Specify content-type header

   int httpCode = http.POST("?temp=20&light=88");   //Send the request
   String payload = http.getString();                  //Get the response payload

   Serial.println(httpCode);   //Print HTTP return code
   Serial.println(payload);    //Print request response payload

   http.end();  //Close connection

 }else{

    Serial.println("Error in WiFi connection");   

 }

  delay(3000);  //Send a request every 30 seconds

}

I suspect that the problem is somewhere in http.addHeader("Content-Type", "text/plain"); or int httpCode = http.POST("?temp=20&light=88"); lines.

Also, this is a just a test program. The final program will take variables in this line int httpCode = http.POST("?temp=20&light=88"); instead of the numbers. So how do I write that as well?

EDIT:
Adding the PHP script as well

<?php
$servername = "localhost";
$username = "root";
$password = "*************";
$dbname = "testbox1";

try {
    $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
    // set the PDO error mode to exception
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $temp = $_GET["temp"];
    $light = $_GET["light"];
    $sql = "INSERT INTO sensor (temp, light)
    VALUES ('$temp', '$light')";
    // use exec() because no results are returned
    $conn->exec($sql);
    echo "New record created successfully";
    }
catch(PDOException $e)
    {
    echo $sql . "<br>" . $e->getMessage();
    }

$conn = null;
?>

Answer 1

You were passing

http.POST("?temp=20&light=88")

Which should be as

http.POST("temp=20&light=88")

and the header

http.addHeader("Content-Type", "text/plain")

is basically used to send plain text, you can refer to the docs of content-type header here. Since, you are sending form data, this should be used

http.addHeader("Content-Type", "application/x-www-form-urlencoded")

The following code should solve your issue.

#include <ESP8266HTTPClient.h>
#include <ESP8266WiFi.h>

void setup() {

  Serial.begin(115200);                                  //Serial connection
  WiFi.begin("ssid", "password");   //WiFi connection

  while (WiFi.status() != WL_CONNECTED) {  //Wait for the WiFI connection completion

    delay(500);
    Serial.println("Waiting for connection");

  }

}

void loop() {

 if(WiFi.status()== WL_CONNECTED){   //Check WiFi connection status

   HTTPClient http;    //Declare object of class HTTPClient

   http.begin("http://server_public_ip_address/test3.php");      //Specify request destination
   http.addHeader("Content-Type", "application/x-www-form-urlencoded");  //Specify content-type header

   int httpCode = http.POST("temp=20&light=88");   //Send the request
   String payload = http.getString();                  //Get the response payload

   Serial.println(httpCode);   //Print HTTP return code
   Serial.println(payload);    //Print request response payload

   http.end();  //Close connection

 }else{

    Serial.println("Error in WiFi connection");   

 }

  delay(3000);  //Send a request every 30 seconds

}

Use this as your PHP file to view the sent values:

<?php 
    print_r($_POST);
?>

The issue was with your PHP script. You have to use $_POST, instead of $_GET. Also, there was a small error in your PDO syntax. I have tried to fix it. Check this:

<?php
$servername = "localhost";
$username = "root";
$password = "*************";
$dbname = "testbox1";

try {
    $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
    // set the PDO error mode to exception
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $temp = $_POST["temp"];
    $light = $_POST["light"];
    $sql = $conn->prepare("INSERT INTO sensor (temp, light) VALUES (?, ?)");
    $sql->bindParam(1, $temp);
    $sql->bindParam(2, $light);
    $sql->execute();
    echo "New record created successfully";
    }
catch(PDOException $e)
    {
    echo $sql . "<br>" . $e->getMessage();
    }
?>