So how are you attempting to do that? Without showing your code we can not help.
[{"id":"d7bd381c.a59b28","type":"tab","label":"Flow 4","disabled":false,"info":""},{"id":"b454bc8.2a2c94","type":"switch","z":"d7bd381c.a59b28","name":"","property":"payload","propertyType":"msg","rules":[{"t":"cont","v":"10","vt":"str"}],"checkall":"true","repair":false,"outputs":1,"x":470,"y":180,"wires":[["f254bedb.4cc88"]]},{"id":"4b7c62c4.af3e4c","type":"debug","z":"d7bd381c.a59b28","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","x":850,"y":220,"wires":},{"id":"f254bedb.4cc88","type":"change","z":"d7bd381c.a59b28","name":"","rules":[{"t":"set","p":"payload","pt":"msg","to":"Yes","tot":"str"}],"action":"","property":"","from":"","to":"","reg":false,"x":640,"y":180,"wires":[["4b7c62c4.af3e4c"]]},{"id":"3dc54bc.16d49b4","type":"switch","z":"d7bd381c.a59b28","name":"","property":"payload","propertyType":"msg","rules":[{"t":"eq","v":"","vt":"str"}],"checkall":"true","repair":false,"outputs":1,"x":470,"y":260,"wires":[["be161ea2.a42e3"]]},{"id":"be161ea2.a42e3","type":"change","z":"d7bd381c.a59b28","name":"","rules":[{"t":"set","p":"payload","pt":"msg","to":"No","tot":"str"}],"action":"","property":"","from":"","to":"","reg":false,"x":640,"y":260,"wires":[["4b7c62c4.af3e4c"]]},{"id":"7f78375f.271af8","type":"inject","z":"d7bd381c.a59b28","name":"","topic":"","payload":"10","payloadType":"str","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":230,"y":180,"wires":[["b454bc8.2a2c94"]]},{"id":"10057a01.2b1b56","type":"inject","z":"d7bd381c.a59b28","name":"","topic":"","payload":"","payloadType":"str","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":230,"y":260,"wires":[["3dc54bc.16d49b4"]]}]
this code is working
but how if inject the value inside variable then using switch node with variable
could you please show me how to use switch node with variable?
This is a diffrent question than you originally asked. You canstart by putting the value you want to search for in msg.payload/ Then using a CHANGE node you can set the topic by building the sql statement using the json expression. Something like
"SELECT * FROM tab WHERE name = '" & $.payload & "';"
@zenofmud
my question now how to compare msg.payload with a variable value using switch node?
https://discourse.nodered.org/uploads/short-url/ksLUSqtzWQnrt5Oncd2S3prCorg.png
"Contains" in the switch node is for testing if a string contains another string, for example payload might be "abcde" and the Contains might be "bcd". If you are actually looking for equal to a variable value then select == then click on the dropdown to the right and you can select msg. something. So you can test msg.payload == msg.datafield for example.
[Edit] Since your question is now nothing to do with questions relating to a database you might be better to start a new thread.
I'm looking for the same solution, could you maybe share your code?
Several different questions were addressed in this thread. It would be a good idea to make it clear what particular problem you are trying to solve. However do please read right through the thread and see if it helps you.
To control a locker with 30 boxes, I want to store RFID tags in a database with a maximum of 30 tags,
When a tag is readed a function needs to search in the database if the tag is already added, when not it needs to be added, when already added he needs to read the index of the database and tell me on which position the tag is stored.
With the index information (number) I want to set a specific output with belongs to the door which need to be opened.
So the answer to this question won't make much sense as your choice of database may be different and your database structure will be different,
What database are you using, what is your database structure?
Also I'm assuming you are going to be expected to support the gym this will be installed in. SO you really need to understand how it works, so when it goes wrong you can fix it ASAP
A database is probably overkill for such a small set of data... if you have node-red v0.19.x you can use a persisted flow or global array, as explained in the Context Stores doc page.
I use sqlite
I chose to do this because I want to show on a touchscreen how many places are still available, and that a user has to enter his name which also has to be saved in the list.
With some help of this tutorial I was able to create nice list's: http://noderedguide.com/tutorial-sqlite-and-node-red/
I am quite new with sqlite and JavaScript, I would have liked help for the following scripts.
When a tag is scanned search in the database, 2 outputs, present or not. If present, read index and set output on GPIO
When not present question on touchscreen to enter name, accept button, and set output according to index number.
I have created the following flow that adds rfid tags to the database and checks if the tag is already present in the database. can anyone help me get these 2 functions in 1 function with 2 outputs?
Code
[{"id":"7aa95c72.b51ec4","type":"tab","label":"Dexter List","disabled":false,"info":""},{"id":"3772fa64.6a78b6","type":"inject","z":"7aa95c72.b51ec4","name":"Create DatabaseTable","topic":"CREATE TABLE userlist1(id INTEGER PRIMARY KEY AUTOINCREMENT, currentdate DATE, rfid INTEGER, number INTEGER)","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":160,"y":80,"wires":[["eb3b522e.80d69"]]},{"id":"aad6f397.b28bd","type":"debug","z":"7aa95c72.b51ec4","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","x":1030,"y":120,"wires":[]},{"id":"b288b5dc.526508","type":"inject","z":"7aa95c72.b51ec4","name":"ID Delete","topic":"DELETE from userlist1","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":120,"y":120,"wires":[["eb3b522e.80d69"]]},{"id":"19502716.c83459","type":"inject","z":"7aa95c72.b51ec4","name":"View Records","topic":"SELECT * FROM userlist1","payload":"","payloadType":"date","repeat":"1","crontab":"","once":false,"onceDelay":0.1,"x":180,"y":380,"wires":[["e9145745.85f168"]]},{"id":"7a207d58.838c64","type":"ui_template","z":"7aa95c72.b51ec4","group":"b8ad1bd0.067ee8","name":"Angemeldet ","order":0,"width":"6","height":"6","format":"<table style="width:100%">\n\n
You flow is not importable. Please see this on how to share your flow.
I'm sorry, see the new code:
Summary
[{"id":"7aa95c72.b51ec4","type":"tab","label":"Dexter List","disabled":false,"info":""},{"id":"3772fa64.6a78b6","type":"inject","z":"7aa95c72.b51ec4","name":"Create DatabaseTable","topic":"CREATE TABLE userlist1(id INTEGER PRIMARY KEY AUTOINCREMENT, currentdate DATE, rfid INTEGER, number INTEGER)","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":160,"y":40,"wires":[["eb3b522e.80d69"]]},{"id":"aad6f397.b28bd","type":"debug","z":"7aa95c72.b51ec4","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","x":1030,"y":60,"wires":[]},{"id":"b288b5dc.526508","type":"inject","z":"7aa95c72.b51ec4","name":"ID Delete","topic":"DELETE from userlist1","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":120,"y":80,"wires":[["eb3b522e.80d69"]]},{"id":"19502716.c83459","type":"inject","z":"7aa95c72.b51ec4","name":"View userlist","topic":"SELECT * FROM userlist1","payload":"","payloadType":"date","repeat":"1","crontab":"","once":false,"onceDelay":0.1,"x":140,"y":500,"wires":[["e9145745.85f168"]]},{"id":"7a207d58.838c64","type":"ui_template","z":"7aa95c72.b51ec4","group":"b8ad1bd0.067ee8","name":"Userlist","order":0,"width":"6","height":"6","format":"<table style=\"width:100%\">\n\n <tr>\n <th>User</th> \n <th>number</th>\n <th>currentdate</th> \n </tr>\n <tr ng-repeat=\"x in msg.payload | limitTo:200\">\n \n \n <td>{{$index}}</td>\n <td>{{msg.payload[$index].rfid}}</td>\n <td>{{msg.payload[$index].number}}</td> \n <td>{{msg.payload[$index].currentdate}}</td>\n </tr>\n</table>\n","storeOutMessages":true,"fwdInMessages":true,"templateScope":"local","x":1020,"y":500,"wires":[[]]},{"id":"dde86da1.1ef7f","type":"inject","z":"7aa95c72.b51ec4","name":"Feed ID 1","topic":"1234","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":120,"y":200,"wires":[["41d715be.9544dc","d3d8974b.cc9488"]]},{"id":"41d715be.9544dc","type":"function","z":"7aa95c72.b51ec4","name":"Add ID in Sqlite with timestamp etc","func":"var timestamp = Date.now();\nvar topic=msg.topic;\nvar id = 2000;\nvar number = 2;\n\nvar newMsg = {\n \"topic\": \"INSERT INTO userlist1 VALUES ( \" + timestamp + \",\" + timestamp + \", \" + topic + \", \" + number + \")\"\n}\n\nreturn newMsg;\n\n\n","outputs":1,"noerr":0,"x":420,"y":140,"wires":[["eb3b522e.80d69"]]},{"id":"eb0f6a7.f796898","type":"inject","z":"7aa95c72.b51ec4","name":"Go","topic":"","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":"","x":170,"y":900,"wires":[["f0b38f60.07cf6"]]},{"id":"f0b38f60.07cf6","type":"function","z":"7aa95c72.b51ec4","name":"INSERT Bind","func":"msg.topic = \"INSERT INTO test (id,text) VALUES (?,?)\";\nmsg.payload = [2, \"This is a funny world's end\"];\nreturn msg;","outputs":1,"noerr":0,"x":365,"y":901,"wires":[["f6a9658.921eb98"]]},{"id":"f6a9658.921eb98","type":"sqlite","z":"7aa95c72.b51ec4","mydb":"90f7ebcc.59dca8","name":"Test DB","x":683,"y":932,"wires":[["94d4068.52859f8"]]},{"id":"94d4068.52859f8","type":"debug","z":"7aa95c72.b51ec4","name":"","active":true,"console":"false","complete":"false","x":771,"y":1032,"wires":[]},{"id":"d8127c37.ea0c","type":"inject","z":"7aa95c72.b51ec4","name":"Go","topic":"","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"x":168,"y":952,"wires":[["85a3ea6b.bf15e8"]]},{"id":"85a3ea6b.bf15e8","type":"function","z":"7aa95c72.b51ec4","name":"INSERT Old","func":"msg.topic = \"INSERT INTO test (id,text) VALUES (3,'Directly in topic')\";\nreturn msg;","outputs":1,"noerr":0,"x":347,"y":956,"wires":[["f6a9658.921eb98"]]},{"id":"d575c24c.848e4","type":"inject","z":"7aa95c72.b51ec4","name":"Go","topic":"","payload":"funny","payloadType":"str","repeat":"","crontab":"","once":false,"onceDelay":"","x":187,"y":1116,"wires":[["64d8e534.499ddc"]]},{"id":"64d8e534.499ddc","type":"function","z":"7aa95c72.b51ec4","name":"SELECT Old","func":"msg.topic = \"SELECT * FROM test WHERE text LIKE '%\" + msg.payload + \"%'\";\nreturn msg;","outputs":1,"noerr":0,"x":414,"y":1119,"wires":[["f6a9658.921eb98"]]},{"id":"caca8577.9393d8","type":"inject","z":"7aa95c72.b51ec4","name":"INSERT Bind - via JSON","topic":"INSERT INTO test (id,text) VALUES (?, ?)","payload":"[4, \"This is a funny world's end\"]","payloadType":"string","repeat":"","crontab":"","once":false,"x":189,"y":1017,"wires":[["72f81a20.26d804"]]},{"id":"72f81a20.26d804","type":"json","z":"7aa95c72.b51ec4","name":"","x":389,"y":1017,"wires":[["f6a9658.921eb98"]]},{"id":"dfddbe90.773cc","type":"inject","z":"7aa95c72.b51ec4","name":"Go \"funny\"","topic":"","payload":"%funny%","payloadType":"str","repeat":"","crontab":"","once":false,"onceDelay":"","x":183,"y":1170,"wires":[["9b9b3e2c.006f1"]]},{"id":"9b9b3e2c.006f1","type":"function","z":"7aa95c72.b51ec4","name":"SELECT Bind","func":"msg.topic = \"SELECT * FROM test WHERE text LIKE ?\";\nmsg.payload = [msg.payload];\nreturn msg;","outputs":1,"noerr":0,"x":410,"y":1173,"wires":[["f6a9658.921eb98"]]},{"id":"7a70747d.7cbb2c","type":"inject","z":"7aa95c72.b51ec4","name":"Go \"world's\"","topic":"","payload":"%world's%","payloadType":"string","repeat":"","crontab":"","once":false,"x":187,"y":1232,"wires":[["9b9b3e2c.006f1"]]},{"id":"9676a112.9398b","type":"inject","z":"7aa95c72.b51ec4","name":"ID check","topic":"1234","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":120,"y":400,"wires":[["d3d8974b.cc9488"]]},{"id":"d3d8974b.cc9488","type":"function","z":"7aa95c72.b51ec4","name":"check if ID is in Sqlite","func":"msg.topic= \"SELECT * FROM userlist1 WHERE rfid = '1234'\";\nreturn msg;\n","outputs":1,"noerr":0,"x":380,"y":340,"wires":[["eb3b522e.80d69"]]},{"id":"eb3b522e.80d69","type":"sqlite","z":"7aa95c72.b51ec4","mydb":"92fd2202.fa863","sqlquery":"msg.topic","sql":"","name":"Userlist","x":820,"y":60,"wires":[["aad6f397.b28bd"]]},{"id":"c450250c.faa018","type":"inject","z":"7aa95c72.b51ec4","name":"Feed ID 2","topic":"5678","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":120,"y":240,"wires":[["41d715be.9544dc","d3d8974b.cc9488"]]},{"id":"44f8dbe8.1ef764","type":"inject","z":"7aa95c72.b51ec4","name":"Feed ID 3","topic":"7891","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":120,"y":280,"wires":[["41d715be.9544dc","d3d8974b.cc9488"]]},{"id":"e9145745.85f168","type":"sqlite","z":"7aa95c72.b51ec4","mydb":"92fd2202.fa863","sqlquery":"msg.topic","sql":"","name":"Userlist","x":800,"y":500,"wires":[["7a207d58.838c64"]]},{"id":"b8ad1bd0.067ee8","type":"ui_group","z":"","name":"UI Table","tab":"eb2229aa.ec0d98","disp":true,"width":"6","collapse":false},{"id":"90f7ebcc.59dca8","type":"sqlitedb","db":"test.sqlite"},{"id":"92fd2202.fa863","type":"sqlitedb","z":"","db":"Userlist","mode":"RWC"},{"id":"eb2229aa.ec0d98","type":"ui_tab","z":"","name":"Home","icon":"dashboard"}]
OK, I have the flow, so what is not working as you expect?
The function node does not make database requests, the sqllite node does. You need to draw up a step be step flow of your logic like:
- enter rfid tag number
- query db to see if it exists
- in it does not exist, insert it into database
- if it DOES exist signal an error.
in this case you would have an
- input (an insert node for example) going to a
- fundtion node to create the select going to a
- sqllite node to run the query going to a
- switch node to check the result
-
- if record not found go to output (1)
-
- if record found go to output (2)
(1) function node to build the insert node going to a
-
-
- sqlnode to run the query
-
(2) some node to signal an error.
Thanks, I succeeded, see code.
Now I want to make the sqlite database fixed with 30 arrays. each array is a box.
how can I set up the database so that I have 30 arrays that I can describe with a rfid tag? and i want to be able to read the index so I know which box to open?
if all arrays are described, a message should be displayed indicating that the database is full
Summary
[{"id":"5a3b2afe.7b55b4","type":"tab","label":"User database","disabled":false,"info":""},{"id":"3094091b.ecc7c6","type":"tab","label":"Add user","disabled":false,"info":""},{"id":"51a0789d.cbaed8","type":"tab","label":"Outputs I2c","disabled":false,"info":""},{"id":"7aee7dd2.81f884","type":"tab","label":"UI Dashboard","disabled":false,"info":""},{"id":"a521fb86.7294d8","type":"ui_group","z":"","name":"Default","tab":"","disp":true,"width":"6","collapse":false},{"id":"eb2229aa.ec0d98","type":"ui_tab","z":"","name":"Home","icon":"dashboard"},{"id":"b8ad1bd0.067ee8","type":"ui_group","z":"","name":"UI Table","tab":"eb2229aa.ec0d98","disp":true,"width":"6","collapse":false},{"id":"cf617df4.3a4b","type":"ui_group","z":"","name":"MiFlora","tab":"bfa5f052.1a265","order":2,"disp":true,"width":"6"},{"id":"63efb2b5.b699dc","type":"ui_group","z":"","name":"Report","tab":"f62ece5.a371b3","order":2,"disp":true,"width":"18"},{"id":"38688b3b.e1e284","type":"ui_group","z":"","name":"Selection","tab":"f62ece5.a371b3","order":1,"disp":true,"width":"5"},{"id":"bfa5f052.1a265","type":"ui_tab","z":"","name":"Home","icon":"home","order":"1"},{"id":"f62ece5.a371b3","type":"ui_tab","z":"","name":"Reports","icon":"dashboard","order":9},{"id":"49e173af.df1b4c","type":"ui_base","theme":{"name":"theme-light","lightTheme":{"default":"#0094CE","baseColor":"#0094CE","baseFont":"-apple-system,BlinkMacSystemFont,Segoe UI,Roboto,Oxygen-Sans,Ubuntu,Cantarell,Helvetica Neue,sans-serif","edited":true,"reset":false},"darkTheme":{"default":"#097479","baseColor":"#097479","baseFont":"-apple-system,BlinkMacSystemFont,Segoe UI,Roboto,Oxygen-Sans,Ubuntu,Cantarell,Helvetica Neue,sans-serif","edited":false},"customTheme":{"name":"Untitled Theme 1","default":"#4B7930","baseColor":"#4B7930","baseFont":"-apple-system,BlinkMacSystemFont,Segoe UI,Roboto,Oxygen-Sans,Ubuntu,Cantarell,Helvetica Neue,sans-serif"},"themeState":{"base-color":{"default":"#0094CE","value":"#0094CE","edited":false},"page-titlebar-backgroundColor":{"value":"#0094CE","edited":false},"page-backgroundColor":{"value":"#fafafa","edited":false},"page-sidebar-backgroundColor":{"value":"#ffffff","edited":false},"group-textColor":{"value":"#1bbfff","edited":false},"group-borderColor":{"value":"#ffffff","edited":false},"group-backgroundColor":{"value":"#ffffff","edited":false},"widget-textColor":{"value":"#111111","edited":false},"widget-backgroundColor":{"value":"#0094ce","edited":false},"widget-borderColor":{"value":"#ffffff","edited":false},"base-font":{"value":"-apple-system,BlinkMacSystemFont,Segoe UI,Roboto,Oxygen-Sans,Ubuntu,Cantarell,Helvetica Neue,sans-serif"}},"angularTheme":{"primary":"indigo","accents":"blue","warn":"red","background":"grey"}},"site":{"name":"Node-RED Dashboard","hideToolbar":"false","allowSwipe":"false","lockMenu":"false","allowTempTheme":"true","dateFormat":"DD/MM/YYYY","sizes":{"sx":48,"sy":48,"gx":6,"gy":6,"cx":6,"cy":6,"px":0,"py":0}}},{"id":"6e4e3e34.e94b3","type":"ui_group","z":"","name":"Insert Data Test","tab":"26177bff.f231e4","order":1,"disp":true,"width":"6","collapse":false},{"id":"26177bff.f231e4","type":"ui_tab","z":"","name":"Input Test","icon":"dashboard","order":3},{"id":"d8ea9400.dcfca8","type":"ui_group","z":"","name":"Insert Data Test","tab":"c0e62bc.3d378d8","order":1,"disp":true,"width":"6"},{"id":"c0e62bc.3d378d8","type":"ui_tab","z":"","name":"Input Test","icon":"dashboard","order":3},{"id":"62b3f15b.f32fc","type":"ui_group","z":"","name":"Assembly Lines","tab":"e9720840.70e218","disp":true,"width":"6","collapse":false},{"id":"e9720840.70e218","type":"ui_tab","z":"","name":"Chasis Lines","icon":"dashboard"},{"id":"b80b5ca2.3608d","type":"MySQLdatabase","z":"","host":"10.21.11.15","port":"3306","db":"i4inari","tz":""},{"id":"5fd32e09.24218","type":"mui_group","z":0,"name":"Inari Logo","tab":"6975def1.805ed","order":1,"disp":false,"width":"4","collapse":false},{"id":"3b9e22d4.b516fe","type":"MySQLdatabase","z":"","host":"10.21.11.15","port":"3306","db":"i4inari","tz":""},{"id":"6975def1.805ed","type":"mui_tab","z":0,"name":"I4Inari","icon":"home","order":1},{"id":"9477fb84.84f678","type":"sqlitedb","z":"","db":"Userlist_1","mode":"RWC"},{"id":"f3dd1542.656068","type":"mui_group","z":"","name":"mGroup 1","tab":"11cd0e8d.92b5a1","order":1,"disp":false,"width":"6","collapse":false},{"id":"c6e80634.9bc7a8","type":"mui_group","z":"","name":"mGroup 2","tab":"11cd0e8d.92b5a1","order":2,"disp":false,"width":"6","collapse":false},{"id":"3f6fd726.972f98","type":"mui_group","z":"","name":"mGroup 3","tab":"11cd0e8d.92b5a1","order":3,"disp":false,"width":"6","collapse":false},{"id":"d1f94059.ba577","type":"mui_group","z":"","name":"mGroup 4","tab":"11cd0e8d.92b5a1","order":4,"disp":false,"width":"6","collapse":false},{"id":"5a53699a.927808","type":"mui_group","z":"","name":"mGroup 5","tab":"11cd0e8d.92b5a1","order":5,"disp":false,"width":"6","collapse":false},{"id":"c892a07c.450c1","type":"MySQLdatabase","z":"","host":"10.21.11.15","port":"3306","db":"i4inari","tz":""},{"id":"11cd0e8d.92b5a1","type":"mui_tab","z":"","name":"I4Inari : Machine Management ","icon":"dashboard","order":3},{"id":"6ccd8376.7a901c","type":"inject","z":"5a3b2afe.7b55b4","name":"Create DatabaseTable","topic":"CREATE TABLE userlist_1(currentdate DATE, rfid INTEGER, number INTEGER)","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":180,"y":40,"wires":[["4f6afe25.91ba3"]]},{"id":"b491d49e.4659c8","type":"debug","z":"5a3b2afe.7b55b4","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","x":1230,"y":40,"wires":[]},{"id":"9b67d8a.1a1d328","type":"inject","z":"5a3b2afe.7b55b4","name":"ID Delete","topic":"DELETE from userlist_1","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":140,"y":80,"wires":[["4f6afe25.91ba3","efb4606.45a4ea"]]},{"id":"22d30b3e.7210f4","type":"inject","z":"5a3b2afe.7b55b4","name":"Feed ID 1","topic":"","payload":"1234","payloadType":"str","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":140,"y":140,"wires":[["58abd615.1bf068"]]},{"id":"1c4d56c8.ee40c9","type":"inject","z":"5a3b2afe.7b55b4","name":"ID check","topic":"","payload":"1234","payloadType":"num","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":140,"y":260,"wires":[[]]},{"id":"4f6afe25.91ba3","type":"sqlite","z":"5a3b2afe.7b55b4","mydb":"9477fb84.84f678","sqlquery":"msg.topic","sql":"","name":"Userlist","x":560,"y":40,"wires":[["b491d49e.4659c8"]]},{"id":"782324fe.e8e5dc","type":"inject","z":"5a3b2afe.7b55b4","name":"Feed ID 2","topic":"","payload":"5678","payloadType":"num","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":140,"y":180,"wires":[["58abd615.1bf068"]]},{"id":"74498364.d1b4ec","type":"inject","z":"5a3b2afe.7b55b4","name":"Feed ID 3","topic":"","payload":"7891","payloadType":"num","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":140,"y":220,"wires":[["58abd615.1bf068"]]},{"id":"43a22c28.18df74","type":"sqlite","z":"5a3b2afe.7b55b4","mydb":"9477fb84.84f678","sqlquery":"msg.topic","sql":"","name":"Userlist","x":560,"y":260,"wires":[["afd8fdb5.4f1fb","71855de8.ce7b14","ecc56849.dbff48"]]},{"id":"d63b5937.508068","type":"debug","z":"5a3b2afe.7b55b4","name":"No","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","x":1210,"y":340,"wires":[]},{"id":"4233f395.50251c","type":"debug","z":"5a3b2afe.7b55b4","name":"Yes","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","x":1210,"y":220,"wires":[]},{"id":"9f228c02.1a796","type":"change","z":"5a3b2afe.7b55b4","name":"","rules":[{"t":"set","p":"payload","pt":"msg","to":"User in system","tot":"str"}],"action":"","property":"","from":"","to":"","reg":false,"x":1040,"y":260,"wires":[["4233f395.50251c"]]},{"id":"9dd7a50e.5add18","type":"change","z":"5a3b2afe.7b55b4","name":"","rules":[{"t":"set","p":"payload","pt":"msg","to":"User not in system","tot":"str"}],"action":"","property":"","from":"","to":"","reg":false,"x":1040,"y":300,"wires":[["d63b5937.508068","9dc9d426.0a0d98","2c0c36f7.b0968a"]]},{"id":"f3607c11.6f6fb","type":"switch","z":"5a3b2afe.7b55b4","name":"","property":"payload.rfid","propertyType":"msg","rules":[{"t":"eq","v":"rfid_key","vt":"global"}],"checkall":"true","repair":false,"outputs":1,"x":890,"y":260,"wires":[["9f228c02.1a796"]]},{"id":"afd8fdb5.4f1fb","type":"split","z":"5a3b2afe.7b55b4","name":"Split array","splt":"\\n","spltType":"str","arraySplt":1,"arraySpltType":"len","stream":false,"addname":"","x":740,"y":260,"wires":[["f3607c11.6f6fb","cb82e323.a5c3c"]]},{"id":"71855de8.ce7b14","type":"switch","z":"5a3b2afe.7b55b4","name":"","property":"payload","propertyType":"msg","rules":[{"t":"empty"}],"checkall":"true","repair":false,"outputs":1,"x":730,"y":300,"wires":[["9dd7a50e.5add18"]]},{"id":"2c0c36f7.b0968a","type":"counter","z":"5a3b2afe.7b55b4","name":"","init":"0","step":"1","lower":"0","upper":"30","mode":"increment","outputs":1,"x":560,"y":100,"wires":[["a78254d0.2df128"]]},{"id":"a78254d0.2df128","type":"function","z":"5a3b2afe.7b55b4","name":"Counter","func":"var counter = msg.count;\nglobal.set(\"counter_locker\",counter)","outputs":1,"noerr":0,"x":720,"y":100,"wires":[[]]},{"id":"efb4606.45a4ea","type":"function","z":"5a3b2afe.7b55b4","name":"msg object","func":"msg.reset = true;\nreturn msg;","outputs":1,"noerr":0,"x":350,"y":100,"wires":[["2c0c36f7.b0968a"]]},{"id":"63477ae9.5b7204","type":"function","z":"3094091b.ecc7c6","name":"Add ID in Sqlite with timestamp etc","func":"var timestamp = Date.now();\nvar rfid = global.get(\"rfid_key\")\nvar counter_locker = global.get(\"counter_locker\")\nvar newMsg = {\n \"topic\": \"INSERT INTO userlist_1 VALUES ( \" + timestamp + \",\" + rfid + \", \" + counter_locker + \")\"\n}\n\nreturn newMsg;\n\n\n","outputs":1,"noerr":0,"x":240,"y":60,"wires":[["eed67c4.677508","fdccb729.06e2c8"]]},{"id":"eed67c4.677508","type":"debug","z":"3094091b.ecc7c6","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","x":510,"y":20,"wires":[]},{"id":"fdccb729.06e2c8","type":"sqlite","z":"3094091b.ecc7c6","mydb":"9477fb84.84f678","sqlquery":"msg.topic","sql":"","name":"Userlist","x":520,"y":60,"wires":[[]]},{"id":"6293cf63.7e0ab","type":"inject","z":"7aee7dd2.81f884","name":"View userlist","topic":"SELECT * FROM userlist_1","payload":"","payloadType":"date","repeat":"1","crontab":"","once":false,"onceDelay":0.1,"x":140,"y":60,"wires":[["94ee478.58521b8"]]},{"id":"9f839391.ab6b","type":"ui_template","z":"7aee7dd2.81f884","group":"b8ad1bd0.067ee8","name":"Userlist","order":0,"width":"6","height":"4","format":"<table style=\"width:100%\">\n\n <tr>\n <th>number</th> \n <th>rfid_key</th>\n <th>currentdate</th> \n </tr>\n <tr ng-repeat=\"x in msg.payload | limitTo:30\">\n \n <td>{{msg.count}}</td>\n <td>{{msg.payload[$index].number}}</td>\n <td>{{msg.payload[$index].rfid}}</td> \n <td>{{msg.payload[$index].currentdate}}</td>\n \n </tr>\n</table>\n\n\n","storeOutMessages":true,"fwdInMessages":true,"templateScope":"local","x":440,"y":60,"wires":[[]]},{"id":"94ee478.58521b8","type":"sqlite","z":"7aee7dd2.81f884","mydb":"9477fb84.84f678","sqlquery":"msg.topic","sql":"","name":"Userlist","x":300,"y":60,"wires":[["9f839391.ab6b"]]},{"id":"c89d42b6.515a4","type":"link in","z":"3094091b.ecc7c6","name":"","links":["9dc9d426.0a0d98","641c0cb3.e3f9b4"],"x":55,"y":60,"wires":[["63477ae9.5b7204"]]},{"id":"9dc9d426.0a0d98","type":"link out","z":"5a3b2afe.7b55b4","name":"","links":["c89d42b6.515a4"],"x":1195,"y":300,"wires":[]},{"id":"cb82e323.a5c3c","type":"link out","z":"5a3b2afe.7b55b4","name":"Box-number I2C","links":["755b55a3.e3f58c","3b690b6c.6aa614","9b44c88f.11d398","96a43077.b23a7","701faf70.08383"],"x":775,"y":220,"wires":[]},{"id":"2126ea.d5b7d916","type":"switch","z":"51a0789d.cbaed8","name":"","property":"payload","propertyType":"msg","rules":[{"t":"eq","v":"1","vt":"str"},{"t":"eq","v":"2","vt":"str"},{"t":"eq","v":"3","vt":"str"},{"t":"eq","v":"4","vt":"str"},{"t":"eq","v":"5","vt":"str"},{"t":"eq","v":"6","vt":"str"},{"t":"eq","v":"7","vt":"str"},{"t":"eq","v":"8","vt":"str"},{"t":"eq","v":"9","vt":"str"},{"t":"eq","v":"10","vt":"str"}],"checkall":"true","repair":false,"outputs":10,"x":290,"y":160,"wires":[[],[],[],[],[],[],[],[],[],[]]},{"id":"1fcdbda7.9f5732","type":"switch","z":"51a0789d.cbaed8","name":"","property":"payload","propertyType":"msg","rules":[{"t":"eq","v":"1","vt":"str"},{"t":"eq","v":"2","vt":"str"},{"t":"eq","v":"3","vt":"str"},{"t":"eq","v":"4","vt":"str"},{"t":"eq","v":"5","vt":"str"},{"t":"eq","v":"6","vt":"str"},{"t":"eq","v":"7","vt":"str"},{"t":"eq","v":"8","vt":"str"},{"t":"eq","v":"9","vt":"str"},{"t":"eq","v":"10","vt":"str"}],"checkall":"true","repair":false,"outputs":10,"x":290,"y":340,"wires":[[],[],[],[],[],[],[],[],[],[]]},{"id":"3b690b6c.6aa614","type":"link in","z":"51a0789d.cbaed8","name":"","links":["cb82e323.a5c3c"],"x":195,"y":340,"wires":[["1fcdbda7.9f5732"]]},{"id":"9b44c88f.11d398","type":"link in","z":"51a0789d.cbaed8","name":"","links":["cb82e323.a5c3c"],"x":195,"y":520,"wires":[["a059ed3e.84ee6"]]},{"id":"a059ed3e.84ee6","type":"switch","z":"51a0789d.cbaed8","name":"","property":"payload","propertyType":"msg","rules":[{"t":"eq","v":"1","vt":"str"},{"t":"eq","v":"2","vt":"str"},{"t":"eq","v":"3","vt":"str"},{"t":"eq","v":"4","vt":"str"},{"t":"eq","v":"5","vt":"str"},{"t":"eq","v":"6","vt":"str"},{"t":"eq","v":"7","vt":"str"},{"t":"eq","v":"8","vt":"str"},{"t":"eq","v":"9","vt":"str"},{"t":"eq","v":"10","vt":"str"}],"checkall":"true","repair":false,"outputs":10,"x":290,"y":520,"wires":[[],[],[],[],[],[],[],[],[],[]]},{"id":"f6fd4e93.d6871","type":"switch","z":"51a0789d.cbaed8","name":"","property":"payload","propertyType":"msg","rules":[{"t":"eq","v":"1","vt":"str"},{"t":"eq","v":"2","vt":"str"},{"t":"eq","v":"3","vt":"str"},{"t":"eq","v":"4","vt":"str"},{"t":"eq","v":"5","vt":"str"},{"t":"eq","v":"6","vt":"str"},{"t":"eq","v":"7","vt":"str"},{"t":"eq","v":"8","vt":"str"},{"t":"eq","v":"9","vt":"str"},{"t":"eq","v":"10","vt":"str"}],"checkall":"true","repair":false,"outputs":10,"x":290,"y":700,"wires":[[],[],[],[],[],[],[],[],[],[]]},{"id":"96a43077.b23a7","type":"link in","z":"51a0789d.cbaed8","name":"","links":["cb82e323.a5c3c"],"x":195,"y":700,"wires":[["f6fd4e93.d6871"]]},{"id":"ecc56849.dbff48","type":"debug","z":"5a3b2afe.7b55b4","name":"Yes","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","x":730,"y":340,"wires":[]},{"id":"ad3b1e36.098ae","type":"function","z":"51a0789d.cbaed8","name":"","func":"var number = msg.payload.number;\n\n \nreturn[{payload:number}]","outputs":1,"noerr":0,"x":510,"y":140,"wires":[["5c07af7d.9a9d2"]]},{"id":"5c07af7d.9a9d2","type":"debug","z":"51a0789d.cbaed8","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":680,"y":140,"wires":[]},{"id":"701faf70.08383","type":"link in","z":"51a0789d.cbaed8","name":"","links":["cb82e323.a5c3c"],"x":295,"y":40,"wires":[["ad3b1e36.098ae"]]},{"id":"58abd615.1bf068","type":"function","z":"5a3b2afe.7b55b4","name":"check ID","func":"global.set(\"rfid_key\",msg.payload);\nvar rfid = global.get(\"rfid_key\");\n\nmsg.topic= \"SELECT * FROM userlist_1 WHERE rfid = '\" + global.get(\"rfid_key\") + \"'\";\nreturn msg;\n\n","outputs":1,"noerr":0,"x":340,"y":260,"wires":[["43a22c28.18df74"]]}]
what do you mean when you say 'each array is a box'? by 'box' does that refer to an rfid tag and the information assciated with it?
Have you any experience with how databases work?
Rach row in a database is one item or record and the information associated with it. You might have a table called cars and define each row the contain the car model, color, year and price.
If this is all new to you, you should take a tutorial like https://www.w3schools.com/sql/ so you will be familiar with how databases work and the terminology used. This way you will be able to communicate your needs with others is an efficient manner.
