Trying to add data to my MySQL database using POST

Question

I'm trying to add data to my MySQL database using a POST method on my server and client.print on my ESP8266 V3 board.

This is my Arduino code that seems like its working because no errors occur and serial monitor goes through all steps.

#include <ESP8266WiFi.h>

const char* ssid     = "FRITZ!Box 7430 BZ";
const char* password = "*************";

const char* host = "alexander-productions.de/mysql";
const char* streamId   = "....................";
const char* privateKey = "....................";

void setup() {
  Serial.begin(115200);
  delay(10);

  // We start by connecting to a WiFi network

  Serial.println();
  Serial.println();
  Serial.print("Connecting to ");
  Serial.println(ssid);

  /* Explicitly set the ESP8266 to be a WiFi-client, otherwise, it by default,
     would try to act as both a client and an access-point and could cause
     network-issues with your other WiFi-devices on your WiFi-network. */
  WiFi.mode(WIFI_STA);
  WiFi.begin(ssid, password);

  while (WiFi.status() != WL_CONNECTED) {
    delay(500);
    Serial.print(".");
  }

  Serial.println("");
  Serial.println("WiFi connected");  
  Serial.println("IP address: ");
  Serial.println(WiFi.localIP());
}

int value = 0;

void loop() {
  delay(5000);
  ++value;

  Serial.print("connecting to ");
  Serial.println(host);

  ///
  String a = "TestID";
  String b = "TestNachmame";
  String data = "value1=" + a + "&value2=" + b;
  ///

  // Use WiFiClient class to create TCP connections
  WiFiClient client;
  const int httpPort = 80;
  if (!client.connect(host, httpPort)) {
    Serial.println("connection failed");
    return;
  } else {
    Serial.println("connection successful");
    client.println("POST /post.php HTTP/1.1");
    client.println("Host: alexander-productions.de/mysql"); // SERVER ADDRESS HERE TOO
    client.println("Content-Type: application/x-www-form-urlencoded");
    client.print("Content-Length: ");
    client.println(data.length());
    client.println();
    client.print(data);
    Serial.println("Sending to Database successful!");
  }


  Serial.println();
  Serial.println("closing connection");
}

And this is my server code that should add data to the MySQL database.

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

// Create connection
$conn = mysqli_connect($servername, $username, $password, $dbname);

// Check connection
if (!$conn) {
        die("Connection failed: " . mysqli_connect_error());
}
echo "Connected successfully";
echo "<br/>";

$var1=$_POST["value1"];
$var2=$_POST["value2"];

echo $var1;
echo "<br/>";
echo $var2;
echo "<br/>";

$query = "INSERT INTO `accounts` (`firstName`, `lastName`)
VALUES ('".$var1."','".$var2."')";

if (mysqli_query($conn, $query)) {
    echo "New record created successfully" . "<br />";
} else {
    echo "Error: " . $query . "<br>" . mysqli_error($conn);
}
#mysql_query($query,$conn);
mysqli_close($conn);
?>

The problem is I don't get any new entries in my database table and I don't know how to debug since I can't see what happens on the server side when the Arduino tries to POST data.

When opening the post.php file on the browser It works since the database gets a new entry containing NULL's.

Answer 1

alexander-productions.de/mysql is not a valid host.
Change it to alexander-productions.de.

Then add the folder to the request path i.e. /mysql/post.php.

It should look like:

const char* host = "alexander-productions.de";

client.println("POST /mysql/post.php HTTP/1.1");
client.print("Host: ");
client.println(host); // non hardcoded host header
client.println("Content-Type: application/x-www-form-urlencoded");
client.print("Content-Length: ");
client.println(data.length());
client.println();
client.print(data);