Send data from a database to a dropdown list

Dashboard
1

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

image
image1273Ă—372 27.1 KB

my flow looks like this:

image
image1192Ă—266 16.7 KB

the code looks like this:

[{"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
image328Ă—541 5.96 KB

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

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

Its the formate the dropdown list accepts..

Best regards
Henrik

2

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

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

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

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

1 Like
3

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 :slight_smile: ). So i need to read the data celle... it should accept strings, numbers
image

code for creating a new room

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

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

Best regards
Henrik

4

got it. it was the debugger making trubble, it was set to msg.option.
it should be msg.options
image