# Split a SQL query in different text boxes

**URL:** <https://discourse.nodered.org/t/split-a-sql-query-in-different-text-boxes/21836>\
**Category:** General\
**Created:** [17 February 2020 09:49 UTC](https://discourse.nodered.org/t/split-a-sql-query-in-different-text-boxes/21836 "2020-02-17T09:49:22Z")\
**Posts on this page:** 8\
**Page:** 1

<div class="post-metadata">

**Author:** ![SteveRem](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/steverem/32/17203_2.png) [@SteveRem](https://discourse.nodered.org/u/SteveRem)\
**Post date:** [17 February 2020 09:49 UTC](https://discourse.nodered.org/t/split-a-sql-query-in-different-text-boxes/21836/1 "2020-02-17T09:49:22Z")

</div>

I made separate queries on a SQL view, and put the received data into some text boxes.  
It works, but, if I want to get more data, it seems to me silly to use a separate query for each data I want to retreive.  
What I'm trying to do, is to write a single query, then split the result, and put it into separates text boxes.  
Has someoune any hints?

Below is my current flow

 ![query](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/3/5/359d70e6f8a49596a34506295cdd3920b5b27618.jpeg)

```auto
[{"id":"c8954a23.36cbe8","type":"inject","z":"cbdd8276.7b74","name":"","topic":"","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":"1","x":120,"y":200,"wires":[["2d44cc0b.df7c9c","2b9a0a15.d55286","9ca88b41.b01528"]]},{"id":"d884499.3863d38","type":"mysql","z":"cbdd8276.7b74","mydb":"f8c4b444.c34a78","name":"get plant name","x":560,"y":120,"wires":[["15c9a293.665d55"]]},{"id":"2d44cc0b.df7c9c","type":"function","z":"cbdd8276.7b74","name":"sql name query","func":"msg.topic = \"select name from view_plant\"\nreturn msg;","outputs":1,"noerr":0,"x":340,"y":120,"wires":[["d884499.3863d38"]]},{"id":"2c0ef108.a82516","type":"ui_text","z":"cbdd8276.7b74","group":"d9b023d.45ee1e","order":1,"width":0,"height":0,"name":"","label":"Plant name","format":"{{msg.payload}}","layout":"col-center","x":1050,"y":120,"wires":[]},{"id":"d3346014.5a741","type":"mysql","z":"cbdd8276.7b74","mydb":"f8c4b444.c34a78","name":"get plant owner","x":580,"y":180,"wires":[["2470d614.03659a"]]},{"id":"2b9a0a15.d55286","type":"function","z":"cbdd8276.7b74","name":"sql owner query","func":"msg.topic = \"select owner from view_plant\"\nreturn msg;","outputs":1,"noerr":0,"x":340,"y":180,"wires":[["d3346014.5a741"]]},{"id":"2470d614.03659a","type":"split","z":"cbdd8276.7b74","name":"","splt":"\\n","spltType":"str","arraySplt":1,"arraySpltType":"len","stream":false,"addname":"topic","x":750,"y":180,"wires":[["c4875804.bc45a"]]},{"id":"c4875804.bc45a","type":"split","z":"cbdd8276.7b74","name":"","splt":"\\n","spltType":"str","arraySplt":1,"arraySpltType":"len","stream":false,"addname":"","x":890,"y":180,"wires":[["a821eaa3.09e088"]]},{"id":"a821eaa3.09e088","type":"ui_text","z":"cbdd8276.7b74","group":"69bcb450.363e94","order":1,"width":0,"height":0,"name":"","label":"Owner","format":"{{msg.payload}}","layout":"col-center","x":1030,"y":180,"wires":[]},{"id":"857095fe.3af3a8","type":"mysql","z":"cbdd8276.7b74","mydb":"f8c4b444.c34a78","name":"get location","x":570,"y":260,"wires":[["187b07c5.7b94b8"]]},{"id":"9ca88b41.b01528","type":"function","z":"cbdd8276.7b74","name":"sql location query","func":"msg.topic = \"select location from view_plant\"\nreturn msg;","outputs":1,"noerr":0,"x":350,"y":260,"wires":[["857095fe.3af3a8"]]},{"id":"187b07c5.7b94b8","type":"split","z":"cbdd8276.7b74","name":"","splt":"\\n","spltType":"str","arraySplt":1,"arraySpltType":"len","stream":false,"addname":"topic","x":750,"y":260,"wires":[["1d4cb57f.06374b"]]},{"id":"1d4cb57f.06374b","type":"split","z":"cbdd8276.7b74","name":"","splt":"\\n","spltType":"str","arraySplt":1,"arraySpltType":"len","stream":false,"addname":"","x":890,"y":260,"wires":[["e49a6db6.cff48"]]},{"id":"e49a6db6.cff48","type":"ui_text","z":"cbdd8276.7b74","group":"3ced5313.d6ff2c","order":1,"width":0,"height":0,"name":"","label":"Location","format":"{{msg.payload}}","layout":"col-center","x":1040,"y":260,"wires":[]},{"id":"15c9a293.665d55","type":"split","z":"cbdd8276.7b74","name":"","splt":"\\n","spltType":"str","arraySplt":1,"arraySpltType":"len","stream":false,"addname":"topic","x":750,"y":120,"wires":[["3e87f1bc.e9648e"]]},{"id":"3e87f1bc.e9648e","type":"split","z":"cbdd8276.7b74","name":"","splt":"\\n","spltType":"str","arraySplt":1,"arraySpltType":"len","stream":false,"addname":"","x":890,"y":120,"wires":[["2c0ef108.a82516"]]},{"id":"f8c4b444.c34a78","type":"MySQLdatabase","z":"","host":"127.0.0.1","port":"3306","db":"mdb","tz":""},{"id":"d9b023d.45ee1e","type":"ui_group","z":"","name":"Group 1","tab":"7b129b8e.3ae0fc","order":1,"disp":false,"width":"4","collapse":false},{"id":"69bcb450.363e94","type":"ui_group","z":"","name":"Group 3","tab":"7b129b8e.3ae0fc","order":2,"disp":false,"width":"6","collapse":false},{"id":"3ced5313.d6ff2c","type":"ui_group","z":"","name":"Group 8","tab":"7b129b8e.3ae0fc","order":6,"disp":false,"width":"6","collapse":false},{"id":"7b129b8e.3ae0fc","type":"ui_tab","z":"","name":"Configurator","icon":"dashboard","order":2,"disabled":false,"hidden":false}]

```

---

<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:** [17 February 2020 10:17 UTC](https://discourse.nodered.org/t/split-a-sql-query-in-different-text-boxes/21836/2 "2020-02-17T10:17:09Z")

</div>

it seems to me you should start by looking at an SQL tutorial like [https://www.w3schools.com/sql/](https://www.w3schools.com/sql/)

---

<div class="post-metadata">

**Author:** ![SteveRem](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/steverem/32/17203_2.png) [@SteveRem](https://discourse.nodered.org/u/SteveRem)\
**Post date:** [17 February 2020 10:31 UTC](https://discourse.nodered.org/t/split-a-sql-query-in-different-text-boxes/21836/3 "2020-02-17T10:31:28Z")

</div>

Maybe I've not been clear...  
I know how to request multiple values from a sql table. 🙂

I don't know how to handle the result, and split it in different text boxes.

---

<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:** [17 February 2020 10:33 UTC](https://discourse.nodered.org/t/split-a-sql-query-in-different-text-boxes/21836/4 "2020-02-17T10:33:44Z")

</div>

Rather than branching out on the inject node, have you thought of branching after the query returns? You can use a change node to move the contents of the payload around.

---

<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:** [17 February 2020 11:28 UTC](https://discourse.nodered.org/t/split-a-sql-query-in-different-text-boxes/21836/5 "2020-02-17T11:28:18Z")

</div>

Say you do a `select * from view_plant` that will return (in msg.payload) an array of every row in the database. Each array occurrence (i.e. msg.payload[0], msg.payload[1]...msg.payload[n]) will be an object an all the column's in each database row will be included. You can then address each element as you need it. example msg.payload[0].name etc

---

<div class="post-metadata">

**Author:** ![karlbo](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/karlbo/32/68793_2.png) [@karlbo](https://discourse.nodered.org/u/karlbo)\
**Post date:** [17 February 2020 11:35 UTC](https://discourse.nodered.org/t/split-a-sql-query-in-different-text-boxes/21836/6 "2020-02-17T11:35:08Z")

</div>

try this first in your function node, then split it by object name. if you do not yet have a timestamp collumn in your database, ADD IT!!  
msg.topic = "select name, owner, location from view\_plant group by timestamp desc"

---

<div class="post-metadata">

**Author:** ![SteveRem](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/steverem/32/17203_2.png) [@SteveRem](https://discourse.nodered.org/u/SteveRem)\
**Post date:** [17 February 2020 12:33 UTC](https://discourse.nodered.org/t/split-a-sql-query-in-different-text-boxes/21836/7 "2020-02-17T12:33:57Z")

</div>

That worked! Brilliant!

I used several Change nodes, one for each column of the table, in which I moved msg.payload[0].name, .owner, ecc.., to msg.payload, and then that payload into the text box.

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/1X/d073cd938eafa2e558d7c2cd59003b3ef4963033.png) [@system](https://discourse.nodered.org/u/system)\
**Post date:** [2 March 2020 12:34 UTC](https://discourse.nodered.org/t/split-a-sql-query-in-different-text-boxes/21836/8 "2020-03-02T12:34:01Z")

</div>

This topic was automatically closed 14 days after the last reply. New replies are no longer allowed.