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

**URL:** <https://discourse.nodered.org/t/how-could-i-display-data-from-a-mysql-database-within-a-chart/64061>\
**Category:** Dashboard\
**Tags:** node-red-dashboard\
**Created:** [17 June 2022 09:41 UTC](https://discourse.nodered.org/t/how-could-i-display-data-from-a-mysql-database-within-a-chart/64061 "2022-06-17T09:41:04Z")\
**Posts on this page:** 20\
**Page:** 1

<div class="post-metadata">

**Author:** ![pauk](https://avatars.discourse-cdn.com/v4/letter/p/9f8e36/32.png) [@pauk](https://discourse.nodered.org/u/pauk)\
**Post date:** [17 June 2022 09:41 UTC](https://discourse.nodered.org/t/how-could-i-display-data-from-a-mysql-database-within-a-chart/64061/1 "2022-06-17T09:41:04Z")

</div>

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.

 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/9/b/9b90b882cccd18075789422b6c71afb442b40372.png)

---

<div class="post-metadata">

**Author:** ![zenofmud](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/zenofmud/32/316_2.png) [@zenofmud](https://discourse.nodered.org/u/zenofmud)\
**Post date:** [17 June 2022 09:51 UTC](https://discourse.nodered.org/t/how-could-i-display-data-from-a-mysql-database-within-a-chart/64061/2 "2022-06-17T09:51:32Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![dynamicdave](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/dynamicdave/32/96_2.png) [@dynamicdave](https://discourse.nodered.org/u/dynamicdave)\
**Post date:** [17 June 2022 09:53 UTC](https://discourse.nodered.org/t/how-could-i-display-data-from-a-mysql-database-within-a-chart/64061/3 "2022-06-17T09:53:08Z")

</div>

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

> <https://github.com/node-red/node-red-dashboard/blob/master/Charts.md>

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.

---

<div class="post-metadata">

**Author:** ![pauk](https://avatars.discourse-cdn.com/v4/letter/p/9f8e36/32.png) [@pauk](https://discourse.nodered.org/u/pauk)\
**Post date:** [17 June 2022 09:58 UTC](https://discourse.nodered.org/t/how-could-i-display-data-from-a-mysql-database-within-a-chart/64061/4 "2022-06-17T09:58:46Z")

</div>

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.

 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/5/d/5dd8d40d3c4991f311fb02db3e01c7834f4332a2.png)

---

<div class="post-metadata">

**Author:** ![pauk](https://avatars.discourse-cdn.com/v4/letter/p/9f8e36/32.png) [@pauk](https://discourse.nodered.org/u/pauk)\
**Post date:** [17 June 2022 10:00 UTC](https://discourse.nodered.org/t/how-could-i-display-data-from-a-mysql-database-within-a-chart/64061/5 "2022-06-17T10:00:09Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![dynamicdave](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/dynamicdave/32/96_2.png) [@dynamicdave](https://discourse.nodered.org/u/dynamicdave)\
**Post date:** [17 June 2022 10:03 UTC](https://discourse.nodered.org/t/how-could-i-display-data-from-a-mysql-database-within-a-chart/64061/6 "2022-06-17T10:03:17Z")

</div>

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?

---

<div class="post-metadata">

**Author:** ![pauk](https://avatars.discourse-cdn.com/v4/letter/p/9f8e36/32.png) [@pauk](https://discourse.nodered.org/u/pauk)\
**Post date:** [17 June 2022 10:05 UTC](https://discourse.nodered.org/t/how-could-i-display-data-from-a-mysql-database-within-a-chart/64061/7 "2022-06-17T10:05:32Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![dynamicdave](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/dynamicdave/32/96_2.png) [@dynamicdave](https://discourse.nodered.org/u/dynamicdave)\
**Post date:** [17 June 2022 10:06 UTC](https://discourse.nodered.org/t/how-could-i-display-data-from-a-mysql-database-within-a-chart/64061/8 "2022-06-17T10:06:25Z")

</div>

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

> [@Create Chart from mySQL Data](https://discourse.nodered.org/t/create-chart-from-mysql-data/3296/27):
>
> Can you post a debug showing the message you send to populate the chart please? Make sure all the objects are expanded.

---

<div class="post-metadata">

**Author:** ![pauk](https://avatars.discourse-cdn.com/v4/letter/p/9f8e36/32.png) [@pauk](https://discourse.nodered.org/u/pauk)\
**Post date:** [17 June 2022 10:48 UTC](https://discourse.nodered.org/t/how-could-i-display-data-from-a-mysql-database-within-a-chart/64061/9 "2022-06-17T10:48:55Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![dynamicdave](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/dynamicdave/32/96_2.png) [@dynamicdave](https://discourse.nodered.org/u/dynamicdave)\
**Post date:** [17 June 2022 10:51 UTC](https://discourse.nodered.org/t/how-could-i-display-data-from-a-mysql-database-within-a-chart/64061/10 "2022-06-17T10:51:51Z")

</div>

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

 ![chart_exgs](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/2/3/23cec967b992c2c5572219910a768f25acc72ae8.png)

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

---

<div class="post-metadata">

**Author:** ![pauk](https://avatars.discourse-cdn.com/v4/letter/p/9f8e36/32.png) [@pauk](https://discourse.nodered.org/u/pauk)\
**Post date:** [17 June 2022 10:52 UTC](https://discourse.nodered.org/t/how-could-i-display-data-from-a-mysql-database-within-a-chart/64061/11 "2022-06-17T10:52:21Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![dynamicdave](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/dynamicdave/32/96_2.png) [@dynamicdave](https://discourse.nodered.org/u/dynamicdave)\
**Post date:** [17 June 2022 10:56 UTC](https://discourse.nodered.org/t/how-could-i-display-data-from-a-mysql-database-within-a-chart/64061/12 "2022-06-17T10:56:05Z")

</div>

> [@pauk](#):
>
> I do want to get only 22 (the case of the ss above), not "array[1]:object:temperatura\_medie".

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

---

<div class="post-metadata">

**Author:** ![pauk](https://avatars.discourse-cdn.com/v4/letter/p/9f8e36/32.png) [@pauk](https://discourse.nodered.org/u/pauk)\
**Post date:** [17 June 2022 10:57 UTC](https://discourse.nodered.org/t/how-could-i-display-data-from-a-mysql-database-within-a-chart/64061/13 "2022-06-17T10:57:06Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![pauk](https://avatars.discourse-cdn.com/v4/letter/p/9f8e36/32.png) [@pauk](https://discourse.nodered.org/u/pauk)\
**Post date:** [17 June 2022 11:01 UTC](https://discourse.nodered.org/t/how-could-i-display-data-from-a-mysql-database-within-a-chart/64061/14 "2022-06-17T11:01:06Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![dynamicdave](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/dynamicdave/32/96_2.png) [@dynamicdave](https://discourse.nodered.org/u/dynamicdave)\
**Post date:** [17 June 2022 11:04 UTC](https://discourse.nodered.org/t/how-could-i-display-data-from-a-mysql-database-within-a-chart/64061/15 "2022-06-17T11:04:06Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![pauk](https://avatars.discourse-cdn.com/v4/letter/p/9f8e36/32.png) [@pauk](https://discourse.nodered.org/u/pauk)\
**Post date:** [17 June 2022 11:06 UTC](https://discourse.nodered.org/t/how-could-i-display-data-from-a-mysql-database-within-a-chart/64061/16 "2022-06-17T11:06:19Z")

</div>

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.

 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/e/c/ecbec77ce9af9d40ae7077b814cea3c3fc096d8a.png)

---

<div class="post-metadata">

**Author:** ![pauk](https://avatars.discourse-cdn.com/v4/letter/p/9f8e36/32.png) [@pauk](https://discourse.nodered.org/u/pauk)\
**Post date:** [17 June 2022 11:06 UTC](https://discourse.nodered.org/t/how-could-i-display-data-from-a-mysql-database-within-a-chart/64061/17 "2022-06-17T11:06:59Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![pauk](https://avatars.discourse-cdn.com/v4/letter/p/9f8e36/32.png) [@pauk](https://discourse.nodered.org/u/pauk)\
**Post date:** [17 June 2022 11:09 UTC](https://discourse.nodered.org/t/how-could-i-display-data-from-a-mysql-database-within-a-chart/64061/18 "2022-06-17T11:09:10Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![zenofmud](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/zenofmud/32/316_2.png) [@zenofmud](https://discourse.nodered.org/u/zenofmud)\
**Post date:** [17 June 2022 11:21 UTC](https://discourse.nodered.org/t/how-could-i-display-data-from-a-mysql-database-within-a-chart/64061/19 "2022-06-17T11:21:27Z")

</div>

I recommend watching this playlist: [Node-RED Essentials](https://www.youtube.com/playlist?list=PLyNBB9VCLmo1hyO-4fIZ08gqFcXBkHy-6). 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.

---

<div class="post-metadata">

**Author:** ![dynamicdave](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/dynamicdave/32/96_2.png) [@dynamicdave](https://discourse.nodered.org/u/dynamicdave)\
**Post date:** [17 June 2022 15:01 UTC](https://discourse.nodered.org/t/how-could-i-display-data-from-a-mysql-database-within-a-chart/64061/20 "2022-06-17T15:01:22Z")

</div>

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

 ![mysql_flow](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/1/c/1c54e1a82a55e8727f9241fc477e1708ee621494.png)  
Note: Your dB credentials need to be inserted in the MySQL node.

```auto
[{"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](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/3/1/311765dd27e1b36d306e1e970944401ff075cbe6.png)

This is what the simple dashboard looks like...  
 ![demo_dashboard](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/f/4/f4bc2fb1ccfc8fbc884efbc557bcc88b01a46032.png)

Database schema is...  
 ![ws_schema](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/e/b/eb7ab176cf68f3af6faa81389c2bc844a4af7e8f.png)

Part of the database contents...  
 ![ws_ap](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/4/f/4fd567c8392479f28174b879ae97faba51891fbe.png)

[Next page](https://discourse.nodered.org/t/how-could-i-display-data-from-a-mysql-database-within-a-chart/64061.md?page=2)