Doubt with data from Mysql A NodeRed and graph it

Good day colleagues, I have a question, it turns out that I am trying to extract data from the MySql table, and make a push to graph it in a chart, all with a date range, it turns out that it does not give any error somewhere in the code but at the same time See my chart, it is not graphing anything, neither the numbers nor the dates of said numbers appear, any ideas?

This is my flow, which in itself is a sketch so far, since in the future I want to make a type of data history in which when I indicate the date of the database I graph it in different graphs.

[
    {
        "id": "c7d50494.9de1e8",
        "type": "mysql",
        "z": "c30fd861d10ee860",
        "mydb": "fb05e811b8b0da75",
        "name": "MySQL",
        "x": 600,
        "y": 240,
        "wires": [
            [
                "c4301fb0.4a0ef",
                "4282d2f5564a14d7"
            ]
        ]
    },
    {
        "id": "c4301fb0.4a0ef",
        "type": "function",
        "z": "c30fd861d10ee860",
        "name": "Format Data",
        "func": "var data = msg.payload;\nvar formattedData = {\n    labels: [],\n    series: [[]]\n};\n\nfor (var i = 0; i < data.length; i++) {\n    formattedData.labels.push(data[i].fecha); \n    formattedData.series[0].push(data[i].energiatotal); \n}\n\nmsg.payload = formattedData;\nreturn msg;\n",
        "outputs": 1,
        "noerr": 0,
        "initialize": "",
        "finalize": "",
        "libs": [],
        "x": 810,
        "y": 240,
        "wires": [
            [
                "c50a25d2.69bf68",
                "a7c5384798c93fba"
            ]
        ]
    },
    {
        "id": "c50a25d2.69bf68",
        "type": "ui_chart",
        "z": "c30fd861d10ee860",
        "name": "",
        "group": "f1a48b30.40c82",
        "order": 0,
        "width": "12",
        "height": "6",
        "label": "Chart",
        "chartType": "line",
        "legend": "false",
        "xformat": "HH:mm:ss",
        "interpolate": "linear",
        "nodata": "No data available",
        "dot": false,
        "ymin": "0",
        "ymax": "100",
        "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": 1020,
        "y": 240,
        "wires": [
            []
        ]
    },
    {
        "id": "81c32302a70e46c6",
        "type": "inject",
        "z": "c30fd861d10ee860",
        "name": "",
        "props": [
            {
                "p": "payload"
            },
            {
                "p": "topic",
                "vt": "str"
            }
        ],
        "repeat": "",
        "crontab": "",
        "once": false,
        "onceDelay": 0.1,
        "topic": "",
        "payload": "",
        "payloadType": "date",
        "x": 140,
        "y": 240,
        "wires": [
            [
                "178b787c08847eb3"
            ]
        ]
    },
    {
        "id": "178b787c08847eb3",
        "type": "function",
        "z": "c30fd861d10ee860",
        "name": "function 217",
        "func": "msg.topic = \"SELECT fecha, energiatotal FROM totales WHERE fecha BETWEEN '2023-10-26 00:00:00' AND '2023-10-26 23:59:59'; \" ;\n\n\nreturn msg;",
        "outputs": 1,
        "noerr": 0,
        "initialize": "",
        "finalize": "",
        "libs": [],
        "x": 370,
        "y": 240,
        "wires": [
            [
                "c7d50494.9de1e8"
            ]
        ]
    },
    {
        "id": "4282d2f5564a14d7",
        "type": "debug",
        "z": "c30fd861d10ee860",
        "name": "debug 42",
        "active": true,
        "tosidebar": true,
        "console": false,
        "tostatus": true,
        "complete": "payload",
        "targetType": "msg",
        "statusVal": "payload",
        "statusType": "auto",
        "x": 800,
        "y": 320,
        "wires": []
    },
    {
        "id": "a7c5384798c93fba",
        "type": "debug",
        "z": "c30fd861d10ee860",
        "name": "debug 43",
        "active": true,
        "tosidebar": true,
        "console": false,
        "tostatus": true,
        "complete": "payload",
        "targetType": "msg",
        "statusVal": "payload",
        "statusType": "auto",
        "x": 800,
        "y": 160,
        "wires": []
    },
    {
        "id": "fb05e811b8b0da75",
        "type": "MySQLdatabase",
        "name": "",
        "host": "127.0.0.1",
        "port": "3306",
        "db": "plc",
        "tz": "",
        "charset": "UTF8"
    },
    {
        "id": "f1a48b30.40c82",
        "type": "ui_group",
        "name": "Histo",
        "tab": "34f7d76f.774d7e",
        "order": 1,
        "disp": true,
        "width": "12",
        "collapse": false,
        "className": ""
    },
    {
        "id": "34f7d76f.774d7e",
        "type": "ui_tab",
        "name": "PROTOTIPO",
        "icon": "dashboard",
        "order": 1,
        "disabled": false,
        "hidden": false
    }
]

I'm not at a computer to load your flow but it looks like you haven't provided any sample data anyway (which makes it difficult for anyone to help you) but here is a universal flow that might help: Dynamic series identification from message, send data to Chart - #5 by Steve-Mcl

1 Like

Thanks, I'm going to read the example topic you sent me and see how I can implement it.

Although you have debug nodes in your flow you have not shown us what they output, so we are guessing.

However, maybe this post will be helpful:

1 Like

Oh sorry, I didn't realize that I missed adding the debug, these are, debug 43 is the one that I understand does not give the correct format for the chart, I'm still trying to graph my Mysql data in a node chart network, but none of them graph me hehe, it has been something difficult.

If you run your data through the flow I pointed you at you would see the data should be an array of arrays of objects. Yours is an array of objects.

Alternatively, follow the info in the docs

To add to Steve's comment, msg.payload[0].data should be an array containing an array/s of objects, while you just have an array of objects.

ooo, that is, it should be a specific json to be able to graph the group of data I want, or am I wrong?

This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.