# I need help to find closest match from DB

**URL:** <https://discourse.nodered.org/t/i-need-help-to-find-closest-match-from-db/38176>\
**Category:** General\
**Created:** [29 December 2020 00:19 UTC](https://discourse.nodered.org/t/i-need-help-to-find-closest-match-from-db/38176 "2020-12-29T00:19:37Z")\
**Posts on this page:** 20\
**Page:** 1

<div class="post-metadata">

**Author:** ![ajbuk21](https://avatars.discourse-cdn.com/v4/letter/a/858c86/32.png) [@ajbuk21](https://discourse.nodered.org/u/ajbuk21)\
**Post date:** [29 December 2020 00:19 UTC](https://discourse.nodered.org/t/i-need-help-to-find-closest-match-from-db/38176/1 "2020-12-29T00:19:38Z")

</div>

hi evryone,

i have been tiring for a few days now to create a flow that will use a light sensor to check a color and i need it too find closest color match to none samples stored in mysql

so fare i have most of it working however i cant get the function too work that finds the closest match, if i make a array within the function it will compare sample to this array fine.  
but what i need it to do is inport the array from the DB and compare it too that and the other problem the number of columns in the DB can change that will change the array size witch is making it harder for me to create the code needed

any help with this would be gratefull

```auto
[{"id":"5af8a725.9a8b4","type":"tab","label":"Results","disabled":false,"info":""},{"id":"865ea1fc.6fe4","type":"mysql","z":"5af8a725.9a8b4","mydb":"42a8cc88.db9fcc","name":"","x":610,"y":260,"wires":[["7f732a3f.bf7f64"]]},{"id":"ae1b973a.53d9e8","type":"function","z":"5af8a725.9a8b4","d":true,"name":"","func":"nearest = -1;\nInteger = 200;\nvar i;\nbestDistanceFoundYet = Integer.MAX_INTEGER;\narray = msg.payload.color[i];\n// We iterate on the array...\nfor (i = 0; i < array.length; i++) {\n // if we found the desired number, we return it.\n if (array[i] == desiredNumber) {\n return array[i];\n } else {\n // else, we consider the difference between the desired number and the current number in the array.\n d = Math.abs(desiredNumber - array[i]);\n if (d < bestDistanceFoundYet) {\n // For the moment, this value is the nearest to the desired number...\n bestDistanceFoundYet = d; // Assign new best distance...\n nearest = array[i];\n }\n }\n}\nreturn nearest;","outputs":1,"noerr":0,"initialize":"","finalize":"","x":1820,"y":440,"wires":[[]]},{"id":"bbf957c.5b1f428","type":"function","z":"5af8a725.9a8b4","name":"","func":"msg.topic = \"SELECT hue From ph_results\";\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","x":440,"y":260,"wires":[["865ea1fc.6fe4"]]},{"id":"6e69f675.2122b","type":"inject","z":"5af8a725.9a8b4","name":"","props":[{"p":"payload"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":240,"y":260,"wires":[["bbf957c.5b1f428","157268ec.8dd1f7"]]},{"id":"157268ec.8dd1f7","type":"function","z":"5af8a725.9a8b4","name":"","func":"msg.payload = \"test\";\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","x":420,"y":320,"wires":[["c886cf3a.7c2a9"]]},{"id":"c886cf3a.7c2a9","type":"serial request","z":"5af8a725.9a8b4","name":"","serial":"118f6787.4d53f","x":600,"y":320,"wires":[["2204ad23.743b9a"]]},{"id":"2204ad23.743b9a","type":"json","z":"5af8a725.9a8b4","name":"","property":"payload","action":"","pretty":true,"x":750,"y":320,"wires":[["fc876bd0.f75b78"]]},{"id":"fc876bd0.f75b78","type":"function","z":"5af8a725.9a8b4","name":"","func":"red = msg.payload[0].Red;\ngreen = msg.payload[1].Green;\nblue = msg.payload[2].Blue;\n\nreturn {payload:[red,green,blue]}\n","outputs":1,"noerr":0,"initialize":"","finalize":"","x":880,"y":320,"wires":[["4a0ed567.2d14d4"]]},{"id":"4a0ed567.2d14d4","type":"color-convert","z":"5af8a725.9a8b4","input":"rgb","output":"hsv","outputType":"array","scaleInput":false,"x":1050,"y":320,"wires":[["a0322a38.566a3","7f732a3f.bf7f64"]]},{"id":"a0322a38.566a3","type":"change","z":"5af8a725.9a8b4","d":true,"name":"","rules":[{"t":"move","p":"payload","pt":"msg","to":"result","tot":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":1230,"y":360,"wires":[["7f732a3f.bf7f64"]]},{"id":"d62b7fda.cacba8","type":"mysql","z":"5af8a725.9a8b4","d":true,"mydb":"42a8cc88.db9fcc","name":"","x":2070,"y":300,"wires":[["c37dde6a.6813f"]]},{"id":"c37dde6a.6813f","type":"debug","z":"5af8a725.9a8b4","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":2150,"y":200,"wires":[]},{"id":"7f732a3f.bf7f64","type":"join","z":"5af8a725.9a8b4","name":"","mode":"custom","build":"array","property":"payload","propertyType":"msg","key":"topic","joiner":"\\n","joinerType":"str","accumulate":false,"timeout":"","count":"2","reduceRight":false,"reduceExp":"","reduceInit":"","reduceInitType":"num","reduceFixup":"","x":1420,"y":260,"wires":[["b684eacd.cc61a8","17297fc3.5110d8"]]},{"id":"17297fc3.5110d8","type":"debug","z":"5af8a725.9a8b4","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":1710,"y":80,"wires":[]},{"id":"b684eacd.cc61a8","type":"function","z":"5af8a725.9a8b4","name":"","func":"\n\n//var counts = [msg.payload[0][0],msg.payload[0][1],msg.payload[0][2],msg.payload[0][3],msg.payload[0][4],msg.payload[0][5],msg.payload[0][6],msg.payload[0][7]]; //only checks first entry\nvar counts =[10,50,100,150,300,360]; //works\n\n goal = msg.payload[1][0]; // works\n //goal = 6;\n\nvar closest = counts.reduce(function(prev, curr) {\n return (Math.abs(curr - goal) < Math.abs(prev - goal) ? curr : prev);\n});\n//msg.topic = \"SELECT value From ph_results WHERE hue='\"+closest+\"'\";\nmsg.payload = closest;\nreturn msg;\n//return counts;\n","outputs":1,"noerr":0,"initialize":"","finalize":"","x":1660,"y":240,"wires":[["17297fc3.5110d8","5132d256.072394"]]},{"id":"2b9352fb.fc604e","type":"inject","z":"5af8a725.9a8b4","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":1390,"y":140,"wires":[["b684eacd.cc61a8"]]},{"id":"5132d256.072394","type":"function","z":"5af8a725.9a8b4","name":"","func":"var hue = msg.payload.hue\nmsg.topic = \"SELECT value From ph_results WHERE hue='\"+hue+\"'\";\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","x":1840,"y":260,"wires":[["d62b7fda.cacba8","c37dde6a.6813f"]]},{"id":"42a8cc88.db9fcc","type":"MySQLdatabase","name":"tests","host":"127.0.0.1","port":"3306","db":"test","tz":"","charset":"UTF8"},{"id":"118f6787.4d53f","type":"serial-port","serialport":"/dev/ttyACM0","serialbaud":"115200","databits":"8","parity":"none","stopbits":"1","waitfor":"","dtr":"none","rts":"none","cts":"none","dsr":"none","newline":"\\n","bin":"false","out":"char","addchar":"\\n","responsetimeout":"15000"}]

```

 ![Untitled](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/4/b/4b04f371b097f39ce2f7a69c2b05e6fc2b5ce1fb.png)

---

<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:** [29 December 2020 11:19 UTC](https://discourse.nodered.org/t/i-need-help-to-find-closest-match-from-db/38176/2 "2020-12-29T11:19:07Z")

</div>

Please tell us the structure of the database, including how colours are stored and what form the colour from the sensor is in.

---

<div class="post-metadata">

**Author:** ![ajbuk21](https://avatars.discourse-cdn.com/v4/letter/a/858c86/32.png) [@ajbuk21](https://discourse.nodered.org/u/ajbuk21)\
**Post date:** [29 December 2020 11:50 UTC](https://discourse.nodered.org/t/i-need-help-to-find-closest-match-from-db/38176/3 "2020-12-29T11:50:07Z")

</div>

im converting the color sensors raw data to HSV, then saving that to database Hue=hue, Saturation=sat, Value=brt. my known ph value is value (yes im having some conflicting problems using value and will be changed soon),

what im tring to do is use the color sensor to read a ph test result and use that to compare to known color sample saved in database to return PH value

the problem is the color sensor is way to senitive so i cant save all ph values, i want to find the closest color match,

so the function will receive a value from sensor lets say hue=10, the function then needs to check data base for hue=10 and return with hue=11 was closest match, then my next function will recheck database for hue=11 and return value=8.0

![Untitled2](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/b/3/b3e34bcf317746b7fed67d070b98351eaaed4050.png)

---

<div class="post-metadata">

**Author:** ![ajbuk21](https://avatars.discourse-cdn.com/v4/letter/a/858c86/32.png) [@ajbuk21](https://discourse.nodered.org/u/ajbuk21)\
**Post date:** [29 December 2020 14:16 UTC](https://discourse.nodered.org/t/i-need-help-to-find-closest-match-from-db/38176/4 "2020-12-29T14:16:37Z")

</div>

![Untitled](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/4/b/4b04f371b097f39ce2f7a69c2b05e6fc2b5ce1fb.png)

---

<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:** [29 December 2020 14:17 UTC](https://discourse.nodered.org/t/i-need-help-to-find-closest-match-from-db/38176/5 "2020-12-29T14:17:50Z")

</div>

What did you mean when you said the number of columns in the db can change? Do you mean the number of rows?

---

<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:** [29 December 2020 14:26 UTC](https://discourse.nodered.org/t/i-need-help-to-find-closest-match-from-db/38176/6 "2020-12-29T14:26:25Z")

</div>

I corrected your initial post so the flow is importable, you had the image inside the backticks at the end of the flow.

I have just noticed that you are new to the forum, so welcome.

The first thing I would do is to sort the query results so they come from the database in hue order (you can do that in the sql statement using ORDER BY). That will make it much easier when you get to the lookup stage.

Then in the Join node change the type to Key/Value so you get in one message the complete array from the database and separately the value from the other stream. [This example](https://cookbook.nodered.org/basic/join-streams) from the cookbook shows how that can be done.  
See if you can get that going.

---

<div class="post-metadata">

**Author:** ![ajbuk21](https://avatars.discourse-cdn.com/v4/letter/a/858c86/32.png) [@ajbuk21](https://discourse.nodered.org/u/ajbuk21)\
**Post date:** [29 December 2020 14:37 UTC](https://discourse.nodered.org/t/i-need-help-to-find-closest-match-from-db/38176/7 "2020-12-29T14:37:37Z")

</div>

> [@Colin](#):
>
> type to Key/Value

yes sorry the number of rows change

---

<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:** [29 December 2020 14:45 UTC](https://discourse.nodered.org/t/i-need-help-to-find-closest-match-from-db/38176/8 "2020-12-29T14:45:07Z")

</div>

My previous suggestion assumes that, so I think that is the way to go. Then it doesn't matter how many rows there are, you just have to search through the array looking for the nearest.

---

<div class="post-metadata">

**Author:** ![ajbuk21](https://avatars.discourse-cdn.com/v4/letter/a/858c86/32.png) [@ajbuk21](https://discourse.nodered.org/u/ajbuk21)\
**Post date:** [29 December 2020 15:40 UTC](https://discourse.nodered.org/t/i-need-help-to-find-closest-match-from-db/38176/9 "2020-12-29T15:40:28Z")

</div>

so i have sorted the flow and removed one of the flows now i have it just look at the database,  
and out of the database im getting a array, im confused on how to search that array.  
i can do var counts = [msg.payload[0].hue,msg.payload[1].hue,msg.payload[2].hue,msg.payload[3].hue,msg.payload[4].hue,msg.payload[5].hue,msg.payload[6].hue,msg.payload[7].hue,msg.payload[8].hue,msg.payload[9].hue];

but how do i shorten this and have the option of it knowing that there is lets say only array of 5 and not 10 ?

---

<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:** [29 December 2020 15:44 UTC](https://discourse.nodered.org/t/i-need-help-to-find-closest-match-from-db/38176/10 "2020-12-29T15:44:39Z")

</div>

Show us what you have coming out of the Join node.

---

<div class="post-metadata">

**Author:** ![ajbuk21](https://avatars.discourse-cdn.com/v4/letter/a/858c86/32.png) [@ajbuk21](https://discourse.nodered.org/u/ajbuk21)\
**Post date:** [29 December 2020 15:49 UTC](https://discourse.nodered.org/t/i-need-help-to-find-closest-match-from-db/38176/11 "2020-12-29T15:49:30Z")

</div>

> [@Colin](#):
>
> ORDER BY

"Message missing key property 'msg.topic' - cannot add to object"

---

<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:** [29 December 2020 15:56 UTC](https://discourse.nodered.org/t/i-need-help-to-find-closest-match-from-db/38176/12 "2020-12-29T15:56:08Z")

</div>

Did you import the example from the cookbook and understand how it works? You must set a topic on each of the inputs so the Join node knows what key to use for each stream. The error means that a message coming in does not have a topic set.

---

<div class="post-metadata">

**Author:** ![ajbuk21](https://avatars.discourse-cdn.com/v4/letter/a/858c86/32.png) [@ajbuk21](https://discourse.nodered.org/u/ajbuk21)\
**Post date:** [29 December 2020 16:06 UTC](https://discourse.nodered.org/t/i-need-help-to-find-closest-match-from-db/38176/13 "2020-12-29T16:06:46Z")

</div>

yes, the only problem is the data is coming out of nodes that i cant change topic information on, i have been trying to slit the information coming out the nodes and moving it to topic then ruining it through the slit node but im not having much luck

---

<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:** [29 December 2020 16:30 UTC](https://discourse.nodered.org/t/i-need-help-to-find-closest-match-from-db/38176/14 "2020-12-29T16:30:42Z")

</div>

Feed it through a Change node that sets msg.topic.

---

<div class="post-metadata">

**Author:** ![ajbuk21](https://avatars.discourse-cdn.com/v4/letter/a/858c86/32.png) [@ajbuk21](https://discourse.nodered.org/u/ajbuk21)\
**Post date:** [29 December 2020 16:45 UTC](https://discourse.nodered.org/t/i-need-help-to-find-closest-match-from-db/38176/15 "2020-12-29T16:45:40Z")

</div>

![Untitled3](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/3/4/347339afa8286cd889d7176246bae84b50217bcb.png)

am i being dumb or am i missing something?

---

<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:** [29 December 2020 16:54 UTC](https://discourse.nodered.org/t/i-need-help-to-find-closest-match-from-db/38176/16 "2020-12-29T16:54:39Z")

</div>

You are trying to set msg.payload to the value currently in msg.topic. Perhaps you meant Set msg.topic to the value in msg.payload. If that is what you meant why are you trying to do that? Why not just set it to a string "db" or something?

---

<div class="post-metadata">

**Author:** ![ajbuk21](https://avatars.discourse-cdn.com/v4/letter/a/858c86/32.png) [@ajbuk21](https://discourse.nodered.org/u/ajbuk21)\
**Post date:** [29 December 2020 17:14 UTC](https://discourse.nodered.org/t/i-need-help-to-find-closest-match-from-db/38176/17 "2020-12-29T17:14:23Z")

</div>

ok so i was having a dumb moment sorry..  
i have the join working now (i hope)  
now my function is not working it is now just returning all of the array and not my closest match and now i really do need help as i have not a clue how to get this working

 ![Untitled4](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/f/b/fba5678615f36a1398f8370af3cc2fc0386788bc.png)

---

<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:** [29 December 2020 17:26 UTC](https://discourse.nodered.org/t/i-need-help-to-find-closest-match-from-db/38176/18 "2020-12-29T17:26:01Z")

</div>

I don't think I would try and use `reduce` for this, though perhaps it can be done. Since the array is, hopefully, sorted, then I would just loop through the array till you find the nearest. It would be quicker to do a binary search through the array, but if there only a small number of rows then it probably isn't worth it. At least get it going first just by stepping through the array. As it is sorted you won't need to go right through.  
Also, if you adjust your query to fetch the value as well as the hue, then you won't have to do another db access to get the value.

---

<div class="post-metadata">

**Author:** ![ajbuk21](https://avatars.discourse-cdn.com/v4/letter/a/858c86/32.png) [@ajbuk21](https://discourse.nodered.org/u/ajbuk21)\
**Post date:** [29 December 2020 17:34 UTC](https://discourse.nodered.org/t/i-need-help-to-find-closest-match-from-db/38176/19 "2020-12-29T17:34:24Z")

</div>

how would i go about creating a loop through the array to find the nearest?  
my java is very limited

---

<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:** [29 December 2020 18:00 UTC](https://discourse.nodered.org/t/i-need-help-to-find-closest-match-from-db/38176/20 "2020-12-29T18:00:46Z")

</div>

It isn't Java, it is JavaScript, which is, confusingly, completely different.  
I am on my phone at the moment, you can use  
`for ( i=0; i<array.length ; I++) {`

[Next page](https://discourse.nodered.org/t/i-need-help-to-find-closest-match-from-db/38176.md?page=2)