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": ""
}
]