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

[omarjalel]

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

[{"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}]

[Steve-Mcl]

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

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

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

[omarjalel]

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 .

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.

I would really be grateful if you manage to solve that.
Thank a lot.

[{"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}]

[Steve-Mcl]

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

[omarjalel]

Beautiful, big thanks for you Steve !