How can i create a table from a DB?

Hey! i'm new on node red, and i'm doing a project with ESP32 and node red.

I'm sending values using MQTT to a database (Mysql). I read the values from the DB but i don't know how to show the DB table on the node red. I searched everywhere in internet and tried some functions but no results. i'm stuck with this one week and don't know where to find a solution. Could you help me? thanks!

Hi @DAVIDLL, welcome to the forum.

You seem to be in right path. I can see that you have a ui_table in your flow. It would be just a matter of configuring this node correctly. Did you read teh node documentation and tried to use it ?

1 Like

Hi @Andrei, yes! i checked the documentation and i configured the node to have three columns, and i tried doing a function that receives the values from the data base and write in the table but, theres something bad. I show you in the next two pictures:

Export your flow and paste it in a response - make sure to read this: How to share code or flow json first.

Also show some more of the data being retrieved.

The most important thing is that you provide an array of objects to the ui_table node. The picture in your first flow shows that your flow is providing the data perfectly formatted. Well done.

If you connect the ui_table node directly to the output of the database node the table should be populated, even if you do not add any configuration field to the ui_node (just input the name of the dashboard group and its size).

Now, if you want to enter the configuration data in the ui_node then you need to make sure that the name of the Property in the ui_configuration matches exactly the name of the keys in the object you pass to the ui_node.

Eg. Configure Property as "Consumo" not "ConsumoWh" so as it matched with the object key (Consumo).

1 Like

Hi, heres my flow:

[{"id":"eb8801de.74904","type":"tab","label":"Flow 4","disabled":false,"info":""},{"id":"97c51f94.20b42","type":"mqtt in","z":"eb8801de.74904","name":"","topic":"/local/CT/00:00h","qos":"2","datatype":"auto","broker":"a1041d14.0ae6d","x":220,"y":80,"wires":[["44a790c9.535f6"]]},{"id":"5d6a5eae.9ea0b","type":"mqtt in","z":"eb8801de.74904","name":"","topic":"/local/CT/02:00h","qos":"2","datatype":"auto","broker":"a1041d14.0ae6d","x":220,"y":120,"wires":[["d56a3f87.6c79f"]]},{"id":"5b408924.841a28","type":"mqtt in","z":"eb8801de.74904","name":"","topic":"/local/CT/04:00h","qos":"2","datatype":"auto","broker":"a1041d14.0ae6d","x":220,"y":160,"wires":[["101227a4.0ceb18"]]},{"id":"bee8e1c.7cc692","type":"mqtt in","z":"eb8801de.74904","name":"","topic":"/local/CT/06:00h","qos":"2","datatype":"auto","broker":"a1041d14.0ae6d","x":220,"y":200,"wires":[["201d95cb.abc10a"]]},{"id":"5515120c.2165ac","type":"mqtt in","z":"eb8801de.74904","name":"","topic":"/local/CT/08:00h","qos":"2","datatype":"auto","broker":"a1041d14.0ae6d","x":220,"y":240,"wires":[["8b7dba8c.78e9f8"]]},{"id":"365528d2.26e228","type":"mqtt in","z":"eb8801de.74904","name":"","topic":"/local/CT/10:00h","qos":"2","datatype":"auto","broker":"a1041d14.0ae6d","x":220,"y":280,"wires":[["67d410b0.4fa18"]]},{"id":"c506d54c.c8b598","type":"mqtt in","z":"eb8801de.74904","name":"","topic":"/local/CT/12:00h","qos":"2","datatype":"auto","broker":"a1041d14.0ae6d","x":220,"y":320,"wires":[["c5cd5704.4415d8"]]},{"id":"c651da6a.3e6af8","type":"mqtt in","z":"eb8801de.74904","name":"","topic":"/local/CT/14:00h","qos":"2","datatype":"auto","broker":"a1041d14.0ae6d","x":220,"y":360,"wires":[["fb5b52b2.892cf"]]},{"id":"42cf9a83.ee58d4","type":"mqtt in","z":"eb8801de.74904","name":"","topic":"/local/CT/16:00h","qos":"2","datatype":"auto","broker":"a1041d14.0ae6d","x":220,"y":400,"wires":[["c28675f8.5e57f8"]]},{"id":"8f75d618.483368","type":"mqtt in","z":"eb8801de.74904","name":"","topic":"/local/CT/18:00h","qos":"2","datatype":"auto","broker":"a1041d14.0ae6d","x":220,"y":440,"wires":[["b0c93b12.7737c8"]]},{"id":"9375cabe.0c4bd8","type":"mqtt in","z":"eb8801de.74904","name":"","topic":"/local/CT/20:00h","qos":"2","datatype":"auto","broker":"a1041d14.0ae6d","x":220,"y":480,"wires":[["56ff5824.45a1d8"]]},{"id":"186cbc09.c0f044","type":"mqtt in","z":"eb8801de.74904","name":"","topic":"/local/CT/22:00h","qos":"2","datatype":"auto","broker":"a1041d14.0ae6d","x":220,"y":520,"wires":[["9b80b7e8.bbc148"]]},{"id":"409251f.1c231b","type":"mqtt in","z":"eb8801de.74904","name":"","topic":"/local/Consumo","qos":"2","datatype":"auto","broker":"a1041d14.0ae6d","x":220,"y":580,"wires":[["4a99eeb5.7f3d2"]]},{"id":"2d102e2.a5241d2","type":"mysql","z":"eb8801de.74904","mydb":"9ef090e5.cb6f7","name":"","x":920,"y":300,"wires":[[]]},{"id":"87af1ed1.5c1cb","type":"debug","z":"eb8801de.74904","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","x":970,"y":860,"wires":[]},{"id":"44a790c9.535f6","type":"function","z":"eb8801de.74904","name":"MQTT to DB","func":"var newMsg = { payload: msg.payload };\nnewMsg.topic= \"UPDATE `DB_ConsumoVivienda`.`Consumos` SET `Consumo` = '\"+newMsg.payload+\"' WHERE (`Hora` = '00:00h')\";\nreturn newMsg;\n","outputs":1,"noerr":0,"x":530,"y":80,"wires":[["2d102e2.a5241d2"]]},{"id":"d56a3f87.6c79f","type":"function","z":"eb8801de.74904","name":"MQTT to DB","func":"var newMsg = { payload: msg.payload };\nnewMsg.topic= \"UPDATE `DB_ConsumoVivienda`.`Consumos` SET `Consumo` = '\"+newMsg.payload+\"' WHERE (`Hora` = '02:00h')\";\nreturn newMsg;\n","outputs":1,"noerr":0,"x":530,"y":120,"wires":[["2d102e2.a5241d2"]]},{"id":"101227a4.0ceb18","type":"function","z":"eb8801de.74904","name":"MQTT to DB","func":"var newMsg = { payload: msg.payload };\nnewMsg.topic= \"UPDATE `DB_ConsumoVivienda`.`Consumos` SET `Consumo` = '\"+newMsg.payload+\"' WHERE (`Hora` = '04:00h')\";\nreturn newMsg;\n","outputs":1,"noerr":0,"x":530,"y":160,"wires":[["2d102e2.a5241d2"]]},{"id":"201d95cb.abc10a","type":"function","z":"eb8801de.74904","name":"MQTT to DB","func":"var newMsg = { payload: msg.payload };\nnewMsg.topic= \"UPDATE `DB_ConsumoVivienda`.`Consumos` SET `Consumo` = '\"+newMsg.payload+\"' WHERE (`Hora` = '06:00h')\";\nreturn newMsg;\n","outputs":1,"noerr":0,"x":530,"y":200,"wires":[["2d102e2.a5241d2"]]},{"id":"67d410b0.4fa18","type":"function","z":"eb8801de.74904","name":"MQTT to DB","func":"var newMsg = { payload: msg.payload };\nnewMsg.topic= \"UPDATE `DB_ConsumoVivienda`.`Consumos` SET `Consumo` = '\"+newMsg.payload+\"' WHERE (`Hora` = '10:00h')\";\nreturn newMsg;\n","outputs":1,"noerr":0,"x":530,"y":280,"wires":[["2d102e2.a5241d2"]]},{"id":"c5cd5704.4415d8","type":"function","z":"eb8801de.74904","name":"MQTT to DB","func":"var newMsg = { payload: msg.payload };\nnewMsg.topic= \"UPDATE `DB_ConsumoVivienda`.`Consumos` SET `Consumo` = '\"+newMsg.payload+\"' WHERE (`Hora` = '12:00h')\";\nreturn newMsg;\n","outputs":1,"noerr":0,"x":530,"y":320,"wires":[["2d102e2.a5241d2"]]},{"id":"fb5b52b2.892cf","type":"function","z":"eb8801de.74904","name":"MQTT to DB","func":"var newMsg = { payload: msg.payload };\nnewMsg.topic= \"UPDATE `DB_ConsumoVivienda`.`Consumos` SET `Consumo` = '\"+newMsg.payload+\"' WHERE (`Hora` = '14:00h')\";\nreturn newMsg;\n","outputs":1,"noerr":0,"x":530,"y":360,"wires":[["2d102e2.a5241d2"]]},{"id":"c28675f8.5e57f8","type":"function","z":"eb8801de.74904","name":"MQTT to DB","func":"var newMsg = { payload: msg.payload };\nnewMsg.topic= \"UPDATE `DB_ConsumoVivienda`.`Consumos` SET `Consumo` = '\"+newMsg.payload+\"' WHERE (`Hora` = '16:00h')\";\nreturn newMsg;\n","outputs":1,"noerr":0,"x":530,"y":400,"wires":[["2d102e2.a5241d2"]]},{"id":"b0c93b12.7737c8","type":"function","z":"eb8801de.74904","name":"MQTT to DB","func":"var newMsg = { payload: msg.payload };\nnewMsg.topic= \"UPDATE `DB_ConsumoVivienda`.`Consumos` SET `Consumo` = '\"+newMsg.payload+\"' WHERE (`Hora` = '18:00h')\";\nreturn newMsg;\n","outputs":1,"noerr":0,"x":530,"y":440,"wires":[["2d102e2.a5241d2"]]},{"id":"56ff5824.45a1d8","type":"function","z":"eb8801de.74904","name":"MQTT to DB","func":"var newMsg = { payload: msg.payload };\nnewMsg.topic= \"UPDATE `DB_ConsumoVivienda`.`Consumos` SET `Consumo` = '\"+newMsg.payload+\"' WHERE (`Hora` = '20:00h')\";\nreturn newMsg;\n","outputs":1,"noerr":0,"x":530,"y":480,"wires":[["2d102e2.a5241d2"]]},{"id":"9b80b7e8.bbc148","type":"function","z":"eb8801de.74904","name":"MQTT to DB","func":"var newMsg = { payload: msg.payload };\nnewMsg.topic= \"UPDATE `DB_ConsumoVivienda`.`Consumos` SET `Consumo` = '\"+newMsg.payload+\"' WHERE (`Hora` = '22:00h')\";\nreturn newMsg;\n","outputs":1,"noerr":0,"x":530,"y":520,"wires":[["2d102e2.a5241d2"]]},{"id":"8b7dba8c.78e9f8","type":"function","z":"eb8801de.74904","name":"MQTT to DB","func":"var newMsg = { payload: msg.payload };\nnewMsg.topic= \"UPDATE `DB_ConsumoVivienda`.`Consumos` SET `Consumo` = '\"+newMsg.payload+\"' WHERE (`Hora` = '08:00h')\";\nreturn newMsg;\n","outputs":1,"noerr":0,"x":530,"y":240,"wires":[["2d102e2.a5241d2"]]},{"id":"4a99eeb5.7f3d2","type":"function","z":"eb8801de.74904","name":"MQTT to DB","func":"var newMsg = { payload: msg.payload };\nnewMsg.topic= \"UPDATE `DB_ConsumoVivienda`.`Consumos` SET `Consumo` = '\"+newMsg.payload+\"' WHERE (`Hora` = 'Total')\";\nreturn newMsg;\n","outputs":1,"noerr":0,"x":530,"y":580,"wires":[["2d102e2.a5241d2"]]},{"id":"885310e6.cf457","type":"function","z":"eb8801de.74904","name":"Leer datos DB","func":"msg.topic = \"SELECT * FROM Consumos\"\nreturn msg;","outputs":1,"noerr":0,"x":440,"y":860,"wires":[["da4a7627.a8f538"]]},{"id":"3f882452.50ec1c","type":"inject","z":"eb8801de.74904","name":"","topic":"","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":220,"y":860,"wires":[["885310e6.cf457"]]},{"id":"da4a7627.a8f538","type":"mysql","z":"eb8801de.74904","mydb":"9ef090e5.cb6f7","name":"","x":700,"y":860,"wires":[["87af1ed1.5c1cb"]]},{"id":"b36eaec2.13f98","type":"ui_table","z":"eb8801de.74904","group":"363bb5d4.e9ee2a","name":"Tabla Base de Datos / Historial consumo","order":1,"width":12,"height":8,"columns":[{"field":"Hora","title":"Hora","width":"","align":"left","formatter":"plaintext","formatterParams":{"target":"_blank"}},{"field":"ConsumoWh","title":"Consumo Wh","width":"","align":"center","formatter":"plaintext","formatterParams":{"target":"_blank"}},{"field":"Precio","title":"Precio €","width":"","align":"center","formatter":"plaintext","formatterParams":{"target":"_blank"}}],"outputs":0,"cts":false,"x":1060,"y":980,"wires":[]},{"id":"b5570845.dac8f8","type":"function","z":"eb8801de.74904","name":"DB to TABLE","func":"var newMsg = {payload: msg.payload };\nnewMsg.topic = table.updateData([{id:1, \"Consumo Wh\":\"'\"+newMsg.payload+\"'\"}]);\nreturn newMsg;","outputs":1,"noerr":0,"x":690,"y":980,"wires":[[]]},{"id":"a1041d14.0ae6d","type":"mqtt-broker","z":"","name":"BrokerLocal","broker":"localhost","port":"1883","clientid":"","usetls":false,"compatmode":true,"keepalive":"60","cleansession":true,"birthTopic":"","birthQos":"0","birthPayload":"","closeTopic":"","closeQos":"0","closePayload":"","willTopic":"","willQos":"0","willPayload":""},{"id":"9ef090e5.cb6f7","type":"MySQLdatabase","z":"","name":"","host":"127.0.0.1","port":"3306","db":"DB_ConsumoVivienda","tz":""},{"id":"363bb5d4.e9ee2a","type":"ui_group","z":"","name":"BASE DE DATOS","tab":"c5752f39.df5a6","order":1,"disp":true,"width":12,"collapse":false},{"id":"c5752f39.df5a6","type":"ui_tab","z":"","name":"BASE DE DATOS / HISTORIAL","icon":"storage","order":4,"disabled":false,"hidden":false}]

In the data base i just have three columns: Hour, Consumption (Consumo in spanish) and Price (it's not filled yet). And i'm getting an array with the values on each row like i showed in the question. Is that information enought?

Wow! It was obvious and I have not been able to see the error in the table settings. Thank you so much!
Can I ask another question?
Could you help me do a data history (with the datapicker node)?

I added the datapicker to the same group, but i don't know how to make that the table changes the values with the date i pick. Maybe this has to be made at Mysql data base?

You might be able to clean up your flow quite a bit using a wildcard character in the mqtt-in topic/ If the topic was /local/CT/* any mqtt topic starting with /local/CT/ would be received. You could then add a change node and a template node after that to build the SQL query.

In the change node use
Screen Shot 2020-06-04 at 3.11.28 PM
that will grab the third level of the topic and put it in msg.hour - next add a template node to build the SQL query like this:

and you would replace 24 nodes with 2. This assumes the third level is always one of those hours. I not, you could add a switch node after the mqtt-in to test for those twelve conditions (use the 'contains' option) and ignore anything but those 12 cases. However this will mean you haave to use three nodes instead of just two :sob:

1 Like

Thanks for your answer, i will work on cleaning my flow, appreciate your help!

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