How to read data from database with PHP for WeMos EPS8266

Assalam-o-Alaikum!

Dear friends today we will learn more interesting thing. We are going to pull out data from Database using PHP for WeMos. It is very important step for creating project for accessing devices status online.

Problem:

Let’s suppose you are going to design a project that have several electronics instruments and you need a web based software that will set the status of each instrument ON or OFF and then from database you pull data and get status of each instrument and then perform action on each instrument based on pulled data with WeMos of Arduino.

Solution:

Here is the solution.

You need a Server, Database and some PHP code that will do the magic. First design a database and then write code in PHP and then from WeMos send GET request to server, it will send you status of instrument and you just do something in WeMos.

For simplicity I will work on only two records. Let’s suppose I have installed two lights, one in my Guest Room and second in my Office for this database will be like this:

CREATE TABLE `devices_status` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `device_name` varchar(50) DEFAULT NULL,
  `device_status` tinyint(1) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;

INSERT INTO `devices_status` VALUES ('1', 'home_light', '1');
INSERT INTO `devices_status` VALUES ('2', 'shop_light', '0');

CREATE TABLE `devices_status` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`device_name` varchar(50) DEFAULT NULL,

`device_status` tinyint(1) DEFAULT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;

INSERT INTO `devices_status` VALUES ('1', 'home_light', '1');

INSERT INTO `devices_status` VALUES ('2', 'shop_light', '0');

Now you have database and now you need PHP code to handle the request. Copy and paste below code and save as get_status.php into server. Here is PHP code:-

<?php
    include_once('inc.inc');
    $device_name    =   $_GET['device_name'];
    // Check connection
    if ($conn->connect_error) {
        die("Connection failed: " . $conn->connect_error);
    }
    
    $sql        =   "SELECT device_status FROM devices_status WHERE device_name='$device_name' LIMIT 1";
    $result     =   $conn->query($sql);
    
    if ($result->num_rows > 0) {
    
    while ($row = $result->fetch_assoc())
    {
        echo $row['device_status'];
    }
        
    } else {
        echo "Error:" . $sql . "<br>" . $conn->error;
    }
    
    $conn->close();
?>

<?php

include_once('inc.inc');

$device_name = $_GET['device_name'];

// Check connection

if ($conn->connect_error) {

die("Connection failed: " . $conn->connect_error);

}

$sql = "SELECT device_status FROM devices_status WHERE device_name='$device_name' LIMIT 1";

$result = $conn->query($sql);

if ($result->num_rows > 0) {

while ($row = $result->fetch_assoc())

{

echo $row['device_status'];

}

} else {

echo "Error:" . $sql . "<br>" . $conn->error;

}

$conn->close();

Code is very simple you just receive GET request from anywhere and then look value into database based on request and then simple echo status. On the other hand, in WeMos, you send the request to get_status.php with a parameter and then receive reply from server and store it into variable and then perform action based on that reply.

Now lets see what happened in WeMos. First see the code:-

//include libraries
#include <ESP8266HTTPClient.h>
#include <ESP8266WiFi.h> 

//Access point credentials
const char* ssid = "Password lyna hay";
const char* pwd = "Google@007";
const char* host = "http://192.168.8.101";
String get_host = "http://192.168.8.101";

WiFiServer server(80);  // open port 80 for server connection

void setup() 
{
  Serial.begin(115200); //initialise the serial communication
  delay(20);

  WiFi.begin(ssid, pwd);

   
    //starting the server
    server.begin();
 
}

void loop()
{
  //call_test();
  get_device_status("home_light","Home Light");
  get_device_status("shop_light","Shop Light");
       
}


void call_test()
{
  Serial.println("It is test");
  }

void get_device_status(String device_name, String device_text)
  {
    
    WiFiClient client = server.available();

        HTTPClient http;
        String url = get_host+"/temperature/get_status.php?device_name="+device_name;
        
        http.begin(url);
       
        //GET method
        int httpCode = http.GET();
        String payload = http.getString();
        if(payload=="1")
        {
          Serial.println(device_text+" is ON");
        }
        else
        {
          Serial.println(device_text+" is Off");
          }
        http.end();
        delay(1000);
  
  }

//include libraries

#include <ESP8266HTTPClient.h>

#include <ESP8266WiFi.h>

//Access point credentials

const char* ssid = "Password lyna hay";

const char* pwd = "Google@007";

const char* host = "http://192.168.8.101";

String get_host = "http://192.168.8.101";

WiFiServer server(80); // open port 80 for server connection

void setup()

{

Serial.begin(115200); //initialise the serial communication

delay(20);

WiFi.begin(ssid, pwd);

//starting the server

server.begin();

}

void loop()

{

//call_test();

get_device_status("home_light","Home Light");

get_device_status("shop_light","Shop Light");

}

void call_test()

{

Serial.println("It is test");

}

void get_device_status(String device_name, String device_text)

{

WiFiClient client = server.available();

HTTPClient http;

String url = get_host+"/temperature/get_status.php?device_name="+device_name;

http.begin(url);

//GET method

int httpCode = http.GET();

String payload = http.getString();

if(payload=="1")

{

Serial.println(device_text+" is ON");

}

else

{

Serial.println(device_text+" is Off");

}

http.end();

delay(1000);

}

As you can see I wrote a function for getting status of device and then in loop I called the function and provide parameters. The purpose of the function is that, you do not need to write complete code for every device, just call function in loop and then set parameters.

Now start the server and upload the code to WeMos and open Serial Monitor. You will see status from database.

Now change the values from 0 to 1 or 1 to 0 in database and see the result on serial monitor.

Hope you enjoy the tutorial. Sorry for broken English.

Have a nice day and Happy Coding. See you in next tutorial.

10 comments

AntonK
February 20, 2018 at 8:52 pm

Thanks for the code sample.
I wonder whether the approach will work if I move ‘WiFiServer server(80);’ into ‘get_device_status’ so the server won’t be global object any more.

Ary Pradicka Sukma
June 23, 2019 at 2:54 pm

(inc.inc) on php code, whats that means ?

- Muhammad Faryad
  May 9, 2020 at 7:46 am
  
  Actually that file responsible for connecting with Database. Sorry for that I have forgotten to include for that file. You can write this code by yourself.
  
lucas
October 25, 2019 at 4:52 pm

Hello,

I was looking for a example exactly like yours, but i’m having a issue.

the arduino IDE says “it found multiples libreries for “WifiClient.h”

Do you know how to solve this?

Rafi
January 28, 2020 at 3:02 pm

where php data to connect to your sql database

is that include once ‘inc.inc’ ????

- Muhammad Faryad
  May 9, 2020 at 7:34 am
  
  Yes, it includes in inc.inc. I am sorry that I forgot to include the code for inc.inc
  
Rick
April 14, 2020 at 9:42 pm

Hi, thank you for your tut. Sadly it does not work for me.
Nowhere in your code I’ve to enter the SQL database name and password.

Also, I assume I can use ” http://website.com” instead of the IP adress

greetings
Rick

- Muhammad Faryad
  May 9, 2020 at 7:06 am
  
  Rick, sorry for that, actually a file name inc.inc has Database connection queries which I have forgot to include into post.
  
Cisco Said
June 3, 2020 at 7:23 pm

Sir, thank you but when I tried it like this in my chrome: https://192.168.1.101/temperature/get_status.php?device_name=0”
I got this error: “Error:SELECT device_status FROM devices_status WHERE device_name=’0′ LIMIT 1”
What could be the issue? I fixed db connection and I wrote it by my self 🙂
but couldn’t get right result?

- Muhammad Faryad
  June 14, 2020 at 6:47 am
  
  Please check your database structure.