# Mysql database in dropdown node

**URL:** <https://discourse.nodered.org/t/mysql-database-in-dropdown-node/11144>\
**Category:** Dashboard\
**Created:** [15 May 2019 06:35 UTC](https://discourse.nodered.org/t/mysql-database-in-dropdown-node/11144 "2019-05-15T06:35:26Z")\
**Posts on this page:** 20\
**Page:** 1

<div class="post-metadata">

**Author:** ![niels\_devos](https://avatars.discourse-cdn.com/v4/letter/n/7c8e57/32.png) [@niels\_devos](https://discourse.nodered.org/u/niels_devos)\
**Post date:** [15 May 2019 06:35 UTC](https://discourse.nodered.org/t/mysql-database-in-dropdown-node/11144/1 "2019-05-15T06:35:26Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![zenofmud](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/zenofmud/32/316_2.png) [@zenofmud](https://discourse.nodered.org/u/zenofmud)\
**Post date:** [15 May 2019 11:14 UTC](https://discourse.nodered.org/t/mysql-database-in-dropdown-node/11144/2 "2019-05-15T11:14:11Z")

</div>

please edit your post after reading this thread: [How to share code or flow json](https://discourse.nodered.org/t/how-to-share-code-or-flow-json/506)

---

<div class="post-metadata">

**Author:** ![zenofmud](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/zenofmud/32/316_2.png) [@zenofmud](https://discourse.nodered.org/u/zenofmud)\
**Post date:** [15 May 2019 12:19 UTC](https://discourse.nodered.org/t/mysql-database-in-dropdown-node/11144/3 "2019-05-15T12:19:36Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![zenofmud](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/zenofmud/32/316_2.png) [@zenofmud](https://discourse.nodered.org/u/zenofmud)\
**Post date:** [15 May 2019 13:30 UTC](https://discourse.nodered.org/t/mysql-database-in-dropdown-node/11144/4 "2019-05-15T13:30:11Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![niels\_devos](https://avatars.discourse-cdn.com/v4/letter/n/7c8e57/32.png) [@niels\_devos](https://discourse.nodered.org/u/niels_devos)\
**Post date:** [16 May 2019 11:10 UTC](https://discourse.nodered.org/t/mysql-database-in-dropdown-node/11144/5 "2019-05-16T11:10:39Z")

</div>

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?

---

<div class="post-metadata">

**Author:** ![zenofmud](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/zenofmud/32/316_2.png) [@zenofmud](https://discourse.nodered.org/u/zenofmud)\
**Post date:** [16 May 2019 11:30 UTC](https://discourse.nodered.org/t/mysql-database-in-dropdown-node/11144/6 "2019-05-16T11:30:02Z")

</div>

> [@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](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/2X/e/ea302cbd9660ce42b1bd269d0373032666f161ca.jpeg)  
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.

> [@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?

---

<div class="post-metadata">

**Author:** ![niels\_devos](https://avatars.discourse-cdn.com/v4/letter/n/7c8e57/32.png) [@niels\_devos](https://discourse.nodered.org/u/niels_devos)\
**Post date:** [16 May 2019 12:40 UTC](https://discourse.nodered.org/t/mysql-database-in-dropdown-node/11144/7 "2019-05-16T12:40:08Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![zenofmud](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/zenofmud/32/316_2.png) [@zenofmud](https://discourse.nodered.org/u/zenofmud)\
**Post date:** [16 May 2019 13:08 UTC](https://discourse.nodered.org/t/mysql-database-in-dropdown-node/11144/8 "2019-05-16T13:08:18Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![niels\_devos](https://avatars.discourse-cdn.com/v4/letter/n/7c8e57/32.png) [@niels\_devos](https://discourse.nodered.org/u/niels_devos)\
**Post date:** [20 May 2019 06:32 UTC](https://discourse.nodered.org/t/mysql-database-in-dropdown-node/11144/9 "2019-05-20T06:32:01Z")

</div>

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?

---

<div class="post-metadata">

**Author:** ![zenofmud](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/zenofmud/32/316_2.png) [@zenofmud](https://discourse.nodered.org/u/zenofmud)\
**Post date:** [20 May 2019 08:18 UTC](https://discourse.nodered.org/t/mysql-database-in-dropdown-node/11144/10 "2019-05-20T08:18:13Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![niels\_devos](https://avatars.discourse-cdn.com/v4/letter/n/7c8e57/32.png) [@niels\_devos](https://discourse.nodered.org/u/niels_devos)\
**Post date:** [21 May 2019 06:49 UTC](https://discourse.nodered.org/t/mysql-database-in-dropdown-node/11144/11 "2019-05-21T06:49:28Z")

</div>

```auto
{"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

---

<div class="post-metadata">

**Author:** ![bakman2](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/bakman2/32/6207_2.png) [@bakman2](https://discourse.nodered.org/u/bakman2)\
**Post date:** [21 May 2019 07:42 UTC](https://discourse.nodered.org/t/mysql-database-in-dropdown-node/11144/12 "2019-05-21T07:42:08Z")

</div>

> [@How to share code or flow json](https://discourse.nodered.org/t/how-to-share-code-or-flow-json/506/):
>
> To share code samples or flow json in this forum, you need to take care to format it properly so that it is displayed correctly. The easiest way to do that is to click the 'Preformatted Text' button in the toolbar: [image] Then paste in your code or flow json. You should end up with three back-tick characters - ``` - on their own line before and after the code. On standard US English keyboards, this is on the same key as the ~ character. Using the backticks will ens…

---

<div class="post-metadata">

**Author:** ![niels\_devos](https://avatars.discourse-cdn.com/v4/letter/n/7c8e57/32.png) [@niels\_devos](https://discourse.nodered.org/u/niels_devos)\
**Post date:** [21 May 2019 07:59 UTC](https://discourse.nodered.org/t/mysql-database-in-dropdown-node/11144/13 "2019-05-21T07:59:14Z")

</div>

Thanks for your help!  
Ive changed it.

---

<div class="post-metadata">

**Author:** ![zenofmud](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/zenofmud/32/316_2.png) [@zenofmud](https://discourse.nodered.org/u/zenofmud)\
**Post date:** [21 May 2019 08:45 UTC](https://discourse.nodered.org/t/mysql-database-in-dropdown-node/11144/14 "2019-05-21T08:45:20Z")

</div>

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)

---

<div class="post-metadata">

**Author:** ![niels\_devos](https://avatars.discourse-cdn.com/v4/letter/n/7c8e57/32.png) [@niels\_devos](https://discourse.nodered.org/u/niels_devos)\
**Post date:** [21 May 2019 12:18 UTC](https://discourse.nodered.org/t/mysql-database-in-dropdown-node/11144/15 "2019-05-21T12:18:03Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![zenofmud](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/zenofmud/32/316_2.png) [@zenofmud](https://discourse.nodered.org/u/zenofmud)\
**Post date:** [21 May 2019 12:23 UTC](https://discourse.nodered.org/t/mysql-database-in-dropdown-node/11144/16 "2019-05-21T12:23:48Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![niels\_devos](https://avatars.discourse-cdn.com/v4/letter/n/7c8e57/32.png) [@niels\_devos](https://discourse.nodered.org/u/niels_devos)\
**Post date:** [21 May 2019 12:52 UTC](https://discourse.nodered.org/t/mysql-database-in-dropdown-node/11144/17 "2019-05-21T12:52:40Z")

</div>

```auto
[{"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

---

<div class="post-metadata">

**Author:** ![bakman2](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/bakman2/32/6207_2.png) [@bakman2](https://discourse.nodered.org/u/bakman2)\
**Post date:** [21 May 2019 13:22 UTC](https://discourse.nodered.org/t/mysql-database-in-dropdown-node/11144/18 "2019-05-21T13:22:30Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![niels\_devos](https://avatars.discourse-cdn.com/v4/letter/n/7c8e57/32.png) [@niels\_devos](https://discourse.nodered.org/u/niels_devos)\
**Post date:** [21 May 2019 13:40 UTC](https://discourse.nodered.org/t/mysql-database-in-dropdown-node/11144/19 "2019-05-21T13:40:41Z")

</div>

I did that but it doesn't work

---

<div class="post-metadata">

**Author:** ![bakman2](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/bakman2/32/6207_2.png) [@bakman2](https://discourse.nodered.org/u/bakman2)\
**Post date:** [21 May 2019 13:52 UTC](https://discourse.nodered.org/t/mysql-database-in-dropdown-node/11144/20 "2019-05-21T13:52:40Z")

</div>

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

[Next page](https://discourse.nodered.org/t/mysql-database-in-dropdown-node/11144.md?page=2)