mySQL INSERT INTO with multiple msg.xxx failing

Been working on this for several days and have exhausted my abilities and various suggestions via the web and these forums...so thank you in advance for any assistance!

Basis:

  1. Have 3 MQTT inputs/message payloads
  2. In node red, converting payload to number(s)
  3. Since all messages had same designation of msg.payload, using a change node/move rule to make unique transformations of 3 msg.payload (s) to (msg.Temp, msg.Humidity, msg.Pressure)
  4. Have PHP MySQL running on Pi, Node red connection tested successfully with simple insert statements where the insert statement contains static values, verifying successful DB insert

Issue/Error
The "INSERT INTO" function is:

var newMsg = {
topic : "INSERT INTO exterior_climate (Temp, Pressure, Humidity, Light, Voltage) VALUES ('"+msg.Temp+"','"+msg.Pressure+"','"+msg.Humidity+"',30,20)"
};
return newMsg

However no records are being inserted into the DB, shown below is the output from the debug window:

All inserts into the DB fail. I am confused that the debug is outputting 3 messages staggered that in sum do show the proper msg.XXX in their correct location (BTW: by design the last 2 values are static for the moment), but that the insert does not appear to be fully populated with the 3 values, then transferred to the insert.

Hi. I think this might help. Can't be certain as you haven't posted your flow or a screenshot...

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

Just to add further clarification, in case you didn't realise, the problem is that your function assumes that each message contains all three values whereas, as you can see, each message has only one of them defined. The solution is to use a Join node as suggested. You won't need to move the values into individual properties, the Join node will do that for you.

Thank you both! I had tried the Join earlier, but missed the "Send the message after xxx message parts" so obviously the join was not properly configured. With this properly configured I am able to get the proper values into the insert statement as shown:

nr3

However, the insert statement is not actually inserting any records into the DB, everything looks OK to me. Can anyone see an issue with my Insert statement?

var newMsg = {
topic : "INSERT INTO exterior_climate (Temp, Pressure,Humidity, Light, Voltage) VALUES ("+msg.payload.Outside_Temp+","+msg.payload.Outside_Pressure+","+msg.payload.Outside_Humidity+",30,20)"
}
return newMsg;

As previously stated, I was able to do inserts with hand entered numbers prior.

Are there any messages coming out of the sql node? Does a Catch node catch any errors from the sql node?

Catch node does not capture any errors. DB is connected in NR.

I changed the insert function to this, but still no success in inserting a new record:

msg.topic="INSERT INTO exterior_climate (Temp,Pressure,Humidity,Light,Voltage) VALUES ("+msg.payload.Outside_Temp+","+msg.payload.Outside_Pressure+","+msg.payload.Outside_Humidity+",30,20)";
msg.payload=[msg.payload.Temp,msg.payload.Pressure,msg.payload.Humidity,msg.payload.Light,msg.payload.Voltage];
return msg;

Don't show us what is in the function, what matters is what is going into the sql node. Use the little Copy Value button next to the value in the debug window.

You didn't answer the question about what is coming out of the sql node.

Also are you using node-red-node-mysql (look in Manage Palette to check). Have you installed any other sql nodes?

Thank you for your patience !

Here is what is coming out of the debug node that is wired after the insert statement:

[null,null,null,null,null]

nr4

Yes I am using the Node Red MY SQL Node, properly configured and connected (as shown by the green dot). During previous troubleshooting using static values in the insert command, I was able to successfully insert records into the DB.

I Found and fixed some typos in the insert which now reads:

msg.topic="INSERT INTO exterior_climate (Temp,Pressure,Humidity,Light,Voltage) VALUES ("+msg.payload.Outside_Temp+","+msg.payload.Outside_Pressure+","+msg.payload.Outside_Humidity+",30,20)";
msg.payload=[msg.payload.Outside_Temp,msg.payload.Outside_Pressure,msg.payload.Outside_Humidity,20,30];
return msg;

Now to output from the debug node shows:

So now the values are in, but still no record being inserted into the DB

Show us the debug node output from your test insert of static data. But before showing us, does it look the same as your actual data?

All, again...many thanks. I continued to fiddle with the insert statement and even though I am uncertain which of the many things I tried did work. The data is now inserting to SQL. I hope to get more proficient with NR so that at some time I may be of assistance to others. Kind Regards