Query to extract data from MariaDB using SELECT statement to link with the RFID card detected

General
1

Hello everyone,

I am trying to link the RFID card detected from my MFRC RC522 card reader to link with the database created on phpMydmin.
I have used MYSQL node and it successfully detects the database but when I am sending a query to read only the data related with the particular tag detected, it gives an EMPTY result. Can anyone guide me accordingly?

Thanks and Regards

2

Add a debug node which is set to show complete message and feed it with the message you are sending to the mysql node, there you will be able to see the query it is asking for.

1 Like
3

Thanks @Colin for your reply, I had a debug node after my sql node and it reads

SELECT ITEM FROM TAG_READ WHERE uID = 'context.data.uID' : msg.payload : array[0]

[ empty ]

FYI: TAG_READ is the table from my database
uID is the column name on my database, which I want to equate with the context.data.uID, to return the related info from the table.

4

Is your SQL statement in msg.query or msg.payload? (a copy of the debug would have answered this question)

I'm not sure you can reference context data in the query.

1 Like
5

As @zenofmud says, you need to put the actual uiD value into the select string. Mysql cannot look in the node-red context.

1 Like
6

Thanks @zenofmud for your response, I am fetching my data from altairsmartcore via MQTT (context.data.uID), so I have initially one MQTT listening node on my node red, it receives data in msg.payload, then I have connected one function node which I am yet to figure out what to write in it to extract data from database, then I have one mysqlnode, linked to one debug node and one MQTT sending node on my remote machine.

So, like you said if I need to link it with my database, I guess I need to write separate query statement for each uID in the function node? Is there any better and convenient way?

7

That would be much of work, I actually thought I could just let it link with my uID in the database and it could fetch the data related with uID.

8

You have to put the id in the select query. When you say context.data.uID what exactly do you mean by that? Have you put it into the node-red context or what?

9

By context.data.uID, I mean the TagID which I am receiving from Altairsmartcore via MQTT node. I want to relate that context.data.uID with the uID column of my database. So that it understands that when

msg.topic = "SELECT * FROM TAG_READ WHERE uID = 'context.data.uID'";
return msg;

It should return the related data with that uID.

11

Show us what you are getting out of the MQTT node.

12

To help us understand what's going on, it would be helpful if you provided your frow from where you get the data from the MQTT node to where you are making the MySQL call.

13

Try looking at the msg.topic and msg.payload you are sending using a debug node.

Is that what you are expecting?

14

On Altairsmartcore, I am publishing "RFID card detected is: " + context.data.uID

After receiving on Node-red via MQTT listening node, my MQTT output (without function node) is
RFID card detected is: 121221254134

15

Is that the ID you want to select in the query?

16

Yes, it is one of my tag IDs

17

Ao in this case you chae to extract the value (121221254134) to put in the where clause so your msg.topic will be:
msg.topic = "SELECT * FROM TAG_READ WHERE uID = '121221254134'";
(using quotes assuming that it is a string and not a number).

If you can change the format or data being send, why not send it as an object like {"RFID"; 121221254134} which would be much easier to access in NR

18

Thanks Paul, I want my function node to read the payload, I am receiving from MQTT, I cannot unfortuntely change the context.data.uID fromat on Altairsmartcore, therefore the only change which I can make on my function node is:

var name;
name = msg.payload
msg.topic = SELECT * FROM TAG_READ WHERE 'name'='uID';
return msg;

But it also, does not work.

19

First you need to get the id out of the string "RFID card detected is: 121221254134" to do that look up the javascript function split(). Using that you can split it at the space character to get the id. Then you need to put that into the select string. This will show you how you can do that.

20

if that is exactly what you have coded you need to set msg.topic to a string that contains the complete sql statement. You could code it as
msg.topic = "SELECT * FROM TAG_READ WHERE 'name' = 'uID'";
but then it would be looking for a row where the column name was equal to the string uID and I don't think that is what you want to do.

Without knowing what the column in the data base is named it is hard to show the correct syntax. Assuming the column name is FOO and you have split the number from the mqtt string AND that number is now in msg.payload, the line should be:
msg.topic = "SELECT * FROM TAG_READ WHERE 'FOO' = " + msg.payload;

I hope that helps, I'll be gone for a couple hours.

21

Thanks for this link, I will surely go through it so that I can learn more