Temp and Hum store in MySQL

General
1

Hello!

I'm new in Node-RED, I made flow to storing 2 pcs MQTT msg to MySQL, it is working fine. But in sql rows sometimes 0, sometimes not 0. I think my function can't sending 2 pcs data in one time. I want to know how can I storing 2 pcs msg in one time.

nodered1
nodered1.jpg720Ă—728 101 KB

Thanks a lot!

2

So you are either sending a line with office temperature
[MQTT - Office Temperature] - [Data To SQL] - [Mysql DB]
or a line with humidity
[MQTT- Office Humidity] - [Data To SQL] - [Mysql DB]

If you want to end up with one line you will have to look at combining the data. One way you can do this is by using the join node, before your function node(s)

3

If the final outcome is too Graph the results you may want to look at InfluxDB instead with Grafana as the graphing tool. InfluxDB is a time based DB, so is a little more efficient on this type of data.

4

If I using join node, I think the best is combine each msg.payload with a key/value. But I don't know how to modify my sql function.

Now my function is:

var newMsg = { payload: msg.payload };
newMsg.topic="insert into iot_data (temp, location) values ("+newMsg.payload+","Office")";
return newMsg;

From the join node I have this object:
{ /Office/DHT22/Temperature: "18.50", /Office/DHT22/Humidity: "33.50" }

5

This page shows you how you can use the debug to identify and copy the path to any part of the message Working with messages : Node-RED

6

What software are you using on the device that is sending the MQTT msg's? Since the sensor is a DHT22, it reads both the temperature and humidity at the same time. Why not change that software to send both in one MQTT msg?

7

I'm using NodeMCU with ESPEasy firmware.

8

Ok, this goes outside the bounds of Node-red but here is what to do. In ESPeasy:

  1. go to the Tools tab, click the Advanced button and check the box Rules then hit Submit at the bottom. This will give you an new tab called Rules
  2. go to the Devices tab and edit your DHT22 device (Let's assume it is DHT22)
  3. note the name and the Values names (Lets assume they are Temp and Humi
  4. uncheck Send to Controller
  5. go to the Rules tab and enter the following:
on dht22#Temp do
   Publish %sysname%/what_ever_else_makes_up_your_topic,"T":[dht22#Temp],"H":[dht22#Humi]
endon
  1. Press the Submit at the bottom of the screen and you are done. You will now get one MQTT msg with both readings.
1 Like
9

In this rules what intervals sending the msg? How can change?

10

The inteval is detemined by what you set for the device. Since you already have the device set up to send data at an interval, that is what it will use. The one thing is to uncheck the `Send to Controller' so you won't get three mqtt msgs when the interval happens

11

Just be careful not to read from a DHT22 too rapidly as it takes some appreciable time to read. Doing it too often will result in errors. Personally, I wouldn't read it less than 5-10 sec apart but I can't remember the actual limitation as I stopped using these sensors a long time ago. My own sensor platforms produce readings about every 30-60 seconds, anything less is rarely really useful, especially with old sensors like the DHT's.

12

Ok, working nice! :slight_smile: Thank for everybody!!!!!

iotdatatables
iotdatatables.jpg703Ă—107 21 KB

13

hello, can you please send to me your flow

14

@Meriam Welcome to the forum. You might have to wait a while since the original poster hasn't been active since the last post on this thread two years ago.

It would be best if you open a new thread showing what you have already tried and explaining what your issue is.

I will close this thread now.