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

**URL:** <https://discourse.nodered.org/t/query-to-extract-data-from-mariadb-using-select-statement-to-link-with-the-rfid-card-detected/16497>\
**Category:** General\
**Created:** [9 October 2019 09:07 UTC](https://discourse.nodered.org/t/query-to-extract-data-from-mariadb-using-select-statement-to-link-with-the-rfid-card-detected/16497 "2019-10-09T09:07:31Z")\
**Posts on this page:** 20\
**Page:** 1

<div class="post-metadata">

**Author:** ![primat11](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/primat11/32/12185_2.png) [@primat11](https://discourse.nodered.org/u/primat11)\
**Post date:** [9 October 2019 09:07 UTC](https://discourse.nodered.org/t/query-to-extract-data-from-mariadb-using-select-statement-to-link-with-the-rfid-card-detected/16497/1 "2019-10-09T09:07:31Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)\
**Post date:** [9 October 2019 09:31 UTC](https://discourse.nodered.org/t/query-to-extract-data-from-mariadb-using-select-statement-to-link-with-the-rfid-card-detected/16497/2 "2019-10-09T09:31:58Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![primat11](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/primat11/32/12185_2.png) [@primat11](https://discourse.nodered.org/u/primat11)\
**Post date:** [9 October 2019 09:50 UTC](https://discourse.nodered.org/t/query-to-extract-data-from-mariadb-using-select-statement-to-link-with-the-rfid-card-detected/16497/3 "2019-10-09T09:50:14Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![zenofmud](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/zenofmud/32/316_2.png) [@zenofmud](https://discourse.nodered.org/u/zenofmud)\
**Post date:** [9 October 2019 10:06 UTC](https://discourse.nodered.org/t/query-to-extract-data-from-mariadb-using-select-statement-to-link-with-the-rfid-card-detected/16497/4 "2019-10-09T10:06:12Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)\
**Post date:** [9 October 2019 10:11 UTC](https://discourse.nodered.org/t/query-to-extract-data-from-mariadb-using-select-statement-to-link-with-the-rfid-card-detected/16497/5 "2019-10-09T10:11:07Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![primat11](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/primat11/32/12185_2.png) [@primat11](https://discourse.nodered.org/u/primat11)\
**Post date:** [9 October 2019 10:23 UTC](https://discourse.nodered.org/t/query-to-extract-data-from-mariadb-using-select-statement-to-link-with-the-rfid-card-detected/16497/6 "2019-10-09T10:23:08Z")

</div>

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?

---

<div class="post-metadata">

**Author:** ![primat11](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/primat11/32/12185_2.png) [@primat11](https://discourse.nodered.org/u/primat11)\
**Post date:** [9 October 2019 10:26 UTC](https://discourse.nodered.org/t/query-to-extract-data-from-mariadb-using-select-statement-to-link-with-the-rfid-card-detected/16497/7 "2019-10-09T10:26:46Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)\
**Post date:** [9 October 2019 10:36 UTC](https://discourse.nodered.org/t/query-to-extract-data-from-mariadb-using-select-statement-to-link-with-the-rfid-card-detected/16497/8 "2019-10-09T10:36:01Z")

</div>

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?

---

<div class="post-metadata">

**Author:** ![primat11](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/primat11/32/12185_2.png) [@primat11](https://discourse.nodered.org/u/primat11)\
**Post date:** [9 October 2019 10:40 UTC](https://discourse.nodered.org/t/query-to-extract-data-from-mariadb-using-select-statement-to-link-with-the-rfid-card-detected/16497/9 "2019-10-09T10:40:45Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)\
**Post date:** [9 October 2019 10:43 UTC](https://discourse.nodered.org/t/query-to-extract-data-from-mariadb-using-select-statement-to-link-with-the-rfid-card-detected/16497/11 "2019-10-09T10:43:29Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![zenofmud](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/zenofmud/32/316_2.png) [@zenofmud](https://discourse.nodered.org/u/zenofmud)\
**Post date:** [9 October 2019 10:44 UTC](https://discourse.nodered.org/t/query-to-extract-data-from-mariadb-using-select-statement-to-link-with-the-rfid-card-detected/16497/12 "2019-10-09T10:44:07Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![ukmoose](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/ukmoose/32/13_2.png) [@ukmoose](https://discourse.nodered.org/u/ukmoose)\
**Post date:** [9 October 2019 10:44 UTC](https://discourse.nodered.org/t/query-to-extract-data-from-mariadb-using-select-statement-to-link-with-the-rfid-card-detected/16497/13 "2019-10-09T10:44:12Z")

</div>

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

Is that what you are expecting?

---

<div class="post-metadata">

**Author:** ![primat11](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/primat11/32/12185_2.png) [@primat11](https://discourse.nodered.org/u/primat11)\
**Post date:** [9 October 2019 10:46 UTC](https://discourse.nodered.org/t/query-to-extract-data-from-mariadb-using-select-statement-to-link-with-the-rfid-card-detected/16497/14 "2019-10-09T10:46:49Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)\
**Post date:** [9 October 2019 10:49 UTC](https://discourse.nodered.org/t/query-to-extract-data-from-mariadb-using-select-statement-to-link-with-the-rfid-card-detected/16497/15 "2019-10-09T10:49:54Z")

</div>

> [@primat11](#):
>
> 121221254134

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

---

<div class="post-metadata">

**Author:** ![primat11](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/primat11/32/12185_2.png) [@primat11](https://discourse.nodered.org/u/primat11)\
**Post date:** [9 October 2019 10:50 UTC](https://discourse.nodered.org/t/query-to-extract-data-from-mariadb-using-select-statement-to-link-with-the-rfid-card-detected/16497/16 "2019-10-09T10:50:24Z")

</div>

Yes, it is one of my tag IDs

---

<div class="post-metadata">

**Author:** ![zenofmud](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/zenofmud/32/316_2.png) [@zenofmud](https://discourse.nodered.org/u/zenofmud)\
**Post date:** [9 October 2019 10:50 UTC](https://discourse.nodered.org/t/query-to-extract-data-from-mariadb-using-select-statement-to-link-with-the-rfid-card-detected/16497/17 "2019-10-09T10:50:45Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![primat11](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/primat11/32/12185_2.png) [@primat11](https://discourse.nodered.org/u/primat11)\
**Post date:** [9 October 2019 11:29 UTC](https://discourse.nodered.org/t/query-to-extract-data-from-mariadb-using-select-statement-to-link-with-the-rfid-card-detected/16497/18 "2019-10-09T11:29:36Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)\
**Post date:** [9 October 2019 11:37 UTC](https://discourse.nodered.org/t/query-to-extract-data-from-mariadb-using-select-statement-to-link-with-the-rfid-card-detected/16497/19 "2019-10-09T11:37:55Z")

</div>

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.

> **[Javascript Template Literals Example | Javascript String Interpolation](https://appdividend.com/2019/01/23/javascript-template-literals-example-javascript-string-interpolation/)**
>
> Javascript Template Literals Example | Javascript String Interpolation Tutorial. Template literals are the string literals allowing embedded expressions.

---

<div class="post-metadata">

**Author:** ![zenofmud](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/zenofmud/32/316_2.png) [@zenofmud](https://discourse.nodered.org/u/zenofmud)\
**Post date:** [9 October 2019 12:25 UTC](https://discourse.nodered.org/t/query-to-extract-data-from-mariadb-using-select-statement-to-link-with-the-rfid-card-detected/16497/20 "2019-10-09T12:25:53Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![primat11](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/primat11/32/12185_2.png) [@primat11](https://discourse.nodered.org/u/primat11)\
**Post date:** [9 October 2019 13:02 UTC](https://discourse.nodered.org/t/query-to-extract-data-from-mariadb-using-select-statement-to-link-with-the-rfid-card-detected/16497/21 "2019-10-09T13:02:09Z")

</div>

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

[Next page](https://discourse.nodered.org/t/query-to-extract-data-from-mariadb-using-select-statement-to-link-with-the-rfid-card-detected/16497.md?page=2)