Copying my Door control status back to my Mysql databse

Hi @shipra, it's hard to provide more help without knowing which part you are struggling with.

We are trying to help you learn how to do this rather than just give you a fully working solution that you don't understand.

Change node where can i take that ,that part i searching Sir

Do you understand why I suggest adding a Change node? If you understand why then the answer to where should be clear.

So let me repeat what I said:

From what I recall of the previous thread, you start with the ID in msg.payload but it gets overwritten when you do the database lookup.

In which case, I suggest you add a Change node after the MQTT node, at the start of the flow,

Let me try to explain that again.

At the moment, you have the ID in msg.payload when it comes out of the MQTT node.

You then pass it to a Template node where you build an SQL query in msg.topic to lookup the ID in the table.

You then pass that to the mysql node to run the query. That node puts its result in msg.payload. This overwrites the old value of msg.payload, so the message no longer contains the ID.

Your flow looks like:

MQTT -> Template -> MySQL -> ....

So you need to find a way to keep the ID on the message. The best way of doing that is to copy its value to a different message property that will not get overwritten by the other nodes.

You can do that with the Change node:

MQTT -> Change -> Template -> MySQL -> ...

And configure that Change node to set msg.id to msg.payload.

You should then find msg.id contains the RFID value later on in your flow - use a Debug node to check that.

If you don't understand any of that, then please try to explain what part you don't understand.

ggg jj

yes i have tried this ,Sir check ones and suggest

No, you have not tried what I said.

change node to connect with template then databasek

That is still not what I said.

You need to add a Change node in between the MQTT and Template nodes.

You have done that, but you have left the wire from the MQTT node to the Template node. Delete that wire.

Also, you have not configured the Change node as I suggested. You have configured it to Move the property. If you move it, then you will break the flow because the Template node needs msg.payload to have the ID in it. This is why I said to configure it as set msg.id to msg.payload

n

Check ones

As usual, you are pasting screenshots of Debug without any explanation. I have no idea if that is right or not. But what do you think? Does it look right to you?

7/20/2020, 8:58:05 PMnode: debug3

RFID : msg : Object

object

topic: "RFID"

payload: "E1234562"

qos: 1

retain: false

_msgid: "6e46eb3f.f2a364"

id: "E1234562"

/////Yes i have stored rfid into an id

Okay... so what do you think you need to do next?

Hint: modify your SQL statement to include msg.id where you have the RFID hardcoded.

UPDATE rfid1
set Door_Status='{{payload}}'
WHERE rfidno='{{msg.id}}';

/// not updating

UPDATE rfid1
set Door_Status='{{payload}}'
WHERE rfidno='{{msg.id}}';

You know you can insert the value of msg.payload by using {{payload}}. So how do you think you can insert the value of msg.id?

Can you spot your mistake?

It's {{id}} - you don't need the msg. part as you can already see from the payload case that works.

Just a design question... to you want to replace the same record over and over with current status? Or keep a history of the status changes? I did something similar some time ago, using python and door sensor, but I kept a history of the status changes using INSERT not UPDATE query, then used a SELECT query (for last/newest record in table) to refresh the UI as applicable.

Sir i have done this RFID----->Change----->template node right
but after entering E1234562 it is showing CR_FAIL ,why ? as my database has E1234562 Please Suggest.

its no updating in database Sir.

I want changed status every time Sir .please help I am not getting updated data in my database

Please anyone help ...I really need help.Thank You


Sir I really want help. Why i am not getting updated data .
As i have searched google for this issue but haven't got anything.
Sir please suggest me where i am wrong

Can you explain why you want to store the current value in the database? Once you succeed in getting it stored what are you going to do with it? I ask because it is unusual just to record current status in a database like this and I wonder whether there is a better way achieve your final solution.