I want to insert these data to MySQL database in the one table. how can i insert multiple data in the same time, now i had insert one item data succeed. who can help me?
This is the sort of thing I use. I've not tried it yet as I'm not near a computer at the moment.
// Extract parameters from the incoming message
let value = msg.payload.value;
let tag = msg.payload.tag;
// Get the current time
let currentTime = new Date().toISOString().slice(0, 19).replace('T', ' ');
// Define the prepared statement with placeholders
let query = "INSERT INTO opc_data_to_mysql (value, create_time, tag) VALUES (?, ?, ?)";
// Define the parameters for the placeholders
let params = [value, currentTime, tag];
// Set the query and params on the message
msg.topic = query;
msg.payload = params;
// Return the message to be passed to the MySQL node
return msg;
Note 1:
new Date().toISOString().slice(0, 19).replace('T', ' ') creates a timestamp in the format YYYY-MM-DD HH:MM:SS which is suitable for MySQL DATETIME fields.
Note 2:
You haven't said if the 'tag' or 'value' fields are a Primary Key.
If one of them is a Primary Key, then leave it out in the var params definition.
Note 3:
I've assumed all the data is in msg.payload. As @Steve-Mcl says, have a look in the cookbook to see how to 'join' separate messages together.
See this article in the cookbook for an example of how to join messages into one object.
1 Like
You really should look up how to use "prepared statements" as these are by far the most efficient and safest ways to do these kinds of updates.
On top of Dave's notes, please make sure that you SANITISE any inputs:
1 Like
