Jump to content

ESP32, MQTT and SQL


dopelemon

Recommended Posts

I am carrying out a personal project that consists of a sensor station through the esp32 microprocessor. I need to store the collected data in a database for later analysis and also to see the information in real time. I am new to this field and I have had two options:
-Use MQTT for its simplicity to communicate the microprocessor with the broker and from there to the SQL database. In this way I would obtain the data in real time
-Use SQL directly from the microprocessor itself.

Sorry if the question is too obvious, although feel free to explain the different options in addition to these.

 

Thanks very much in advance

 

Link to comment
Share on other sites

I'm not familiar with the ESP32 in particular but usually these micro-controller products are no where near capable of running an SQL database locally.

For the ones that have networking capabilities, you can usually get some sort of TCP/IP connection going either via a minimal HTTP server or custom socket setup.  It looks like that's what the whole MQTT thing is so that sounds like a good option.

For the ones without networking capabilities you'd generally hook them up to a computer via a serial connection and connect to it that way.  This is what I've usually done for my occasional Arduino based projects.

 

  • Great Answer 1
Link to comment
Share on other sites

21 hours ago, dopelemon said:

And, do you know any MQTT broker which allow to link easily with php databse?

There isn't really a "php database".  PHP can talk to many different types of databases through client libraries.

So... I can not guarantee this is going to work, but it's worth looking into.  The primary goal here is to keep you within PHP which you know, which should make writing the code you need within your comfort zone.

  • You need an MQTT Broker that your ESP32 device will be publishing to.  This needs to be some sort of network connected device that can run your MQTT broker, as well as your SQL database + storage for your data.  You will also be running an MQTT client job that will be subscribed to the broker and will take data and add it to the SQL database in (near) realtime
  • Here is an async server package called Swole that is added to PHP via an extension.  You would need to use PECL to add it to your PHP installation which will live on the aforementioned server
  • You write a client who subscribes to your MQTT Topic for the data you are charting, and transforms that data.  Again there is a php library for this, and there may be others if you do some googling, packagist searches etc:  https://github.com/php-mqtt/client
  • Your client will receive the data and add it to your SQL database each time a relevant message is received with data you want to store

Now theoretically, you can cut out this client piece by adding code to your Swole based MQTT broker.  As messages are published to the topic, you should be able to process the data and add it directly to the the sql database, so like anything there are multiple different ways to do this with strengths and weaknesses for each.  If the server code can do it directly, clearly that's simpler and better.

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.