Hello, I am working on a project and I need to show some values from a mysql database in a dropdown node. It doesn't work, can anyone help me? [{"id":"b4621290.54dd1","type":"mysql","z":"e6d7a892.242908","mydb":"b004cfb2.cc7a7","name":"Temp.servicetools","x":670,"y":240,"wires":[["61c62a4a.7859e4","888ed3b4.9d62d"]]},{"id":"7066ca86.10b044","type":"function","z":"e6d7a892.242908","name":"SQL query","func":"msg.topic = \"SELECT description FROM Tools\";\nreturn msg;","outputs":1,"noerr":0,"x":390,"y":160,"wires":[["b4621290.54dd1"]]},{"id":"61c62a4a.7859e4","type":"ui_dropdown","z":"e6d7a892.242908","name":"","label":"Tools","tooltip":"","place":"Select option","group":"e4829a1e.5f60e8","order":2,"width":0,"height":0,"passthru":true,"options":[{"label":"","value":"1","type":"str"},{"label":"","value":"2","type":"str"},{"label":"","value":"3","type":"str"}],"payload":"","topic":"","x":990,"y":260,"wires":[[]]},{"id":"e6adda2d.65b688","type":"ui_button","z":"e6d7a892.242908","name":"","group":"e4829a1e.5f60e8","order":2,"width":0,"height":0,"passthru":false,"label":"button","tooltip":"","color":"","bgcolor":"","icon":"","payload":"1","payloadType":"num","topic":"","x":140,"y":160,"wires":[["7066ca86.10b044"]]},{"id":"888ed3b4.9d62d","type":"ui_text","z":"e6d7a892.242908","group":"e4829a1e.5f60e8","order":3,"width":0,"height":0,"name":"","label":"text","format":"{{msg.payload}}","layout":"row-spread","x":1000,"y":200,"wires":[]},{"id":"b004cfb2.cc7a7","type":"MySQLdatabase","z":"","host":"127.0.0.1","port":"3306","db":"servicetools","tz":""},{"id":"e4829a1e.5f60e8","type":"ui_group","z":"","name":"Test","tab":"e647361f.7a4908","disp":true,"width":"6","collapse":false},{"id":"e647361f.7a4908","type":"ui_tab","z":"","name":"Home","icon":"dashboard","disabled":false,"hidden":false}]

[{"id":"aedb14f9.8d6008","type":"inject","z":"5896831d.0ddf0c","name":"","topic":"","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":140,"y":160,"wires":[["9406f167.f0408"]]},{"id":"9406f167.f0408","type":"function","z":"5896831d.0ddf0c","name":"SQL query","func":"msg.topic = \"SELECT description FROM Tools where adress > '1000'\";\nreturn msg;","outputs":1,"noerr":0,"x":350,"y":160,"wires":[["489c5bf8.972a64"]]},{"id":"489c5bf8.972a64","type":"mysql","z":"5896831d.0ddf0c","mydb":"b004cfb2.cc7a7","name":"Temp.servicetools","x":530,"y":160,"wires":[["b9aa52df.7a27f","1706e5c9.a3acfa"]]},{"id":"8274bd70.5e3cc","type":"ui_dropdown","z":"5896831d.0ddf0c","name":"","label":"Tools","tooltip":"","place":"","group":"885a1e7d.4f5dc","order":2,"width":0,"height":0,"passthru":true,"options":[],"payload":"","topic":"msg.options","x":850,"y":220,"wires":[["8680349b.8b95d8"]]},{"id":"1706e5c9.a3acfa","type":"change","z":"5896831d.0ddf0c","name":"change","rules":[{"t":"set","p":"options","pt":"msg","to":"$.payload.description","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":700,"y":220,"wires":[["8274bd70.5e3cc"]]},{"id":"b9aa52df.7a27f","type":"debug","z":"5896831d.0ddf0c","name":"","active":false,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","x":730,"y":140,"wires":[]},{"id":"b004cfb2.cc7a7","type":"MySQLdatabase","z":"","host":"127.0.0.1","port":"3306","db":"servicetools","tz":""},{"id":"885a1e7d.4f5dc","type":"ui_group","z":"","name":"Pick-up","tab":"e647361f.7a4908","disp":true,"width":"6","collapse":false},{"id":"e647361f.7a4908","type":"ui_tab","z":"","name":"Home","icon":"dashboard","disabled":false,"hidden":false}] [image] [image] in the change node I had to use set instead of move.

Your flow is still bad, I see it is missing the original open bracket. Try inserting the flow all over again and as a test, once you have saved the post, see if you can import it.

Ok, it finally imports! So let's look at how you can solve this problem. First check what data is coming out of the mysql by sticking a debug node on the output of the mysql node, change the debug node to display the 'complete msg object'. Run it and then look at the debug tab in the right sidebar. You will probably have an array, open some of the elements. Now you have the data in an array, you could add an inject node and put the data in the inject node, then attach the inject node to your ui nodes. This way, you won't have to access the database until you have the ui nodes working correctly. Now if you click on the ui_dropdown node and then select the Info tab in the right sidebar, you will get some information on how the node works including this tidbit: The Options may be configured by inputting msg.options containing an array. If just text then the value will be the same as the label, otherwise you can specify both by using an object of "label":"value" pairs : So you have to move your data from msg.payload to msg.options. The easise way is to use the change node. At this point I'll leave you to go try some things out. Feel free to ask for more pointers if you get stuck.

Thanks for your help! How do you put the data from the array in the inject node? Can you tell some more about the change node?

[image] niels_devos: How do you put the data from the array in the inject node? If you look at the debug output in the sidebar you will see a couple small icons on the right. If you hover on them the tool tip will show up. the middle not [DATA_COPY] will copy the data. Now look at the inject node and at all it's options, which one do you think you can use? Try them all and see what the result is. If you put a debug node on the output of the inject node you will be able to see what the data looks like. [image] niels_devos: Can you tell some more about the change node? At this point I'm sure you have looked at the change node's options and have read the info tab of the node so what questions do you have about it?

The array I want to use from the database is variable so if I just copy the text and I want to change the database then I need to change the program to. I would like that if the database changes I don't need to change the program.

You are only grabbing the data and putting it into the inject node so you can test how things will work without having to access the database while testing. With actual data, you can test out your code to build the dropdown. Once it is done, you will just connect up the output of the mysql node to the change node or function node (whch ever you choose to use) to populate the dropdown.

thanks for your help! The dropdown node works! If I open the dropdown node it only shows the label. Do you know how to set the node that it shows the value?

Not sure what you are asking. In the drop down you can have a label and value. The label is displayed and when you pick one, its value is used. If you send only a label it is used as the value

{"id":"7ea82093.8b0e9","type":"inject","z":"5896831d.0ddf0c","name":"","topic":"","payload":"1","payloadType":"num","repeat":"60","crontab":"","once":false,"onceDelay":0.1,"x":190,"y":100,"wires":[["9406f167.f0408"]]},{"id":"9406f167.f0408","type":"function","z":"5896831d.0ddf0c","name":"SQL query","func":"msg.topic = \"SELECT description FROM Tools\";\nreturn msg;","outputs":1,"noerr":0,"x":350,"y":160,"wires":[["489c5bf8.972a64"]]},{"id":"489c5bf8.972a64","type":"mysql","z":"5896831d.0ddf0c","mydb":"b004cfb2.cc7a7","name":"Temp.servicetools","x":530,"y":160,"wires":[["1706e5c9.a3acfa"]]},{"id":"8274bd70.5e3cc","type":"ui_dropdown","z":"5896831d.0ddf0c","name":"","label":"Tools","tooltip":"","place":"","group":"885a1e7d.4f5dc","order":2,"width":0,"height":0,"passthru":true,"options":[],"payload":"","topic":"msg.options","x":830,"y":220,"wires":[[]]},{"id":"1706e5c9.a3acfa","type":"change","z":"5896831d.0ddf0c","name":"change","rules":[{"t":"move","p":"payload","pt":"msg","to":"options","tot":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":700,"y":220,"wires":[["8274bd70.5e3cc"]]},{"id":"b004cfb2.cc7a7","type":"MySQLdatabase","z":"","host":"127.0.0.1","port":"3306","db":"servicetools","tz":""},{"id":"885a1e7d.4f5dc","type":"ui_group","z":"","name":"Pick-up","tab":"e647361f.7a4908","disp":true,"width":"6","collapse":false},{"id":"e647361f.7a4908","type":"ui_tab","z":"","name":"Home","icon":"dashboard","disabled":false,"hidden":false}] this is the flow, in the dropdown node is only say description instead of the value

Node-RED

Node-RED Forum

Mysql database in dropdown node

Dashboard

zenofmud 15 May 2019 11:14 2

please edit your post after reading this thread: How to share code or flow json

Topic		Replies	Views
UI Dropdown - get result from SQL Dashboard	50	19322	12 November 2018
UI Dropdown - Populating Data General	27	20863	30 July 2021
Data into dropdown General	28	1004	10 June 2022
Dashboard_Dropdown doesn't use DataArray Dashboard	11	1144	27 February 2020
Populate data from database into Dropdownlist General	8	4683	27 September 2019

Copyright OpenJS Foundation and Node-RED contributors. All rights reserved. The OpenJS Foundation has registered trademarks and uses trademarks. For a list of trademarks of the OpenJS Foundation, please see our Trademark Policy and Trademark List. Trademarks and logos not indicated on the list of OpenJS Foundation trademarks are trademarks™ or registered® trademarks of their respective holders. Use of them does not imply any affiliation with or endorsement by them.

Mysql database in dropdown node

Related topics