Copying my Door control status back to my Mysql databse

General
61

Lets take one e.g
I have enterred E1234562 throught my mqtt code and by help of node red i am checking that E1234562 is stored or not if stored it shows and send string"CR_SUCCESS" otherwise "CR_FAIL".
Now next step if E1234562 entered and it is valid also now that person wants to enter his/her room and by controlling switches he/she can close/open the door.
Next step this Door_Status (open or close) i want to update this status in my DataBase Column (my column name is Door_Status).

In Summary i want a flow in which only ENTERED VALID rfid can only access Door Switches

62

First of all, please realise that most of the people trying to help you are in the UK. So when you post at 7am our time, you are not going to get an instant response. Posting multiple times asking for help is not going to get an answer quicker.

Second, can I ask why are you building this solution at all? Is this a school/university assignment? Is it a personal project for some reason?

I have spent a huge amount of time trying to help you. I have tried to explain in detail each step we're going through. I have done that to try to give you the information you need to do this for yourself.

Unfortunately I'm not seeing any attempt by you to understand what I am writing. Whilst I can continue to try to help you, I am not going to be able to give you as much time today.

So, lets try to get to the point where you understand what this flow is doing so you are able to debug it yourself.

In the most recent screenshot you have shared, you appear to have removed the set msg.id Change node from the flow. Why have you done that? We can just see in the Debug sidebar the message that arrived on the Debug_rfid node - the topic property shows the query you have tried to run:

UDPATE rfid1 Set Door_status='DR1_ON' WHERE rfidno='';

Can you see the problem with that query?

Answer:

rfidno=''

Now, I assume the Template node is configured as I suggested yesterday - with WHERE rfidno='{{id}}' in it. But as you have removed the Change node from the flow, then msg.id won't be set to anything.

63

Sir its my urgent project which i get know about node red ,its a great platform to deploy my flow.

ok you mean that i have to attached set.msg.id to template(in which updated query is written)

1

2
2997×369 32.5 KB
3
31039×242 9.28 KB
5
Now I am attaching that screenshots,and output too.
(

64

Open should be updated on E1234562 person column

65

Then you'll need to look at what SQL your flow is running and figure out what is going wrong. Sharing lots of screenshots won't make that happen.

You need to look at the SQL you are passing to the database node for yourself.

66

Sir SQL Query as you told like that only i have entered,then where i am wrong .Sorry for trouble as i need your help thats why i am asking

UPDATE rfid1
SET Door_Status='{{payload}}'
WHERE rfidno='{{id}}';

I am not getting why id is showing blank like this

rfidno='' "
67

If that is the output you are getting, then msg.id must be blank at the point the message is passed to the Template node. You need to figure out why that is. Use a Debug node to look at the message you are passing into the Template node. Confirm it has msg.id. Use a Debug node to look at the message coming out of the Template node. Keep debugging it until you get the right input and output.

I'm sorry, but if this really is urgent, you'd spend a bit more time reading the replies you are given and try to understand the help we're giving.

I'm going out now - I won't be replying to anything until later today. Read back through the full history of this discussion. Reread the information I have given you.

68

Sir i have putted the debug node too ,it is showing id:E1234562 but not passing that id to template i dont why it is happening.

69

export your current flow and attach it to a reply.

Did you complete the mysql tutorial?

70

yes sir

ok

7/21/2020, 2:43:52 PMnode: MQTT_IN DebugRFID : msg : Object
object
topic: "RFID"
payload: "E1234562"
qos: 1
retain: false
_msgid: "4f3bd9fe.f6c748"
7/21/2020, 2:43:52 PMnode: debug3RFID : msg : Object
object
topic: "RFID"
payload: "E1234562"
qos: 1
retain: false
_msgid: "4f3bd9fe.f6c748"
id: "E1234562"
7/21/2020, 2:43:52 PMnode: Debug23SELECT rfidno FROM rfid1 WHERE rfidno='E1234562'; : msg.payload : string[8]
"E1234562"
7/21/2020, 2:43:52 PMnode: Debug_templateUPDATE rfid1 SET Door_Status='E1234562' WHERE rfidno='E1234562'; : msg : Object
object
topic: "UPDATE rfid1 ↔SET Door_Status='E1234562'↔WHERE rfidno='E1234562';↔"
payload: "E1234562"
qos: 1
retain: false
_msgid: "4f3bd9fe.f6c748"
id: "E1234562"
7/21/2020, 2:43:52 PMnode: Debug2SELECT rfidno FROM rfid1 WHERE rfidno='E1234562'; : msg.payload : string[10]
"CR_SUCCESS"
7/21/2020, 2:43:52 PMnode: Debug_rfidUPDATE rfid1 SET Door_Status='E1234562' WHERE rfidno='E1234562'; : msg : Object
object
topic: "UPDATE rfid1 ↔SET Door_Status='E1234562'↔WHERE rfidno='E1234562';↔"
payload: object
qos: 1
retain: false
_msgid: "4f3bd9fe.f6c748"
id: "E1234562"
7/21/2020, 2:44:27 PMnode: Debug_templateUPDATE rfid1 SET Door_Status='DR1_ON' WHERE rfidno=''; : msg : Object
object
payload: "DR1_ON"
socketid: "0X1qahQGabD1HcmNAAAA"
_msgid: "def2d8c4.cb20d8"
topic: "UPDATE rfid1 ↔SET Door_Status='DR1_ON'↔WHERE rfidno='';↔"
7/21/2020, 2:44:28 PMnode: Debug_rfidUPDATE rfid1 SET Door_Status='DR1_ON' WHERE rfidno=''; : msg : Object
object
payload: object
socketid: "0X1qahQGabD1HcmNAAAA"
_msgid: "def2d8c4.cb20d8"
topic: "UPDATE rfid1 ↔SET Door_Status='DR1_ON'↔WHERE rfidno='';↔"
71

That is a very nice copy of your debug log, but what has that got to do with my request?

go to the hamburger menu and select the 'Export' option

72 
[{"id":"f4ce26f0.22055","type":"tab","label":"Flow 2","disabled":false,"info":""},{"id":"ec3347d0.94006","type":"mqtt in","z":"f4ce26f0.22055","name":"","topic":"RFID","qos":"1","datatype":"utf8","broker":"f119edb0.e6ff1","x":110,"y":160,"wires":[["8f06369a.7fb86","c7068d53.104278","a9497623.eb7a78"]]},{"id":"386fc529.c39e9a","type":"mysql","z":"f4ce26f0.22055","mydb":"38417304.575e9c","name":"","x":410,"y":240,"wires":[["3b5df68b.3ef092"]]},{"id":"a9497623.eb7a78","type":"template","z":"f4ce26f0.22055","name":"","field":"topic","fieldType":"msg","format":"handlebars","syntax":"mustache","template":"SELECT rfidno FROM rfid1 \nWHERE rfidno='{{payload}}';","output":"str","x":260,"y":240,"wires":[["386fc529.c39e9a","d1329fd6.f801a"]]},{"id":"3b5df68b.3ef092","type":"switch","z":"f4ce26f0.22055","name":"","property":"payload","propertyType":"msg","rules":[{"t":"empty"},{"t":"nempty"}],"checkall":"true","repair":false,"outputs":2,"x":570,"y":240,"wires":[["69c0acd5.fcc094"],["951e3461.d81718"]]},{"id":"69c0acd5.fcc094","type":"change","z":"f4ce26f0.22055","name":"","rules":[{"t":"set","p":"payload","pt":"msg","to":"CR_FAIL","tot":"str"}],"action":"","property":"","from":"","to":"","reg":false,"x":720,"y":140,"wires":[["fc20bb4d.9f3b9","8f218188.f7c7d"]]},{"id":"951e3461.d81718","type":"change","z":"f4ce26f0.22055","name":"","rules":[{"t":"set","p":"payload","pt":"msg","to":"CR_SUCCESS","tot":"str"}],"action":"","property":"","from":"","to":"","reg":false,"x":720,"y":340,"wires":[["27049bf1.bb8dd4","fc20bb4d.9f3b9","207d854c.6e3b7a","b00f41b0.3c09e8"]]},{"id":"fc20bb4d.9f3b9","type":"mqtt out","z":"f4ce26f0.22055","name":"","topic":"MATCH","qos":"2","retain":"true","broker":"f119edb0.e6ff1","x":800,"y":240,"wires":[]},{"id":"b00f41b0.3c09e8","type":"ui_switch","z":"f4ce26f0.22055","name":"","label":"Open","tooltip":"","group":"ea12b474.8ed4d8","order":0,"width":"2","height":"2","passthru":true,"decouple":"false","topic":"","style":"","onvalue":"DR1_ON","onvalueType":"str","onicon":"","oncolor":"green","offvalue":"--","offvalueType":"str","officon":"","offcolor":"red","x":990,"y":60,"wires":[["6780d94e.dee3f","81999de5.5133f"]]},{"id":"207d854c.6e3b7a","type":"ui_switch","z":"f4ce26f0.22055","name":"","label":"Close","tooltip":"","group":"ea12b474.8ed4d8","order":0,"width":"2","height":"2","passthru":true,"decouple":"false","topic":"","style":"","onvalue":"DR1_OFF","onvalueType":"str","onicon":"","oncolor":"green","offvalue":"--","offvalueType":"str","officon":"","offcolor":"red","x":990,"y":120,"wires":[["81999de5.5133f","6780d94e.dee3f"]]},{"id":"6780d94e.dee3f","type":"ui_text","z":"f4ce26f0.22055","group":"59684e9b.60672","order":2,"width":0,"height":0,"name":"","label":"DOOR1","format":"{{msg.payload}}","layout":"row-spread","x":1220,"y":60,"wires":[]},{"id":"81999de5.5133f","type":"template","z":"f4ce26f0.22055","name":"update_template","field":"topic","fieldType":"msg","format":"handlebars","syntax":"mustache","template":"UPDATE rfid1 \nSET Door_Status='{{payload}}'\nWHERE rfidno='{{id}}';\n","output":"str","x":1190,"y":160,"wires":[["15c8e7a2.fd3328","a9bdcf4.c362e3"]]},{"id":"15c8e7a2.fd3328","type":"mysql","z":"f4ce26f0.22055","mydb":"38417304.575e9c","name":"","x":1350,"y":220,"wires":[["42a36416.b6dfe4"]]},{"id":"8f06369a.7fb86","type":"debug","z":"f4ce26f0.22055","name":"MQTT_IN Debug","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":290,"y":80,"wires":[]},{"id":"8f218188.f7c7d","type":"debug","z":"f4ce26f0.22055","name":"debug","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":690,"y":60,"wires":[]},{"id":"27049bf1.bb8dd4","type":"debug","z":"f4ce26f0.22055","name":"Debug2","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":740,"y":420,"wires":[]},{"id":"eabc8767.4fabf8","type":"debug","z":"f4ce26f0.22055","name":"debug3","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":500,"y":100,"wires":[]},{"id":"c7068d53.104278","type":"change","z":"f4ce26f0.22055","name":"","rules":[{"t":"set","p":"id","pt":"msg","to":"payload","tot":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":310,"y":160,"wires":[["eabc8767.4fabf8","81999de5.5133f"]]},{"id":"d1329fd6.f801a","type":"debug","z":"f4ce26f0.22055","name":"Debug23","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":400,"y":340,"wires":[]},{"id":"a9bdcf4.c362e3","type":"debug","z":"f4ce26f0.22055","name":"Debug_template","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":1320,"y":340,"wires":[]},{"id":"42a36416.b6dfe4","type":"debug","z":"f4ce26f0.22055","name":"Debug_rfid","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":1490,"y":160,"wires":[]},{"id":"f119edb0.e6ff1","type":"mqtt-broker","z":"","name":"RFID","broker":"tcp://mqtt.eclipse.org","port":"1883","clientid":"NCS_Client_02","usetls":false,"compatmode":true,"keepalive":"60","cleansession":true,"birthTopic":"","birthQos":"2","birthPayload":"","closeTopic":"","closeQos":"0","closePayload":"","willTopic":"","willQos":"0","willPayload":""},{"id":"38417304.575e9c","type":"MySQLdatabase","z":"","name":"","host":"127.0.0.1","port":"3306","db":"rfid","tz":""},{"id":"ea12b474.8ed4d8","type":"ui_group","z":"","name":"Door 1","tab":"45bf936.9ba396c","order":2,"disp":true,"width":"6","collapse":false},{"id":"59684e9b.60672","type":"ui_group","z":"","name":"Door Controller","tab":"45bf936.9ba396c","order":1,"disp":true,"width":"6","collapse":false},{"id":"45bf936.9ba396c","type":"ui_tab","z":"","name":" NCS_Door Controller Panel","icon":"door controller","order":1,"disabled":false,"hidden":false}]
73

You have shared lots of Debug sidebar output.

In that output we can see lots of different SQL queries you are running:

 "UPDATE rfid1 SET Door_Status='E1234562' WHERE rfidno='E1234562';"

"UPDATE rfid1 SET Door_Status='DR1_ON' WHERE rfidno='';"

That tells us you are still not generating the right SQL statement to run.

74

I want you to analyse your flow. Take a look and tell me what happens when the MQTT-in node receives a message. explain it in detail, node by node following each wire a msg would travel.

Screen Shot 2020-07-21 at 5.30.34 AM
Screen Shot 2020-07-21 at 5.30.34 AM1251×359 53.4 KB

75

Try using rfid instead of id and use that for the query. Perhaps id is interfering with the internals of node-red/node.

76

Sir whenever i am clicking open or close button from the UI Dashboard it is sending rfid='--'
I have not hardcoded any E1234562

77

I tried Sir.Same issue is coming

78

@bakman2 I would not have suggested she use msg.id if it was going to cause issues. Please don't confuse matters.

@shipra this is the first time you have shown a flow that has the Dashboard buttons wired up. You now have two wires going into the Template node. That means it will get triggered when the initial MQTT message arrives (which is what I thought you wanted), and it will also get triggered when the dashboard buttons are changed - and in that part of the flow msg.id will not be set so the Template does not generate the query you want.

So the question is - when exactly do you want the database to be updated? If you only want it updated when the dashboard button is clicked, then that should be the only path to the template node for updating the database.

79

RFID:- Takes a RFID input from user for e.g me
set.msg.payload:- it i shelping me to strore my RFID in some id variable
template:- it is checking in my database that whether that entered RFID is present or not
rfid:my database
switch:to differentiate between bad rfid or good rfid
set.msg.payload:sending user friendly string("CR_SUCCESS or CR_FAIL")
MATCH:my mqtt out node for publishing CR_SUCCESS/CR_FAIL
Open and Close:my dashboard switches
update template:updating my database whenever switch gets a trigger

80

How does the scanning of an RFID tag relate to the dashboard buttons?

If a user scans their tag, are they then shown the dashboard to open/close the doors?

So is it right to say:

When a dashboard button is pressed, it should update the database for whoever last scanned their RFID tag?