Using ESP8266 and Arduino Mega to read values from an online MySQL database?

Hi,

I am still pretty new to Arduinos and am needing some help. I have been searching for a way to do this and trying to follow tutorials to learn with no luck, so I am in dire need of some help here. This is very time sensitive at this point because I only have a few days to get this to work and am doing this as part of a project I have chosen for a class. I will not have access to the physical parts of this project after the next few days and this counts as a significant part of my grade for the overall class. Although this is only a part of a larger project I REALLY need to get this to work to make sure I have a chance to pass the class and don't end up having to spend another year in classes when I get back.

Here's what I'm needing help with:

I am wanting to use an Arduino Mega connected to an ESP 8266 module to read the contents of a MySQL database that is hosted online to act as a remote turn on option.

I have created a PHP script that can access a specific field of a MySQL Table and the script will always return a single digit value of either 0 or 1.

I have setup 3 webpages (1 for each prop) that have buttons on them that change a specific value in the MySQL Table that corresponds to the prop that I want to activate.

I now need to figure out a way to get the Arduino to constantly run the PHP script I have written to monitor the given 1/0 status in the database for the prop it is associated with and retrieve the value so it can be used in the sketch so that when it reads that the 0 has turned into a 1 it will activate a relay attached to it.

I know that this should be possible, but I have been looking around and trying to follow tutorials with no luck and am really desperate at this point. Can someone please help me?

terminal206:
I am wanting to use an Arduino Mega connected to an ESP 8266 module to read the contents of a MySQL database that is hosted online to act as a remote turn on option.

You must have a very good reason to use two processors.
Even a simple WeMos D1 mini is more powerful and has more memory than a Mega.
There are single-relay modules designed to plug directly onto a WeMos.
Leo..

I'm open to any suggestions! I'm just still really new to the Arduino family and already have at least some experience with the Mega, Uno, and Nano boards. I thought it would be easier to start with something I know a little about and expand from there, but if you can help me with a better option and it will be able to do what I am needing I would love to hear about it!

Just an update guys, I have confirmed that I have a solution working for the code on the website side of things!

At this point I just need help with code that will constantly run my PHP script, then activate a relay when the value changes to a 1. I am confident on the physical side of things (relay connection and activation), but if someone could help with the code to monitor the database I would really appreciate it!

Thanks!

  • Accidentally posted twice.

Hey everyone, I have been looking into this more after Wawa suggested that I didn't have a need for 2 boards. After looking around for options for a while I have decided to switch to just using an ESP8266 ESP-12E board and attaching a relay board directly to it.

I have written a PHP script that will access the needed value in my database and return either a 1 or a 0 as a result. On receiving a 1 I want the board to turn the relay on, and on receiving a 0 I want the board to make sure the relay is turned off.

I have also come up with the code below to send a HTTP GET request to that PHP script and use the result to determine which "if" statement it should run repeatedly in the loop. I have ordered the ESP-12E boards, but they won't be in for a few days so I have no way to test this and have a deadline to get this done that is VERY close. Would someone mind taking a look at my code to see if it looks valid and/or test it for me to see if it works?

I feel like I am getting close to something here that should be viable! Any help is VERY much appreciated guys!

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

// Declare The Pin The Relay Is Connected To.
char relayPin = 4;

// WiFi Login Info
const char* ssid = "yourWifiName";
const char* password = "yourWifiPassword";

// Create A HTTP Client Instance.
HTTPClient http;

// Host To Get Data From.
const char* host= "http://thehouseofdeath.com/arduino3readscript.php";

void setup() {

  // Set The Pin The Relay Is Connected To As An Output.
  pinMode(relayPin, OUTPUT);

  // Start The Serial Monitor & Try To Connect.
  Serial.begin(115200);
  Serial.print("Connecting to ");
  Serial.println(ssid);
  WiFi.begin(ssid, password);

  // Wait For The WiFi To Connect.
  while (WiFi.status() != WL_CONNECTED) {
    delay(500);
    Serial.print(".");
  }
  delay(3000);

  // Let the user know the WiFi connected and the address of the board.
  Serial.println("WiFi connected");
  Serial.println("IP address: ");
  Serial.println(WiFi.localIP());
}

void loop() {

  // Send An HTTP GET Request.
  http.begin(host);
  int httpCode = http.GET();
  String propStatus = http.getString(); // get data from webhost continously

  // Just For Debugging.
  Serial.print(propStatus);

  // If A 1 Is Returned, Activate The Relay.
  if(propStatus == "1"){
      digitalWrite(relayPin, HIGH);
  }

  // If A 0 Is Returned, Make Sure The Relay Is Off.
  else if(propStatus == "0"){
    digitalWrite(relayPin, LOW);
  }

  // Wait 500 milliseconds before sending another request.
  delay(500);

  // End the HTTP Session.
  http.end();

}

The ESP-12E is the bare WiFi module.

Do you mean you have ordered the WeMos D1 mini, with the ESP-12 module and all the supporting parts?
And the relay module designed for the WeMos D1 mini?
Leo..

'E' or 'F' is only the shape of the module's aerial.

Please forgive my ignorance, as I said I'm still pretty new at this. I think it is called the Node MCU. This is the board I bought:

If you have a spare second to look at my code in the post above, would you mind telling me if there is anything that immediately stands out as if I'm not understanding it's use correctly?

The WeMos D1 min is basically the same, but smaller. And has a relay shied available (no wires).

The NodeMCU will also work, but maybe not as easy, depending on which relay module.

Both can be powered with a 5volt cellphone charger, connected to the USB socket.

I might leave the http coding over to others with more experience in that field.
Leo..

Ok, thank you for the advice on the board setup! I'll see if I can get some advice on the actual code itself in another section of the forum.

terminal206:
I'll see if I can get some advice on the actual code itself in another section of the forum.

That would be against the forum rules.
Just be patient. Most users on a global forum are at work or asleep.
Leo..

Oh, I wasn't aware of that. Thank you for letting me know!

Code compiled and loaded onto a D1 Mini gives the below output

..........WiFi connected
IP address: 
192.168.1.65
00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
--------------- CUT HERE FOR EXCEPTION DECODER ---------------

Exception (9):
epc1=0x40206efc epc2=0x00000000 epc3=0x00000000 excvaddr=0x00ea020a depc=0x00000000

>>>stack>>>

ctx: cont
sp: 3ffffdc0 end: 3fffffc0 offset: 0190
3fffff50:  3ffeddc0 3ffee550 000001f4 3ffee4f4  
3fffff60:  3fffdad0 3ffee550 3ffee3a8 402012d8  
3fffff70:  402056d9 00000000 3ffee3a8 40202080  
3fffff80:  3fffdad0 00000000 3ffee3a8 40201174  
3fffff90:  3ffe0030 0031003f 81fee4b4 40100175  
3fffffa0:  3fffdad0 00000000 3ffee4b4 402051ec  
3fffffb0:  feefeffe feefeffe 3ffe84f0 40100bed  
<<<stack<<<

--------------- CUT HERE FOR EXCEPTION DECODER ---------------

 ets Jan  8 2013,rst cause:2, boot mode:(3,6)

load 0x4010f000, len 3584, room 16 
tail 0
chksum 0xb0
csum 0xb0
v2843a5ac
~ld
Connecting to ZyXEL
....WiFi connected
IP address: 
192.168.1.65
00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
--------------- CUT HERE FOR EXCEPTION DECODER ---------------

Exception (9):
epc1=0x40206efc epc2=0x00000000 epc3=0x00000000 excvaddr=0x010101af depc=0x00000000

>>>stack>>>

ctx: cont
sp: 3ffffdc0 end: 3fffffc0 offset: 0190
3fffff50:  3ffeddc0 3ffee550 000001f4 3ffee4f4  
3fffff60:  3fffdad0 3ffee550 3ffee3a8 402012d8  
3fffff70:  402056d9 00000000 3ffee3a8 40202080  
3fffff80:  3fffdad0 00000000 3ffee3a8 40201174  
3fffff90:  3ffe0030 0031003f 81fee4b4 40100175  
3fffffa0:  3fffdad0 00000000 3ffee4b4 402051ec  
3fffffb0:  feefeffe feefeffe 3ffe84f0 40100bed  
<<<stack<<<

--------------- CUT HERE FOR EXCEPTION DECODER ---------------

 ets Jan  8 2013,rst cause:2, boot mode:(3,6)

load 0x4010f000, len 3584, room 16 
tail 0
chksum 0xb0
csum 0xb0
v2843a5ac
~ld
Connecting to ZyXEL
....WiFi connected
IP address: 
192.168.1.65
00000000

As you can see it causes an exception after a short period of running. I have not looked up the exception in detail but the exception decoder output is...

Exception 28: LoadProhibited: A load referenced a page mapped with an attribute that does not permit loads
PC: 0x40206ed4: HTTPClient::connected() at D:\PortableApps_arduino\portable\packages\esp8266\hardware\esp8266\2.7.4\libraries\ESP8266HTTPClient\src\ESP8266HTTPClient.cpp line 475
EXCVADDR: 0x0101020c

Decoding stack results
0x402012b0: HTTPClient::disconnect(bool) at D:\PortableApps_arduino\portable\packages\esp8266\hardware\esp8266\2.7.4\libraries\ESP8266HTTPClient\src\ESP8266HTTPClient.cpp line 434
0x402056b1: __delay(unsigned long) at D:\PortableApps_arduino\portable\packages\esp8266\hardware\esp8266\2.7.4\cores\esp8266\core_esp8266_wiring.cpp line 57
0x40202058: HTTPClient::end() at D:\PortableApps_arduino\portable\packages\esp8266\hardware\esp8266\2.7.4\libraries\ESP8266HTTPClient\src\ESP8266HTTPClient.cpp line 425
0x4020114d: loop() at D:\PortableApps_arduino\portable\sketchbook\temp1/temp1.ino line 66
0x40100175: esp_schedule() at D:\PortableApps_arduino\portable\packages\esp8266\hardware\esp8266\2.7.4\cores\esp8266\core_esp8266_main.cpp line 125
0x402051c4: loop_wrapper() at D:\PortableApps_arduino\portable\packages\esp8266\hardware\esp8266\2.7.4\cores\esp8266\core_esp8266_main.cpp line 197

Your proposed code does not look too bad. It should work in principle I think.

The problem I can imagine is performance and efficiency. Using http to poll a database value continuously for a change in a value is very slow and cumbersome. Far better to have the esp notified in some way when the value has changed.

But I also think there could be a much better, simpler way.

You can simply have the esp modules act as Web servers. You can remotely access them to switch the relays directly. No need for an sql database or php running on a separate server.

This would require that the esp modules and the devices used to access them are on the same netork (maybe give the esp fixed ip addresses for convenience).

If they can't be on the same network, maybe use port forwarding on your router.

Or use the Blynk application, which would make the whole project easy.

I would put this after the call to http.end() :

 // Wait 500 milliseconds before sending another request.
  delay(500);

Riva - Thank you so much for being willing to try my code out for me. The errors are obviously a problem, but at least I know that it was doing what I intended and that gives me hope that I am on the right track to a solution!

PaulRB - For this particular project the goal is to have users be able to activate the relays from anywhere there is an internet connection, so unfortunately I don't think a local server would work. I do have a bit of experience with port forwarding though, so I may look into something like that later for other projects. I also need to be able to integrate the controls into a personal website so I think that rules Blynk out from what I have seen. Thanks for the suggestions though!

6v6gt - Thanks for the advice on moving the delay, I will definitely do that! Do you think that is part of the stability issue? I've seen your posts in several other threads about HTTP Get calls, and it seems like you really know your stuff and have been able to help a lot of other people get things sorted out! Thanks for taking a look at my code!

Thanks again to all of you for taking the time to check out my posts!

I had missed 6v6gt's reply so did not add the extra delay after closing the connection. The site is now down so cannot test the new code.