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:
1 2 3 4 5 6 7 8 9 |
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:-
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
<?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:-
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 |
//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.
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.
(inc.inc) on php code, whats that means ?
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.
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?
where php data to connect to your sql database
is that include once ‘inc.inc’ ????
Yes, it includes in inc.inc. I am sorry that I forgot to include the code for inc.inc
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
Rick, sorry for that, actually a file name inc.inc has Database connection queries which I have forgot to include into post.
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?
Please check your database structure.