Query MySQL and displaying it in a dashboard Chart

General
1

Hi guys, a new user to node-red here.

How do I query MySQL in node-red like e.g. I just want a normal chart for the number of times a certain event triggers and display it in a chart? I have the query ready, but I am not too sure on how to display it on the chart, the injected nodes has been replying with timestamp only.

This is my current function code

msg.topic = "SELECT COUNT(*) 'DINION Camera 2 : No. Of Times Triggered' FROM bvcddb.objectinfield WHERE ID = '172.27.185.90'";
return msg;

Would highly appreciate if someone could solve this for me

Regards,

Jack

2

Show us what you did. Share a screen shot and you flow.

3

image
image1920Ă—1080 314 KB

For the top flow, I have tried writing MQTT message to the database, so its saving data to MySQL.

for the bottom flow, what I am currently trying to achieve is to just hoping the program returning me a value, and able to display it on the dashboard.

Note : All this are just for testing purposes

Regads,

Jack

4

Ok, on the surface things look normal but without sharing your flow (ctrl+e) difficult to help further.

5

Hi Steve,
as I am a new user, node-red does not allow me to upload the flow

are there any other possible solution for me to upload flows.json?

6

You don't need to upload.

Copy flow to clipboard then paste into your reply (just like screenshots)

In order to keep code more readable and importable it is important to surround your code with three backticks
```
like this
```

7 
[{"id":"28abaa7d.30d2e6","type":"tab","label":"Flow 1","disabled":false,"info":""},{"id":"a3966552.332fb8","type":"mqtt-broker","name":"","broker":"localhost","port":"1883","clientid":"","usetls":false,"protocolVersion":"4","keepalive":"60","cleansession":true,"birthTopic":"","birthQos":"0","birthPayload":"","birthMsg":{},"closeTopic":"","closeQos":"0","closePayload":"","closeMsg":{},"willTopic":"","willQos":"0","willPayload":"","willMsg":{},"sessionExpiry":""},{"id":"a8651bd.a83c8e8","type":"MySQLdatabase","name":"","host":"127.0.0.1","port":"3306","db":"bvcddb","tz":"","charset":"UTF8"},{"id":"62a9cea8.ba3c5","type":"ui_tab","name":"Info","icon":"dashboard","disabled":false,"hidden":false},{"id":"aa29d8.c5ff3628","type":"ui_tab","name":"Info","icon":"dashboard","disabled":false,"hidden":false},{"id":"816f6911.01f668","type":"ui_group","name":"Camera","tab":"62a9cea8.ba3c5","order":1,"disp":true,"width":"20","collapse":false},{"id":"2ccc87b9.8b4738","type":"ui_base","theme":{"name":"theme-light","lightTheme":{"default":"#0094CE","baseColor":"#0094CE","baseFont":"-apple-system,BlinkMacSystemFont,Segoe UI,Roboto,Oxygen-Sans,Ubuntu,Cantarell,Helvetica Neue,sans-serif","edited":true,"reset":false},"darkTheme":{"default":"#097479","baseColor":"#097479","baseFont":"-apple-system,BlinkMacSystemFont,Segoe UI,Roboto,Oxygen-Sans,Ubuntu,Cantarell,Helvetica Neue,sans-serif","edited":false},"customTheme":{"name":"Untitled Theme 1","default":"#4B7930","baseColor":"#4B7930","baseFont":"-apple-system,BlinkMacSystemFont,Segoe UI,Roboto,Oxygen-Sans,Ubuntu,Cantarell,Helvetica Neue,sans-serif"},"themeState":{"base-color":{"default":"#0094CE","value":"#0094CE","edited":false},"page-titlebar-backgroundColor":{"value":"#0094CE","edited":false},"page-backgroundColor":{"value":"#fafafa","edited":false},"page-sidebar-backgroundColor":{"value":"#ffffff","edited":false},"group-textColor":{"value":"#1bbfff","edited":false},"group-borderColor":{"value":"#ffffff","edited":false},"group-backgroundColor":{"value":"#ffffff","edited":false},"widget-textColor":{"value":"#111111","edited":false},"widget-backgroundColor":{"value":"#0094ce","edited":false},"widget-borderColor":{"value":"#ffffff","edited":false},"base-font":{"value":"-apple-system,BlinkMacSystemFont,Segoe UI,Roboto,Oxygen-Sans,Ubuntu,Cantarell,Helvetica Neue,sans-serif"}},"angularTheme":{"primary":"indigo","accents":"blue","warn":"red","background":"grey"}},"site":{"name":"Node-RED Dashboard","hideToolbar":"false","allowSwipe":"false","lockMenu":"false","allowTempTheme":"true","dateFormat":"DD/MM/YYYY","sizes":{"sx":48,"sy":48,"gx":6,"gy":6,"cx":6,"cy":6,"px":0,"py":0}}},{"id":"34937132.e7f23e","type":"mqtt in","z":"28abaa7d.30d2e6","name":"Object in field","topic":"bvcd/camera/event/obj_in_field","qos":"0","datatype":"json","broker":"a3966552.332fb8","nl":false,"rap":true,"rh":0,"x":90,"y":140,"wires":[["8c6716e.cdd60e8","eee67ad9.4dd0c8"]]},{"id":"8abe112d.82cad","type":"mysql","z":"28abaa7d.30d2e6","mydb":"a8651bd.a83c8e8","name":"","x":580,"y":260,"wires":[[]]},{"id":"8c6716e.cdd60e8","type":"debug","z":"28abaa7d.30d2e6","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":330,"y":80,"wires":[]},{"id":"eee67ad9.4dd0c8","type":"function","z":"28abaa7d.30d2e6","name":"","func":"var test = msg.payload;\nvar ID = test.ID;\nvar UTC = test.UTC;\nvar ObjectID = test.ObjectID;\nvar eventType = test.eventType;\nvar ruleID = test.ruleID;\n\nmsg.payload = [ID, UTC, ObjectID, eventType, ruleID];\n\nmsg.topic = 'INSERT INTO objectinfield(ID, UTC, ObjectID, eventType, ruleID) VALUES (?,?,?,?,?);';\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":340,"y":180,"wires":[["8abe112d.82cad"]]},{"id":"196385ff.6e973a","type":"mqtt in","z":"28abaa7d.30d2e6","name":"loitering","topic":"bvcd/camera/event/loitering","qos":"0","datatype":"json","broker":"a3966552.332fb8","nl":false,"rap":true,"rh":0,"x":70,"y":380,"wires":[["ded1ea52.ca0b78","f849327f.fe334"]]},{"id":"ded1ea52.ca0b78","type":"function","z":"28abaa7d.30d2e6","name":"","func":"var test = msg.payload;\nvar ID = test.ID;\nvar UTC = test.UTC;\nvar ObjectID = test.ObjectID;\nvar eventType = test.eventType;\nvar ruleID = test.ruleID;\n\nmsg.payload = [ID, UTC, ObjectID, eventType, ruleID];\n\nmsg.topic = 'INSERT INTO loitering(ID, UTC, ObjectID, eventType, ruleID) VALUES (?,?,?,?,?);';\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":340,"y":380,"wires":[["8abe112d.82cad"]]},{"id":"f849327f.fe334","type":"debug","z":"28abaa7d.30d2e6","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":330,"y":280,"wires":[]},{"id":"ca3b867f.e70db8","type":"function","z":"28abaa7d.30d2e6","name":"","func":"msg.topic = \"SELECT COUNT(*) AS 'DINION Camera 2 : No. Of Times Triggered' FROM bvcddb.objectinfield WHERE ID = '172.27.185.90'\";\nreturn msg;\n","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":340,"y":500,"wires":[["f110b5af.da4cc8"]]},{"id":"ecce8e56.d1347","type":"inject","z":"28abaa7d.30d2e6","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"5","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":110,"y":500,"wires":[["ca3b867f.e70db8"]]},{"id":"f110b5af.da4cc8","type":"mysql","z":"28abaa7d.30d2e6","mydb":"a8651bd.a83c8e8","name":"","x":540,"y":500,"wires":[["5a96c769.669858","40db8166.c4d","9e5d1a5a.b6ce38"]]},{"id":"816efe8c.b0ee8","type":"function","z":"28abaa7d.30d2e6","name":"","func":"msg.topic = \"SELECT COUNT(*) AS 'DINION Camera 2 : No. Of Times Triggered' FROM bvcddb.loitering WHERE ID = '172.27.185.90'\";\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":340,"y":580,"wires":[["79d3527c.90103c"]]},{"id":"c6b97ee3.9b01b","type":"inject","z":"28abaa7d.30d2e6","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"5","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":110,"y":580,"wires":[["816efe8c.b0ee8"]]},{"id":"79d3527c.90103c","type":"mysql","z":"28abaa7d.30d2e6","mydb":"a8651bd.a83c8e8","name":"","x":540,"y":580,"wires":[[]]},{"id":"a3eb70ee.27f4c","type":"function","z":"28abaa7d.30d2e6","name":"","func":"msg.topic = \"SELECT COUNT(*) AS 'FLEXIDOME Camera 1 : No. Of Times Triggered' FROM bvcddb.objectinfield WHERE ID = '172.27.185.91'\";\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":340,"y":660,"wires":[["33c68d1e.2f8842"]]},{"id":"1e9c0424.36faac","type":"inject","z":"28abaa7d.30d2e6","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"5","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":110,"y":660,"wires":[["a3eb70ee.27f4c"]]},{"id":"33c68d1e.2f8842","type":"mysql","z":"28abaa7d.30d2e6","mydb":"a8651bd.a83c8e8","name":"","x":540,"y":660,"wires":[[]]},{"id":"5a96c769.669858","type":"ui_chart","z":"28abaa7d.30d2e6","name":"","group":"816f6911.01f668","order":0,"width":"10","height":"7","label":"chart","chartType":"line","legend":"false","xformat":"HH:mm:ss","interpolate":"linear","nodata":"","dot":false,"ymin":"","ymax":"","removeOlder":1,"removeOlderPoints":"","removeOlderUnit":"60","cutout":0,"useOneColor":false,"useUTC":false,"colors":["#1f77b4","#aec7e8","#ff7f0e","#2ca02c","#98df8a","#d62728","#ff9896","#9467bd","#c5b0d5"],"outputs":1,"useDifferentColor":false,"x":710,"y":560,"wires":[[]]},{"id":"40db8166.c4d","type":"ui_text","z":"28abaa7d.30d2e6","group":"816f6911.01f668","order":1,"width":0,"height":0,"name":"","label":"Object In Field","format":"{{msg.payload}}","layout":"row-left","x":740,"y":500,"wires":[]},{"id":"9e5d1a5a.b6ce38","type":"debug","z":"28abaa7d.30d2e6","name":"","active":false,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":730,"y":440,"wires":[]}]
8

The only issue i see is you are attempting tp put msg.payload from the mySQL select directly into a chart and a ui text.

If you look at the output of the mySQL node you will see it is an array - a chart expects the value to display to be in msg.payload and the topic to be the name of the series (this is explained in the chart built in help).
image

Use a change node to move the result into msg.payload and set the topic to a nice name

e.g...

image
image721Ă—219 23 KB

modified flow...

[{"id":"40db8166.c4d","type":"ui_text","z":"28abaa7d.30d2e6","group":"816f6911.01f668","order":1,"width":0,"height":0,"name":"","label":"Object In Field","format":"{{msg.payload}}","layout":"row-left","x":960,"y":560,"wires":[]},{"id":"88e8b41.ced5648","type":"change","z":"28abaa7d.30d2e6","name":"move 1st row value to payload & set topic","rules":[{"t":"set","p":"payload","pt":"msg","to":"payload[0].trigger_count","tot":"msg"},{"t":"set","p":"topic","pt":"msg","to":"CAMERA 2 Trigger Count","tot":"str"}],"action":"","property":"","from":"","to":"","reg":false,"x":820,"y":500,"wires":[["40db8166.c4d","5a96c769.669858","c6165000.6b97e"]]},{"id":"f110b5af.da4cc8","type":"mysql","z":"28abaa7d.30d2e6","mydb":"a8651bd.a83c8e8","name":"","x":540,"y":500,"wires":[["9e5d1a5a.b6ce38","88e8b41.ced5648"]]},{"id":"5a96c769.669858","type":"ui_chart","z":"28abaa7d.30d2e6","name":"","group":"816f6911.01f668","order":0,"width":"10","height":"7","label":"chart","chartType":"line","legend":"false","xformat":"HH:mm:ss","interpolate":"linear","nodata":"","dot":false,"ymin":"","ymax":"","removeOlder":1,"removeOlderPoints":"","removeOlderUnit":"60","cutout":0,"useOneColor":false,"useUTC":false,"colors":["#1f77b4","#aec7e8","#ff7f0e","#2ca02c","#98df8a","#d62728","#ff9896","#9467bd","#c5b0d5"],"outputs":1,"useDifferentColor":false,"x":930,"y":600,"wires":[[]]},{"id":"c6165000.6b97e","type":"debug","z":"28abaa7d.30d2e6","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":1050,"y":440,"wires":[]},{"id":"ca3b867f.e70db8","type":"function","z":"28abaa7d.30d2e6","name":"","func":"msg.topic = \"SELECT COUNT(*) AS 'trigger_count' FROM bvcddb.objectinfield WHERE ID = '172.27.185.90'\";\nreturn msg;\n","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":340,"y":500,"wires":[["f110b5af.da4cc8"]]},{"id":"9e5d1a5a.b6ce38","type":"debug","z":"28abaa7d.30d2e6","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":730,"y":440,"wires":[]},{"id":"ecce8e56.d1347","type":"inject","z":"28abaa7d.30d2e6","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"5","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":110,"y":500,"wires":[["ca3b867f.e70db8"]]},{"id":"816f6911.01f668","type":"ui_group","name":"Camera","tab":"62a9cea8.ba3c5","order":1,"disp":true,"width":"20","collapse":false},{"id":"a8651bd.a83c8e8","type":"MySQLdatabase","name":"","host":"127.0.0.1","port":"3306","db":"bvcddb","tz":"","charset":"UTF8"},{"id":"62a9cea8.ba3c5","type":"ui_tab","name":"Info","icon":"dashboard","disabled":false,"hidden":false}]

PS: this is untested since i dont have your data or database.

1 Like
9

Hi Steve,

Much appreciated. Will take some time to read through and I have copy and pasted your flow, it worked out. Thanks alot pal!

Regards,

Jack

1 Like
10

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