ESP32, MQTT and SQL

[dopelemon]

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

 

[requinix]

I would think the option that is easiest on the microprocessor itself would be best. That probably means MQTT. Plus apparently that gets you real-time data.

[kicken]

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.

 

[dopelemon]

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

[requinix]

You mean some thing you can download and install that will do everything for you?

No.

I suggest you start by Googling "php mqtt".

[gizmola]

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
  • With Swole you run a php based MQTT broker/server.  Here's simple code that shows how you would do it:  https://www.swoole.co.uk/docs/modules/swoole-mqtt-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.