# MySQL NODE-RED table

**URL:** <https://discourse.nodered.org/t/mysql-node-red-table/7612>\
**Category:** General\
**Created:** [5 February 2019 12:11 UTC](https://discourse.nodered.org/t/mysql-node-red-table/7612 "2019-02-05T12:11:46Z")\
**Posts on this page:** 20\
**Page:** 1

<div class="post-metadata">

**Author:** ![smalhao](https://avatars.discourse-cdn.com/v4/letter/s/a3d4f5/32.png) [@smalhao](https://discourse.nodered.org/u/smalhao)\
**Post date:** [5 February 2019 12:11 UTC](https://discourse.nodered.org/t/mysql-node-red-table/7612/1 "2019-02-05T12:11:46Z")

</div>

I'm trying to send 4 values ​​to the 4 columns of the database but I'm not able to put the payloads correctly because the three values ​​are inserted in each column and I wanted to separate each one of them into the corresponding column, so the timestamp is that it's correct. My function is this:

 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/2X/1/1bb9fd84377a86bf272206c323b0f3459ce0f062.png)

and the database table output is this:

 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/2X/4/42f65de0d5145707df2bf891f6f6abb067bae5c3.png)

how can I get the correct values ​​in each column?

---

<div class="post-metadata">

**Author:** ![knolleary](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/knolleary/32/3_2.png) [@knolleary](https://discourse.nodered.org/u/knolleary)\
**Post date:** [5 February 2019 12:15 UTC](https://discourse.nodered.org/t/mysql-node-red-table/7612/2 "2019-02-05T12:15:25Z")

</div>

What does msg.payload contain? Can you provide an example? I guess it's a string containing all of your values in some format and you need to split up to get the individual values.

---

<div class="post-metadata">

**Author:** ![smalhao](https://avatars.discourse-cdn.com/v4/letter/s/a3d4f5/32.png) [@smalhao](https://discourse.nodered.org/u/smalhao)\
**Post date:** [5 February 2019 12:24 UTC](https://discourse.nodered.org/t/mysql-node-red-table/7612/3 "2019-02-05T12:24:01Z")

</div>

I have 3 msg.payload for 3 sensors of temperature the msg.paload is a number, the debug values is this:

 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/2X/2/2ea1cd011d52907536621dd09474a4939c152681.png)

yes I need to split them to get the values ​​of each sensor

---

<div class="post-metadata">

**Author:** ![knolleary](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/knolleary/32/3_2.png) [@knolleary](https://discourse.nodered.org/u/knolleary)\
**Post date:** [5 February 2019 12:27 UTC](https://discourse.nodered.org/t/mysql-node-red-table/7612/4 "2019-02-05T12:27:53Z")

</div>

So to be clear, you don't have _one_ message containing all values. You have multiple messages, each one containing a different sensor's reading.

So this is not a question of _splitting_ the payload, but you need to _join_ those separate messages into one.

Have a look at the Join node - you can use that to join multiple messages into one - which you can then pass to the Function node to build the appropriate SQL Insert statement.

---

<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:** [5 February 2019 12:51 UTC](https://discourse.nodered.org/t/mysql-node-red-table/7612/5 "2019-02-05T12:51:52Z")

</div>

Also, your insert statement uses msg.payload three times in the VALUES section so you will get three identical values in the three columns

---

<div class="post-metadata">

**Author:** ![Robbo](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/robbo/32/4491_2.png) [@Robbo](https://discourse.nodered.org/u/Robbo)\
**Post date:** [5 February 2019 13:01 UTC](https://discourse.nodered.org/t/mysql-node-red-table/7612/6 "2019-02-05T13:01:25Z")

</div>

It will be better to restructure your database so your "sensors" table is structured like "date,sensor,value". When writing the SQL to display you can use sub queries and "group by" to generate the output you require if still want in columns.

---

<div class="post-metadata">

**Author:** ![Robbo](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/robbo/32/4491_2.png) [@Robbo](https://discourse.nodered.org/u/Robbo)\
**Post date:** [5 February 2019 14:04 UTC](https://discourse.nodered.org/t/mysql-node-red-table/7612/7 "2019-02-05T14:04:16Z")

</div>

PS. If it's purely for timebased data, InfluxDB is a better DB for this.

---

<div class="post-metadata">

**Author:** ![smalhao](https://avatars.discourse-cdn.com/v4/letter/s/a3d4f5/32.png) [@smalhao](https://discourse.nodered.org/u/smalhao)\
**Post date:** [5 February 2019 15:39 UTC](https://discourse.nodered.org/t/mysql-node-red-table/7612/8 "2019-02-05T15:39:42Z")

</div>

I configured the join like this:

 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/2X/9/9cb1d799ac92e3d7bd05a2deb2554029eafb9050.png)  
the debug of the join is this :  
 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/2X/f/f4445a21fa0e7edc99d9fe3b4b0e3987ee1258fd.png)  
now i need apply the split block correct?

---

<div class="post-metadata">

**Author:** ![knolleary](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/knolleary/32/3_2.png) [@knolleary](https://discourse.nodered.org/u/knolleary)\
**Post date:** [5 February 2019 15:48 UTC](https://discourse.nodered.org/t/mysql-node-red-table/7612/9 "2019-02-05T15:48:16Z")

</div>

> [@smalhao](#):
>
> now i need apply the split block correct?

No. The split node will take that _one_ message and give you back three separate messages. That isn't what you want. You need _one_ message to arrive at the Function node containing all the data you want to insert as a single row - which is what the Join node has given you.

Pass that message to your Function node. You can then access they three separate values as `msg.payload[0]`, `msg.payload[1]` and `msg.payload[2]` (because msg.payload is now an array).

---

<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:** [5 February 2019 15:53 UTC](https://discourse.nodered.org/t/mysql-node-red-table/7612/10 "2019-02-05T15:53:28Z")

</div>

Configure the Join node to generate Key/Value pairs then you will be able to identify which one is which. Make sure the messages coming in have different topics then you can reference them by name. You might need to think about what happens if one of them is missing. Perhaps you need to specify a timeout there too, so it will send just what it has received after the timeout if one or more are missing.

---

<div class="post-metadata">

**Author:** ![smalhao](https://avatars.discourse-cdn.com/v4/letter/s/a3d4f5/32.png) [@smalhao](https://discourse.nodered.org/u/smalhao)\
**Post date:** [5 February 2019 16:11 UTC](https://discourse.nodered.org/t/mysql-node-red-table/7612/11 "2019-02-05T16:11:42Z")

</div>

Like this or i need define variable to msg.payload[0]

 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/2X/c/cbdef6a55d11e2b67c54f2c14d07d7f22e3e5bc8.png)

My schematic is this:

 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/2X/a/a3888640bcb4862a473736601b165c4da57cc6dc.png)

but not send the data to database

---

<div class="post-metadata">

**Author:** ![knolleary](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/knolleary/32/3_2.png) [@knolleary](https://discourse.nodered.org/u/knolleary)\
**Post date:** [5 February 2019 16:15 UTC](https://discourse.nodered.org/t/mysql-node-red-table/7612/12 "2019-02-05T16:15:01Z")

</div>

I see you have a Debug node wired to the Function node output. It will show the query you are sending to the database - check it looks right.

As @Colin suggested, you may also want to change the Join node configuration to create a key/value object. At the moment, the readings will be in whatever order they happen to arrive - which may not be consistent.

---

<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:** [5 February 2019 16:17 UTC](https://discourse.nodered.org/t/mysql-node-red-table/7612/13 "2019-02-05T16:17:36Z")

</div>

Also most databases have the ability to configure a timestamp field that is automatically set to the current time when you add a record. That is much better than inserting it yourself.

---

<div class="post-metadata">

**Author:** ![smalhao](https://avatars.discourse-cdn.com/v4/letter/s/a3d4f5/32.png) [@smalhao](https://discourse.nodered.org/u/smalhao)\
**Post date:** [5 February 2019 16:49 UTC](https://discourse.nodered.org/t/mysql-node-red-table/7612/14 "2019-02-05T16:49:15Z")

</div>

the debug don´t show anything, i'm not shure if the query of msg.topic in function is the correct way for send the data to the database or i need to create variables to msg.payload[0], msg.payload[1] and msg.payload[3], can help me in the function?

---

<div class="post-metadata">

**Author:** ![knolleary](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/knolleary/32/3_2.png) [@knolleary](https://discourse.nodered.org/u/knolleary)\
**Post date:** [5 February 2019 16:51 UTC](https://discourse.nodered.org/t/mysql-node-red-table/7612/15 "2019-02-05T16:51:22Z")

</div>

Change the Debug node to display `msg.topic` - as that is the property you are setting and is the property you want to check the value of.

---

<div class="post-metadata">

**Author:** ![smalhao](https://avatars.discourse-cdn.com/v4/letter/s/a3d4f5/32.png) [@smalhao](https://discourse.nodered.org/u/smalhao)\
**Post date:** [5 February 2019 16:55 UTC](https://discourse.nodered.org/t/mysql-node-red-table/7612/16 "2019-02-05T16:55:56Z")

</div>

Like this?

 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/2X/2/286d01b44a23867a5a5f6bf936e4c991725161d1.png)

---

<div class="post-metadata">

**Author:** ![smalhao](https://avatars.discourse-cdn.com/v4/letter/s/a3d4f5/32.png) [@smalhao](https://discourse.nodered.org/u/smalhao)\
**Post date:** [5 February 2019 16:58 UTC](https://discourse.nodered.org/t/mysql-node-red-table/7612/17 "2019-02-05T16:58:28Z")

</div>

Not appears anything in debug !!!

---

<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:** [5 February 2019 17:17 UTC](https://discourse.nodered.org/t/mysql-node-red-table/7612/18 "2019-02-05T17:17:12Z")

</div>

Is anything appearing in the "Input" debug? If not then it may be because the join node is waiting for three values.

---

<div class="post-metadata">

**Author:** ![smalhao](https://avatars.discourse-cdn.com/v4/letter/s/a3d4f5/32.png) [@smalhao](https://discourse.nodered.org/u/smalhao)\
**Post date:** [5 February 2019 17:23 UTC](https://discourse.nodered.org/t/mysql-node-red-table/7612/19 "2019-02-05T17:23:21Z")

</div>

in "output debug" don´t appears anything, the Join i configurated that way:

 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/2X/3/3c3ddf4fc3d69ec4eb89fb9319edb8a8bf20ee5a.png)

---

<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:** [5 February 2019 17:29 UTC](https://discourse.nodered.org/t/mysql-node-red-table/7612/20 "2019-02-05T17:29:16Z")

</div>

That Join will never send anything as you have not told it how many message parts to expect. You probably want it set to 3.

[Next page](https://discourse.nodered.org/t/mysql-node-red-table/7612.md?page=2)