Go Down

Topic: Retrieving data from database using Wemos D1 ESP8266 (Read 350 times) previous topic - next topic

LamySae

Hello everyone,

Actually I'm working on a project related to home automation and solar energy . It envolves a Wemos D1 ESP8266 to retrieve data from a database which stores the photovoltaic power, the load consumption and the power consumed from the grid.

In order to simplify the project , I create a simple database on a local server using XAMPP. I fill the table with  random values of powers .You can see the table in the attachements.

Then, I write a php file (display_mysql.php )to fetch the table and store it in connectDB folder which is stored in the htdocs XAMPP folder :
Code: [Select]
<html><head><title>MySQL Table Viewer</title></head><body>
<?php
$db_host 
'localhost' ;
$db_user 'ESP8266';
$db_pwd 'passwd';
$database 'power';
$table 'data';

if (!
mysql_connect($db_host$db_user$db_pwd))
    die(
"Can't connect to database");

if (!
mysql_select_db($database))
    die(
"Can't select database");


$result mysql_query("SELECT * FROM {$table}");
if (!
$result) {
    die(
"Query to show fields from table failed");
}

$fields_num mysql_num_fields($result);

echo 
"<h1>Table: {$table}</h1>";
echo 
"<table border='1'><tr>";

for(
$i=0$i<$fields_num$i++)
{
    
$field mysql_fetch_field($result);
    echo 
"<td>{$field->name}</td>";
}
echo 
"</tr>\n";

while(
$row mysql_fetch_row($result))
{
    echo 
"<tr>";

    foreach(
$row as $cell)
        echo 
"<td>$cell</td>";

    echo 
"</tr>\n";
}
mysql_free_result($result);
?>

</body></html>


Finally , I write an arduino code to connect the ESP8266 to the database and display the table on the serial monitor:
Code: [Select]
#include <ESP8266WiFi.h>
#include <WiFiClient.h>
#include <MySQL_Connection.h>

IPAddress server_addr(192,168,1,13);
char user[] = "ESP8266";
char password[] = "passwd";
char ssid[] = "Wi-Fi";
char pass[] = "120302";

WiFiClient client;
MySQL_Connection conn((Client *)&client);

void setup() {
        Serial.begin(115200);
       WiFi.begin(ssid, pass);
       
       while ( WiFi.status() != WL_CONNECTED ) {
              delay ( 500 );
              Serial.print ( "." );
       }
       
       Serial.println ( "" );
       Serial.print ( "Connected to " );
       Serial.println ( ssid );
       Serial.print ( "IP address: " );
       Serial.println ( WiFi.localIP() );
     
       Serial.println("DB - Connecting...");
     
}

void loop(){

  while (conn.connect(server_addr, 3306, user, password) != true) {
              delay(500);
              Serial.print ( "." );
              String url = String("connectDB/display_mysql.php");
              client.println(String("GET") + url +  "HTTP/1.1\r\n" +  "\r\n" +
               "Connection: close\r\n\r\n");
       }

 
  delay(1000);
 
 
  while(client.available()){
    String line = client.readStringUntil('\r');
    Serial.print(line);
  }

   
  Serial.println();
  Serial.println("connection closed");

  delay(30000);
 
}



The ESP8266 connects to the wifi but I don't get any data on the serial monitor.
My final goal is to fetch the 3 powers and store each one in a variable to use them later.
Any help please !

rw950431

You are mixing up HTTP GETs with SQL Selects in your arduino code.

That is, you attempt to connect the 'conn' object but then send data to the 'client' object which is never initialised.

Suggest you start with the example https://github.com/esp8266/Arduino/blob/master/libraries/ESP8266WiFi/examples/WiFiClient/WiFiClient.ino and modify to suit. You dont need SQL connection if you have PHP script as front-end.


Go Up