5 Replies Latest reply on Oct 10, 2019 9:08 AM by neilk

    Esp8266 and mySQL

    nicolaoscon96

      Hello again,

       

      To begin with, thanks on the advice that you gave me on my latest post (MySql and Php )... Now am coming back with one more question for my project... I set up my database which incudes (an id (which is auto increasment), username, email, and 2 more which are place0 and place1 (those two are boolean type)). Now my question is how can i connect an esp8266 directly to the database and update the booleans from 0 to 1 and back using a button for each one? Also how can i read the boolean on the esp and turn an led for example? I need to make it to update the values for a selected user...not all of them....for exaple i have users with id 1 , 2, 3...i need to make the updates only on 1 if that one is selected...

       

      Thanks very much

      Nikolaos Konstantinou

      NcCon

        • Re: Esp8266 and mySQL
          BigG

          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?

          3 of 3 people found this helpful
            • Re: Esp8266 and mySQL
              nicolaoscon96

              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?

                • Re: Esp8266 and mySQL
                  BigG

                  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.

                  1 of 1 people found this helpful
                  • Re: Esp8266 and mySQL
                    neilk

                    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

                    • Re: Esp8266 and mySQL
                      neilk

                      And I just spotted this:

                       

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

                       

                      You should find this useful

                       

                      Neil