Why won't my arduino code upload data to database>?

NodeMCU board! I thought I had cracked it - but although my code now compiles and seems to work - it doesnt actually add to my database?

#include <TinyGPS++.h>
#include <SoftwareSerial.h>
#include <ESP8266WiFi.h>
#include <SPI.h>

// GPS Variables
static const int TXPin = 5; //(RX GPS -> D1(MCU)/TX(MCU)
static const int RXPin = 4; //(TX GPS -> D2(MCU)/RX(MCU)
static const uint32_t GPSBaud = 9600;
int i = 0, x = 0, loop1 = 0;
float latitude;
float longitude;

// The TinyGPS++ object
TinyGPSPlus gps;

// The serial connection to the GPS device
SoftwareSerial ss(RXPin, TXPin);

//WiFi Variables
const char* ssid = "Pretty Fly for a Wifi";
const char* password = "!153Rolleston153!";
WiFiServer server(80);



//Server Variables
const char* host = "192.168.0.11";
WiFiClient client;



void setup()
{
  //Starts GPS modules
  Serial.begin(115200);
  ss.begin(GPSBaud);

  // Connect to WiFi network
  connectwifi();

  // Start the server
  server.begin();
  Serial.println("Server started");

  // Print the IP address
  Serial.print("Use this URL to connect: ");
  Serial.print("http://");
  Serial.print(WiFi.localIP());
  Serial.println("/");

  Serial.println("Setup Complete.");
}



void loop()
{
  Serial.println("\nTest: ");
  Serial.println(i);
  delay(500);

  while (ss.available() < 0)
  {
  }
  // This sketch displays information every time a new sentence is correctly encoded.
  while (ss.available() > 0)
    if (gps.encode(ss.read()))
    {
      displayInfo();
    }
    else
    {
      Serial.println("Data not encoded");
    }

  if (millis() > 5000 && gps.charsProcessed() < 10)
  {
    Serial.println(F("No GPS detected: check wiring."));
    while (true);
  }
  i++;

  if (x == 10)
    while (1) {
      Serial.println("LOOP STUCK");
    }
}


void displayInfo()
{
  Serial.print(F("Location: "));
  if (gps.location.isValid())
  {
    latitude = gps.location.lat();
    longitude = gps.location.lng();
    Serial.print(gps.location.lat(), 6);
    Serial.print(F(", "));
    Serial.print(gps.location.lng(), 6);
    if (client.connect(host, 80))
    {

      Serial.println("\nAdding to database...");

      client.print("GET / send_data.php ? latitude = ");
      client.print(latitude);
      client.print("&longitude = ");
      client.print(longitude);
      client.println(" HTTP / 1.1");
      client.println("Host : 192.168.0.11"); // SERVER ADDRESS
      client.println( "Content - Type : text / php" );
      client.println("Connection : close");
      client.println();
      client.println();
      x++;
    }
    delay(5000);
  }
  else
  {
    Serial.print(F("INVALID"));
  }
  Serial.println();
}

void connectwifi() {
  Serial.println();
  Serial.println();
  Serial.print("Connecting to ");
  Serial.println(ssid);
  WiFi.begin(ssid, password);

  while (WiFi.status() != WL_CONNECTED)
  {
    delay(500);
    Serial.print("\nAttempting to Connect..");
  }
  //Acknowledge the connection
  Serial.println("");
  Serial.printf("\nConnected to the host : % s...", host);
}

The format or syntax of your GET request seems a little off to me.

Hi,

Cheers for the response - I watched that video earlier, but it refers to posting to a website / text file as opposed to an SQL database

My concern with my code is actually about my "host"

I am using phpmyadmin databsse running from Xampp.

What should I be entering as my "host" - and is this the same here:

//Server Variables
const char* host = "192.168.0.11";
WiFiClient client;

As here:

if (client.connect(host, 80))
    {

      Serial.println("\nAdding to database...");

      client.print("GET / send_data.php?latitude = ");
      client.print(latitude);
      client.print("&longitude = ");
      client.print(longitude);
      client.println(" HTTP / 1.1");
      client.println("Host : 192.168.0.11"); // SERVER ADDRESS <----
      client.println( "Content - Type : text / php" );
      client.println("Connection : close");
      client.println();
      client.println();
      x++;
    }

I think of server as remote server.

There is a significant difference between

"GET / send_data.php?latitude = "

and

"GET /send_data.php?latitude="

unless your server os allows filenames to begin with a space, which is foreign to me :slight_smile:

The Host address can be on your home network, I think (i.e. 192.168.xxx.xxx), but it should not be the router, is that what you're asking?

Also maybe you can post send_data.php?

Hi,

Okay, so I added in some code to my php file so it writes to a text file to see if the values are being received - they are not. The php file writes values when I manually run it in a browser.

Here is my updated arduino code:

    if (client.connect(host, 80))
    {

      Serial.println("\nConnected to database...");

      client.print("GET /send_data.php?latitude=");
      Serial.print("GET /send_data.php?latitude=");
      client.print(latitude);
      Serial.print(latitude);
      client.print("&longitude=");
      Serial.print("&longitude=");
      client.print(longitude);
      Serial.print(longitude);
      client.println("HTTP/1.1");
      client.println("Host: 192.168.0.11"); // SERVER ADDRESS
      client.println( "Content - Type : text/php" );
      client.println("Connection: close");
      client.println();
      client.println();

PHP file:

<?php
//require("config.php");
$dbHost = "localhost";
$dbUsername = "root";
$dbPassword = '';
$dbName = "map1";

// Opens a connection to a MySQL server
$connection = mysqli_connect($dbHost, $dbUsername, $dbPassword, $dbName); 
if (!$connection) {
die('Not connected : Ah shit ' . mysqli_error());
}

//Set the active MySQL database
$db_selected = mysqli_select_db($connection, $dbName );
if (!$db_selected) {
 die ('Can\'t use db : ' . mysqli_error());
}


//$request = $_GET['request'];

//$sql = "INSTERT INTO request.request (request) 
//VALUES ('$request', '$request', '$request', '$request', '$request')";

$latitude = $_GET['latitude'];
$longitude = $_GET['longitude'];

//TEST to see if values received. 
$myfile = fopen("testfile.txt", "w");
fwrite($myfile,$latitude);
fwrite($myfile,"\n");
fwrite($myfile,$longitude);
fwrite($myfile,"\n");
fclose($myfile);

//INSERT into SQL database
$sql = "INSERT INTO markers (name, address, lat, lng, type)
VALUES ('GPSname', 'GPSaddress','$latitude','$longitude', 'GPStype')";



// VALUES ('GPS', 'GPS','".$GET["latitude"]."','".$_GET["longitude"]."', 'GPS');

//Checks to see if new record made
if (mysqli_query($connection, $sql)) {
    echo "New record created successfully";
} else {
    echo "Error: " . $sql . "
" . mysqli_error($connection);
}

mysqli_close($connection);
?>

Thanks again for helping!

Hi,

Small task - trying to write data created via arduino to a text file using php.

What would the correct syntax be please?

Arduino code:

if (client.connect(host, 80))
    {

      Serial.println("\nConnected to database...");

      client.print("GET /send_data.php?latitude=");
      Serial.print("GET /send_data.php?latitude=");
      client.print(latitude);
      Serial.print(latitude);
      client.print("&longitude=");
      Serial.print("&longitude=");
      client.print(longitude);
      Serial.print(longitude);
      client.println("HTTP/1.1\r\n");
      client.println("Host: 192.168.0.11"); // SERVER ADDRESS
      client.println("Content-Type: text/php" );
      client.println("Connection: close");
      client.println();
      client.println();
      x++;
    }

PHP code:

<?php
//require("config.php");
$dbHost = "localhost";
$dbUsername = "root";
$dbPassword = '';
$dbName = "map1";

// Opens a connection to a MySQL server
$connection = mysqli_connect($dbHost, $dbUsername, $dbPassword, $dbName); 
if (!$connection) {
die('Not connected : Ah shit ' . mysqli_error());
}

//Set the active MySQL database
$db_selected = mysqli_select_db($connection, $dbName );
if (!$db_selected) {
 die ('Can\'t use db : ' . mysqli_error());
}


//$request = $_GET['request'];

//$sql = "INSTERT INTO request.request (request) 
//VALUES ('$request', '$request', '$request', '$request', '$request')";

$latitude = $_GET["latitude"];
$longitude = $_GET["longitude"];

//TEST to see if values received. 
$file = "testfile.txt"; 
file_put_contents($file,$latitude);
file_put_contents($file,$longitude);

$myfile = fopen("testfile.txt", "w");
fwrite($myfile,$latitude);
fwrite($myfile,"\n");
fwrite($myfile,$longitude);
fwrite($myfile,"\n");
fclose($myfile);

//INSERT into SQL database
$sql = "INSERT INTO markers (name, address, lat, lng, type)
VALUES ('GPSname', 'GPSaddress','$latitude','$longitude', 'GPStype')";



// VALUES ('GPS', 'GPS','".$GET["latitude"]."','".$_GET["longitude"]."', 'GPS');

//Checks to see if new record made
if (mysqli_query($connection, $sql)) {
    echo "New record created successfully";
} else {
    echo "Error: " . $sql . "
" . mysqli_error($connection);
}

mysqli_close($connection);
?>

Nothing writes to the text file "testfile.txt" for some reason? If I manually run my php script in browser and plug in values, it writes to text file.

Cheers in advance

OK, this is the output on my serial buffer from a successful post request, which I think would be the same as get

Received String: AT+CIPSTART="TCP","mclsandpoint.org",80

CONNECT

OK

Received String: AT+CIPSEND=151


OK
> 

Received String: 
Recv 151 bytes

SEND OK

+IPD,154:HTTP/1.1 200 OK
Date: Wed, 11 Apr 2018 13:55:44 GMT
Server: Apache
Transfer-Encoding: chunked
Content-Type: text/html; charset=utf-8

9
Success



+IPD,5:0


CLReceived String: OSED

In your code I don't see a AT+CIPSEND=x bytes. As far as I know you must let the server know how long
it is, but sounds like you're a little more advanced than me!

maybe move your thread to Networking Protocols ... Someone more experienced than me could probably solve your issue.

Anybody?

client.println("HTTP/1.1\r\n");

println already prints the CR LF, you are printing it twice, so the request is invalid (the server sees the headers as the body).

Your biggest mistake is that you completely ignore the response of the server. It would've told you what's wrong with the request.

But the vulnerabilities in your PHP script are even worse: you should read up on SQL injection.
Use PDO and prepared statements instead of mysqli and strings.
http://wiki.hashphp.org/PDO_Tutorial_for_MySQL_Developers

Pieter

Hi Pieter,

I'm new to this - how do I check the server response? Im using Xampp

I'm trying to send my latitude and longitude values to my xampp sql database.

  1. How do I check if my server is actually connected?
  2. Assuming it connects fine, why isnt my database updating with new rows?

Arduino code:

#include <TinyGPS++.h>
#include <SoftwareSerial.h>
#include <ESP8266WiFi.h>
#include <SPI.h>

// GPS Variables
static const int TXPin = 5; //(RX GPS -> D1(MCU)/TX(MCU)
static const int RXPin = 4; //(TX GPS -> D2(MCU)/RX(MCU)
static const uint32_t GPSBaud = 9600;
int i = 0, x = 0, loop1 = 0;
float latitude;
float longitude;

// The TinyGPS++ object
TinyGPSPlus gps;

// The serial connection to the GPS device
SoftwareSerial ss(RXPin, TXPin);

//WiFi Variables
const char* ssid = "Pretty Fly for a Wifi";
const char* password = "!153Rolleston153!";
//WiFiServer server(80);



//Server Variables
//IPAddress ip(192,168,0,11); 
const char* host = "192.168.0.11";
//char server[] = "192.168.0.11"; 
IPAddress server(192,168,0,11);
WiFiClient client;



void setup()
{
  //Starts GPS modules
  Serial.begin(115200);
  ss.begin(GPSBaud);

  // Connect to WiFi network
  connectwifi();

  // Start the server
  //server.begin();
  //Serial.println("Server started");

  // Print the IP address
  Serial.print("Use this URL to connect: ");
  Serial.print("http://");
  Serial.print(WiFi.localIP());
  Serial.println("/");

  Serial.println("Setup Complete.");
}



void loop()
{

  while (ss.available() < 0)
  {
  }
  // This sketch displays information every time a new sentence is correctly encoded.
  while (ss.available() > 0)
    if (gps.encode(ss.read()))
    {
      displayInfo();
    }

  if (millis() > 5000 && gps.charsProcessed() < 10)
  {
    Serial.println(F("No GPS detected: check wiring."));
    while (true);
  }
  i++;

  if (x == 10)
    while (1) {
      Serial.println("LOOP STUCK");
    }
}


void displayInfo()
{
  Serial.print(F("Location: "));
  if (gps.location.isValid())
  {
    Serial.println("Number of Satellites: ");
    Serial.println(gps.satellites.value());
    latitude = gps.location.lat();
    longitude = gps.location.lng();
    Serial.print(gps.location.lat(), 6);
    Serial.print(F(", "));
    Serial.print(gps.location.lng(), 6);
    if (client.connect(server, 80))
    {

      Serial.println("\nConnected to database...");

      client.print("GET /send_data.php?");
      Serial.print("GET /send_data.php?");
      client.print("latitude=");
      Serial.print("latitude=");
      client.print(latitude);
      Serial.print(latitude);
      client.print("&longitude=");
      Serial.print("&longitude=");
      client.print(longitude);
      Serial.print(longitude);
      client.println(" HTTP/1.1");
      client.println("Host: localhost"); // SERVER ADDRESS
      client.println("Content-Type: text/php" );
      client.println("Connection: close");
      client.println();
      client.println();
      client.stop();
      x++;
    }
    else 
    {
      Serial.println("\nERROR - Cannot connect to Database"); 
    }
    delay(500);
  }
  else
  {
    Serial.print(F("INVALID"));
  }
  Serial.println();
}

void connectwifi() {
  Serial.println();
  Serial.println();
  Serial.print("Connecting to ");
  Serial.println(ssid);
  WiFi.begin(ssid, password);

  while (WiFi.status() != WL_CONNECTED)
  {
    delay(500);
    Serial.print("\nAttempting to Connect..");
  }
  //Acknowledge the connection
  Serial.println("");
  Serial.printf("\nConnected to the host : % s...", host);
}
/

PHP code:

<?php
//require("config.php");
$dbHost = "localhost";
$dbUsername = "root";
$dbPassword = '';
$dbName = "map1";

// Opens a connection to a MySQL server
$connection = mysqli_connect($dbHost, $dbUsername, $dbPassword, $dbName); 
if (!$connection) {
die('Not connected : Ah shit ' . mysqli_error());
}

//Set the active MySQL database
$db_selected = mysqli_select_db($connection, $dbName );
if (!$db_selected) {
 die ('Can\'t use db : ' . mysqli_error());
}


//$request = $_GET['request'];

//$sql = "INSTERT INTO request.request (request) 
//VALUES ('$request', '$request', '$request', '$request', '$request')";

$latitude = $_GET['latitude'];
$longitude = $_GET['longitude'];


//TEST to see if values received. 
$file = "testfile.txt"; 
file_put_contents($file,$latitude);
file_put_contents($file,$longitude);

$myfile = fopen("testfile.txt", "w");
fwrite($myfile,$latitude);
fwrite($myfile,"\n");
fwrite($myfile,$longitude);
fwrite($myfile,"\n");
fclose($myfile);

//INSERT into SQL database
$sql = "INSERT INTO markers (name, address, lat, lng, type)
VALUES ('GPS', 'GPS','".$GET["latitude"]."','".$_GET["longitude"]."', 'GPS')";

// VALUES ('GPSname', 'GPSaddress','$latitude','$longitude', 'GPStype')";



// 

//Checks to see if new record made
if (mysqli_query($connection, $sql)) {
    echo "New record created successfully";
} else {
    echo "Error: " . $sql . "
" . mysqli_error($connection);
}

mysqli_close($connection);
?>

Also; is there a specific folder my arduino code document should be placed in? Do I place it in htdocs also, or does it not matter?

Trust me im no expert! haha! I made a similar post in the forum you suggested!

Does anyone else have any ideas?

Try:

      client.print(longitude);
      Serial.print(longitude);
      client.println(" HTTP/1.1");

All I did was insert a space before "HTTP/1.1". Otherwise there is no whitespace between the URL and the rest of the message.

Have a look at my code for comparison.

Hi Paul,

Thank you for the response.

I have put a space in - and copied some of your code to see if it gets it working. Where you have your host as "www.hostname.co.uk" - what should I have?

I am using Xampp, should I have "localhost" or should I have the IP my computer is currently using (192.168.0.11)?

Cheers

It's probably not so clever to close the server connection without waiting for the servers reply.

I have posted many project on this topic.. might be worth searching for the files.

https://forum.arduino.cc/index.php?topic=452765.msg3134238#msg3134238

(and many others)

  • And you should use the IP of the machine that has the XAMP install...

Does your PHP script dump/insert the to database correctly? (without the Arduino portion)

I would also suggest you use PDO instead of MYSQLI... or at the very least scrub/sanitize your data!

  • as it stands now.. this is wide open to a SQL injection attack. :slight_smile:

My MySQL database is not running locally, it's on a remote (annually rented) server. I give the address of that server as an "xyz.co.uk" address rather than an IP address.

reported to moderator

cross-post

don't bother

Stop cross-posting

http://forum.arduino.cc/index.php?topic=540875
http://forum.arduino.cc/index.php?topic=540929
http://forum.arduino.cc/index.php?topic=540901
http://forum.arduino.cc/index.php?topic=540914

You're wasting everybody's time!

Read this:

Much merging and purging.

@Ryanlb96, do not cross-post again.

@Ryanlb96 will be spending some time away from the forum.