Saving data from MySQL table to Arduino (Using php?)

Hey everyone,

I have a project where I need to store temperature and humidity data from a dht11 sensor into a table. I do this with an ESP01 as a webclient. The data is saved in the table with a php file. This already works

The second thing I need to do is read the data out of the table with a second webclient that is also an ESP01. But I have no idea how to do this. I want to save the temperature and humidity as a variable so I can use it to control a thermostat.

So far I have a php file that can read the newest data from the table using id’s. So i have a php file with the variables I need. This is where I got stuck. How do I get those php variables into my ESP01? The php file is located in the webserver. I’m using xampp as a webserver on my local network.

Here is my php code: (read_data.php)

<?php


$dbusername = "arduino_user";
$dbpassword = "1234";  
$server = "localhost"; 
$dbname = "opslagsensordata"; 

// Create connection
$conn = new mysqli($server, $dbusername, $dbpassword, $dbname);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}else{

} 

$sql = "SELECT ID, temp, humidity FROM sensordata ORDER BY id DESC LIMIT 1";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
// output data of each row
while($row = $result->fetch_assoc()) {
echo "ID: " . $row["ID"];
echo " - temp: ";
echo $row["temp"];
echo " humidity: ";
echo $row["humidity"];
echo "
";

$temp = $row["temp"]; //The temperature from the table gets saved in a temp variable. How do i get this variable in my arduino code?

}
} else {
echo "No result";
}
$conn->close();
?>

And here is my arduino code:

#include <ESP8266WiFi.h> 

const char* ssid = "networkname";       
const char* password = "password";

char server[] = "192.168.0.107";        

WiFiClient client;                       

#define RELAIS 2

unsigned long previousMillis = 0;    
const long interval = 30000;       

unsigned long currentMillis = 0;

void setup() {
 // put your setup code here, to run once:
 Serial.begin(115200); 
 delay(10);            

 Serial.println();
 Serial.println();
 Serial.print("Connecting with ");
 Serial.println(ssid);

 WiFi.begin(ssid, password);

 while(WiFi.status() != WL_CONNECTED){ 
   delay(500);                         
   Serial.print(".");
 }
 Serial.println();
 Serial.println("Connected with network");
 
 Serial.print("IP: ");
 Serial.print("http://");
 Serial.print(WiFi.localIP());         
 Serial.println("/");
 Serial.println();
}

void loop() {
 // put your main code here, to run repeatedly:

 if (client.available()) { 
   char c = client.read();
   Serial.print(c);       
 }

 requestData();
 
 currentMillis = millis();   }

void requestData(){

 if(currentMillis - previousMillis >= interval){
   
   previousMillis = currentMillis;

   if(client.connect(server, 80)){
     Serial.println("Connected with server");
     Serial.println();
     //HTTP request
     client.print("GET /read_data.php?"); //I make a connection with the server and use the read_data php file where the variables i need are located. So now what?
     client.println(" HTTP/1.1");
     client.println("Host: 192.168.0.107");
     client.println("Connection: close");
     client.println();
     client.println();
     client.stop();
     }
     else{
     Serial.println("Server connection failed");
     Serial.println();
     }
   }
 }

The result I get in the serial monitor so far is:

Connecting with (network)

Connected
IP: http://192.168.0.226/

Connected with server

//I make a connection with the server and use the read_data php file where the variables i need are located. So now what?

Find an example of an ESP8266 Arduino web client sketch which show retrieving data into a variable and then parsing it.

This may get you somewhere:

https://github.com/esp8266/Arduino/blob/master/libraries/Ethernet/examples/WebClient/WebClient.ino

In that example there is just a simple request to a website that reports back it's "status" i guess you could say. I'm not asking data from a website but from a php file and i don't know how to do that. I've already integrated some of the code from that example but it returns nothing to the serial monitor so far.
Thank you for the tip i'll do some more searching.

OK. But the URL http://192.168.0.107/read_data.php? works in a browser ?
Your PHP script is delivering rows of data, not a complete file so you'll have to parse out the rows.
You can use json to make it a bit easier by formatting the data, but regard that as a later optimisation because it has complexities of its own. If you can't retrieve anything at all from the Web server via the client, solve that problem first.

Yes that link does work. When i search that in google i see the latest row from the table in my database. And it also works to save data from the table into a variable. So that means that i have the php file with the variables. I just don’t know how i get those variables into my arduino code. I have no experience at all with json. Right now my esp01 succeeds to connect with the webserver but the webserver just doesn’t send anything back to my webclient. Do i have to add extra code in my php file?

Yes that link does work. When i search that in google i see the latest row from the table in my database.

I'm missing something here. That PHP/Web/mySQL server is in your local network ? From the non-routable address that is what it looks like. Where does Google fit in (or do you mean Google's Chrome web browser?).

When you enter the URL in the browser, do you see the http:// change to https:// ?

Anyway, what you see in the web browser should also be visible in the serial console of ESP8266 if it is based partly on the admittedly simple example I gave.

Post the modified code that you are using.

6v6gt:
Post the modified code that you are using.

and please read

How to use this forum

item #7 about how to post code.
It at the top of every forum topic.

I’m using xampp as a webserver on my localserver and that’s the IP of my computer. And yes i ment google chrome. When i search that link http://192.168.0.107/read_data.php? i get the result that i want. So you’re saying that what i see in my webbrowser i should also see in my serial monitor? then there must be something wrong with my arduino code.

Here’s the read_data.php code again:

<?php

$dbusername = "arduino_user"; 
$dbpassword = "1234";
$server = "localhost";
$dbname = "opslagsensordata";

// Create connection
$conn = new mysqli($server, $dbusername, $dbpassword, $dbname);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}else{

} 

$sql = "SELECT ID, temp, humidity FROM sensordata ORDER BY id DESC LIMIT 1";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
// output data of each row
while($row = $result->fetch_assoc()) {
echo "ID: " . $row["ID"];
echo " - temp: ";
echo $row["temp"];
echo " humidity: ";
echo $row["humidity"];
echo "
";

}
} else {
echo "No result";
}
$conn->close();
?>

Wvn22:
Here's the read_data.php code again:

Reposting code while completely ignoring what others have told you on how to post code.
That deserves a time out ! >:(

ieee488:
Reposting code while completely ignoring what others have told you on how to post code.
That deserves a time out !

I was posting that code while I got that reaction so i didn’t see it yet. I had to wait 5 minutes to change it again but it’s all good now!

The PHP is not the problem. It is the Arduino code. I didn't notice that you had updated the content of your opening post (which is also not good form here). I'm looking at it now.

OK. You can try this. The link I gave you was not completely appropriate for a wifi client. I haven’t tested it. I’ve simply based it on a different template (see first comment in sketch) and added bits from your sketch.

// based on  https://github.com/esp8266/Arduino/blob/master/libraries/ESP8266WiFi/examples/WiFiClient/WiFiClient.ino


// #include "DHT.h"
#include <ESP8266WiFi.h>



const char* ssid = "networkname";
const char* password = "password";

char server[] = "192.168.0.107";

WiFiClient client;

#define RELAIS 2

unsigned long previousMillis = 0;
const long interval = 30000;

unsigned long currentMillis = 0;

void setup() {
  // put your setup code here, to run once:
  Serial.begin(115200);
  delay(10);

  Serial.println();
  Serial.println();
  Serial.print("Connecting with ");
  Serial.println(ssid);

  WiFi.begin(ssid, password);

  while (WiFi.status() != WL_CONNECTED) {
    delay(500);
    Serial.print(".");
  }
  Serial.println();
  Serial.println("Connected with network");

  Serial.print("IP: ");
  Serial.print("http://");
  Serial.print(WiFi.localIP());
  Serial.println("/");
  Serial.println();
}

void loop() {
  delay(5000);

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

  // Use WiFiClient class to create TCP connections
  WiFiClient client;
  const int httpPort = 80;
  if (!client.connect( server, httpPort)) {
    Serial.println("connection failed");
    return;
  }

  // We now create a URI for the request


  Serial.print("Requesting URL: ");


  // This will send the request to the server

  //HTTP request
  client.print("GET /read_data.php?"); //I make a connection with the server and use the read_data php file where the variables i need are located. So now what?
  client.println(" HTTP/1.1");
  client.print("Host: " ) ; 
  client.println( server ) ; 
  client.println("Connection: close");
  client.println();
  client.println();


  unsigned long timeout = millis();
  while (client.available() == 0) {
    if (millis() - timeout > 5000) {
      Serial.println(">>> Client Timeout !");
      client.stop();
      return;
    }
  }

  // Read all the lines of the reply from server and print them to Serial
  while (client.available()) {
    String line = client.readStringUntil('\r');
    Serial.print(line);
  }

  Serial.println();
  Serial.println("closing connection");
}

That actually worked! Thank you for your efforts! This time the serial monitor showed this:

Connected with network
IP: http://192.168.0.226/

connecting to host192.168.0.107
Requesting URL: HTTP/1.1 200 OK
Date: Sat, 10 Mar 2018 14:31:32 GMT
Server: Apache/2.4.29 (Win32) OpenSSL/1.1.0g PHP/7.2.2
X-Powered-By: PHP/7.2.2
Content-Length: 34
Connection: close
Content-Type: text/html; charset=UTF-8

ID: 30 - temp: 19 humidity: 38

closing connection

Why does the original code not work? Also what if i want to save the temperature into a variable? Again thank you very much!

Why does the original code not work? Also what if i want to save the temperature into a variable? Again thank you very much!

I can't really answer #1. Sorry. But to save the data in variables, you have to parse it to separate it from the embedded text.
Looking at your PHP, it appears that it is so structured that it can return multiple rows (ie several temperature / humidity readings). Is this what you want to do ? Easiest is of course to process only the last set of readings. Important is that the PHP always returns a consistent format.
Anyway, you can google for "Arduino parse HTTP response" and you'll see a number of examples.

6v6gt:
Looking at your PHP, it appears that it is so structured that it can return multiple rows (ie several temperature / humidity readings). Is this what you want to do ?

I want the most recent value from the table and that already works in the php file.
Thank you for helping me out! I'll search for that parse HTTP response now.

I haven't saved the data in variables yet but I did find out why my original code didn't work. Right after the request to the server I run the command "client.stop();". I close my connection with the server before it can even send the data to me. So that's one mystery solved!