How can i create a table from a DB?

[DAVIDLL]

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!

[Andrei]

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 ?

[DAVIDLL]

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:

[zenofmud]

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.

[Andrei]

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).

[DAVIDLL]

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?

[DAVIDLL]

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?

[zenofmud]

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

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

[DAVIDLL]

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