I have an error in my sql syntax for importing values from arduino via post, to a php script that should send them to my mysql table

Question

I am trying to setup a server and function that can take numerical data from an esp8266 and save it in a mysql database table. I am trying to use the http "POST" function to send the values from the esp8266 to a php script on my server that can transfer the received values into my mysql database on the same server.

The problem I am having is when I initialize the function on the esp8266, I receive this response from the server:

-> Error: INSERT INTO sensor_data (device_id, status, key, heading, velocity)

-> VALUES ('1', '1', '11000000111001', '', '')
You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'key, heading, velocity)

-> VALUES ('1', '1', '11000000111001', '', '')' at line 1

This is my first time using mysql as well as php so I'm sorry for the probable stupid question. My goal is to send multiple values (ints are acceptable) from my esp8266 to my mysql database to update a row in a table with new "current" values.

I have searched around but haven't found anyone with my exact problem yet. My hardware is a esp8266 connected to my home network via WiFi, and my apache/php/mysql server is a fresh install on a Raspberry Pi 3 B+. I have made sure the apache/php/mysql server is configured correctly by uploading a simple html page as index.php, and I've been able to connect and configure mysql via phpMyAdmin as well as HeidiSQL on my Windows 10 PC. All network settings are correct as far as I can tell when working over my LAN.

The esp8266 client/POST code:

  int Device_id = 1;
  int Status = 1;
  int key = 12345;
  int Heading = 69;
  int Velocity = 21;

  String data = "&device_id=" + String(Device_id, 2) + "&status=" + String(Status, 2) + "&key=" + String(key, 2) + "&heading" + String(Heading, 2) + "&velocity" + String(Velocity, 2);
String phpScript = "/sensor_data_upload.php";

    client.print("POST ");
    client.print(phpScript);
    client.println(" HTTP/1.1");
    client.println("Host: 192.168.1.155");
    client.println("User-Agent: Arduino/1.8.9");
    client.println("Connection: close");
    client.println("Content-Type: application/x-www-form-urlencoded; charset=UTF-8");
    client.print("Content-Length: ");
    client.println(data.length());
    client.println();
    client.print(data);

The php import/data-transfer code:

<?php
$device_id = $_POST['device_id'];
$status = $_POST['status'];
$key = $_POST['key'];
$heading = $_POST['heading'];
$velocity = $_POST['velocity'];

// MySQL server info
$servername = "localhost";
$username = "phpublisher";
$password = "phpublisher";
$dbname = "rov1_beta";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

$sql = "INSERT INTO sensor_data (device_id, status, key, heading, velocity)
VALUES ('$device_id', '$status', '$key', '$heading', '$velocity')";

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

$conn->close();
?>

I expect the mysql table sensor_data to be updated in the corresponding device_id row but as I said before, I get this error message:

20:20:00.587 -> Error: INSERT INTO sensor_data (device_id, status, key, heading, velocity)
20:20:00.587 -> VALUES ('1', '1', '11000000111001', '', '')<br>You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'key, heading, velocity)
20:20:00.621 -> VALUES ('1', '1', '11000000111001', '', '')' at line 1

Learn about prepared statements to prevent sql injection – Jens Jun 14 '19 at 04:40 — Jens, Jun 14 '19 at 04:40

score 1 · Accepted Answer · edited Jun 14 '19 at 05:12

1

key is the reserved keyword in MySQL, hence you need to escape the keyword with backtick symbol "`".

For example:

$sql = "INSERT INTO sensor_data (device_id, status, `key`, heading, velocity)
VALUES ('$device_id', '$status', '$key', '$heading', '$velocity')";

edited Jun 14 '19 at 05:12

Arulkumar

12,966
14
47
68

answered Jun 14 '19 at 04:22

Raka

427
2
10

Thank you @Raka! That seemed to be the problem. I decided to change my "key" to "passkey" which was an easy fix across systems. Now I just need to figure out why velocity and heading are zero values... :) – Amarotica Jun 14 '19 at 04:36
@Amarotica are you missing "=" sing for heading and velocity? in code `String data = "&device_id=" + String(Device_id, 2) + "&status=" + String(Status, 2) + "&key=" + String(key, 2) + "&heading" + String(Heading, 2) + "&velocity" + String(Velocity, 2);` – Raka Jun 14 '19 at 04:39
Yes, that was exactly right @Raka. I need to remember to proof read after I write... Thank you! – Amarotica Jun 14 '19 at 04:46

I have an error in my sql syntax for importing values from arduino via post, to a php script that should send them to my mysql table

1 Answers1