How to split data from node-red in mysql

I have 2 input from mqtt and i want to split the data/value into 2 column in mysql. For example i want to separate the value of temperature as 29.70 and humidity as 84.00. But based on my result the 2 column shown the same data/value.

Capture1

This is my code at function node:

Temperature = msg.payload
Humidity = msg.payload
msg.topic = "INSERT INTO dht11 (Temperature,Humidity)"+" VALUES ('"+ Temperature +"','"+ Humidity +"')";
return msg;

You are feeding two separate inputs to the same function. In the function you are always putting the msg.payload into both fields so of course they will be the same.

In your listing, the first of every 2 entries is the temperature and the second is the humidity. In other words you have split by row not column.

This isn't easy to do with SQL which is why many of us use InfluxDB. As a timeseries db, it is much easier to manage individual entries.

If you really want to have 1 row for a pair of temp/hum values, you will need to use a context variable and work out when you have a pair of new values and only then write a row to the db.

1 Like

Alternatively you could use a Join node to merge the two together following this example from the cookbook.

Yet another alternative may be appropriate if the temperature and humidity come from the same source. If they do then you could put them both into one object and pass that via MQTT, so they are both available ready for adding to the database.

However, unless you have a good reason to stick with mysql, and if the data are all time series, then use Influxdb instead, as already suggested.

1 Like

Noted, thankyou for your help and explanation

Noted. ill try. Thankyou for your help.

This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.