MySql Query with Switch Node function - Help Needed

[Richard Sears]

Hello - 

I am kind of stuck and was hoping someone could help me out. I have a simple mysql query:

msg.topic = "SELECT home FROM presence WHERE presenceid LIKE 1";
msg.payload = [msg.payload];
return msg;

this is what the query is returning:

5/27/2016, 11:54:23 PM516124b1.b05e5cSELECT home FROM presence WHERE presenceid LIKE ? : msg.payload : array [1][ { "home": 1 } ]

home will only ever return a 0 or a 1.

I want to use the switch function to do something different depending on getting a 1 (I am home) or a 0 (I am not home)  back, but no matter what I select in the switch function, I cannot get it to match my output aboveand do what I want it to do.
I have tried every combination that I could think of:
[{"home":1}]
{"home":1}
"home":1
home:1
:1
 I have also tried mgs, flow, global , string and number as the match type, no matter what I put in, I cannot seem to get a match and move forward. 
Any help would be greatly appreciated.

[Mark Setrem]

You are returning an array with an object inside.
So you need to select the first item of the array, then the home bit.

If you were using a function node this would be
var theBitYouWantIs = msg.payload[0].home

[Richard Sears]

Thanks for the quick reply Mark, but I am afraid I am a bit lost. In python, no problem, but new to node-red so not sure where to go with your suggestion.

Would I need to update my SQL function something like this:

msg.topic = "SELECT home FROM presence WHERE presenceid LIKE ?";

var theBitYouWantIs = msg.payload[0].

home
msg.payload = [theBitYouWantIs];
return msg;

Or create a new function after the select?

Thanks

[Richard Sears]

Mark - 

Nevermind, using your example just created another function and it worked great. 

Thank you so much for the quick reply and spot-on suggestion!

[Mark Setrem]

Assuming your flow is

Function (define query) -> sql ->

Than I'm pretty sure you don't need your msg.payload in your initial function.

As you have it the query gets passed in msg.topic
Whatever is in msg.payload gets overwritten by the output of the sql node.

Getting your head around JavaScript objects and arrays takes a while but there are some good tutorials you can find with your favourite search engine of choice

[JR01]

Hello Richard, as a matter of interest, how do you determine presence, some sensors, or how?

[Richard Sears]

Hi JR01 - 

I am using the node-red ping app to ping my cell phone which has a static DHCP IP address assigned to it! My goal is to set it up for the entire family and then monitor who is home, etc and customize greetings and other settings based on who might be home. 

I am much more a Python guy, just trying out the Node-Red stuff to see if it is going to be any easier to work with - its difficult since I want to keep running back to python, but I am going to give it a good try!

[Walter Kraembring]

I am much more a Python guy

Myself I am also much into Python (and learning javascript) and now facing an "environment" where I need to combine some "older" code I wrote in Python with Node-RED where javascript and html rules. So what I have done is re-used my old code and created a number of dedicated services written in Python (running under Linux in Multiple number of RPi's) exchanging events & data with Node-RED using MQTT. This is just working perfect and it makes my maintenance not too difficult. 

BR Walter

[Julian Knight]

This might not work so well depending on what phone it is. iPhones in particular disconnect from WiFi when in standby.

For myself, I'm using an ARP scan so I don't have to worry about having fixed IP addresses. I have a custom input file which allows me to track specific mac addresses rather than just generic types. I run the scan every minute and output to MQTT with a timestamp so that I can always see when a MAC address was last seen.

[JR01]

Wow, great ideas of determining presence, must add to my todo list, thanks all!