# How to pass payload data from user "form" to function

**URL:** <https://discourse.nodered.org/t/how-to-pass-payload-data-from-user-form-to-function/30106>\
**Category:** General\
**Created:** [15 July 2020 07:34 UTC](https://discourse.nodered.org/t/how-to-pass-payload-data-from-user-form-to-function/30106 "2020-07-15T07:34:36Z")\
**Posts on this page:** 17\
**Page:** 1

<div class="post-metadata">

**Author:** ![zazas321](https://avatars.discourse-cdn.com/v4/letter/z/59ef9b/32.png) [@zazas321](https://discourse.nodered.org/u/zazas321)\
**Post date:** [15 July 2020 07:34 UTC](https://discourse.nodered.org/t/how-to-pass-payload-data-from-user-form-to-function/30106/1 "2020-07-15T07:34:37Z")

</div>

Hello. I am working with node-red and mysql database. I would like to fill a form and send the data from the form to the database.  
The flow :

 ![2020-07-15-102948_1920x1080_scrot](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/8/0/80f0ca7db6e914e864bb3f77585028f3b50d9441.png)

So I just have a form where I fill my data, then I have the function node which holds the query for the mysql.  
I can already insert data to mysql data if I define the function as such:

msg.payload={};  
msg.topic="INSERT INTO users (Name,Surname,Age,City)VALUES('test\_name','test\_surname','10','test\_city')";  
return msg;

However, what I actually need, is take the variables from the form and insert them into the querry as such:

msg.payload={};  
msg.topic="INSERT INTO users (Name,Surname,Age,City)VALUES(variable1,variable2,variable3,variable4)";  
return msg;

Im just not too sure how do I pass the form variables into the querry?

 ![2020-07-15-103403_1920x1080_scrot](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/5/5/55ea2c68ce6b15315c8067bbc10907db1c7c4e82.png)

---

<div class="post-metadata">

**Author:** ![bakman2](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/bakman2/32/6207_2.png) [@bakman2](https://discourse.nodered.org/u/bakman2)\
**Post date:** [15 July 2020 07:48 UTC](https://discourse.nodered.org/t/how-to-pass-payload-data-from-user-form-to-function/30106/2 "2020-07-15T07:48:22Z")

</div>

Note that you have to set the debug node output to show the `complete msg object` in order to see your query in msg.topic.

You can read/set the variables like:

```auto
m = msg.payload
name = m.Name
surname = m.Surname
age = m.Age
city = m.City

msg.topic=`INSERT INTO users (Name,Surname,Age,City) VALUES ('${name}','${surname}',${age},'${city}')`;

return msg;

```

---

<div class="post-metadata">

**Author:** ![zazas321](https://avatars.discourse-cdn.com/v4/letter/z/59ef9b/32.png) [@zazas321](https://discourse.nodered.org/u/zazas321)\
**Post date:** [15 July 2020 08:02 UTC](https://discourse.nodered.org/t/how-to-pass-payload-data-from-user-form-to-function/30106/3 "2020-07-15T08:02:29Z")

</div>

Thanks - im making some progress. Now my function recognise the payload input with your suggested code, however the mysql does not like the format that I am providing:  
"Error: ER\_PARSE\_ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '{age},'${city}')' at line 1"

The function that I use :

```auto

m=msg.payload;
name = m.Name;
surname = m.Surname;
age = m.Age;
city = m.City;
msg.topic="INSERT INTO users (Name,Surname,Age,City)VALUES('${name}','${surname}',${age},'${city}')";
return msg;

```

Would you be able to tell me whats the exact purpose of $ sign in front of every variable and why use {} brackets?

Would it not be the same if I use:

```auto
msg.topic="INSERT INTO users (Name,Surname,Age,City)VALUES('$name','$surname',$age,'$city')";

```

---

<div class="post-metadata">

**Author:** ![dynamicdave](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/dynamicdave/32/96_2.png) [@dynamicdave](https://discourse.nodered.org/u/dynamicdave)\
**Post date:** [15 July 2020 08:14 UTC](https://discourse.nodered.org/t/how-to-pass-payload-data-from-user-form-to-function/30106/4 "2020-07-15T08:14:27Z")

</div>

I usually use a template node to format my MySQL queries. Make sure the property is set to... msg.topic  
I find it easier to understand and not get tripped-up with all the quotation marks.

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

 ![ScreenShot160](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/7/1/71585235cbfe5d5f825b44f69f61deed17d82f7d.png)

Like @bakman2 I've assumed the age column in your MySQL database is an integer!!!

---

<div class="post-metadata">

**Author:** ![bakman2](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/bakman2/32/6207_2.png) [@bakman2](https://discourse.nodered.org/u/bakman2)\
**Post date:** [15 July 2020 08:16 UTC](https://discourse.nodered.org/t/how-to-pass-payload-data-from-user-form-to-function/30106/5 "2020-07-15T08:16:02Z")

</div>

The `${var}` is a template variable in javascript.

Another way to write is like

`msg.topic="INSERT INTO users (Name,Surname,Age,City)VALUES('"+name+"'...`

But that is not as clear with all the single double quotes. Note that you **need** the single quotes for strings in the query.

A template node like @dynamicdave shows also works.

The error you received, did you define the age column as a integer or as varchar ?

---

<div class="post-metadata">

**Author:** ![zazas321](https://avatars.discourse-cdn.com/v4/letter/z/59ef9b/32.png) [@zazas321](https://discourse.nodered.org/u/zazas321)\
**Post date:** [15 July 2020 08:20 UTC](https://discourse.nodered.org/t/how-to-pass-payload-data-from-user-form-to-function/30106/6 "2020-07-15T08:20:11Z")

</div>

Thanks for the suggestion.  
What is the purpose of the function node if you are building your query using template?

---

<div class="post-metadata">

**Author:** ![zazas321](https://avatars.discourse-cdn.com/v4/letter/z/59ef9b/32.png) [@zazas321](https://discourse.nodered.org/u/zazas321)\
**Post date:** [15 July 2020 08:20 UTC](https://discourse.nodered.org/t/how-to-pass-payload-data-from-user-form-to-function/30106/7 "2020-07-15T08:20:41Z")

</div>

In my phpmyadmin I have defined the age as integer.

---

<div class="post-metadata">

**Author:** ![bakman2](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/bakman2/32/6207_2.png) [@bakman2](https://discourse.nodered.org/u/bakman2)\
**Post date:** [15 July 2020 08:22 UTC](https://discourse.nodered.org/t/how-to-pass-payload-data-from-user-form-to-function/30106/8 "2020-07-15T08:22:00Z")

</div>

> I can already insert data to mysql data if I define the function as such:

Are you sure ?

```auto
msg.topic="INSERT INTO users (Name,Surname,Age,City)VALUES('test_name','test_surname','10','test_city')";
return msg;

```

Here you are inserting it as a string, that would give an error.

---

<div class="post-metadata">

**Author:** ![dynamicdave](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/dynamicdave/32/96_2.png) [@dynamicdave](https://discourse.nodered.org/u/dynamicdave)\
**Post date:** [15 July 2020 08:22 UTC](https://discourse.nodered.org/t/how-to-pass-payload-data-from-user-form-to-function/30106/9 "2020-07-15T08:22:32Z")

</div>

You need the function node to get all your variables into the right places.

You might find you can get rid of it if things are in the right place.

My solution assumes things are in msg.

e.g. msg.name msg.surname etc...

---

<div class="post-metadata">

**Author:** ![zazas321](https://avatars.discourse-cdn.com/v4/letter/z/59ef9b/32.png) [@zazas321](https://discourse.nodered.org/u/zazas321)\
**Post date:** [15 July 2020 08:27 UTC](https://discourse.nodered.org/t/how-to-pass-payload-data-from-user-form-to-function/30106/10 "2020-07-15T08:27:34Z")

</div>

I have defined the age as int:

 ![2020-07-15-112543_1920x1080_scrot](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/5/3/53a742d73851b346eb3f82d893dfa1d0e5a2dd0b.png)  
I have double checked about this function:

```auto
msg.topic="INSERT INTO users (Name,Surname,Age,City)VALUES('test_name','test_surname','10','test_city')";

```

and that works fine - it inserted a new line in my table

 ![2020-07-15-112647_1920x1080_scrot](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/f/1/f1947a68f5d19c3e24b27eb25ee9c351f19b26a8.png)

Trying to figure out how to get your function to work now

---

<div class="post-metadata">

**Author:** ![dynamicdave](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/dynamicdave/32/96_2.png) [@dynamicdave](https://discourse.nodered.org/u/dynamicdave)\
**Post date:** [15 July 2020 08:41 UTC](https://discourse.nodered.org/t/how-to-pass-payload-data-from-user-form-to-function/30106/11 "2020-07-15T08:41:45Z")

</div>

If your data is all in the `msg.payload`  
e.g. `msg.payload.Name, msg.payload.Surname` etc..

Then you could alter my template node to..  
`INSERT INTO users (Name,Surname,Age,City) VALUES { '{{payload.name}}', '{{payload.Surname}}', {{payload.age}}, '{{payload.city}}' );`

And get rid of the function node.

Hope this helps.

---

<div class="post-metadata">

**Author:** ![zazas321](https://avatars.discourse-cdn.com/v4/letter/z/59ef9b/32.png) [@zazas321](https://discourse.nodered.org/u/zazas321)\
**Post date:** [15 July 2020 09:18 UTC](https://discourse.nodered.org/t/how-to-pass-payload-data-from-user-form-to-function/30106/13 "2020-07-15T09:18:49Z")

</div>

That works! The function node does almost exactly the same thing but does not like the integer for some reason - cant figure it out.

---

<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:** [15 July 2020 10:22 UTC](https://discourse.nodered.org/t/how-to-pass-payload-data-from-user-form-to-function/30106/15 "2020-07-15T10:22:46Z")

</div>

Put a `debug` node (set to display the 'Complete mag object') on the output of your 'template' node. Do the same for the `function` Run a test and then in the debug sidebar, open all the triangles for the debugs and copy the results and past them in a reply

---

<div class="post-metadata">

**Author:** ![zazas321](https://avatars.discourse-cdn.com/v4/letter/z/59ef9b/32.png) [@zazas321](https://discourse.nodered.org/u/zazas321)\
**Post date:** [15 July 2020 10:38 UTC](https://discourse.nodered.org/t/how-to-pass-payload-data-from-user-form-to-function/30106/16 "2020-07-15T10:38:45Z")

</div>

When using the function node:

```auto
{"payload":{"Item":"item_test_function","Serial":12345},"socketid":"fo8kJFtN6HuwBUOHAAAE","_msgid":"33b945ef.2d52ba","topic":"INSERT INTO pack_to_light (Device,Item,Serial)VALUES('device1','${item}',${serial})"}

```

when using the template node:

```auto
{"payload":{"Item":"item_test_template","Serial":12345},"socketid":"fo8kJFtN6HuwBUOHAAAE","_msgid":"20d8bcb0.82c844","topic":"INSERT INTO pack_to_light (Device,Item,Serial) VALUES ('Device1', 'item_test_template',12345 );"}

```

Note that I have changed my database and function slightly:

function node

```auto

m=msg.payload
item = m.Item
serial = m.Serial

msg.topic="INSERT INTO pack_to_light (Device,Item,Serial)VALUES('device1','${item}',${serial})";
return msg;

```

---

<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:** [15 July 2020 11:49 UTC](https://discourse.nodered.org/t/how-to-pass-payload-data-from-user-form-to-function/30106/17 "2020-07-15T11:49:43Z")

</div>

Try using this

```auto
msg.topic="INSERT INTO pack_to_light (Device,Item,Serial) VALUES ('device1','${item}',${serial})";

```

---

<div class="post-metadata">

**Author:** ![zazas321](https://avatars.discourse-cdn.com/v4/letter/z/59ef9b/32.png) [@zazas321](https://discourse.nodered.org/u/zazas321)\
**Post date:** [20 July 2020 06:12 UTC](https://discourse.nodered.org/t/how-to-pass-payload-data-from-user-form-to-function/30106/18 "2020-07-20T06:12:58Z")

</div>

Isnt thats the exactly the same as I have already used? (The post above)

---

<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:** [18 September 2020 06:13 UTC](https://discourse.nodered.org/t/how-to-pass-payload-data-from-user-form-to-function/30106/19 "2020-09-18T06:13:03Z")

</div>

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