How can I show the chart from my data?

mszeta · 19 June 2023 02:11

Hello, Everyone.

I'm happy to know this amazing forum.

I'm stuck in showing node-red dashboard chart from my mysql database;

Database changed
mysql> show tables;
+------------------------+
| Tables_in_water_sensor |
+------------------------+
| sensor_data |
+------------------------+
1 row in set (0.00 sec)

mysql> select * from sensor_data;
+----+-------------+------------+
| id | water_level | date |
+----+-------------+------------+
| 1 | 12.5 | 2023-06-01 |
| 2 | 13.2 | 2023-06-02 |
| 3 | 11.8 | 2023-06-03 |
| 4 | 10.3 | 2023-06-04 |
| 5 | 12.1 | 2023-06-05 |

function node code

let labels = [];
let data = [];

msg.payload.forEach(row => {
    labels.push(row.date);
    // console.log(`row.date ${row.date.toString()}`);
    data.push(row.water_level);
});

msg.payload = [
    {
        series: ['Water Level'],
        data: [{
            x: labels,
            y: data.slice(0, labels.length)
        }],
        labels: labels
    }
];


return msg;

flow json

[
    {
        "id": "bad3255e28febdae",
        "type": "tab",
        "label": "db",
        "disabled": false,
        "info": "",
        "env": []
    },
    {
        "id": "9d6acaa2d2ed70c7",
        "type": "mysql",
        "z": "bad3255e28febdae",
        "mydb": "5909f0fde201f313",
        "name": "",
        "x": 300,
        "y": 180,
        "wires": [
            [
                "e07ba751b59c0f58"
            ]
        ]
    },
    {
        "id": "7264023da74a4a8c",
        "type": "debug",
        "z": "bad3255e28febdae",
        "name": "debug 46",
        "active": true,
        "tosidebar": true,
        "console": false,
        "tostatus": false,
        "complete": "true",
        "targetType": "full",
        "statusVal": "",
        "statusType": "auto",
        "x": 780,
        "y": 200,
        "wires": []
    },
    {
        "id": "fd3e944368c16007",
        "type": "inject",
        "z": "bad3255e28febdae",
        "name": "",
        "props": [
            {
                "p": "payload"
            },
            {
                "p": "topic",
                "vt": "str"
            }
        ],
        "repeat": "",
        "crontab": "",
        "once": false,
        "onceDelay": 0.1,
        "topic": "SELECT * FROM sensor_data",
        "payload": "",
        "payloadType": "date",
        "x": 120,
        "y": 180,
        "wires": [
            [
                "9d6acaa2d2ed70c7"
            ]
        ]
    },
    {
        "id": "e07ba751b59c0f58",
        "type": "function",
        "z": "bad3255e28febdae",
        "name": "function 19",
        "func": "let labels = [];\nlet data = [];\n\nmsg.payload.forEach(row => {\n    labels.push(row.date);\n    // console.log(`row.date ${row.date.toString()}`);\n    data.push(row.water_level);\n});\n\nmsg.payload = [\n    {\n        series: ['Water Level'],\n        // data: [{\n        //     x: labels,\n        //     y: data.slice(0, labels.length)\n        // }],\n        labels: labels\n    }\n];\n\n// console.log(`labels.length : ${labels.length}`);\n// console.log(`data.length : ${data.length}`);\n// for (var i = 0; i < data.length; i++)\n// {\n//     console.log(data[i]);\n//     node.send({ payload: data[i] });\n// }\n\n// console.dir(msg);\nreturn msg;\n",
        "outputs": 1,
        "noerr": 0,
        "initialize": "",
        "finalize": "",
        "libs": [],
        "x": 550,
        "y": 280,
        "wires": [
            [
                "7264023da74a4a8c",
                "a4cf520cba4c62af"
            ]
        ]
    },
    {
        "id": "a4cf520cba4c62af",
        "type": "ui_chart",
        "z": "bad3255e28febdae",
        "name": "",
        "group": "aa8eec0d5014b069",
        "order": 1,
        "width": 0,
        "height": 0,
        "label": "chart",
        "chartType": "line",
        "legend": "false",
        "xformat": "HH:mm:ss",
        "interpolate": "linear",
        "nodata": "",
        "dot": false,
        "ymin": "0",
        "ymax": "50",
        "removeOlder": 1,
        "removeOlderPoints": "",
        "removeOlderUnit": "3600",
        "cutout": 0,
        "useOneColor": false,
        "useUTC": false,
        "colors": [
            "#1f77b4",
            "#aec7e8",
            "#ff7f0e",
            "#2ca02c",
            "#98df8a",
            "#d62728",
            "#ff9896",
            "#9467bd",
            "#c5b0d5"
        ],
        "outputs": 1,
        "useDifferentColor": false,
        "className": "",
        "x": 790,
        "y": 320,
        "wires": [
            []
        ]
    },
    {
        "id": "5909f0fde201f313",
        "type": "MySQLdatabase",
        "name": "",
        "host": "127.0.0.1",
        "port": "3306",
        "db": "water_sensor",
        "tz": "",
        "charset": "UTF8"
    },
    {
        "id": "aa8eec0d5014b069",
        "type": "ui_group",
        "name": "second",
        "tab": "4f903fa3c7ed98d8",
        "order": 2,
        "disp": true,
        "width": "6",
        "collapse": false,
        "className": ""
    },
    {
        "id": "4f903fa3c7ed98d8",
        "type": "ui_tab",
        "name": "Dashboard2",
        "icon": "dashboard",
        "order": 1,
        "disabled": false,
        "hidden": false
    }
]

Thank you.

smanjunath211 · 19 June 2023 04:49

can you connect a debug node to the 'water_sensor' mysql node and copy paste the output here ?

meanwhile, you can also check here

mszeta · 19 June 2023 04:52

Hello, @smanjunath211

Thank you for your support.

2023. 6. 19. 오후 1:51:26node: debug 46
SELECT * FROM sensor_data : msg : Object
object
_msgid: "d35a8257051c6e35"
payload: array[1]
0: object
series: array[1]
0: "Water Level"
labels: array[45]
[0 … 9]
0: "2023-05-31T15:00:00.000Z"
1: "2023-06-01T15:00:00.000Z"
2: "2023-06-02T15:00:00.000Z"
3: "2023-06-03T15:00:00.000Z"
4: "2023-06-04T15:00:00.000Z"
5: "2023-06-05T15:00:00.000Z"
6: "2023-06-06T15:00:00.000Z"
7: "2023-06-07T15:00:00.000Z"
8: "2023-06-08T15:00:00.000Z"
9: "2023-06-09T15:00:00.000Z"
[10 … 19]
[20 … 29]
[30 … 39]
[40 … 44]
topic: "SELECT * FROM senso

Thank you.

mszeta · 19 June 2023 04:54

Hello,

2023. 6. 19. 오후 1:53:14node: debug 46
SELECT * FROM sensor_data : msg : Object
object
_msgid: "793c5d04db170b17"
payload: array[1]
0: object
series: array[1]
0: "Water Level"
data: array[1]
0: object
x: array[45]
[0 … 9]
0: "2023-05-31T15:00:00.000Z"
1: "2023-06-01T15:00:00.000Z"
2: "2023-06-02T15:00:00.000Z"
3: "2023-06-03T15:00:00.000Z"
4: "2023-06-04T15:00:00.000Z"
5: "2023-06-05T15:00:00.000Z"
6: "2023-06-06T15:00:00.000Z"
7: "2023-06-07T15:00:00.000Z"
8: "2023-06-08T15:00:00.000Z"
9: "2023-06-09T15:00:00.000Z"
[10 … 19]
10: "2023-06-10T15:00:00.000Z"
11: "2023-06-11T15:00:00.000Z"
12: "2023-06-12T15:00:00.000Z"
13: "2023-06-13T15:00:00.000Z"
14: "2023-06-14T15:00:00.000Z"
15: "2023-06-15T15:00:00.000Z"
16: "2023-06-16T15:00:00.000Z"
17: "2023-06-17T15:00:00.000Z"
18: "2023-06-18T15:00:00.000Z"
19: "2023-06-19T15:00:00.000Z"
[20 … 29]
[30 … 39]
[40 … 44]
y: array[45]
labels: array[45]
[0 … 9]
0: "2023-05-31T15:00:00.000Z"
1: "2023-06-01T15:00:00.000Z"
2: "2023-06-02T15:00:00.000Z"
3: "2023-06-03T15:00:00.000Z"
4: "2023-06-04T15:00:00.000Z"
5: "2023-06-05T15:00:00.000Z"
6: "2023-06-06T15:00:00.000Z"
7: "2023-06-07T15:00:00.000Z"
8: "2023-06-08T15:00:00.000Z"
9: "2023-06-09T15:00:00.000Z"
[10 … 19]
[20 … 29]
[30 … 39]
[40 … 44]
topic: "SELECT * FROM sensor_data"

Thank you.

smanjunath211 · 19 June 2023 05:05

connect a new debug node to the mysql node, i think you are giving the output of debug node to the function node

mszeta · 19 June 2023 05:11

Hello,

2023. 6. 19. 오후 2:11:06node: debug 57
SELECT * FROM sensor_data : msg.payload : array[45]
array[45]
[0 … 9]
0: object
id: 1
water_level: 12.5
date: "2023-05-31T15:00:00.000Z"
1: object
2: object
3: object
4: object
5: object
6: object
7: object
8: object
9: object
[10 … 19]
10: object
id: 11
water_level: 13.4
date: "2023-06-10T15:00:00.000Z"
11: object
12: object
13: object
14: object

Thank you.

smanjunath211 · 19 June 2023 05:23

I am not an expert in writing answers, and the data you provided is not in an usable format. if you could give me sample data, it would be better. sorry if i am misguiding you.

however, try this and see if it works.

[{"id":"e0d895986048fb46","type":"mysql","z":"370cbf905893daf4","mydb":"c966bba326b387f9","name":"","x":300,"y":180,"wires":[["e93a8de06a8b3c52","3eb465c7a9a238d4"]]},{"id":"ca868da90caccc4a","type":"inject","z":"370cbf905893daf4","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"SELECT * FROM sensor_data","payload":"","payloadType":"date","x":120,"y":180,"wires":[["e0d895986048fb46"]]},{"id":"e93a8de06a8b3c52","type":"function","z":"370cbf905893daf4","name":"function 19","func":"let labels = [];\nlet data = [];\n\nmsg.payload.forEach(row => {\n    labels.push(row.date);\n    // console.log(`row.date ${row.date.toString()}`);\n    data.push(row.water_level);\n});\n\nmsg.payload = [\n    {\n        series: ['Water Level'],\n        // data: [{\n        //     x: labels,\n        //     y: data.slice(0, labels.length)\n        // }],\n        labels: labels\n    }\n];\n\n// console.log(`labels.length : ${labels.length}`);\n// console.log(`data.length : ${data.length}`);\n// for (var i = 0; i < data.length; i++)\n// {\n//     console.log(data[i]);\n//     node.send({ payload: data[i] });\n// }\n\n// console.dir(msg);\nreturn msg;\n","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":550,"y":280,"wires":[["39c998e474e3ed67"]]},{"id":"3eb465c7a9a238d4","type":"function","z":"370cbf905893daf4","name":"Prepare Bar Graph Data","func":"let mname = \"some series\"\nlet dataC = [[]];\nlet dataLabels = [];\n\nfor (let X in msg.payload) {\n    dataLabels[X] = msg.payload[X].date;\n    dataC[0][X] = msg.payload[X].water_level;\n}\n\nvar m = {\n    \"series\": [{mname}],\n    \"data\": dataC,\n    \"labels\": dataLabels\n};\n\nreturn { payload: [m] };\n\n","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":430,"y":510,"wires":[["36c29e42700861f5"]]},{"id":"39c998e474e3ed67","type":"debug","z":"370cbf905893daf4","name":"debug 46","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":780,"y":200,"wires":[]},{"id":"36c29e42700861f5","type":"change","z":"370cbf905893daf4","name":"Set Graph Title","rules":[{"t":"set","p":"mname","pt":"msg","to":"'Chart Title'","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":680,"y":510,"wires":[["c46c6d4a554b2165"]]},{"id":"c46c6d4a554b2165","type":"ui_chart","z":"370cbf905893daf4","name":"","group":"aa8eec0d5014b069","order":1,"width":0,"height":0,"label":"chart","chartType":"line","legend":"false","xformat":"HH:mm:ss","interpolate":"linear","nodata":"","dot":false,"ymin":"0","ymax":"50","removeOlder":1,"removeOlderPoints":"","removeOlderUnit":"3600","cutout":0,"useOneColor":false,"useUTC":false,"colors":["#1f77b4","#aec7e8","#ff7f0e","#2ca02c","#98df8a","#d62728","#ff9896","#9467bd","#c5b0d5"],"outputs":1,"useDifferentColor":false,"className":"","x":880,"y":510,"wires":[[]]},{"id":"c966bba326b387f9","type":"MySQLdatabase","name":"","host":"127.0.0.1","port":"3306","db":"water_sensor","tz":"","charset":"UTF8"},{"id":"aa8eec0d5014b069","type":"ui_group","name":"second","tab":"4f903fa3c7ed98d8","order":2,"disp":true,"width":"20","collapse":false,"className":""},{"id":"4f903fa3c7ed98d8","type":"ui_tab","name":"Dashboard2","icon":"dashboard","order":1,"disabled":false,"hidden":false}]

mszeta · 19 June 2023 05:27

Hello,

Thank you for your support.

It works fine.

Could you tell me what is my mistake more detail?

Thank you.

mszeta · 19 June 2023 05:32

Hello,

Could you help me wide x - axis in chart on dashboard?

It grows vertically.
Don't span widely. it fixed 6 in width.

Thank you.

mszeta · 19 June 2023 05:59

Hello,

I solved this by myself.

Thank you.

smanjunath211 · 19 June 2023 06:04

the syntax expected by chart node was not correct.

mszeta · 19 June 2023 06:06

Thank you so much.

smanjunath211 · 19 June 2023 08:03

glad we could help.

close the thread if you are satisfied with the solution by marking one of the post as solution.

Topic		Replies	Views
Charts in node-red with data from mysql database Dashboard	19	11172	11 March 2022
Inserting data in chart Dashboard database	4	558	18 December 2021
How could I display data from a mysql database within a chart? Dashboard node-red-dashboard	25	3473	19 July 2022
Display line chart with data retrieved from mysql Dashboard node-red-dashboard , database , function-node	7	119	8 February 2025
Query MySQL and displaying it in a dashboard Chart General	9	1196	17 May 2021

How can I show the chart from my data?

Related topics