# How to display data from Mysql database in dashboard with text input

**URL:** <https://discourse.nodered.org/t/how-to-display-data-from-mysql-database-in-dashboard-with-text-input/44256>\
**Category:** General\
**Created:** [16 April 2021 00:35 UTC](https://discourse.nodered.org/t/how-to-display-data-from-mysql-database-in-dashboard-with-text-input/44256 "2021-04-16T00:35:40Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![omarjalel](https://avatars.discourse-cdn.com/v4/letter/o/439d5e/32.png) [@omarjalel](https://discourse.nodered.org/u/omarjalel)\
**Post date:** [16 April 2021 00:35 UTC](https://discourse.nodered.org/t/how-to-display-data-from-mysql-database-in-dashboard-with-text-input/44256/1 "2021-04-16T00:35:40Z")

</div>

Hi all,

I have a flow where I want to display a specific data from my database after submitting the form with a number (named NBT) which is the first column in the table in my database.  
I do the select query and the text output ..

Any help on that please?  
Thanks

```auto
[{"id":"fb14b98.5271948","type":"mysql","z":"3515ec16.36c0d4","mydb":"b2ace8ac.1da3d8","name":"","x":580,"y":220,"wires":[["a6cbdad5.d4fa38"]]},{"id":"c3c71442.4b0788","type":"function","z":"3515ec16.36c0d4","name":"sql querry","func":"var topic = msg.topic ;\nmsg.payload = \"SELECT * FROM rapport WHERE NBT = topic ;\n\nreturn msg;","outputs":1,"noerr":6,"initialize":"","finalize":"","x":360,"y":180,"wires":[["fb14b98.5271948"]]},{"id":"a6cbdad5.d4fa38","type":"ui_text","z":"3515ec16.36c0d4","group":"b5fc0921.7b6738","order":2,"width":0,"height":0,"name":"","label":"text","format":"{{msg.payload}}","layout":"row-spread","x":730,"y":120,"wires":[]},{"id":"ec8f8d13.1a52c","type":"ui_form","z":"3515ec16.36c0d4","name":"","label":"","group":"b5fc0921.7b6738","order":2,"width":0,"height":0,"options":[{"label":"NBT","value":"NBT","type":"number","required":true,"rows":null}],"formValue":{"NBT":""},"payload":"","submit":"submit","cancel":"cancel","topic":"topic","topicType":"msg","splitLayout":"","x":110,"y":180,"wires":[["c3c71442.4b0788"]]},{"id":"b2ace8ac.1da3d8","type":"MySQLdatabase","name":"","host":"localhost","port":"3306","db":"rapport","tz":"","charset":"UTF8"},{"id":"b5fc0921.7b6738","type":"ui_group","name":"Envoyer NOTF-text","tab":"bd1e4391.2812","order":5,"disp":true,"width":"5","collapse":false},{"id":"bd1e4391.2812","type":"ui_tab","name":"Appel au Technicien","icon":"dashboard","order":3,"disabled":false,"hidden":false}]

```

 ![Database table](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/c/e/ce99e5f10b6607da7ffbefa5026be98846779693.jpeg)

---

<div class="post-metadata">

**Author:** ![Steve-Mcl](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/steve-mcl/32/4826_2.png) [@Steve-Mcl](https://discourse.nodered.org/u/Steve-Mcl)\
**Post date:** [16 April 2021 07:14 UTC](https://discourse.nodered.org/t/how-to-display-data-from-mysql-database-in-dashboard-with-text-input/44256/2 "2021-04-16T07:14:11Z")

</div>

Judging by the function code, you are not familiar with javascript. Check the function in my flow you will see how I concatenate values to generate a SQL statement.

Next, for mySQL, the QUERY must be in msg.topic (not msg.payload).  
This is clearly stated in the built in help...  
 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/c/4/c4cbe73a132717c81a210e8855e9a053cb2d59c3.png)

Also, judging by the SQL you wrote, you are also new to databases? Essentially, as you wish to display 1 item, you should only request 1 item (use `limit 1`)

Lastly, since the database can return 0, 1, 2 or more rows, the result is always an array. So in order to display an item from it you either need to address the item in element `[0]` or move the first element into `payload` (I did this with a change node after the DB node)

To get you started, here is what I _think_ you want...

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

```auto
[{"id":"fb14b98.5271948","type":"mysql","z":"b872cb4b.5a6448","mydb":"b2ace8ac.1da3d8","name":"","x":1100,"y":1540,"wires":[["611ca0f7.2dca7","3ca52664.8d98aa"]]},{"id":"c3c71442.4b0788","type":"function","z":"b872cb4b.5a6448","name":"sql querry","func":"var topic = msg.topic ;\nmsg.topic = `SELECT * FROM rapport WHERE NBT = '${topic}' LIMIT 1` ;\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":940,"y":1540,"wires":[["fb14b98.5271948","55f1d3d4.a76a8c"]]},{"id":"a6cbdad5.d4fa38","type":"ui_text","z":"b872cb4b.5a6448","group":"b5fc0921.7b6738","order":2,"width":0,"height":0,"name":"","label":"text","format":"{{msg.payload.NBT}}","layout":"row-spread","x":1530,"y":1540,"wires":[]},{"id":"ec8f8d13.1a52c","type":"ui_form","z":"b872cb4b.5a6448","name":"","label":"","group":"b5fc0921.7b6738","order":2,"width":0,"height":0,"options":[{"label":"NBT","value":"NBT","type":"number","required":true,"rows":null}],"formValue":{"NBT":""},"payload":"","submit":"submit","cancel":"cancel","topic":"topic","topicType":"msg","splitLayout":"","x":790,"y":1540,"wires":[["c3c71442.4b0788"]]},{"id":"611ca0f7.2dca7","type":"change","z":"b872cb4b.5a6448","name":"move 1st row into payload","rules":[{"t":"set","p":"payload","pt":"msg","to":"payload[0]","tot":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":1310,"y":1540,"wires":[["a6cbdad5.d4fa38","bf979bbf.e9f528"]]},{"id":"55f1d3d4.a76a8c","type":"debug","z":"b872cb4b.5a6448","name":"query","active":true,"tosidebar":true,"console":false,"tostatus":true,"complete":"topic","targetType":"msg","statusVal":"payload","statusType":"auto","x":970,"y":1620,"wires":[]},{"id":"3ca52664.8d98aa","type":"debug","z":"b872cb4b.5a6448","name":"data","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":1210,"y":1620,"wires":[]},{"id":"bf979bbf.e9f528","type":"debug","z":"b872cb4b.5a6448","name":"to display","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":1460,"y":1620,"wires":[]},{"id":"b2ace8ac.1da3d8","type":"MySQLdatabase","name":"","host":"localhost","port":"3306","db":"rapport","tz":"","charset":"UTF8"},{"id":"b5fc0921.7b6738","type":"ui_group","name":"Envoyer NOTF-text","tab":"bd1e4391.2812","order":5,"disp":true,"width":"5","collapse":false},{"id":"bd1e4391.2812","type":"ui_tab","name":"Appel au Technicien","icon":"dashboard","order":3,"disabled":false,"hidden":false}]

```

please use debug nodes to check what is happening.

---

<div class="post-metadata">

**Author:** ![omarjalel](https://avatars.discourse-cdn.com/v4/letter/o/439d5e/32.png) [@omarjalel](https://discourse.nodered.org/u/omarjalel)\
**Post date:** [17 April 2021 03:32 UTC](https://discourse.nodered.org/t/how-to-display-data-from-mysql-database-in-dashboard-with-text-input/44256/3 "2021-04-17T03:32:48Z")

</div>

Thanks for the great answer and your instructions.  
I have tried with your flow code. but the result of the node: data is empty, and the node: to display shows 'undefined' LIMIT 1 : msg.payload : undefined .

 ![debug](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/b/f/bfa96603405ca8b80329bf8b7b95f9281cf7d8e3.jpeg)  
To simplify the problem, for example, if i input the number 24 in my dashboard the text output display the row corresponding to this number.  
 ![dash](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/0/9/09ce371089fa0bbfde9130e9588b06192fd6c9b2.jpeg)  
 ![data](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/c/e/cea3de0d89bf56e2ec3bd48459fab5776e7e4335.jpeg)  
I would really be grateful if you manage to solve that.  
Thank a lot.

```auto
[{"id":"fb14b98.5271948","type":"mysql","z":"b872cb4b.5a6448","mydb":"b2ace8ac.1da3d8","name":"","x":1100,"y":1540,"wires":[["611ca0f7.2dca7","3ca52664.8d98aa"]]},{"id":"c3c71442.4b0788","type":"function","z":"b872cb4b.5a6448","name":"sql querry","func":"var topic = msg.topic ;\nmsg.topic = `SELECT * FROM rapport WHERE NBT = '${topic}' LIMIT 1` ;\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":940,"y":1540,"wires":[["fb14b98.5271948","55f1d3d4.a76a8c"]]},{"id":"a6cbdad5.d4fa38","type":"ui_text","z":"b872cb4b.5a6448","group":"b5fc0921.7b6738","order":2,"width":0,"height":0,"name":"","label":"text","format":"{{msg.payload.NBT}}","layout":"row-spread","x":1530,"y":1540,"wires":[]},{"id":"ec8f8d13.1a52c","type":"ui_form","z":"b872cb4b.5a6448","name":"","label":"","group":"b5fc0921.7b6738","order":2,"width":0,"height":0,"options":[{"label":"NBT","value":"NBT","type":"number","required":true,"rows":null}],"formValue":{"NBT":""},"payload":"","submit":"submit","cancel":"cancel","topic":"topic","topicType":"msg","splitLayout":"","x":790,"y":1540,"wires":[["c3c71442.4b0788"]]},{"id":"611ca0f7.2dca7","type":"change","z":"b872cb4b.5a6448","name":"move 1st row into payload","rules":[{"t":"set","p":"payload","pt":"msg","to":"payload[0]","tot":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":1310,"y":1540,"wires":[["a6cbdad5.d4fa38","bf979bbf.e9f528"]]},{"id":"55f1d3d4.a76a8c","type":"debug","z":"b872cb4b.5a6448","name":"query","active":true,"tosidebar":true,"console":false,"tostatus":true,"complete":"topic","targetType":"msg","statusVal":"payload","statusType":"auto","x":970,"y":1620,"wires":[]},{"id":"3ca52664.8d98aa","type":"debug","z":"b872cb4b.5a6448","name":"data","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":1210,"y":1620,"wires":[]},{"id":"bf979bbf.e9f528","type":"debug","z":"b872cb4b.5a6448","name":"to display","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":1460,"y":1620,"wires":[]},{"id":"b2ace8ac.1da3d8","type":"MySQLdatabase","name":"","host":"localhost","port":"3306","db":"rapport","tz":"","charset":"UTF8"},{"id":"b5fc0921.7b6738","type":"ui_group","name":"Envoyer NOTF-text","tab":"bd1e4391.2812","order":5,"disp":true,"width":"5","collapse":false},{"id":"bd1e4391.2812","type":"ui_tab","name":"Appel au Technicien","icon":"dashboard","order":3,"disabled":false,"hidden":false}]

```

---

<div class="post-metadata">

**Author:** ![Steve-Mcl](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/steve-mcl/32/4826_2.png) [@Steve-Mcl](https://discourse.nodered.org/u/Steve-Mcl)\
**Post date:** [17 April 2021 07:33 UTC](https://discourse.nodered.org/t/how-to-display-data-from-mysql-database-in-dashboard-with-text-input/44256/4 "2021-04-17T07:33:54Z")

</div>

Put a debug node after the form node. Expand the items in that payload. What do you see?  
Now look in the function where the SQL `where` clause is added. What do you see?

I suspect you need to modify the SQL QUERY function to use `... where NBT = ${msg.payload.NBT} LIMIT 1`

---

<div class="post-metadata">

**Author:** ![omarjalel](https://avatars.discourse-cdn.com/v4/letter/o/439d5e/32.png) [@omarjalel](https://discourse.nodered.org/u/omarjalel)\
**Post date:** [19 April 2021 03:21 UTC](https://discourse.nodered.org/t/how-to-display-data-from-mysql-database-in-dashboard-with-text-input/44256/5 "2021-04-19T03:21:50Z")

</div>

Beautiful, big thanks for you Steve ! 😍

---

<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:** [21 May 2021 23:21 UTC](https://discourse.nodered.org/t/how-to-display-data-from-mysql-database-in-dashboard-with-text-input/44256/6 "2021-05-21T23:21:17Z")

</div>

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