# Assemble mqtt data to mysql

**URL:** <https://discourse.nodered.org/t/assemble-mqtt-data-to-mysql/21944>\
**Category:** General\
**Created:** [18 February 2020 20:06 UTC](https://discourse.nodered.org/t/assemble-mqtt-data-to-mysql/21944 "2020-02-18T20:06:57Z")\
**Posts on this page:** 9\
**Page:** 1

<div class="post-metadata">

**Author:** ![quito96](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/quito96/32/17252_2.png) [@quito96](https://discourse.nodered.org/u/quito96)\
**Post date:** [18 February 2020 20:06 UTC](https://discourse.nodered.org/t/assemble-mqtt-data-to-mysql/21944/1 "2020-02-18T20:06:58Z")

</div>

Hi there,

i am currently working on a private project by storing data of type number via mqtt into a mysql database.  
The connection to mysql works, i have tested this with the inject node.

The sql insert command looks like this:

INSERT INTO `mqtt-weather`.`temp`  
(`timestamp`,  
`value0`)  
VALUES  
(\<{timestamp: CURRENT\_TIMESTAMP}\>,\<{value0: }\>);

With this example payload I have successfully written to the sql database

INSERT INTO `mqtt-weather`.`temp`  
(`timestamp`,  
`value0`)  
VALUES  
(20200217180055,-2.1);

Now to my question proper:  
How can i combine the data in node red with timestamp and value0 to pass the Insert Into command to the mysql node? (the mqtt channel returns only value0)  
What would be the easiest and best way to reach the line??

In this respect I am a beginner and would very much like to learn!  
Many thanks in advance.  
Quito

---

<div class="post-metadata">

**Author:** ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)\
**Post date:** [18 February 2020 20:48 UTC](https://discourse.nodered.org/t/assemble-mqtt-data-to-mysql/21944/2 "2020-02-18T20:48:49Z")

</div>

If you always want to write the current time to that column then the best way is to create the timestamp column with a default of `CURRENT_TIMESTAMP`, then you don't need to provide a value for that column at all and mysql will automatically fill it in, so your query becomes

```auto
INSERT INTO mqtt-weather.temp
(timestamp)
VALUES
(-2.1);

```

If you don't want to do that then you can use NOW() so the query is

```auto
INSERT INTO mqtt-weather.temp
(timestamp,
value0)
VALUES
(NOW(),-2.1);

```

---

<div class="post-metadata">

**Author:** ![quito96](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/quito96/32/17252_2.png) [@quito96](https://discourse.nodered.org/u/quito96)\
**Post date:** [18 February 2020 21:15 UTC](https://discourse.nodered.org/t/assemble-mqtt-data-to-mysql/21944/3 "2020-02-18T21:15:39Z")

</div>

Hey, Colin both ways are great 👍 Thanks

the mqtt channel returns only value0 as type number.  
What would be the best way to integrate it? Via a function node ?

---

<div class="post-metadata">

**Author:** ![zenofmud](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/zenofmud/32/316_2.png) [@zenofmud](https://discourse.nodered.org/u/zenofmud)\
**Post date:** [18 February 2020 21:25 UTC](https://discourse.nodered.org/t/assemble-mqtt-data-to-mysql/21944/4 "2020-02-18T21:25:38Z")

</div>

I like to build my queries in a `template` node something like this

```auto
SELECT * FROM wp_users 
where user_login = '{{payload}}';

```

---

<div class="post-metadata">

**Author:** ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)\
**Post date:** [18 February 2020 21:30 UTC](https://discourse.nodered.org/t/assemble-mqtt-data-to-mysql/21944/5 "2020-02-18T21:30:02Z")

</div>

Either a function node or a template (not ui template) node. The template node may be considered simpler. The info tab for the node gives some examples that should let you do it. Don't forget to set the Property field to msg.topic.

---

<div class="post-metadata">

**Author:** ![quito96](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/quito96/32/17252_2.png) [@quito96](https://discourse.nodered.org/u/quito96)\
**Post date:** [18 February 2020 21:33 UTC](https://discourse.nodered.org/t/assemble-mqtt-data-to-mysql/21944/6 "2020-02-18T21:33:41Z")

</div>

Template Node leike this..? payload set to value0

INSERT INTO `mqtt-weather`.  
`temp` (`timestamp`, `value0`)  
VALUES (NOW(),{{value0}});

---

<div class="post-metadata">

**Author:** ![zenofmud](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/zenofmud/32/316_2.png) [@zenofmud](https://discourse.nodered.org/u/zenofmud)\
**Post date:** [18 February 2020 21:50 UTC](https://discourse.nodered.org/t/assemble-mqtt-data-to-mysql/21944/7 "2020-02-18T21:50:01Z")

</div>

If the MQTT node returns only the value, it should be in msg.payload. In that case you should use

```auto
INSERT INTO mqtt-weather.
temp (timestamp, value0)
VALUES (NOW(),{{payload}});

```

because the value is in msg.payload

---

<div class="post-metadata">

**Author:** ![quito96](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/quito96/32/17252_2.png) [@quito96](https://discourse.nodered.org/u/quito96)\
**Post date:** [18 February 2020 22:14 UTC](https://discourse.nodered.org/t/assemble-mqtt-data-to-mysql/21944/8 "2020-02-18T22:14:10Z")

</div>

Hi Colin, Hi zenofmud,

I thank you both because it's working out like I thought. Super

---

<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:** [3 March 2020 22:14 UTC](https://discourse.nodered.org/t/assemble-mqtt-data-to-mysql/21944/9 "2020-03-03T22:14:12Z")

</div>

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