# MQTT to MYSQL help is needed

**URL:** <https://discourse.nodered.org/t/mqtt-to-mysql-help-is-needed/60190>\
**Category:** General\
**Tags:** database, function-node, mqtt\
**Created:** [22 March 2022 12:38 UTC](https://discourse.nodered.org/t/mqtt-to-mysql-help-is-needed/60190 "2022-03-22T12:38:57Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![Evos](https://avatars.discourse-cdn.com/v4/letter/e/8e8cbc/32.png) [@Evos](https://discourse.nodered.org/u/Evos)\
**Post date:** [22 March 2022 12:38 UTC](https://discourse.nodered.org/t/mqtt-to-mysql-help-is-needed/60190/1 "2022-03-22T12:38:57Z")

</div>

Hi Everyone,

I am new to Node-Red but have been running some home automation via HA for a while.  
With a new project I have a electricity monitor that pass's out 6 messages via MQTT and I would like to write these into a MYSQL database but I am not getting it right.

below are examples of each message from the MQTT device that I have configured in Node-Red

```auto
{"id":"3701211100108","ua":231.59,"ub":0,"uc":0,"ia":0,"ib":0,"time":"20220322202100","isend":"0"}
{"id":"3701211100108","ic":0.04,"uab":232.028,"ubc":0,"uca":232.028,"pa":0,"time":"20220322202100","isend":"0"}
{"id":"3701211100108","pb":0,"pc":0,"zyggl":0.0009,"qa":0.0073,"qb":0,"time":"20220322202100","isend":"0"}
{"id":"3701211100108","qc":0,"zwggl":0.002,"sa":0.0035,"sb":0,"sc":0,"time":"20220322202100","isend":"0"}
{"id":"3701211100108","zszgl":0.0049,"pfa":0.621,"pfb":0,"pfc":0,"zglys":0.621,"f":49.93,"time":"20220322202100","isend":"0"}
{"id":"3701211100108","unb":0.999,"inb":0,"pdm":-0.0004,"qdm":0.0003,"sdm":0.0015,"ig":2039.708,"time":"20220322202100","isend":"1"}

```

I have my MQTT node Feeding a JSON node with the debug node to get the above.

What I need is for these 6 messages that all have the same time stamp to be written into one line in the table in the MYSQL database.

Any assistance on this would be amazing.

Thanks.

---

<div class="post-metadata">

**Author:** ![Steve-Mcl](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/steve-mcl/32/4826_2.png) [@Steve-Mcl](https://discourse.nodered.org/u/Steve-Mcl)\
**Post date:** [22 March 2022 13:12 UTC](https://discourse.nodered.org/t/mqtt-to-mysql-help-is-needed/60190/2 "2022-03-22T13:12:58Z")

</div>

Messages arrive one at a time. To put all values into a database in one row you will need ALL values in one message.

So do that bit first...

See [this article in the cookbook](https://cookbook.nodered.org/basic/join-streams) for an example of how to join messages into one object.

---

<div class="post-metadata">

**Author:** ![Evos](https://avatars.discourse-cdn.com/v4/letter/e/8e8cbc/32.png) [@Evos](https://discourse.nodered.org/u/Evos)\
**Post date:** [22 March 2022 13:13 UTC](https://discourse.nodered.org/t/mqtt-to-mysql-help-is-needed/60190/3 "2022-03-22T13:13:31Z")

</div>

thanks will give it a read.

---

<div class="post-metadata">

**Author:** ![E1cid](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/e1cid/32/77971_2.png) [@E1cid](https://discourse.nodered.org/u/E1cid)\
**Post date:** [22 March 2022 13:56 UTC](https://discourse.nodered.org/t/mqtt-to-mysql-help-is-needed/60190/4 "2022-03-22T13:56:58Z")

</div>

I see that isend on last message is "1", if this signifies the last message then you could use this to merge all objects and complete join when isend equals "1"  
e.g.

```auto
[{"id":"2c3a94cf.7d5cec","type":"inject","z":"bf9e1e33.030598","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"[{\"id\":\"3701211100108\",\"ua\":231.59,\"ub\":0,\"uc\":0,\"ia\":0,\"ib\":0,\"time\":\"20220322202100\",\"isend\":\"0\"},{\"id\":\"3701211100108\",\"ic\":0.04,\"uab\":232.028,\"ubc\":0,\"uca\":232.028,\"pa\":0,\"time\":\"20220322202100\",\"isend\":\"0\"},{\"id\":\"3701211100108\",\"pb\":0,\"pc\":0,\"zyggl\":0.0009,\"qa\":0.0073,\"qb\":0,\"time\":\"20220322202100\",\"isend\":\"0\"},{\"id\":\"3701211100108\",\"qc\":0,\"zwggl\":0.002,\"sa\":0.0035,\"sb\":0,\"sc\":0,\"time\":\"20220322202100\",\"isend\":\"0\"},{\"id\":\"3701211100108\",\"zszgl\":0.0049,\"pfa\":0.621,\"pfb\":0,\"pfc\":0,\"zglys\":0.621,\"f\":49.93,\"time\":\"20220322202100\",\"isend\":\"0\"},{\"id\":\"3701211100108\",\"unb\":0.999,\"inb\":0,\"pdm\":-0.0004,\"qdm\":0.0003,\"sdm\":0.0015,\"ig\":2039.708,\"time\":\"20220322202100\",\"isend\":\"1\"}]","payloadType":"json","x":130,"y":60,"wires":[["db4a76e4.7f2978"]]},{"id":"db4a76e4.7f2978","type":"split","z":"bf9e1e33.030598","name":"simulate incoming messages","splt":"\\n","spltType":"str","arraySplt":1,"arraySpltType":"len","stream":false,"addname":"","x":370,"y":60,"wires":[["38184dee.eb7bb2"]]},{"id":"38184dee.eb7bb2","type":"switch","z":"bf9e1e33.030598","name":"","property":"payload.isend","propertyType":"msg","rules":[{"t":"eq","v":"1","vt":"str"},{"t":"else"}],"checkall":"true","repair":false,"outputs":2,"x":190,"y":100,"wires":[["64329005.9ef378"],["cda82e63.9c89a"]]},{"id":"64329005.9ef378","type":"change","z":"bf9e1e33.030598","name":"","rules":[{"t":"set","p":"complete","pt":"msg","to":"true","tot":"bool"}],"action":"","property":"","from":"","to":"","reg":false,"x":410,"y":100,"wires":[["cda82e63.9c89a"]]},{"id":"cda82e63.9c89a","type":"join","z":"bf9e1e33.030598","name":"","mode":"custom","build":"merged","property":"payload","propertyType":"msg","key":"topic","joiner":"\\n","joinerType":"str","accumulate":false,"timeout":"","count":"39","reduceRight":false,"reduceExp":"","reduceInit":"","reduceInitType":"","reduceFixup":"","x":170,"y":160,"wires":[["cb5dd3ae.255a3"]]},{"id":"cb5dd3ae.255a3","type":"debug","z":"bf9e1e33.030598","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":580,"y":160,"wires":[]}]

```

You then have all the data in one message and can construct your sql query.  
p.s.  
The MQTT node can output a parse json object if you select it in the config, no need for json node.

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/1X/d073cd938eafa2e558d7c2cd59003b3ef4963033.png) [@system](https://discourse.nodered.org/u/system)\
**Post date:** [21 May 2022 13:57 UTC](https://discourse.nodered.org/t/mqtt-to-mysql-help-is-needed/60190/5 "2022-05-21T13:57:23Z")

</div>

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