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

[zazas321]

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 :

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?

[bakman2]

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:

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;

[zazas321]

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 :

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:

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

[dynamicdave]

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.

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

[bakman2]

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 ?

[zazas321]

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

[zazas321]

In my phpmyadmin I have defined the age as integer.

[bakman2]

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

Are you sure ?

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.

[dynamicdave]

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...

[zazas321]

I have defined the age as int:

I have double checked about this function:

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

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

[dynamicdave]

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.

[zazas321]

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

[zenofmud]

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

[zazas321]

When using the function node:

{"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:

{"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

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;

[zenofmud]

Try using this

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

[zazas321]

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