# Send data from a database to a dropdown list

**URL:** <https://discourse.nodered.org/t/send-data-from-a-database-to-a-dropdown-list/57172>\
**Category:** Dashboard\
**Created:** [24 January 2022 20:06 UTC](https://discourse.nodered.org/t/send-data-from-a-database-to-a-dropdown-list/57172 "2022-01-24T20:06:59Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![rothestar](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/rothestar/32/30672_2.png) [@rothestar](https://discourse.nodered.org/u/rothestar)\
**Post date:** [24 January 2022 20:06 UTC](https://discourse.nodered.org/t/send-data-from-a-database-to-a-dropdown-list/57172/1 "2022-01-24T20:06:59Z")

</div>

I Have a sample database where I want to extract the values from the first column Called zone

 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/7/a/7a1229eb96437862821a438bc38dd82fb37db95c.png)

my flow looks like this:

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

the code looks like this:

```auto
[{"id":"5988b4fb.4e292c","type":"ui_dropdown","z":"33b0e6d0.3a306a","name":"","label":"","tooltip":"","place":"Select option","group":"8cab92c0.9fd48","order":1,"width":7,"height":1,"passthru":true,"multiple":false,"options":[{"label":"","value":"zone1","type":"str"},{"label":"","value":"zone2","type":"str"},{"label":"","value":"zone3","type":"str"},{"label":"","value":"zone4","type":"str"},{"label":"","value":"zone5","type":"str"},{"label":"","value":"zone6","type":"str"},{"label":"","value":"zone7","type":"str"},{"label":"","value":"zone8","type":"str"},{"label":"","value":"zone9","type":"str"},{"label":"","value":"zone10","type":"str"}],"payload":"","topic":"","topicType":"str","className":"","x":870,"y":1290,"wires":[[]]},{"id":"4d698fee.f1d92","type":"inject","z":"33b0e6d0.3a306a","name":"","props":[{"p":"options","v":"","vt":"str"},{"p":"topic","vt":"str"}],"repeat":"10","crontab":"","once":true,"onceDelay":"1","topic":"","x":95,"y":1290,"wires":[["6da524f3.1a85dc"]]},{"id":"6da524f3.1a85dc","type":"function","z":"33b0e6d0.3a306a","name":"Get all zones","func":"msg.topic=\"SELECT zone FROM roomsettings\";\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","x":265,"y":1290,"wires":[["80d23340.bc3e1"]]},{"id":"80d23340.bc3e1","type":"mysql","z":"33b0e6d0.3a306a","mydb":"19cd94c9.124aeb","name":"hpsim","x":410,"y":1290,"wires":[["b9fe30a9.58a4d","a0f670dc.ce3f5"]]},{"id":"b9fe30a9.58a4d","type":"function","z":"33b0e6d0.3a306a","name":"Write to dropdown menu","func":"\n\nmsg.options=[\"test\",\"test2\"]\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","x":635,"y":1290,"wires":[["5988b4fb.4e292c"]]},{"id":"a0f670dc.ce3f5","type":"debug","z":"33b0e6d0.3a306a","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":680,"y":1380,"wires":[]},{"id":"8cab92c0.9fd48","type":"ui_group","z":"","name":"Group 2","tab":"f8e03f23.edb1b","order":1,"disp":true,"width":"7","collapse":false},{"id":"19cd94c9.124aeb","type":"MySQLdatabase","z":"","name":"sql database","host":"mariadb","port":"3306","db":"hpsim","tz":"","charset":"UTF8"},{"id":"f8e03f23.edb1b","type":"ui_tab","z":"","name":"Plant","icon":"dashboard","disabled":false,"hidden":false}]

```

the problem is the output from the database query:

 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/5/9/59e57ee35bd83f3bd0540ed440f72cacb56631b4.png)

so my problem is how can I extract and merge the text so i would look like this:

```auto
msg.options=["zone1","zone2"......]
return msg;

```

Its the formate the dropdown list accepts..

Best regards  
Henrik

---

<div class="post-metadata">

**Author:** ![E1cid](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/e1cid/32/77971_2.png) [@E1cid](https://discourse.nodered.org/u/E1cid)\
**Post date:** [24 January 2022 20:21 UTC](https://discourse.nodered.org/t/send-data-from-a-database-to-a-dropdown-list/57172/2 "2022-01-24T20:21:06Z")

</div>

The trick here is to order by the digits at the end  
e.g.

```auto
[{"id":"7af0f4f5f9b1996a","type":"inject","z":"c791cbc0.84f648","name":"","props":[{"p":"payload"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"[{\"zone\":\"zone1\"},{\"zone\":\"zone10\"},{\"zone\":\"zone2\"}]","payloadType":"json","x":410,"y":1800,"wires":[["f989c896d144302a"]]},{"id":"f989c896d144302a","type":"change","z":"c791cbc0.84f648","name":"","rules":[{"t":"set","p":"option","pt":"msg","to":"$$.payload^($number($substringAfter(zone, \"zone\"))).zone","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":590,"y":1820,"wires":[["1b76fb93117376c6"]]},{"id":"1b76fb93117376c6","type":"debug","z":"c791cbc0.84f648","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"option","targetType":"msg","statusVal":"","statusType":"auto","x":780,"y":1820,"wires":[]}]

```

the expresion

```auto
$$.payload^($number($substringAfter(zone, "zone"))).zone

```

Should also be possible to do this in the DB query with `ORDER BY`

---

<div class="post-metadata">

**Author:** ![rothestar](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/rothestar/32/30672_2.png) [@rothestar](https://discourse.nodered.org/u/rothestar)\
**Post date:** [25 January 2022 07:44 UTC](https://discourse.nodered.org/t/send-data-from-a-database-to-a-dropdown-list/57172/3 "2022-01-25T07:44:21Z")

</div>

Thanks E1cid

Sorry perhaps I was not clear enough, I also want to be able to create a new entry in the database (witch is working 🙂 ). So i need to read the data celle... it should accept strings, numbers  
 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/d/b/db732f4cd85b12a602d7b5dd42340688216be3df.png)

code for creating a new room

```auto
[{"id":"d2f1a5df.637438","type":"mysql","z":"33b0e6d0.3a306a","g":"94d2c547.b8e728","mydb":"19cd94c9.124aeb","name":"hpsim","x":425,"y":1155,"wires":[[]]},{"id":"d3c941d.47b54c","type":"function","z":"33b0e6d0.3a306a","g":"94d2c547.b8e728","name":"","func":"room=msg.payload.room;\nif (room !== \"\"){\nmsg.topic = \"insert INTO roomsettings(zone) VALUES ('\"+room+\"');\"\n}\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","x":285,"y":1155,"wires":[["d2f1a5df.637438"]]},{"id":"1fdccdee.45dc92","type":"ui_form","z":"33b0e6d0.3a306a","g":"94d2c547.b8e728","name":"","label":"Create a room","group":"8cab92c0.9fd48","order":1,"width":0,"height":0,"options":[{"label":"Create a room","value":"room","type":"text","required":true,"rows":null}],"formValue":{"room":""},"payload":"","submit":"Opret","cancel":"fortryd","topic":"topic","topicType":"msg","splitLayout":"","className":"","x":125,"y":1155,"wires":[["d3c941d.47b54c"]]},{"id":"19cd94c9.124aeb","type":"MySQLdatabase","z":"","name":"sql database","host":"mariadb","port":"3306","db":"hpsim","tz":"","charset":"UTF8"},{"id":"8cab92c0.9fd48","type":"ui_group","z":"","name":"Group 2","tab":"f8e03f23.edb1b","order":1,"disp":true,"width":"7","collapse":false},{"id":"f8e03f23.edb1b","type":"ui_tab","z":"","name":"Plant","icon":"dashboard","disabled":false,"hidden":false}]

```

Hope this clearifies my problem, I must say you are realy close. But I just don't understand what every part in this code does:

```auto
$$.payload^($number($substringAfter(zone, "zone"))).zone

```

Best regards  
Henrik

---

<div class="post-metadata">

**Author:** ![rothestar](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/rothestar/32/30672_2.png) [@rothestar](https://discourse.nodered.org/u/rothestar)\
**Post date:** [25 January 2022 07:55 UTC](https://discourse.nodered.org/t/send-data-from-a-database-to-a-dropdown-list/57172/4 "2022-01-25T07:55:46Z")

</div>

got it. it was the debugger making trubble, it was set to msg.option.  
it should be msg.options  
 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/4/1/4142a16f9bbc3b79e23ef8c2debf548929fa6bda.png)