Mysql database in dropdown node

Dashboard
1

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}]

2

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

1 Like
3

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.

1 Like
4

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.

1 Like
5

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?

6

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.

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?

1 Like
7

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.

8

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.

1 Like
9

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?

10

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

2 Likes
11 
{"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

12
1 Like
13

Thanks for your help!
Ive changed it.

14

  1. your flow still can not be imported. your back tics missed the begining '[' and you included the letters 'th' of the word 'this' following the flow.

  2. That said, how many columns are you retrieving in the sql statement?

  3. what does the Node help for the ui_dropdown say when you use msg.option? (If you remember, I told you in post 4 of this thread)

15

I have changed the flow.
I retrieve one column and I don't understand where I can specify the array.

16

put a debug on the output of your mysql node - what does it show? is it an array or a string or an object?

17 
[{"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":[["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":[["8680349b.8b95d8"]]},{"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","b9aa52df.7a27f"]]},{"id":"b9aa52df.7a27f","type":"debug","z":"5896831d.0ddf0c","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","x":930,"y":100,"wires":[]},{"id":"c7a008b.297acf8","type":"ui_button","z":"5896831d.0ddf0c","name":"","group":"885a1e7d.4f5dc","order":1,"width":0,"height":0,"passthru":false,"label":"button","tooltip":"","color":"","bgcolor":"","icon":"","payload":"1","payloadType":"num","topic":"","x":160,"y":180,"wires":[["9406f167.f0408"]]},{"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}]

the msg doesn't show anything

18

connect the debug node (or an additional one) to the mysql node.

19

I did that but it doesn't work

20

What "doesn't work" ?
Does the button work ?
Is mysql connected ?
Connect an inject node to the mysql node.