How to pass payload data from node red to mysql

[asyrahshah]

Hello I am new with node red. Currently, I am working with node-red and mysql database for my final year project. I would like to send temperature data to the database but i have error stated that the array is undefined.

this is my node red flow..
and this is the function in mysql function

var test = msg.payload;
var temp = test.temperature;

msg.payload = [temp];

msg.topic = "INSERT INTO temperature(temperature) VALUES (?)";
return msg;

however, the msg.payload :

INSERT INTO temperature(temperature) VALUES (?) : msg.payload : array[1]
[ undefined ]

can you help me to figure out my problem?

[Steve-Mcl]

Hi, welcome to the forum.

Unless you name your debug nodes it is difficult to tell from a picture.

At a guess, msg.payload is a number BUT here...

var test = msg.payload;
var temp = test.temperature;

msg.payload = [temp];

you are affectively saying "Set msg.payload to a new array [ ] and put msg.payload.temperature inside"

Try this instead...

const temp = msg.payload;
msg.payload = [temp];
msg.topic = "INSERT INTO temperature(`temperature`) VALUES (?)";
return msg;

---

PS, please remember to wrap code in three backticks
```
like this
```

[asyrahshah]

thank you.. i got it

[asyrahshah]

if i want to add another column such as id from the node red together with the temperature data .. how should i write it?

[Steve-Mcl]

if the data comes from different nodes, then you need to join the 2 bits of data together into 1 message/payload so that you can use them together in your insert query.

See this article in the cookbook for an example of how to join messages into one object.

[asyrahshah]

i have tried using join, but i still got an error

this is the debug message:

{"INSERT INTO temperature(temperature) VALUES (?)":[36.75],"INSERT INTO temperature(id) VALUES (?)":[{"id":1729336}]}

how ever, the id is not updated in mysql.
can you please help me.

[Steve-Mcl]

dont put the object in the payload array - only put the value.

e.g.

msg.payload = [ msg.payload.id ]; 

or if you have 2 params then update your query and add the 2 params (use debug window as instructed below)

const data = msg.payload;
msg.payload = [data.id, data.temperature];
msg.topic = "INSERT INTO temperature(`id`, `temperature`) VALUES (?,?)";
return msg;

↑ does that make sense?

---

There’s a great page in the docs that will explain how to use the debug panel to find the right path to any data item.

Pay particular attention to the part about the buttons that appear under your mouse pointer when you over hover a debug message property in the sidebar.

https://nodered.org/docs/user-guide/messages