# Parsing MySQL data to dashboard

**URL:** <https://discourse.nodered.org/t/parsing-mysql-data-to-dashboard/49636>\
**Category:** General\
**Created:** [11 August 2021 23:39 UTC](https://discourse.nodered.org/t/parsing-mysql-data-to-dashboard/49636 "2021-08-11T23:39:06Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![win7viena](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/win7viena/32/46272_2.png) [@win7viena](https://discourse.nodered.org/u/win7viena)\
**Post date:** [11 August 2021 23:39 UTC](https://discourse.nodered.org/t/parsing-mysql-data-to-dashboard/49636/1 "2021-08-11T23:39:06Z")

</div>

Hello.  
Need help with parsing array data from MySQL and show it in dashboard without using node-red-node-ui-table (because im using _ **Node-RED 0.18.1** _ and _ **dashboard 2.8.0** _ and seems its not compatible with this plugin).  
What I have:

1. I have sensors that transmit readings (temperature and humidity) to the server
2. Data from these sensors are saved in MySQL in this form  
 ![Screenshot 2021-08-12 022023](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/c/7/c78f8456d67cad4a34455b1170f03b2d258a8f2f.jpeg)
3. In Node-RED using the command

```auto
SELECT * FROM datchik2 WHERE date = '{{payload}}';

```

I read data from the database for the desired day  
4. Data from the database comes in this form  
`[{"date":"2021-08-12","time":"00:12:22","temp":"27.80","hum":"51.50","timestamp":"1628716342"},{"date":"2021-08-12","time":"00:41:49","temp":"27.00","hum":"50.60","timestamp":"1628718109"},{"date":"2021-08-12","time":"01:11:34","temp":"25.70","hum":"46.70","timestamp":"1628719894"},{"date":"2021-08-12","time":"01:15:32","temp":"27.30","hum":"47.30","timestamp":"1628720132"},{"date":"2021-08-12","time":"01:16:54","temp":"27.50","hum":"47.60","timestamp":"1628720214"},{"date":"2021-08-12","time":"01:46:26","temp":"27.50","hum":"47.90","timestamp":"1628721986"}]`  
 ![Screenshot 2021-08-12 022525](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/4/9/497f0f0a786819c1cb734120eaa701910276b637.jpeg)  
4. And finally i want do display this data in dashboard in some convenient form where you would see the time of reading the data and the readings themselves during this time

Due to certain reasons, I can't use node-id-node-uitable, so I'm looking for any other way to visually see the data.  
At the moment with my own efforts I was able to parse the data and display only one specific reading of the selected date with this function:

```auto
msg1 = {"topic":"temp", "payload":msg.payload[0].temp};
return [msg1];

```

 ![Screenshot 2021-08-12 023720](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/f/d/fde08791323af9e26fc85dc566676473ee2e958a.jpeg)  
 ![Screenshot 2021-08-12 023806](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/4/c/4c6e07336cbe664b2c2fe8de42f55bf4be85eb25.jpeg)

Need help with both parsing and showing data in dashboard...  
Thanks!

---

<div class="post-metadata">

**Author:** ![UnborN](https://avatars.discourse-cdn.com/v4/letter/u/4491bb/32.png) [@UnborN](https://discourse.nodered.org/u/UnborN)\
**Post date:** [11 August 2021 23:58 UTC](https://discourse.nodered.org/t/parsing-mysql-data-to-dashboard/49636/2 "2021-08-11T23:58:42Z")

</div>

Thats very old version .. do you at least have the ui-template node ? 😉

Here's an example from your db data and with the use of Angular's `ng-repeat` loops through the `msg.payload` array to create the rows.

```auto
[{"id":"16385d687d066673","type":"inject","z":"4895ea10b4ee9ead","name":"db data","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"[{\"date\":\"2021-08-12\",\"time\":\"00:12:22\",\"temp\":\"27.80\",\"hum\":\"51.50\",\"timestamp\":\"1628716342\"},{\"date\":\"2021-08-12\",\"time\":\"00:41:49\",\"temp\":\"27.00\",\"hum\":\"50.60\",\"timestamp\":\"1628718109\"},{\"date\":\"2021-08-12\",\"time\":\"01:11:34\",\"temp\":\"25.70\",\"hum\":\"46.70\",\"timestamp\":\"1628719894\"},{\"date\":\"2021-08-12\",\"time\":\"01:15:32\",\"temp\":\"27.30\",\"hum\":\"47.30\",\"timestamp\":\"1628720132\"},{\"date\":\"2021-08-12\",\"time\":\"01:16:54\",\"temp\":\"27.50\",\"hum\":\"47.60\",\"timestamp\":\"1628720214\"},{\"date\":\"2021-08-12\",\"time\":\"01:46:26\",\"temp\":\"27.50\",\"hum\":\"47.90\",\"timestamp\":\"1628721986\"}]","payloadType":"json","x":350,"y":580,"wires":[["667fc249772d1731","dd281f4b964c7c96"]]},{"id":"dd281f4b964c7c96","type":"ui_template","z":"4895ea10b4ee9ead","group":"fe8bd71ec6128157","name":"","order":1,"width":"29","height":"10","format":"<style>\n .styled-table {\n border-collapse: collapse;\n margin: 25px 0;\n font-size: 0.9em;\n font-family: sans-serif;\n min-width: 400px;\n box-shadow: 0 0 20px rgba(0, 0, 0, 0.15);\n }\n\n .styled-table thead tr {\n background-color: #009879;\n color: #ffffff;\n text-align: left;\n }\n\n .styled-table th,\n .styled-table td {\n padding: 12px 15px;\n }\n\n .styled-table tbody tr {\n border-bottom: 1px solid #dddddd;\n }\n\n .styled-table tbody tr:nth-of-type(even) {\n background-color: #f3f3f3;\n }\n\n .styled-table tbody tr:last-of-type {\n border-bottom: 2px solid #009879;\n }\n</style>\n\n\n<table class=\"styled-table\">\n <thead>\n <tr>\n <th>Date</th>\n <th>Temperature</th>\n <th>Humidity</th>\n </tr>\n </thead>\n <tbody>\n <tr ng-repeat=\"x in msg.payload\">\n <td>{{ x.date }} {{x.time}}</td>\n <td>{{x.temp}}</td>\n <td>{{x.hum}}</td>\n </tr>\n </tbody>\n</table>","storeOutMessages":true,"fwdInMessages":true,"resendOnRefresh":true,"templateScope":"local","x":580,"y":580,"wires":[[]]},{"id":"667fc249772d1731","type":"debug","z":"4895ea10b4ee9ead","name":"1","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":450,"y":520,"wires":[]},{"id":"fe8bd71ec6128157","type":"ui_group","name":"Table","tab":"ff25ab1d26c005a4","order":1,"disp":true,"width":"30","collapse":false},{"id":"ff25ab1d26c005a4","type":"ui_tab","name":"Table","icon":"dashboard","disabled":false,"hidden":false}]

```

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

---

<div class="post-metadata">

**Author:** ![edgarfu721](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/edgarfu721/32/36005_2.png) [@edgarfu721](https://discourse.nodered.org/u/edgarfu721)\
**Post date:** [12 August 2021 01:58 UTC](https://discourse.nodered.org/t/parsing-mysql-data-to-dashboard/49636/3 "2021-08-12T01:58:09Z")

</div>

Maybe you can try function node as below.  
 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/7/3/7364c50ad05cc0af12806dd3ac58d0f873380351.png)

[{"id":"54e8e172.d5022","type":"function","z":"330fd06b.d297a","g":"b2c564a3.48a5d8","name":"","func":"var last=msg.payload.length-1;\nmsg.payload=msg.payload[last];\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":,"x":940,"y":1760,"wires":[["3da50367.f5a32c"]]}]

---

<div class="post-metadata">

**Author:** ![win7viena](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/win7viena/32/46272_2.png) [@win7viena](https://discourse.nodered.org/u/win7viena)\
**Post date:** [12 August 2021 22:45 UTC](https://discourse.nodered.org/t/parsing-mysql-data-to-dashboard/49636/4 "2021-08-12T22:45:48Z")

</div>

@UnborN Thanks so much Andy, works perfectly!  
And yea _ **dashboard 2.8.0** _ have ui-template 😌  
Just edited slightly your flow to much my project and works awesome! While I was figuring out what was what, it became clear which parameter is responsible for what and how the table itself is drawn, thanks for the experience!

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/1X/d073cd938eafa2e558d7c2cd59003b3ef4963033.png) [@system](https://discourse.nodered.org/u/system)\
**Post date:** [26 August 2021 22:46 UTC](https://discourse.nodered.org/t/parsing-mysql-data-to-dashboard/49636/5 "2021-08-26T22:46:28Z")

</div>

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