Assemble mqtt data to mysql

[quito96]

Hi there,

i am currently working on a private project by storing data of type number via mqtt into a mysql database.
The connection to mysql works, i have tested this with the inject node.

The sql insert command looks like this:

INSERT INTO mqtt-weather.temp
(timestamp,
value0)
VALUES
(<{timestamp: CURRENT_TIMESTAMP}>,<{value0: }>);

With this example payload I have successfully written to the sql database

INSERT INTO mqtt-weather.temp
(timestamp,
value0)
VALUES
(20200217180055,-2.1);

Now to my question proper:
How can i combine the data in node red with timestamp and value0 to pass the Insert Into command to the mysql node? (the mqtt channel returns only value0)
What would be the easiest and best way to reach the line??

In this respect I am a beginner and would very much like to learn!
Many thanks in advance.
Quito

[Colin]

If you always want to write the current time to that column then the best way is to create the timestamp column with a default of CURRENT_TIMESTAMP, then you don't need to provide a value for that column at all and mysql will automatically fill it in, so your query becomes

INSERT INTO mqtt-weather.temp
(timestamp)
VALUES
(-2.1);

If you don't want to do that then you can use NOW() so the query is

INSERT INTO mqtt-weather.temp
(timestamp,
value0)
VALUES
(NOW(),-2.1);

[quito96]

Hey, Colin both ways are great Thanks

the mqtt channel returns only value0 as type number.
What would be the best way to integrate it? Via a function node ?

[zenofmud]

I like to build my queries in a template node something like this

SELECT * FROM wp_users 
where user_login = '{{payload}}';

[Colin]

Either a function node or a template (not ui template) node. The template node may be considered simpler. The info tab for the node gives some examples that should let you do it. Don't forget to set the Property field to msg.topic.

[quito96]

Template Node leike this..? payload set to value0

INSERT INTO mqtt-weather.
temp (timestamp, value0)
VALUES (NOW(),{{value0}});

[zenofmud]

If the MQTT node returns only the value, it should be in msg.payload. In that case you should use

INSERT INTO mqtt-weather.
temp (timestamp, value0)
VALUES (NOW(),{{payload}});

because the value is in msg.payload

[quito96]

Hi Colin, Hi zenofmud,

I thank you both because it's working out like I thought. Super