MySQL query for a range of dates

Hello Sir,
I stuck in a mysql query. I want to pick two dates and want to display the data in tabular form on dashboard after fetching data from database. I ran the query in mysql workbench and it was successfull. But, while running the same query in msg.topic in function node the result shows msg.payload[0]. The flows is exported here as:
[{"id":"ea171992.c2c538","type":"function","z":"99e51cc7.c42cb","name":"","func":"var start = msg.payload.from;\nvar end = msg.payload.to;\nmsg.topic =\"SELECT * FROM random where data_date between \"+start+\" and \"+end+\";\";\nreturn msg;\n","outputs":1,"noerr":0,"initialize":"","finalize":"","x":330,"y":80,"wires":[["4a583b85.12a814"]]},{"id":"4a583b85.12a814","type":"mysql","z":"99e51cc7.c42cb","mydb":"4408c380.b7cffc","name":"","x":500,"y":80,"wires":[["cae47677.83b378","4e657f46.64fb7"]]},{"id":"cae47677.83b378","type":"debug","z":"99e51cc7.c42cb","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":670,"y":80,"wires":[]},{"id":"4e657f46.64fb7","type":"ui_table","z":"99e51cc7.c42cb","group":"4d5e7557.929f7c","name":"","order":3,"width":0,"height":0,"columns":[],"outputs":0,"cts":false,"x":640,"y":160,"wires":[]},{"id":"a8ca02ab.c91c6","type":"ui_template","z":"99e51cc7.c42cb","group":"4d5e7557.929f7c","name":"form_date","order":4,"width":0,"height":0,"format":"<p>From: <input type=\"text\" name=\"from\" value=\"from\" ng-model=\"from\" placeholder=\"YYYY-MM-DD\" /></p>\n<p>To: <input type=\"text\" name=\"to\" value=\"to\" ng-model=\"to\" placeholder=\"YYYY-MM-DD\"/></p>\n<md-button ng-click=\"send({payload:{from:from, to:to}})\">Submit</md-button>\n","storeOutMessages":true,"fwdInMessages":true,"resendOnRefresh":true,"templateScope":"local","x":120,"y":80,"wires":[["ea171992.c2c538"]]},{"id":"4408c380.b7cffc","type":"MySQLdatabase","z":"","name":"dischargeDB","host":"localhost","port":"3306","db":"nathpa","tz":"GMT + 5:30"},{"id":"4d5e7557.929f7c","type":"ui_group","z":"","name":"Sem","tab":"3871e724.85cf98","order":1,"disp":true,"width":"4","collapse":false},{"id":"3871e724.85cf98","type":"ui_tab","z":"","name":"Home","icon":"dashboard","disabled":false,"hidden":false}]
Please suggest me where i am wrong?
Thanks in advance.

If you put a debug node on the output of the function node - set to display the Complete msg object - what does it show?

Look at the msg.topic and how does it compare with the query that works in mysql workbench?

Thanks for prompt reply sir.
I put the debug node and the result is " 8/7/2020, 8:10:40 PMnode: b8d50e4d.1403fSELECT * FROM random where data_date between 2020-07-01 and 2020-07-29; : msg : Object

object

payload: object

socketid: "bWaTyjxjRMIMTg7wAAAC"

_msgid: "62e54e5d.4da2e"

topic: "SELECT * FROM random where data_date between 2020-07-01 and 2020-07-29;"". The flow is as:

[{"id":"ea171992.c2c538","type":"function","z":"99e51cc7.c42cb","name":"","func":"var start = msg.payload.from;\nvar end = msg.payload.to;\nmsg.topic =\"SELECT * FROM random where data_date between \"+start+\" and  \"+end+\";\";\nreturn msg;\n","outputs":1,"noerr":0,"initialize":"","finalize":"","x":330,"y":80,"wires":[["4a583b85.12a814","b8d50e4d.1403f"]]},{"id":"4a583b85.12a814","type":"mysql","z":"99e51cc7.c42cb","mydb":"4408c380.b7cffc","name":"","x":500,"y":80,"wires":[["cae47677.83b378","4e657f46.64fb7"]]},{"id":"cae47677.83b378","type":"debug","z":"99e51cc7.c42cb","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":670,"y":80,"wires":[]},{"id":"4e657f46.64fb7","type":"ui_table","z":"99e51cc7.c42cb","group":"4d5e7557.929f7c","name":"","order":3,"width":0,"height":0,"columns":[],"outputs":0,"cts":false,"x":640,"y":160,"wires":[]},{"id":"a8ca02ab.c91c6","type":"ui_template","z":"99e51cc7.c42cb","group":"4d5e7557.929f7c","name":"form_date","order":4,"width":0,"height":0,"format":"<p>From: <input type=\"text\" name=\"from\" value=\"from\" ng-model=\"from\" placeholder=\"YYYY-MM-DD\" /></p>\n<p>To: <input type=\"text\" name=\"to\" value=\"to\" ng-model=\"to\" placeholder=\"YYYY-MM-DD\"/></p>\n<md-button ng-click=\"send({payload:{from:from, to:to}})\">Submit</md-button>\n","storeOutMessages":true,"fwdInMessages":true,"resendOnRefresh":true,"templateScope":"local","x":120,"y":80,"wires":[["ea171992.c2c538"]]},{"id":"b8d50e4d.1403f","type":"debug","z":"99e51cc7.c42cb","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":460,"y":160,"wires":[]},{"id":"4408c380.b7cffc","type":"MySQLdatabase","z":"","name":"dischargeDB","host":"localhost","port":"3306","db":"nathpa","tz":"GMT + 5:30"},{"id":"4d5e7557.929f7c","type":"ui_group","z":"","name":"Sem","tab":"3871e724.85cf98","order":1,"disp":true,"width":"4","collapse":false},{"id":"3871e724.85cf98","type":"ui_tab","z":"","name":"Home","icon":"dashboard","disabled":false,"hidden":false}]

You don't need to provide the flow a second time. So the debug shows the query is:
SELECT * FROM random where data_date between 2020-07-01 and 2020-07-29;
now what does the query you used in mysql workbench look like?

Compare the two...are there any differences?

Yes Sir,
There is difference in date value. In mysql workbench
SELECT * FROM random where data_date between '2020-07-01' and '2020-07-29';
date values are withing quotes while node red function node these are without quotes. I think this might be reason. So, please suggest me how to include quotes as these are values received from user interface node.

Try changing the statement:

msg.topic ="SELECT * FROM random where data_date between '"+start+"' and  '"+end+"';";
1 Like

Thank you so much sir.
It worked.
Thank you once again.

1 Like

This is why I had you add the debug node - so you could see the difference and then realize that you were not running the same query. Using the debug node is a great way to see what you are actually doing vrs what you think you are doing :smile:

1 Like