MQTT to mySQL2 with multiply topics

Hey Guys. My first post here. Needs help to tune up this.
I have an weather station givin me PHT values. I send them to my MQTT (mosquitto) and try to get them into mySQL. It work fine on the top flow illustrated, but offcourse it comes in on individual entries in the db. Now, I need to have the 3 reading combined into one sql query (like in the buttom flow), but i am strugling with naming the values and cant get it to work. Plese help.

[
    {
        "id": "fee8ebee450d1f0d",
        "type": "tab",
        "label": "Flow 1",
        "disabled": false,
        "info": "",
        "env": []
    },
    {
        "id": "9676ab8d05635873",
        "type": "mqtt in",
        "z": "fee8ebee450d1f0d",
        "name": "",
        "topic": "esp/bme280/pressure",
        "qos": "2",
        "datatype": "auto-detect",
        "broker": "abb92b8de7545eb4",
        "nl": false,
        "rap": true,
        "rh": 0,
        "inputs": 0,
        "x": 140,
        "y": 200,
        "wires": [
            [
                "1992391bd5511b94",
                "f7f1e3d654fa3cbc"
            ]
        ]
    },
    {
        "id": "cca579307b085bf3",
        "type": "mqtt in",
        "z": "fee8ebee450d1f0d",
        "name": "",
        "topic": "esp/bme280/temperature",
        "qos": "2",
        "datatype": "auto-detect",
        "broker": "abb92b8de7545eb4",
        "nl": false,
        "rap": true,
        "rh": 0,
        "inputs": 0,
        "x": 150,
        "y": 80,
        "wires": [
            [
                "1992391bd5511b94",
                "332bbcdee31f5d77"
            ]
        ]
    },
    {
        "id": "c2a5d71923f2db07",
        "type": "mqtt in",
        "z": "fee8ebee450d1f0d",
        "name": "",
        "topic": "esp/bme280/humidity",
        "qos": "2",
        "datatype": "auto-detect",
        "broker": "abb92b8de7545eb4",
        "nl": false,
        "rap": true,
        "rh": 0,
        "inputs": 0,
        "x": 140,
        "y": 140,
        "wires": [
            [
                "1992391bd5511b94",
                "64b70d5ec98b11f3"
            ]
        ]
    },
    {
        "id": "86d681a4bbc870df",
        "type": "mysql-r2",
        "z": "fee8ebee450d1f0d",
        "name": "env_Data",
        "host": "localhost",
        "database": "xxx",
        "username": "xxx",
        "password": "xxx",
        "sql": "",
        "port": "3306",
        "pooling": false,
        "waitForConnections": true,
        "connectionLimit": "10",
        "queueTimeout": "10000",
        "x": 700,
        "y": 80,
        "wires": [
            []
        ]
    },
    {
        "id": "50aa7e302aa6b46a",
        "type": "debug",
        "z": "fee8ebee450d1f0d",
        "name": "debug 3",
        "active": true,
        "tosidebar": true,
        "console": false,
        "tostatus": false,
        "complete": "payload",
        "targetType": "msg",
        "statusVal": "",
        "statusType": "auto",
        "x": 920,
        "y": 80,
        "wires": []
    },
    {
        "id": "1992391bd5511b94",
        "type": "debug",
        "z": "fee8ebee450d1f0d",
        "name": "debug 2",
        "active": true,
        "tosidebar": true,
        "console": false,
        "tostatus": false,
        "complete": "payload",
        "targetType": "msg",
        "statusVal": "",
        "statusType": "auto",
        "x": 460,
        "y": 20,
        "wires": []
    },
    {
        "id": "7655cce3a079d198",
        "type": "debug",
        "z": "fee8ebee450d1f0d",
        "name": "debug 4",
        "active": true,
        "tosidebar": true,
        "console": false,
        "tostatus": false,
        "complete": "payload",
        "targetType": "msg",
        "statusVal": "",
        "statusType": "auto",
        "x": 680,
        "y": 20,
        "wires": []
    },
    {
        "id": "461cc5ae368efad6",
        "type": "mysql-r2",
        "z": "fee8ebee450d1f0d",
        "name": "env_Data",
        "host": "localhost",
        "database": "xxx",
        "username": "xxx",
        "password": "xxx",
        "sql": "",
        "port": "3306",
        "pooling": false,
        "waitForConnections": false,
        "connectionLimit": "10",
        "queueTimeout": "10000",
        "x": 700,
        "y": 80,
        "wires": [
            [
                "50aa7e302aa6b46a"
            ]
        ]
    },
    {
        "id": "2167966495703faf",
        "type": "mysql-r2",
        "z": "fee8ebee450d1f0d",
        "name": "env_Data",
        "host": "localhost",
        "database": "xxx",
        "username": "xxx",
        "password": "xxx",
        "sql": "",
        "port": "3306",
        "pooling": false,
        "waitForConnections": false,
        "connectionLimit": "10",
        "queueTimeout": "10000",
        "x": 700,
        "y": 140,
        "wires": [
            []
        ]
    },
    {
        "id": "19424326649cb014",
        "type": "mysql-r2",
        "z": "fee8ebee450d1f0d",
        "name": "env_Data",
        "host": "localhost",
        "database": "xxx",
        "username": "xxx",
        "password": "xxx",
        "sql": "",
        "port": "3306",
        "pooling": false,
        "waitForConnections": false,
        "connectionLimit": "10",
        "queueTimeout": "10000",
        "x": 700,
        "y": 200,
        "wires": [
            []
        ]
    },
    {
        "id": "660fdcb646def9ae",
        "type": "mqtt in",
        "z": "fee8ebee450d1f0d",
        "name": "",
        "topic": "esp/bme280/pressure",
        "qos": "2",
        "datatype": "json",
        "broker": "abb92b8de7545eb4",
        "nl": false,
        "rap": true,
        "rh": 0,
        "inputs": 0,
        "x": 140,
        "y": 480,
        "wires": [
            [
                "0d23800279a5cf4c"
            ]
        ]
    },
    {
        "id": "3ec6fb2480eff80c",
        "type": "mqtt in",
        "z": "fee8ebee450d1f0d",
        "name": "",
        "topic": "esp/bme280/temperature",
        "qos": "2",
        "datatype": "json",
        "broker": "abb92b8de7545eb4",
        "nl": false,
        "rap": true,
        "rh": 0,
        "inputs": 0,
        "x": 150,
        "y": 360,
        "wires": [
            [
                "0d23800279a5cf4c"
            ]
        ]
    },
    {
        "id": "b7fac1eb841aa3f7",
        "type": "mqtt in",
        "z": "fee8ebee450d1f0d",
        "name": "",
        "topic": "esp/bme280/humidity",
        "qos": "2",
        "datatype": "json",
        "broker": "abb92b8de7545eb4",
        "nl": false,
        "rap": true,
        "rh": 0,
        "inputs": 0,
        "x": 140,
        "y": 420,
        "wires": [
            [
                "0d23800279a5cf4c"
            ]
        ]
    },
    {
        "id": "e3c9fb74e73a43f8",
        "type": "function",
        "z": "fee8ebee450d1f0d",
        "name": "Insert Data",
        "func": "var temp = \" + msg.payload + \" ;\nmsg.sql = \"INSERT INTO env_Data (temperature) VALUES (\" + temp + \");\"\nreturn msg;",
        "outputs": 1,
        "timeout": 0,
        "noerr": 0,
        "initialize": "",
        "finalize": "",
        "libs": [],
        "x": 470,
        "y": 360,
        "wires": [
            [
                "cd1a58fb89c2f334",
                "3ccff03a49f9db0a"
            ]
        ]
    },
    {
        "id": "65006c2018c64196",
        "type": "debug",
        "z": "fee8ebee450d1f0d",
        "name": "debug 1",
        "active": false,
        "tosidebar": true,
        "console": false,
        "tostatus": false,
        "complete": "payload",
        "targetType": "msg",
        "statusVal": "",
        "statusType": "auto",
        "x": 920,
        "y": 420,
        "wires": []
    },
    {
        "id": "0d23800279a5cf4c",
        "type": "debug",
        "z": "fee8ebee450d1f0d",
        "name": "debug 2",
        "active": false,
        "tosidebar": true,
        "console": false,
        "tostatus": false,
        "complete": "payload",
        "targetType": "msg",
        "statusVal": "",
        "statusType": "auto",
        "x": 460,
        "y": 300,
        "wires": []
    },
    {
        "id": "cd1a58fb89c2f334",
        "type": "debug",
        "z": "fee8ebee450d1f0d",
        "name": "debug 3",
        "active": false,
        "tosidebar": true,
        "console": false,
        "tostatus": false,
        "complete": "payload",
        "targetType": "msg",
        "statusVal": "",
        "statusType": "auto",
        "x": 700,
        "y": 300,
        "wires": []
    },
    {
        "id": "7dc7a1c59c995d21",
        "type": "function",
        "z": "fee8ebee450d1f0d",
        "name": "Insert Data",
        "func": "msg.sql = \"INSERT INTO env_Data (humidity) VALUES (\" + msg.payload + \");\"\nreturn msg;\n",
        "outputs": 1,
        "timeout": 0,
        "noerr": 0,
        "initialize": "",
        "finalize": "",
        "libs": [],
        "x": 470,
        "y": 420,
        "wires": [
            [
                "3ccff03a49f9db0a"
            ]
        ]
    },
    {
        "id": "f9a4d182e232c8a4",
        "type": "function",
        "z": "fee8ebee450d1f0d",
        "name": "Insert Data",
        "func": "msg.sql = \"INSERT INTO env_Data (pressure) VALUES (\" + msg.payload + \");\"\nreturn msg;\n",
        "outputs": 1,
        "timeout": 0,
        "noerr": 0,
        "initialize": "",
        "finalize": "",
        "libs": [],
        "x": 470,
        "y": 480,
        "wires": [
            [
                "3ccff03a49f9db0a"
            ]
        ]
    },
    {
        "id": "3ccff03a49f9db0a",
        "type": "mysql-r2",
        "z": "fee8ebee450d1f0d",
        "name": "env_Data",
        "host": "localhost",
        "database": "xxx",
        "username": "xxx",
        "password": "xxx",
        "sql": "",
        "port": "3306",
        "pooling": false,
        "waitForConnections": false,
        "connectionLimit": "10",
        "queueTimeout": "10000",
        "x": 700,
        "y": 420,
        "wires": [
            [
                "65006c2018c64196"
            ]
        ]
    },
    {
        "id": "332bbcdee31f5d77",
        "type": "function",
        "z": "fee8ebee450d1f0d",
        "name": "Insert Data",
        "func": "msg.sql = \"INSERT INTO env_Data (temperature) VALUES (\" + msg.payload + \");\"\nreturn msg;",
        "outputs": 1,
        "timeout": 0,
        "noerr": 0,
        "initialize": "",
        "finalize": "",
        "libs": [],
        "x": 470,
        "y": 80,
        "wires": [
            [
                "461cc5ae368efad6",
                "7655cce3a079d198"
            ]
        ]
    },
    {
        "id": "64b70d5ec98b11f3",
        "type": "function",
        "z": "fee8ebee450d1f0d",
        "name": "Insert Data",
        "func": "msg.sql = \"INSERT INTO env_Data (humidity) VALUES (\" + msg.payload + \");\"\nreturn msg;",
        "outputs": 1,
        "timeout": 0,
        "noerr": 0,
        "initialize": "",
        "finalize": "",
        "libs": [],
        "x": 470,
        "y": 140,
        "wires": [
            [
                "2167966495703faf"
            ]
        ]
    },
    {
        "id": "f7f1e3d654fa3cbc",
        "type": "function",
        "z": "fee8ebee450d1f0d",
        "name": "Insert Data",
        "func": "msg.sql = \"INSERT INTO env_Data (pressure) VALUES (\" + msg.payload + \");\"\nreturn msg;",
        "outputs": 1,
        "timeout": 0,
        "noerr": 0,
        "initialize": "",
        "finalize": "",
        "libs": [],
        "x": 470,
        "y": 200,
        "wires": [
            [
                "19424326649cb014"
            ]
        ]
    },
    {
        "id": "abb92b8de7545eb4",
        "type": "mqtt-broker",
        "name": "",
        "broker": "localhost",
        "port": 1883,
        "clientid": "",
        "autoConnect": true,
        "usetls": false,
        "protocolVersion": 4,
        "keepalive": 60,
        "cleansession": true,
        "autoUnsubscribe": true,
        "birthTopic": "",
        "birthQos": "0",
        "birthRetain": "false",
        "birthPayload": "",
        "birthMsg": {},
        "closeTopic": "",
        "closeQos": "0",
        "closeRetain": "false",
        "closePayload": "",
        "closeMsg": {},
        "willTopic": "",
        "willQos": "0",
        "willRetain": "false",
        "willPayload": "",
        "willMsg": {},
        "userProps": "",
        "sessionExpiry": ""
    }
]

I am aware that the flows are not connected fully in the example below. But need to figure out what to have between the mqtt input and the mysql connection nodes first.

Welcome to the forum @kaniwd.

Your flow cannot be imported because the forum mangles code in the body of a post.
Please can you go back and edit your post (there is an edit button down at the bottom)
Insert a line above the code and another below the code, each containing three backticks
image

Most people use node-red-node-mysql (1531 downloads in the last week) to connect to MySQL & MariaDB. It's maintained by two important figures in the Node-red world, Dave & Nick.
You seem to be using node-red-node-mysql-r2 (100 downloads in the past week) It may well be a very fine contrib node but you would be wise to try the "official" one first.

Without importing your code I don't know if you have made the frequent beginners error of expecting a function node to remember previous messages.
That's not how Nde-red works. Each message is processed entirely in isolation. The way to combine data from multiple messages (your temperature, humidity & pressure readings) is with a Join node.
This page in the documentation might be of value to you, also these short videos Node-RED Essentials.

Note: I've edited your first post so the flow can be copied. In the future when you export you flow, select the 'compact' format

1 Like

zenofmud obviously had allready done that before i tried :+1:

This is the 4th function node from the top

var temp = " + msg.payload + " ;
msg.sql = "INSERT INTO env_Data (temperature) VALUES (" + temp + ");"
return msg;

You are attempting to insert literally this string + msg.payload + into the database, I doubt if that's what's intended?

Is msg.sql the right message property for your MySQL node?

One of the minor delights of Node-red is that you don't need a semicolon at the end of every statement. I don't know if one is needed in your SQL query either (it wouldn't be if you were using node-red-node-mysql).

Hi jbudd. Thx. I was thinking about this with og the other node (not r2), was reading about it but found an article that stated that r2 was significantly faster..... however - i moght thy the other anyway.
No - i do not expect the code to remember. I was looking at the join, but gave up due to lack of coding-knowledge!
In generel - i think my limitation is to name references within the passing from on node to another - and also this with the "encapsulation" in quotes, plings etc!
While waiting for somebody to help i will stude the join node once again!

ehhh - the 4th node is still under construction - i was tryind different ways to parse the value to a variable in the hope that could be "parsed" into my query. But i did not make it work, so you are right, i do not want to put in that "wording" in my db.
The "msg.sql" is comming from the config page in mysql.r2, i have also tryed with msg.topic, seem to me that both works!

Hey Guys.

I now got this to work. I still use the mysql-r2. This missing part was a timeout in the join node, which made i collect forever until releasing anything. Setting a timeout of 2 seconds made i collect my 3 measurement, and parse them on as an arrey. I am happy. Closing the topic.