Arduino UNO, update data in MySQL table

Question

I need to update a record in a MySQL table with Arduino UNO. I want to send data from HC-SR06 sensor to db. Firstly I need to see whether a record is updated or not. I am using Ethernet shield and Arduino UNO.

Here is my Arduino source code:

    #include <SPI.h>

#include <Ethernet.h>

byte mac[] = { 0xDE, 0xAD, 0xBE, 0xEF, 0xFE, 0xED }; 

byte ip[] = { 192, 168, 1, 177};

EthernetServer server(80); 

String txData ="";
String wname = "sensor1";

void setup()
{
  Serial.begin(9600);
  Ethernet.begin(mac, ip);
  server.begin(); 
}

void loop()
{
  txData = "name="+ wname;
  EthernetClient client = server.available(); 

  if (client) {
    delay (1000);
    Serial.println("  client is ok-->");
    boolean current_line_is_blank = true; 
    while (client.connected()) 
    {
      delay (1000);
      Serial.println("    client connected-->");
      if (client.available()) 
      {
        delay (1000);
        Serial.println("    client available-->");
        Serial.println("Connected to MySQL server. Sending data...");

        client.print("POST /update_data.php HTTP/1.1\n");
        client.print("Host: 192.168.1.177:80\n");
        client.print("Connection: close\n");
        client.print("Content-Type: application/x-www-form-urlencoded\n");
        client.print("Content-Length: ");
        client.print(txData.length());
        client.print("\n\n");
        client.print(txData);
        Serial.println("Successfull");
        delay (1000);

     }
   }
   delay(1);
   client.stop();
  }
}

Here is the update_data.php file:

<?php
    error_reporting(E_ALL);
    ini_set("display_errors", 1);

    echo "dbconnect.php will run";
    // Connect to MySQL
    include("dbconnect.php");

    // Prepare the SQL statement
    $query =  "update arduino.sensors SET value=1
    where name =  '$_POST[name]' ";    

    // Go to the review_data.php (optional)
    //header("Location: review_data.php");

    if(!@mysql_query($query))
    {
        echo "&Answer; SQL Error - ".mysql_error();
        return;

    mysql_close();
    }
?>

Here is the dbconnect.php file:

<?php
    error_reporting(E_ALL);
    ini_set("display_errors", 1);

$Username = "root";  // enter your username for mysql
$Password = "1234";  // enter your password for mysql
$Hostname = "localhost";      // this is usually "localhost" unless your database resides on a different server
$Database = "arduino"; //database name

$dbh = mysql_connect($Hostname , $Username, $Password) or die (mysql_error());;

 if (!$dbh){
   die('MySQL ERROR: ' . mysql_error());
    }

@mysql_select_db($Database) or die ('MySQL Error:'.mysql_error());
?>

Client is started and I see the logs on browser like this, bu sensor1 record is not updated.

name=sensor1POST /update_data.php HTTP/1.1
Host: 192.168.1.177:80
Connection: close
Content-Type: application/x-www-form-urlencoded
Content-Length: 12

mysql_* API is deprecated since 5.5 and removed in 7.0. And you are vulnerable to SQL injection. You should find a new SQL/PHP tutorial that isn't ancient. — gre_gor

JeffUK JeffUK · Accepted Answer · 2017-07-10T11:51:15

You are literally sending:

 update arduino.sensors SET value=1
 where name =  '$_POST[name]'

to the database. Unless you have a sensor called $_POST[name] nothing will happen!

You need to send the value of variable $_POST[name] as a parameter. I suggest you research Prepared Statements, as building a query by concatenating strings is open to SQL injection attacks.

Arduino UNO, update data in MySQL table

1 Answers