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?)
That query is okƩ, it only shows the value's greather than 1000.
mij dropdown node displays this 
while the value's need to be this 
In your query, '1000' is a string, it should be a number
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
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ā;
Both of these query's work. There is no difference
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.
[{"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.
thanks for all the help! We found our mistake.
What was the problem so others can learn?
[{"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}]
in the change node I had to use set instead of move.
1 Like
