Nodemcu esp8266 to Database

[evkitz]

Hello, I am having a hard time fixing this

if error it will print insufficient coins

if no error it will print no error

the uploading data was not in the right table

heres my nodemcu esp8266 code

void loop() {
  if (Serial.available() > 0) {
    // Data is available, read it
    String dataFromArduino = Serial.readStringUntil('\n');

    // Print the received data
    Serial.print("Received data from Arduino: ");
    Serial.println(dataFromArduino);

    // Accumulate the data
    accumulatedData += dataFromArduino;

    // Check if "Coins Counted" message received
    if (dataFromArduino.indexOf("Coins Counted:") != -1) {
      coinsCounted = true; // Set flag to true
    }

    // Check if all data is received and coins counted
    if (coinsCounted && accumulatedData.indexOf("Total Money:") != -1) {
      // Start HTTP connection
      WiFiClient client;
      HTTPClient http;
      http.begin(client, "http://" + String(HOST) + "/upload_data.php");
      http.addHeader("Content-Type", "application/x-www-form-urlencoded");

      // Extracting data from the accumulated string
String income_fee;
String money_inserted;
String dispense_coins;
String total_money;
String error;
String coin_counter;

int index1 = accumulatedData.indexOf("Money Inserted: ");
int index2 = accumulatedData.indexOf("Fee: ");
int index3 = accumulatedData.indexOf("Dispense Coins: ");
int index4 = accumulatedData.indexOf("Total Money: ");
int index5 = accumulatedData.indexOf("Insufficient Coins!");
int index6 = accumulatedData.indexOf("Coins Counted: ");

if (index1 != -1 && index2 != -1 && index3 != -1 && index4 != -1) {
    income_fee = accumulatedData.substring(index2 + 5, index3 - 1); // Extracting fee dynamically
    money_inserted = accumulatedData.substring(index1 + 16, index2 - 1);
    dispense_coins = accumulatedData.substring(index3 + 15, index4 - 1);
    total_money = accumulatedData.substring(index4 + 13, index5 != -1 ? index5 - 1 : accumulatedData.length()); // Extracting total money

    // Extracting error message
    if (index5 != -1) {
        error = accumulatedData.substring(index5, accumulatedData.indexOf("\n", index5)); // Extracting error message dynamically
    } else {
        error = "No Error"; // Set error to "No Error" if no error message present
    }

    // Extracting coin counter
    if (index6 != -1) {
        coin_counter = accumulatedData.substring(index6 + 15, accumulatedData.indexOf("\n", index6)); // Extracting coin counter dynamically
    } else {
        coin_counter = ""; // If no coin counter message found
    }

    // Send data received from Arduino as POST request
    String postData = "income_fee=" + income_fee + "&money_inserted=" + money_inserted + "&dispense_coins=" + dispense_coins + "&total_money=" + total_money + "&error=" + error + "&coin_counter=" + coin_counter;
    int httpCode = http.POST(postData);

    // Check for a successful response
    if (httpCode == HTTP_CODE_OK) {
        Serial.println("Data uploaded successfully.");
    } else {
        Serial.print("HTTP request failed with error code: ");
        Serial.println(httpCode);
    }

    // End HTTP connection
    http.end();

    // Reset accumulated data and coinsCounted flag
    accumulatedData = "";
    coinsCounted = false;
}

  }
  }
  delay(500); // Wait for 0.5 seconds before checking again for data
}

my upload_data.php code

// Retrieve data from POST request
$money_inserted = $_POST['money_inserted'];
$fee = $_POST['fee'];
$dispense_coins = $_POST['dispense_coins'];
$total_money = $_POST['total_money'];
$error = $_POST['error'];
$coin_counter = $_POST['coin_counter'];

// Prepare SQL statement
$sql = "INSERT INTO coin_changer (money_inserted, income_fee, dispense_coins, total_money, error, coin_counter, date_time)
        VALUES ('$money_inserted', '$fee', '$dispense_coins', '$total_money', '$error', '$coin_counter', NOW())";

if ($conn->query($sql) === TRUE) {
    echo "New record created successfully";
} else {
    echo "Error: " . $sql . "<br>" . $conn->error;
}

// Close connection
$conn->close();
?>

[rsmls]

The problem is most likely in a problem with escaping special characters (quotation marks in particular).

In a case like this, I'd suggest locating the source of the problem by making the output visible of your ESP8266 code as well as the PHP script. For the ESP8266, hook it up to a serial monitor and ensure that the http request is always output to Serial so you can verify it. For the PHP script, do the same thing and echo the SQL query, but also the separate POST variables you've retrieved. This should give you some insight into where it goes wrong.

More generally speaking, one reason why you're hitting upon this is suboptimal database design, which may bite you in other ways in the future as well. It seems that you're storing numerical values (like no. of coins etc.) as textual data instead of integer numbers. Apart from presenting problems with data integrity (which is what you're bumping your head on currently), this makes your database large and inefficient, which can become a very real problem once your system is out there in the field. Also, indexing won't work particularly well on textual data, and it'll be limiting in analytical queries you may want to run later on.

Further inefficiencies arise from storing error messages as text strings; since the number of errors is limited to whatever your coin counter subsystem puts out, you could encode these error messages with an identification number and store this in the database. The frontend application can then display whatever text for each error number, making your database only a fraction of the size it is now and your frontend more flexible to boot.

A further concern is the use of a PHP script as a database frontend for your ESP8266; while this works OK in principle, an actual SQL connector (instead of a HTTP request) generally works more efficiently. However, there are also downsides to this approach since the MySQL/MariaDB connectors for the ESP platform can sometimes be difficult to troubleshoot. Still, I use this myself in a home monitor system successfully and it's certainly much faster and more efficient than HTTP requests.

In conclusion, instead of patching up this code, my recommendation would be to go back to the data model underlying your application and re-building your MySQL/MariaDB database in a more fitting way. It all starts by reading up on data types; I'd suggest starting here and determining what the smallest data type is for each information element you're storing, and then using this: https://mariadb.com/kb/en/data-types/

[evkitz]

thank you so much <3