# Using msg.payload in a SQL query

**URL:** <https://discourse.nodered.org/t/using-msg-payload-in-a-sql-query/15995>\
**Category:** General\
**Created:** [27 September 2019 11:55 UTC](https://discourse.nodered.org/t/using-msg-payload-in-a-sql-query/15995 "2019-09-27T11:55:40Z")\
**Posts on this page:** 8\
**Page:** 1

<div class="post-metadata">

**Author:** ![kresten88](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/kresten88/32/13197_2.png) [@kresten88](https://discourse.nodered.org/u/kresten88)\
**Post date:** [27 September 2019 11:55 UTC](https://discourse.nodered.org/t/using-msg-payload-in-a-sql-query/15995/1 "2019-09-27T11:55:40Z")

</div>

This might be a simple one...

When I select a value from a dropdownlist the msg.payload is e.g. 1E6  
Now I would like to use that msg.payload to make a query (mysql node)

However I cannot find a working function for this.

Currently I am at:

```auto
msg.topic = "SELECT * FROM `NodeRed`.`7bchannels` WHERE `channel`= {{{msg.payload}}} "
return msg;

```

It has to be in the Syntax, but what am I doing wrong?

---

<div class="post-metadata">

**Author:** ![bakman2](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/bakman2/32/6207_2.png) [@bakman2](https://discourse.nodered.org/u/bakman2)\
**Post date:** [27 September 2019 12:13 UTC](https://discourse.nodered.org/t/using-msg-payload-in-a-sql-query/15995/2 "2019-09-27T12:13:22Z")

</div>

This should work.

```auto
t = "SELECT * FROM NodeRed.7bchannels WHERE channel="+msg.payload
return {topic:t}

```

Probably need to format payload as a string:

```auto
t = "SELECT * FROM NodeRed.7bchannels WHERE channel='"+msg.payload+"'"
return {topic:t}

```

---

<div class="post-metadata">

**Author:** ![kresten88](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/kresten88/32/13197_2.png) [@kresten88](https://discourse.nodered.org/u/kresten88)\
**Post date:** [27 September 2019 15:09 UTC](https://discourse.nodered.org/t/using-msg-payload-in-a-sql-query/15995/3 "2019-09-27T15:09:53Z")

</div>

We have a winner, simple, it did the job.

Would you mind explaining me why my trial didn`t work? What does the **{topic:t}** that made it work.

It is probably basic, but that is where I am in this steep learning curve 🥴

---

<div class="post-metadata">

**Author:** ![bakman2](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/bakman2/32/6207_2.png) [@bakman2](https://discourse.nodered.org/u/bakman2)\
**Post date:** [27 September 2019 15:34 UTC](https://discourse.nodered.org/t/using-msg-payload-in-a-sql-query/15995/4 "2019-09-27T15:34:53Z")

</div>

We need to see the full code to determine why it didn't work. I don't know if you can use the mustache format in a function like that - `{{{x}}}`.

---

<div class="post-metadata">

**Author:** ![afelix](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/afelix/32/9743_2.png) [@afelix](https://discourse.nodered.org/u/afelix)\
**Post date:** [27 September 2019 16:16 UTC](https://discourse.nodered.org/t/using-msg-payload-in-a-sql-query/15995/5 "2019-09-27T16:16:42Z")

</div>

> [@bakman2](#):
>
> I don't know if you can use the mustache format in a function like that - `{{{x}}}` .

You can’t, but you can use ES6 template strings instead. The following would have worked too:

```auto
msg.topic = `SELECT * FROM \`NodeRed\`.\`7bchannels\` WHERE channel= ${msg.payload}`;
return msg;

```

Since template strings use backticks as surrounding quotes, you need to escape the backticks inside the string. To reference variables inside, you use the `${var}` syntax.  
If those backticks inside the query are optional which I don’t know/want to know (I can no longer do SQL, PTSD; it’s a long story), you could also leave them out as in @bakman2’s answer.

---

<div class="post-metadata">

**Author:** ![dynamicdave](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/dynamicdave/32/96_2.png) [@dynamicdave](https://discourse.nodered.org/u/dynamicdave)\
**Post date:** [28 September 2019 05:57 UTC](https://discourse.nodered.org/t/using-msg-payload-in-a-sql-query/15995/6 "2019-09-28T05:57:56Z")

</div>

Have you thought of using a template node and putting the MySQL query in there?  
Just remember to set the property to 'msg.topic'.

![ScreenShot030](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/2X/4/45efbf8046c207f87e012d5de559adfbe267ad8f.png)

![ScreenShot028](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/2X/0/0709821d3da9f0c169d6fec2145fa50f5e9f5dcb.png)  
You might have to alter {{payload}} to {{channel}} and put the channel number in msg.channel before the message enters the template node.

Here's an 'INSERT' example from one of my flows.

 ![ScreenShot029](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/2X/6/63cf394cdaf13765f181080f1d8daea765875201.png)

---

<div class="post-metadata">

**Author:** ![kresten88](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/kresten88/32/13197_2.png) [@kresten88](https://discourse.nodered.org/u/kresten88)\
**Post date:** [28 September 2019 09:50 UTC](https://discourse.nodered.org/t/using-msg-payload-in-a-sql-query/15995/7 "2019-09-28T09:50:12Z")

</div>

Thank you @dynamicdave I will give it a try as well.

The issue is that the whole plan is in my head but as I am trying to understand javascript and the meanings of all these msg.options, msg.paylad,... it is a matter of keeping things as simple as possible.

Still trying to find the best way to learn all these things, specially writing functions. Like the "learn javascript for node-red" manual would be good. For now I learn most via this forum.

Thanks everyone for helping the beginners...

---

<div class="post-metadata">

**Author:** ![chuckf201](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/chuckf201/32/91382_2.png) [@chuckf201](https://discourse.nodered.org/u/chuckf201)\
**Post date:** [23 June 2020 18:05 UTC](https://discourse.nodered.org/t/using-msg-payload-in-a-sql-query/15995/8 "2020-06-23T18:05:42Z")

</div>

For those using mariaDB I've create a 'select' function that works with msg.payload.  
```var t = "SELECT DateAndTime, Temperature, Humidity, BarrPress FROM `temp-at-interrupt` where minute(Time)= '00' order by DateAndTime DESC LIMIT " + msg.payload

return {topic:t} ```

I'm injecting a numberic payload of 22 (record count)  
Eventually, I'll add a slide node to make my select statement dynamic.  
Thank you "dynamicdave" for you great post.  
Also, in general, this site is my favorite help site for everything "node-red" and R-Pi.