# 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:** 12\
**Page:** 2

<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:57 UTC](https://discourse.nodered.org/t/mysql-database-in-dropdown-node/11144/21 "2019-05-21T13:57:03Z")

</div>

![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/2X/3/3d1e47e024c35abfedbf1610aac2310f2904f1b0.png)  
it says OK, the debug node does nothing.

---

<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 14:12 UTC](https://discourse.nodered.org/t/mysql-database-in-dropdown-node/11144/22 "2019-05-21T14:12:13Z")

</div>

What is the output of `SELECT description FROM Tools where adress > '1000'` in your database ? I suspect it is empty, because you use a greater than `>` operator on a string:

`SELECT description FROM Tools where adress > 1000`

(and `address` might be misspelled?)

---

<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:** [22 May 2019 06:23 UTC](https://discourse.nodered.org/t/mysql-database-in-dropdown-node/11144/23 "2019-05-22T06:23:22Z")

</div>

That query is oké, it only shows the value's greather than 1000.  
mij dropdown node displays this ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/2X/3/3a58e15176267b2a6f67b5fe4fa5c6e39e74d2d9.png)  
while the value's need to be this ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/2X/f/f41ac22716afa66ffbf53d51b25445c22fc7e478.png)

---

<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:** [22 May 2019 06:24 UTC](https://discourse.nodered.org/t/mysql-database-in-dropdown-node/11144/24 "2019-05-22T06:24:41Z")

</div>

In your query, '1000' is a string, it should be a number

---

<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:** [22 May 2019 06:37 UTC](https://discourse.nodered.org/t/mysql-database-in-dropdown-node/11144/25 "2019-05-22T06:37:19Z")

</div>

yes I understand that but it works. it only shows the tools where the adress is more than 1000. Its the right syntax from mysql

---

<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:** [22 May 2019 07:10 UTC](https://discourse.nodered.org/t/mysql-database-in-dropdown-node/11144/26 "2019-05-22T07:10:59Z")

</div>

Your screenshot omitted the address part.

On command line, what is the difference between

select \* from tools where adress \> 1000;  
and  
select \* from tools where adress \> ’1000’;

---

<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:** [22 May 2019 07:22 UTC](https://discourse.nodered.org/t/mysql-database-in-dropdown-node/11144/27 "2019-05-22T07:22:39Z")

</div>

Both of these query's work. There is no difference

---

<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:** [22 May 2019 10:30 UTC](https://discourse.nodered.org/t/mysql-database-in-dropdown-node/11144/28 "2019-05-22T10:30:24Z")

</div>

Do those queries work in your flow?

If what you want to see in the dropdown is  
drill  
grinder  
saw  
but you want to send the value  
1 of drill is selected  
1001 if grinder is selected  
1002 if saw is selected

then you have to grab both `description` AND `adress` in the sql query.

---

<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:** [22 May 2019 11:13 UTC](https://discourse.nodered.org/t/mysql-database-in-dropdown-node/11144/29 "2019-05-22T11:13:35Z")

</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","b9aa52df.7a27f"]]},{"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":"3daf6a55.abe656","type":"mysql","z":"5896831d.0ddf0c","mydb":"b004cfb2.cc7a7","name":"Temp.servicetools","x":1150,"y":300,"wires":[["1a7dd842.601198","387dd632.b665aa"]]},{"id":"8680349b.8b95d8","type":"function","z":"5896831d.0ddf0c","name":"SQL query","func":"msg.topic = \"SELECT adress FROM Tools where description = '\"+msg.payload+\"';\";\nreturn msg;","outputs":1,"noerr":0,"x":970,"y":300,"wires":[["3daf6a55.abe656"]]},{"id":"1a7dd842.601198","type":"function","z":"5896831d.0ddf0c","name":"if state","func":"\nif (msg.payload.adress == '1001');\n{\n msg.payload = 1;\n}\nreturn msg;","outputs":1,"noerr":0,"x":1230,"y":480,"wires":[["f8b0cc6f.7ee4a","2a1f2fc8.fa936"]]},{"id":"22bade0d.ae92f2","type":"rpi-gpio out","z":"5896831d.0ddf0c","name":"locker 1","pin":"7","set":"","level":"0","freq":"","out":"out","x":1600,"y":480,"wires":[]},{"id":"2a1f2fc8.fa936","type":"trigger","z":"5896831d.0ddf0c","op1":"1","op2":"0","op1type":"str","op2type":"str","duration":"2","extend":false,"units":"s","reset":"","bytopic":"topic","name":"","x":1400,"y":480,"wires":[["22bade0d.ae92f2"]]},{"id":"b9aa52df.7a27f","type":"debug","z":"5896831d.0ddf0c","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","x":990,"y":160,"wires":[]},{"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":"f8b0cc6f.7ee4a","type":"debug","z":"5896831d.0ddf0c","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","x":1460,"y":400,"wires":[]},{"id":"387dd632.b665aa","type":"debug","z":"5896831d.0ddf0c","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","x":1480,"y":220,"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}]

```

this is the whole flow. the adress query happens later.

---

<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:** [22 May 2019 11:33 UTC](https://discourse.nodered.org/t/mysql-database-in-dropdown-node/11144/30 "2019-05-22T11:33:40Z")

</div>

thanks for all the help! We found our mistake.

---

<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:** [22 May 2019 11:58 UTC](https://discourse.nodered.org/t/mysql-database-in-dropdown-node/11144/31 "2019-05-22T11:58:54Z")

</div>

What was the problem so others can learn?

---

<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:** [22 May 2019 12:14 UTC](https://discourse.nodered.org/t/mysql-database-in-dropdown-node/11144/32 "2019-05-22T12:14:01Z")

</div>

```auto

[{"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](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/2X/d/de8853f6e3d66c5d62402c2ec8e677a54f9583fb.png)  
 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/2X/d/df2fea8a042b6cd3c4db8530807b138681209134.png)  
in the change node I had to use set instead of move.

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