How could I display data from a mysql database within a chart?

pauk · 17 June 2022 09:41

I tried to display a chart with the information gathered from a mysql database. The problem is that the mysql node returns a structure which cannot be processed by the chart node. I also tried to add a change node, but I don't know how to configure it. Please help me.

zenofmud · 17 June 2022 09:51

@pauk welcome to the forum.

Without knowing what the sql data looks like, what the change node is doing and how you have configured the chart node, the only thing I can say is you did something wrong.

Now if you were to

add a debug node (set to display the complete msg object) to the output of the sql node
copied that output from the debug sidebar and pasted it to a reply
exported your flow and pasted it to a reply
told use what version of node-red and node.js you are using (you can get this from the startup log)
Then someone might be able to help you.

dynamicdave · 17 June 2022 09:53

It might be worth reading this... as it describes the format needed by the chart node.

github.com

node-red/node-red-dashboard/blob/master/Charts.md

# Node-RED-Dashboard Charts

## Live data

To display live data just wire up an input with a `msg.payload` that contains a number.


### Line charts

To display two or more lines on the same chart then each `msg` must also contain `topic`
property that identifies which data series it belongs to - for example:

    {topic:"temperature", payload:22}
    {topic:"humidity", payload:66}

Each series will be represented by a different colour.
Alternatively you can use the property `series` instead of `topic` if you prefer.

It is possible to create "gaps" in line charts by sending either a null or boolean false as the payload.

This file has been truncated. show original

The mysql node typically returns a payload which will be an array of the result rows. This means the returned data needs to be formatted to be acceptable to the chart node.

pauk · 17 June 2022 09:58

Thank you for reply. The query is doing its job. My problem is how the data is displayed. I would like, first of all, to be able to display number, not a tree of arrays like below.

pauk · 17 June 2022 10:00

Thank you for reply. I read this article. The problems it that I don't have any idea about how to convert the returned text. I don't master js.

dynamicdave · 17 June 2022 10:03

I think you need to do what @zenofmud suggested, as that will enable people to help your further.
I noticed you have changed from using a chart node to a table node - was that intentional?

pauk · 17 June 2022 10:05

Yes. I wanted to check out if the data from the table is fetched correctly.

dynamicdave · 17 June 2022 10:06

Here's another link with an example (which I think is similar to what you are trying to do).

pauk · 17 June 2022 10:48

I can see the problem. I do not know how to print only the value of the last record, without the surrounding metadata (msg arrary[1] etc). Can you help me please? I do not know very much about javascript. I do want to get only 22 (the case of the ss above), not "array[1]:object:temperatura_medie".

dynamicdave · 17 June 2022 10:51

If you scroll down to the end of the first link I posted, there are some Chart examples.

It would help if you shared your flow and a sample of the output from you dB.

pauk · 17 June 2022 10:52

I don't know how to share my flow. I am new to NodeRED.

dynamicdave · 17 June 2022 10:56

You could try...
msg.payload = msg.payload[0].temperatura_medie;
Feed that into a debug node and see if that gives you 22.

pauk · 17 June 2022 10:57

Thank you a lot. I will try it now and I will announce my results.

pauk · 17 June 2022 11:01

Unfortunately I got this message: "Invalid JSONata expression: msg.payload = msg.payload[0].temperatura_medie;" .

dynamicdave · 17 June 2022 11:04

Oh well, it was worth a try.

I'll try to find some animated-text to show how to export your flow.
There's also some "Getting Started" videos that explain the basics of Node-RED using Javascript - I'll try to find a link for you or maybe someone on the forum will post the link.

pauk · 17 June 2022 11:06

Thank you a lot for you help, it was my fault...I didn't use a separate node in order to create a function which feeds the debug node. Now everything is fine.

pauk · 17 June 2022 11:06

Now I am going to try to feed a gauge or a chart. I will tell you the results.

pauk · 17 June 2022 11:09

Yes...it works. Thank you again for spending your time. You really helped me a lot.

zenofmud · 17 June 2022 11:21

I recommend watching this playlist: Node-RED Essentials. The videos are done by the developers of node-red. They're nice & short and to the point. You will understand a whole lot more in about 1 hour. A small investment for a lot of gain.

dynamicdave · 17 June 2022 15:01

Here's an example of querying a dB and showing the results using a table node.

Note: Your dB credentials need to be inserted in the MySQL node.

[{"id":"1fcda8e3a687cb7d","type":"ui_table","z":"1e550c613147af4e","group":"8041c366.c4435","name":"","order":1,"width":"8","height":"8","columns":[{"field":"ws_map_id","title":"Map id","width":"25%","align":"center","formatter":"plaintext","formatterParams":{"target":"_blank"}},{"field":"ws_ref","title":"Ref","width":"25%","align":"center","formatter":"plaintext","formatterParams":{"target":"_blank"}},{"field":"ws_location","title":"Location","width":"50%","align":"left","formatter":"plaintext","formatterParams":{"target":"_blank"}}],"outputs":0,"cts":false,"x":810,"y":320,"wires":[]},{"id":"4496a060c13020f3","type":"template","z":"1e550c613147af4e","name":"SELECT all (*)","field":"topic","fieldType":"msg","format":"handlebars","syntax":"mustache","template":"SELECT * FROM ws_map ORDER BY ws_ref ASC LIMIT 5","output":"str","x":400,"y":280,"wires":[["f9b22b02b9a10dc1"]]},{"id":"9c1b62b1678c35a1","type":"ui_button","z":"1e550c613147af4e","name":"","group":"8041c366.c4435","order":2,"width":"3","height":"1","passthru":false,"label":"Select all","tooltip":"","color":"","bgcolor":"","className":"","icon":"","payload":"","payloadType":"str","topic":"","topicType":"str","x":180,"y":280,"wires":[["4496a060c13020f3"]]},{"id":"a80357d23ba4541b","type":"function","z":"1e550c613147af4e","name":"Clear table","func":"msg.payload=[{\n\"ws_map_id\":\"\",\n\"ws_ref\":\"\",\n\"ws_location\": \"\"\n}];\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":390,"y":320,"wires":[["1fcda8e3a687cb7d"]]},{"id":"a53d02a314aad244","type":"ui_button","z":"1e550c613147af4e","name":"","group":"8041c366.c4435","order":3,"width":"3","height":"1","passthru":false,"label":"clear table","tooltip":"","color":"","bgcolor":"","icon":"","payload":"","payloadType":"str","topic":"","x":190,"y":320,"wires":[["a80357d23ba4541b"]]},{"id":"1fd3cc4766427d61","type":"ui_template","z":"1e550c613147af4e","group":"8041c366.c4435","name":"Styling for Odd/Even rows in ui-table ","order":1,"width":0,"height":0,"format":"<style>\n.tabulator-row-odd {\n\tbackground: #097479 !important;\n}\n.tabulator-row-even {\n\tbackground: #666666 !important;\n}\n</style>","storeOutMessages":true,"fwdInMessages":true,"resendOnRefresh":true,"templateScope":"local","className":"","x":470,"y":180,"wires":[[]]},{"id":"c4c52d1a4bae69d8","type":"inject","z":"1e550c613147af4e","name":"Do a select query","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payloadType":"date","x":210,"y":240,"wires":[["4496a060c13020f3"]]},{"id":"781ef2bdf0e44311","type":"inject","z":"1e550c613147af4e","name":"Clear table","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payloadType":"date","x":180,"y":360,"wires":[["a80357d23ba4541b"]]},{"id":"f9b22b02b9a10dc1","type":"mysql","z":"1e550c613147af4e","mydb":"4b1566d2.1f4b38","name":"","x":570,"y":280,"wires":[["1fcda8e3a687cb7d"]]},{"id":"8041c366.c4435","type":"ui_group","name":"WS_map","tab":"e8284117.b3908","order":1,"disp":true,"width":"8","collapse":false,"className":""},{"id":"4b1566d2.1f4b38","type":"MySQLdatabase","name":"","host":"","port":"3306","db":"","tz":"","charset":"UTF8"},{"id":"e8284117.b3908","type":"ui_tab","name":"mySQL demo","icon":"dashboard","order":23,"disabled":false,"hidden":false}]

Settings in the table node are...
table_settings

This is what the simple dashboard looks like...
demo_dashboard

Database schema is...
ws_schema

Part of the database contents...
ws_ap

Topic		Replies	Views
How can I show the chart from my data? General	13	276	18 August 2023
MYSQL query data is not displaying on line chart in dashboard Dashboard node-red-dashboard , database , chart	7	332	23 October 2022
Inserting data in chart Dashboard database	4	509	18 December 2021
Charts in node-red with data from mysql database Dashboard	19	10182	11 March 2022
Query MySQL and displaying it in a dashboard Chart General	9	1063	17 May 2021

How could I display data from a mysql database within a chart?

Related topics