Esp8266 and mySQL

Nicolas

It sometimes helps to list out all your user requirements first before choosing your solution, which according to your posts is a website with php/mysql. I am not sure why you chose that option but that decision may need to be revisited, if you want to make things easier for yourself.

So from your post I see your requirements as:

1- want to control various things around the house (IoT).

2- want other people added to the database each one with his/her own credentials (my family for example).

3- want to connect an esp8266 directly to the database and update the booleans from 0 to 1 and back using a button for each one

4- want to read the boolean on the esp and turn an led

5- want to make it to update the values for a selected user...not all of them

It is not clear from your posts where you are hosting your website+database. If we assume it is on a web hosting site outside of your home network then this will lead to one type of simple solution vs. if you are hosting it just locally on your home network.

So, where are you wanting to host the website or the "back-office" server or controller?

My website and the database are hosted at my house (a small server is running for that) along with the esp(that am planning to install) ...I need the esp to be able to send and receive data from the database. Is this possible?

My website and the database are hosted at my house (a small server is running for that) along with the esp(that am planning to install) ...I need the esp to be able to send and receive data from the database. Is this possible?

Short answer, is yes it is possible.

Your next step is to think about which localised (as in on home network only) communication protocol to use to meet your requirements.

As you well know, web browsers, which would be classed as a "client", use http/https to request information from a web server. The information request is based on an IP address (or via domain name) and then a path or route to a specific web page. Additional parameters can be used by the "client" to GET information from the server or to POST information to the server.

With this method is it not possible for a server to initiate communication with a client. Only a client can initiate communication with a server.

So on a simplified level RESTful methods of requesting and posting information using http/https is not suitable.

So what next. Well, there are ways around this.

Probably the more popular method to use would be to establish an MQTT broker. There are numerous options available. Then you can have your ESP8266 "subscribe" to the broker to listen for requests and can "publish" data to the broker when an event occurs etc.

Another option would be to establish a "web socket". A Websocket allows you to open a bi-directional communication channel, or tunnel, between your ESP8266 and your local server.

If you search online you can find info on how to do. For example, here is one for php+mqtt: 

https://mosquitto.org/blog/2010/09/mqtt-with-php/

Maybe others can give other suggestions and/or guidance on leading you through this process.

Hi Nicolas

I have system running in which an ESP-8266, operating as an HTTP CLIENT, regularly updates my local server, writing temperature data to a ,csv file via a POST command and a php script on the server.

As @BigG has said, it is not possible for the server to initiate communication with the client, although I think it is possible (I haven't done it) for the client to request data back from the server.

Best wishes

Neil

And I just spotted this:

https://randomnerdtutorials.com/esp32-esp8266-mysql-database-php/

You should find this useful

Neil