# Insert Into MySQL Database

**URL:** <https://discourse.nodered.org/t/insert-into-mysql-database/32949>\
**Category:** General\
**Created:** [16 September 2020 09:24 UTC](https://discourse.nodered.org/t/insert-into-mysql-database/32949 "2020-09-16T09:24:44Z")\
**Posts on this page:** 17\
**Page:** 1

<div class="post-metadata">

**Author:** ![swanside](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/swanside/32/28781_2.png) [@swanside](https://discourse.nodered.org/u/swanside)\
**Post date:** [16 September 2020 09:24 UTC](https://discourse.nodered.org/t/insert-into-mysql-database/32949/1 "2020-09-16T09:24:44Z")

</div>

Hi All  
I have now got a connection to work for a remote MySQL database and I have this in my function

```auto
Current = msg.payload.Current
Voltage = msg.payload.Voltage
msg.topic = "INSERT INTO Data(`Current`, `Voltage`) VALUES ('Current', 'Voltage')";
return msg;

```

But it actually put in the words Current and Voltage  
 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/0/5/05eec2079c17271c13879a4ea445f7574d8537a9.png)

But it does read the correct values, so can somebody please help me with the Insert IN to please so it puts the payload in?  
Thank you  
 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/2/d/2d334cbec37e5baa7633397d8d5204b9013d8044.png)

---

<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:** [16 September 2020 09:32 UTC](https://discourse.nodered.org/t/insert-into-mysql-database/32949/2 "2020-09-16T09:32:33Z")

</div>

Hi @swanside

The code doesn't know you want to insert the values of `Current` and `Voltage` into the string - it just sees the text Current and Voltage.

One way to do it is to build the string up by joining together the different parts:

```auto
var Current = msg.payload.Current
var Voltage = msg.payload.Voltage
msg.topic = "INSERT INTO Data(`Current`, `Voltage`) VALUES ('"+Current+"', '"+Voltage+"')";
return msg;

```

---

<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:** [16 September 2020 09:36 UTC](https://discourse.nodered.org/t/insert-into-mysql-database/32949/3 "2020-09-16T09:36:50Z")

</div>

Although I may have replied a bit too quickly as your screenshot of the messages shows you don't have `msg.payload.Current` and `msg.payload.Voltage` as your Function code assumes.

Instead you appear to have the two values arriving in different messages, with `msg.topic` identifying whether its Current or Voltage, and the corresponding value in `msg.payload`.

So you first need to get those two separate messages into one message so you can do a single insert with them both.

To do that, add a Join node, configured as follows:

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

---

<div class="post-metadata">

**Author:** ![swanside](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/swanside/32/28781_2.png) [@swanside](https://discourse.nodered.org/u/swanside)\
**Post date:** [16 September 2020 09:38 UTC](https://discourse.nodered.org/t/insert-into-mysql-database/32949/4 "2020-09-16T09:38:46Z")

</div>

Thanks.  
Was just going to reply when you latest message popped up. Will give that a try. Thank you

---

<div class="post-metadata">

**Author:** ![swanside](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/swanside/32/28781_2.png) [@swanside](https://discourse.nodered.org/u/swanside)\
**Post date:** [16 September 2020 09:49 UTC](https://discourse.nodered.org/t/insert-into-mysql-database/32949/5 "2020-09-16T09:49:07Z")

</div>

Thanks It is giving an undefined in the database  
 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/c/0/c08364783d2a590cb5ebddf67fcab28e3a0b6ef0.png)

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

---

<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:** [16 September 2020 09:52 UTC](https://discourse.nodered.org/t/insert-into-mysql-database/32949/6 "2020-09-16T09:52:32Z")

</div>

At the moment it is hard to see how all of those screenshots relate to each other.

What does your flow look like? Where are the Debug nodes you are showing the output of in relation to the Function node and the MySQL node? Where have you put the Join node?

The Debug screenshot shows you have a message where `msg.payload` in now an object - but we can't see what that object looks like. Expand that out to confirm the payload contains a `Current` and `Voltage` property.

---

<div class="post-metadata">

**Author:** ![swanside](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/swanside/32/28781_2.png) [@swanside](https://discourse.nodered.org/u/swanside)\
**Post date:** [16 September 2020 09:55 UTC](https://discourse.nodered.org/t/insert-into-mysql-database/32949/7 "2020-09-16T09:55:37Z")

</div>

This is my actual flow if that will help make sense of what I am trying to do. I have deleted the MySQl server just for security.  
Thank you

[flows(7).json](https://discourse.nodered.org/uploads/short-url/t6wvxvqpmUrKxx5mXXplOpFASOX.json) (24.6 KB)

---

<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:** [16 September 2020 10:00 UTC](https://discourse.nodered.org/t/insert-into-mysql-database/32949/8 "2020-09-16T10:00:14Z")

</div>

Sorry, I'm not in a position to import a large flow containly lots of dashboard nodes and the like.

Can you just share a screenshot that shows the relationship between the Join, Function and MySQl nodes?

---

<div class="post-metadata">

**Author:** ![swanside](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/swanside/32/28781_2.png) [@swanside](https://discourse.nodered.org/u/swanside)\
**Post date:** [16 September 2020 10:06 UTC](https://discourse.nodered.org/t/insert-into-mysql-database/32949/9 "2020-09-16T10:06:02Z")

</div>

Oh Sorry about that. Yep here are some shots. Thank You.

 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/d/6/d6a110463185f4d6791f84dfee0b9dad524efc9d.png)  
 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/e/c/ec7184f95e54e81ce57688e7271794e3e07686ec.png)  
 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/c/e/cebed26a265663b57750fdea5e818f61b98452ff.png)  
 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/c/e/ce6eae28b4315326d987914cd4217f2e1406f3e8.png)  
 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/d/3/d3aca77ff455d6860340a2fe81ee1e7298697750.png)  
 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/8/f/8fa61cd53b328cdd446384f8f12e64f179e7746f.png)

---

<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:** [16 September 2020 10:10 UTC](https://discourse.nodered.org/t/insert-into-mysql-database/32949/10 "2020-09-16T10:10:58Z")

</div>

So you have the Join node combining the Voltage/Current messages (although you haven't expanded the Debug sidebar to confirm that fact).

But you aren't passing the output of the Join node to the Function node, so the data doesn't go anywhere.

---

<div class="post-metadata">

**Author:** ![swanside](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/swanside/32/28781_2.png) [@swanside](https://discourse.nodered.org/u/swanside)\
**Post date:** [16 September 2020 10:14 UTC](https://discourse.nodered.org/t/insert-into-mysql-database/32949/11 "2020-09-16T10:14:39Z")

</div>

Oh I see.  
OK I will give that a try, go to go to work now but will try when I get home and update the post. Thank You

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

---

<div class="post-metadata">

**Author:** ![swanside](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/swanside/32/28781_2.png) [@swanside](https://discourse.nodered.org/u/swanside)\
**Post date:** [19 September 2020 12:30 UTC](https://discourse.nodered.org/t/insert-into-mysql-database/32949/12 "2020-09-19T12:30:40Z")

</div>

HI.  
Right Sorry about the delay getting back. I have edited the Join mode to the below, so the first value is my Voltage and the second is my Current, and then it hits the Function it gives the error in the Insert INTO

 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/5/3/53fbd9e4b1f8d3e313888f7bef222c3ab0084475.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:** [19 September 2020 12:44 UTC](https://discourse.nodered.org/t/insert-into-mysql-database/32949/13 "2020-09-19T12:44:15Z")

</div>

Did you want an array from the Join node? If not then don't select it. I suspect you want Key/Value objects instead.

---

<div class="post-metadata">

**Author:** ![swanside](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/swanside/32/28781_2.png) [@swanside](https://discourse.nodered.org/u/swanside)\
**Post date:** [19 September 2020 13:16 UTC](https://discourse.nodered.org/t/insert-into-mysql-database/32949/14 "2020-09-19T13:16:22Z")

</div>

Thanks Colin.  
I changed it to a key/value Object, but still shows an array. I will reboot the device

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

---

<div class="post-metadata">

**Author:** ![swanside](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/swanside/32/28781_2.png) [@swanside](https://discourse.nodered.org/u/swanside)\
**Post date:** [19 September 2020 13:32 UTC](https://discourse.nodered.org/t/insert-into-mysql-database/32949/15 "2020-09-19T13:32:35Z")

</div>

FANTASTIC GUYS.

Thank for all your help I have it sending my data to a remote MySQL database now.

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

---

<div class="post-metadata">

**Author:** ![swanside](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/swanside/32/28781_2.png) [@swanside](https://discourse.nodered.org/u/swanside)\
**Post date:** [19 September 2020 14:12 UTC](https://discourse.nodered.org/t/insert-into-mysql-database/32949/16 "2020-09-19T14:12:57Z")

</div>

Now while it is sending data it seems also send undefind data also, and it looks like it comes from the same Function Modual. Any reason why that would occur please

 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/5/6/5603cdd827c4fb457922c623ec36e27c4e07be1c.png)  
My mistake, It was sending data when received from the Modbus interface and I also had an inject on it alos, so it would send no data. All done now, Time to play with the PHP and hTML to make a nice datalogger. Thank again

---

<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 October 2020 14:13 UTC](https://discourse.nodered.org/t/insert-into-mysql-database/32949/17 "2020-10-03T14:13:03Z")

</div>

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