UV Sensor Data Upload Problem

[otronics]

Hi.

I have been using an Arduino Nano to upload UV data for a number of years.

Recently, I've changed website hosts and therefore have had to update where the SQL database lives.

Part of the reason for changing hosts is to go from non-secure to SSL.

I've updated my Arduino code and various script files to reflect new IP addresses.

The Arduino reads the sensor, connects to wi-fi and seems to upload but it isn't.

The result should appear here: https://weather.otronics.co.uk/html/uv_data.html but data hasn't updated since 13/02/2023.

I'm trying to work out if this is a server issue or coding issue.

See Serial Monitor output below:

Calculating sensor values...

ML8511 output: 306 | ML8511 voltage: 1.01 | UV Intensity (mW/cm^2): 0.16

Connecting to WiFi.......................

WiFi connected. SSID: OTronics Dining | Signal: -82 | IP address: 192.168.0.45

connecting to IP address - hidden from this post
Requesting URL: /UV/script.php?sensor=UV&value=0.16

Response from server:
-----------------------------------------------------------------------
HTTP/1.1 200 OK
date: Sat, 25 Feb 2023 16:20:19 GMT
content-type: text/plain
content-length: 9
cache-control: no-cache
x-cdn-cache-status: MISS
x-via: LHR1
connection: close

It works!-----------------------------------------------------------------------
Time taken: 5345
Disconnecting Wifi
Sleeping...

[ua6em]

phpMyAdmin...???

[otronics]

Thanks.

It's been a long time since I've worked with this.

It seems I am so close and some test uploads have connected, but haven't sent any data (see all the zeros in the right column).

[otronics]

Does anyone have any thoughts as I'm at a loss here.

If you'd like files with the code in or anything else, then let me know.

Many thanks.

[otronics]

@PaulRB - you have been helpful in the past. Any ideas?

[otronics]

Would it be anything to do with going from http to https?

[PaulRB]

Let's see script.php. Maybe add some debugging code to it, to discover if the URL parameters are being received ok, the database connection is accepted, the result from the SQL execution etc.

[otronics]

Usernames, passwords etc removed.

script.php:

<?php
$sensor=explode(",", $_GET["sensor"]);
$value=explode(",", $_GET["value"]);
$servername = "";
$username = "";
$password = "";
$dbname = "";

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

$stmt = $conn->prepare("INSERT INTO UV_Sensor (sensor_name, sensor_value) VALUES (?, ?)");


for ($i = 0; $i < count($sensor); $i++) {

	$stmt->bind_param("sd", $sensor[$i], $value[$i]);
	$stmt->execute();
	echo "New record created successfully: ", $sensor[$i], "=", $value[$i], "\n";
}

$conn->close();
?>

Interestingly, I can add a manual entry via a browser.

Eg/ entering this into my browser:

https://weather.otronics.co.uk/UV/script.php?sensor=UV&value=0.17

Makes the data appear here: UV Data

[otronics]

Not sure if you've seen the above @PaulRB

[PaulRB]

No, I didn't see it. You didn't reply to my post directly or mention me by name (@PaulRB) so the forum didn't notify me.

I'll take a look now.

[PaulRB]

@otronics

It works!

Where is this printed from?

I expected it was coming from the server, meaning the php script, and being echoed to serial monitor by the arduino, because it is between the two lines of "--------" that were intended to mark the start and end of the response from the server. But I don't see that text in your php script. Maybe it's printed by your Arduino code, but being printed in the wrong place, making it look like it came from the server.

What we don't see in the response from the server is "Connection failed" or "New record created successfully". So I wonder if the php script is running at all, or failing. Let's add some extra debugging to the PHP script:

<?php
echo "Starting script.php\n"
$sensor=explode(",", $_GET["sensor"]);
$value=explode(",", $_GET["value"]);
$servername = "";
$username = "";
$password = "";
$dbname = "";

// Create connection
echo "Connecting to database...\n"
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection

if ($conn->connect_error) {
    echo "Database connection failed!\n"
    die("Connection failed: " . $conn->connect_error);
}
echo "Database connected ok, inserting data...\n"

$stmt = $conn->prepare("INSERT INTO UV_Sensor (sensor_name, sensor_value) VALUES (?, ?)");


for ($i = 0; $i < count($sensor); $i++) {
    echo "Inserting data: ", $sensor[$i], "=", $value[$i], "\n";
	$stmt->bind_param("sd", $sensor[$i], $value[$i]);
	$stmt->execute();
	echo "New record created successfully: ", $sensor[$i], "=", $value[$i], "\n";
}

echo "Data inserted, closing database connection\n"

$conn->close();
echo "Ending script.php\n"
?>

These extra messages should be echoed back to the Arduino and appear on serial monitor between the "-----" lines. But first, test it by calling the php script from the browser, to see the extra messages in the response. Then test it from the Arduino.

[otronics]

I have updated the script.php code.

Here is a screenshot of the serial monitor.

My guess would be that "it works!" comes from the server. Not sure.

[otronics]

Trying from a browser came up with this:

and now comes up with error 500.

[otronics]

@PaulRB

[PaulRB]

By bad, I'm getting rusty on php! I missed the ; at the end of the echo lines I added to the script. Here you go

<?php
echo "Starting script.php\n";
$sensor=explode(",", $_GET["sensor"]);
$value=explode(",", $_GET["value"]);
$servername = "";
$username = "";
$password = "";
$dbname = "";

// Create connection
echo "Connecting to database...\n";
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection

if ($conn->connect_error) {
    echo "Database connection failed!\n";
    die("Connection failed: " . $conn->connect_error);
}
echo "Database connected ok, inserting data...\n";

$stmt = $conn->prepare("INSERT INTO UV_Sensor (sensor_name, sensor_value) VALUES (?, ?)");


for ($i = 0; $i < count($sensor); $i++) {
    echo "Inserting data: ", $sensor[$i], "=", $value[$i], "\n";
	$stmt->bind_param("sd", $sensor[$i], $value[$i]);
	$stmt->execute();
	echo "New record created successfully: ", $sensor[$i], "=", $value[$i], "\n";
}

echo "Data inserted, closing database connection\n";

$conn->close();
echo "Ending script.php\n";
?>

@otronics

My guess would be that "it works!" comes from the server. Not sure.

I don't think so. I think it comes from either your Arduino code or the php code. I can't see it in the php code so that's why I'm assuming it's in your arduino code. Did you search for it?

@otronics

Here is a screenshot of the serial monitor.

Please don't post screen shots on the forum when they contain only text. Just copy the text and paste it into your post between code tags.

[otronics]

Getting somewhere: adding data from a browser works with no errors using the new code, eg: https://weather.otronics.co.uk/UV/script.php?sensor=UV&value=0.18

"it works!" is not in the Arduino code or any php code.

Same output from the Arduino though @PaulRB:

Calculating sensor values...

ML8511 output: 304 | ML8511 voltage: 1.00 | UV Intensity (mW/cm^2): 0.10

Connecting to WiFi.......................

WiFi connected. SSID:  | Signal: -75 | IP address: 

connecting to 
Requesting URL: /script.php?sensor=UV&value=0.10

Response from server:
-----------------------------------------------------------------------
HTTP/1.1 200 OK
date: Wed, 26 Apr 2023 05:39:21 GMT
content-type: text/plain
content-length: 9
cache-control: no-cache
x-cdn-cache-status: MISS
x-via: LHR1
connection: close

It works!-----------------------------------------------------------------------
Time taken: 5250
Disconnecting Wifi
Sleeping...

[PaulRB]

I think it's pretty clear that the php script is working ok but the Arduino code is not triggering it at all.

Time to post your Arduino code.

[otronics]

@PaulRB

extern "C" {
#include "user_interface.h"
}

#include <ESP8266WiFi.h>
#include <Wire.h>
#include <SoftwareSerial.h>

#define SLAVE_RX D3
#define SLAVE_TX D4

int UVOUT = A0; //Output from the sensor
const byte REF_3V3 = A0; //3.3V power on the Arduino board

// Use WiFiClient class to create TCP connections
WiFiClient wsClient;

// Serial connection with slave MCU
SoftwareSerial slaveMCU(SLAVE_RX, SLAVE_TX, false, 64);

const char ssid[]     = "";
const char password[] = "";
const char host[]     =  "";

IPAddress ip();
IPAddress gateway(192,168,0,1);
IPAddress subnet(255,255,255,0);

const double e = 2.71828;

int readSerialWord() {
  
  byte h = slaveMCU.read();
  byte l = slaveMCU.read();
  return word(h, l);
  
}

void setup() {

  Serial.begin(115200);

  pinMode(UVOUT, INPUT);
  pinMode(REF_3V3, INPUT);

  pinMode(2, OUTPUT);
  
}

void loop() {

    digitalWrite(2, LOW);
    delay(500);
    digitalWrite(2, HIGH);
    delay(500);

  Serial.println();
  Serial.println("Calculating sensor values...");
  Serial.println();

   int uvLevel = averageAnalogRead(UVOUT);
 
  //Use the 3.3V power pin as a reference to get a very accurate output value from sensor
  float outputVoltage = 3.3 / 1000 * uvLevel;
 
  float uvIntensity = mapfloat(outputVoltage, 0.99, 2.9, 0.0, 15.0);

  Serial.print("ML8511 output: ");
  Serial.print(uvLevel);

  Serial.print(" | ML8511 voltage: ");
  Serial.print(outputVoltage);

  Serial.print(" | UV Intensity (mW/cm^2): ");
  Serial.print(uvIntensity);

  Serial.println();

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

  unsigned long startTime = millis();
  WiFi.begin(ssid, password);
  WiFi.config(ip, gateway, subnet);

  while (WiFi.status() != WL_CONNECTED && millis() - startTime < 30000UL) {
    delay(100);
    Serial.print(".");
  }

  if (WiFi.status() != WL_CONNECTED) {
    Serial.println();
    Serial.println("WiFi connection failed");
    delay (3000);
    ESP.restart();
  }
  else {
    digitalWrite(2, LOW);
    delay(100);
    digitalWrite(2, HIGH);
    delay(100);
    digitalWrite(2, LOW);
    delay(100);
    digitalWrite(2, HIGH);
    delay(100);

    Serial.println();
    
    Serial.println();
    Serial.print("WiFi connected. SSID: ");
    Serial.print(WiFi.SSID());
    Serial.print(" | Signal: ");
    Serial.print(WiFi.RSSI());
    Serial.print(" | IP address: ");
    Serial.println(WiFi.localIP());

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

    const int httpPort = 80;
    if (!wsClient.connect(host, httpPort)) {
      Serial.println("Host connection failed");
    }
    else {

      String sensors, values;

        if (uvLevel >= 0 && uvLevel <= 65000) {
        sensors += "UV";
        values += uvIntensity;
      }

      // We now create a URL for the request
      String url = "/script.php";
      url += "?sensor=";
      url += sensors;
      url += "&value=";
      url += values;

      Serial.print("Requesting URL: ");
      Serial.println(url);

      // This will send the request to the server
      wsClient.print(String("GET ") + url + " HTTP/1.1\r\n" +
                     "Host: " + host + "\r\n" +
                     "Connection: close\r\n\r\n");

      while (!wsClient.available()) {
        if (millis() - startTime > 30000UL) {
          Serial.println("request failed");
          break;
        }
        delay(1000);
      }
      Serial.println();
      Serial.println("Response from server:");
      Serial.println("-----------------------------------------------------------------------");
      // Read all the lines of the reply from server and print them to Serial
      while (wsClient.available()) {
        String line = wsClient.readStringUntil('\r');
        Serial.print(line);
      }
      
      Serial.println("-----------------------------------------------------------------------");
      Serial.print("Time taken: ");
      Serial.println( millis() - startTime);

    }
    Serial.println("Disconnecting Wifi");
    WiFi.disconnect();
  }

  Serial.println("Sleeping...");
  delay(60000);

}

//Takes an average of readings on a given pin
//Returns the average
int averageAnalogRead(int pinToRead)
{
  byte numberOfReadings = 8;
  unsigned int runningValue = 0;

  for (int x = 0 ; x < numberOfReadings ; x++)
    runningValue += analogRead(pinToRead);
  runningValue /= numberOfReadings;

  return (runningValue);
}

//The Arduino Map function but for floats
//From: http://forum.arduino.cc/index.php?topic=3922.0
float mapfloat(float x, float in_min, float in_max, float out_min, float out_max)
{
  return (x - in_min) * (out_max - out_min) / (in_max - in_min) + out_min;
}

[PaulRB]

@otronics

Part of the reason for changing hosts is to go from non-secure to SSL.

I've updated my Arduino code and various script files to reflect new IP addresses.

So the only thing you changed in the Arduino code is the IP address? I think that's the problem then. You also need to change it to use SSL instead of a non-secure connection.

I should have thought to ask you this back in post #2, but I think I must have read that you had updated the Arduino code, but missed that fact that you only actually changed the IP address.

@otronics

WiFiClient wsClient;

This is only non-secure connections I think. I've not updated my code to use SSL either, so I don't know, off the top of my head, everything that needs to change.

Here's a tutorial that may help

ESP8266 NodeMCU HTTPS Requests (Arduino IDE) | Random Nerd Tutorials

[otronics]

Yes @PaulRB - the only thing I've changed in the Arduino code is the IP address.

I'll have a look through that article you've sent. Thanks.