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

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.

@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.

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

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.

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.

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.

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?

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

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

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".

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.

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

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

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

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

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.

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.

Now I am going to try to feed a gauge or a chart. I will tell you the results. works. Thank you again for spending your time. You really helped me a lot.

1 Like

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.

1 Like

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...

This is what the simple dashboard looks like...

Database schema is...

Part of the database contents...