Does the mqtt data include idpaciente?
Also what sql query are you trying to generate and what is it supposed to do?
Data of idpaicente is not included in the mqtt data, this data has only been included in the "data" table
What I want to do is the following:
- Enter the patient's information (name, surname, age, sex); but in the "patients" table an idpaciente attribute has been created that is automatically generated because it is incremental.
- Data from the mqtt nodes are sent independently
- There is the "data" table that stores each data of the mqtt node (Tamb, Tcorp, ritm ......) and additionally add the patient column that is a field related to the "patient" table.
My question is if in node red you can work with related tables in mysql?
Yes, of course. If you provide the appropriate SQL query which joins the tables then it will work. node-RED doesn't know anything about tables, it just lets you build queries and send them to the database.
Thanks Colin, and there is some basic example that can guide me because I have actually searched for information about this and not found
Do you mean guide you with the SQL queries that you need?
Not exactly, but to perform SQL queries with related tables mysql
To perform SQL queries with related tables you need to generate the correct SQL that specifies the relationships between the tables. Google for SQL Tutorial, there are thousands available. If English is not your native language then you may be able to find one in your language.
oohh I was referring to related tables (mysql) that perform queries in node red, but I will look for information; Thanks for the help
You don't perform queries in node red. You first need to know what query you want, then you design your flow so that the query you want is built and is passed to the database to run the query, then the result is passed back to node red.
Thank you very much, I will look for more mysql information from related tables.
@Tefita - When you add a person to the pacientes table, the idpacientes is automatically generated. If you want to add a row to the datos table and link it to this table, you must provide the value that is now in the row in the pacientes table for this person.
You need to retreive the row from the pacientes table for the person and then you will have access to the idpacientes. You can then use this value to put in the row you wish to add to the datos table.
What will you do itf you have two people with the same nombre and apellido? It would seem to me that the pacientes table needs some other unique identifier like birthday.
If you are not clear on defining different tables in a database and relating them to each other, you should stop and learn about that before you keep plugging along. Take a database tutorial (or three) so you will understand how to relate tables together.
Thank you and I will take your advice.
Hi
I have solved the problem of the relational tables in mysql, but now the inconvenience that I want to do the following is presented:
- Enter the patient's data (name, surname, CEDULA, age, sex) that are stored in a table called "patients", insert the patient's data with a button, if I have managed to do this.
- The attribute CEDULA is related to a table called "data".
- In the data table there are other attributes (tamb, tcorp, ....) that come from an mqtt node.
- The data table "data" is inserted only once but I need to be inserted continuously, since the data is only stored once, I have configured in node function the following but I have not managed to get what I want.
Can somebody help me
I attach the flow
[{"id":"c4b2e0f6.c428","type":"mqtt in","z":"efb9ade7.d85b3","name":"","topic":"tamb","qos":"0","broker":"55d76ff9.f1284","x":410,"y":420,"wires":[["4b5cc1b9.78f03","a63dee1.920b71"]]},{"id":"4f90cac9.e79404","type":"mqtt in","z":"efb9ade7.d85b3","name":"","topic":"tcorp","qos":"0","broker":"55d76ff9.f1284","x":410,"y":480,"wires":[["4b5cc1b9.78f03","33aec865.8f94e8"]]},{"id":"d8ae66a7.3f63c8","type":"debug","z":"efb9ade7.d85b3","name":"debug_mysql","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","x":1310,"y":280,"wires":[]},{"id":"4b5cc1b9.78f03","type":"join","z":"efb9ade7.d85b3","name":"Datos","mode":"custom","build":"object","property":"payload","propertyType":"msg","key":"topic","joiner":"\\n","joinerType":"str","accumulate":false,"timeout":"","count":"12","reduceRight":false,"reduceExp":"","reduceInit":"","reduceInitType":"","reduceFixup":"","x":590,"y":240,"wires":[["cf21a4b9.d772e8","ffc9830b.9778c"]]},{"id":"cf21a4b9.d772e8","type":"debug","z":"efb9ade7.d85b3","name":"debug_nodo_join","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","x":830,"y":300,"wires":[]},{"id":"d7e9fca7.51cd5","type":"debug","z":"efb9ade7.d85b3","name":"debug_nodo_function","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","x":1140,"y":340,"wires":[]},{"id":"ed9f29ea.c15c18","type":"mqtt in","z":"efb9ade7.d85b3","name":"","topic":"rcard","qos":"0","broker":"55d76ff9.f1284","x":410,"y":540,"wires":[["4b5cc1b9.78f03","75e19e98.e3484"]]},{"id":"35fad81f.e225c8","type":"mqtt in","z":"efb9ade7.d85b3","name":"","topic":"dcaid","qos":"0","broker":"55d76ff9.f1284","x":410,"y":600,"wires":[["4b5cc1b9.78f03","6fcc3646.6030e8"]]},{"id":"7f0d9c0.2298064","type":"mqtt in","z":"efb9ade7.d85b3","name":"","topic":"acorp","qos":"0","broker":"55d76ff9.f1284","x":410,"y":660,"wires":[["4b5cc1b9.78f03","2b728062.239e5"]]},{"id":"70183e84.b434","type":"mqtt in","z":"efb9ade7.d85b3","name":"","topic":"aritm","qos":"0","broker":"55d76ff9.f1284","x":410,"y":720,"wires":[["4b5cc1b9.78f03","26bbaec4.e3b9b2"]]},{"id":"b028cd8f.cff9e","type":"comment","z":"efb9ade7.d85b3","name":"Ingreso BD","info":"","x":277.50000381469727,"y":253.99998664855957,"wires":[]},{"id":"c5c3320c.217f3","type":"mqtt in","z":"efb9ade7.d85b3","name":"","topic":"ccaid","qos":"0","broker":"55d76ff9.f1284","x":410,"y":780,"wires":[["4b5cc1b9.78f03","a68fbd56.e69a7"]]},{"id":"dffd8354.4c8ea","type":"mqtt in","z":"efb9ade7.d85b3","name":"","topic":"ccorp","qos":"0","broker":"55d76ff9.f1284","x":410,"y":840,"wires":[["4b5cc1b9.78f03","718163e3.f8df1c"]]},{"id":"69f736e8.291de8","type":"mqtt in","z":"efb9ade7.d85b3","name":"","topic":"critm","qos":"0","broker":"55d76ff9.f1284","x":410,"y":900,"wires":[["4b5cc1b9.78f03","bacb7ba.3858688"]]},{"id":"5a992fd9.cce9b","type":"mqtt in","z":"efb9ade7.d85b3","name":"","topic":"pamb","qos":"0","broker":"55d76ff9.f1284","x":410,"y":960,"wires":[["4b5cc1b9.78f03","10d482ae.271a4d"]]},{"id":"9192c946.6800e8","type":"mqtt in","z":"efb9ade7.d85b3","name":"","topic":"pcorp","qos":"0","broker":"55d76ff9.f1284","x":410,"y":1000,"wires":[["4b5cc1b9.78f03","ad15b790.941cb8"]]},{"id":"3932dde5.398ad2","type":"comment","z":"efb9ade7.d85b3","name":"Página Web","info":"","x":260.99999618530273,"y":385.0781135559082,"wires":[]},{"id":"741933b9.fecd4c","type":"mysql","z":"efb9ade7.d85b3","mydb":"b0768069.53b72","name":"BD_DATOS","x":1130,"y":260,"wires":[["d8ae66a7.3f63c8"]]},{"id":"6fcc3646.6030e8","type":"ui_text_input","z":"efb9ade7.d85b3","name":"","label":"Detector de caidas","tooltip":"","group":"56f79abd.6320c4","order":2,"width":0,"height":0,"passthru":true,"mode":"text","delay":300,"topic":"","x":850,"y":600,"wires":[[]]},{"id":"2b728062.239e5","type":"ui_text_input","z":"efb9ade7.d85b3","name":"","label":"Alarma temperatura","tooltip":"","group":"56f79abd.6320c4","order":3,"width":0,"height":0,"passthru":true,"mode":"text","delay":300,"topic":"","x":860,"y":660,"wires":[[]]},{"id":"26bbaec4.e3b9b2","type":"ui_text_input","z":"efb9ade7.d85b3","name":"","label":"Alarma ritmo","tooltip":"","group":"56f79abd.6320c4","order":4,"width":0,"height":0,"passthru":true,"mode":"text","delay":300,"topic":"","x":830,"y":720,"wires":[[]]},{"id":"a68fbd56.e69a7","type":"ui_text","z":"efb9ade7.d85b3","group":"b57a22fd.812da","order":2,"width":0,"height":0,"name":"","label":"Caídas","format":"{{msg.payload}}","layout":"row-spread","x":820,"y":780,"wires":[]},{"id":"718163e3.f8df1c","type":"ui_text","z":"efb9ade7.d85b3","group":"b57a22fd.812da","order":3,"width":0,"height":0,"name":"","label":"Alarma temp corp","format":"{{msg.payload}}","layout":"row-spread","x":850,"y":840,"wires":[]},{"id":"bacb7ba.3858688","type":"ui_text","z":"efb9ade7.d85b3","group":"b57a22fd.812da","order":4,"width":0,"height":0,"name":"","label":"Alarmas ritmo","format":"{{msg.payload}}","layout":"row-spread","x":840,"y":900,"wires":[]},{"id":"a63dee1.920b71","type":"ui_gauge","z":"efb9ade7.d85b3","name":"","group":"293ba6c0.530d0a","order":0,"width":0,"height":0,"gtype":"gage","title":"Temperatura Ambiente","label":"°C","format":"{{value}}","min":"0","max":"100","colors":["#1a719b","#29bcbc","#3e9ec4"],"seg1":"","seg2":"","x":840,"y":400,"wires":[]},{"id":"33aec865.8f94e8","type":"ui_gauge","z":"efb9ade7.d85b3","name":"","group":"95bc1cba.c56de","order":0,"width":0,"height":0,"gtype":"gage","title":"Temperatura corporal","label":"°C","format":"{{value}}","min":"0","max":"100","colors":["#00b500","#e6e600","#ca3838"],"seg1":"","seg2":"","x":840,"y":460,"wires":[]},{"id":"75e19e98.e3484","type":"ui_gauge","z":"efb9ade7.d85b3","name":"","group":"9d168e43.85bdb","order":0,"width":0,"height":0,"gtype":"gage","title":"Ritmo cardiaco","label":"LPM","format":"{{value}}","min":0,"max":"100","colors":["#00b500","#e6e600","#ca3838"],"seg1":"","seg2":"","x":840,"y":520,"wires":[]},{"id":"10d482ae.271a4d","type":"ui_gauge","z":"efb9ade7.d85b3","name":"","group":"b7aab8a8.973748","order":3,"width":0,"height":0,"gtype":"compass","title":"Prom tamb","label":"units","format":"{{value}}°C","min":0,"max":"100","colors":["#00b500","#ff80ff","#ca3838"],"seg1":"","seg2":"","x":830,"y":960,"wires":[]},{"id":"ad15b790.941cb8","type":"ui_gauge","z":"efb9ade7.d85b3","name":"","group":"b7aab8a8.973748","order":2,"width":0,"height":0,"gtype":"compass","title":"Prom tcorp","label":"units","format":"{{value}}°C","min":0,"max":"100","colors":["#400080","#e6e600","#ca3838"],"seg1":"","seg2":"","x":830,"y":1000,"wires":[]},{"id":"c73e4ef6.370bb","type":"ui_text_input","z":"efb9ade7.d85b3","name":"name","label":"Name","tooltip":"","group":"356fac5d.d980c4","order":2,"width":0,"height":0,"passthru":false,"mode":"text","delay":"300","topic":"name","x":650,"y":1080,"wires":[["422c45cd.b237cc"]]},{"id":"8b1ce9c2.e32e18","type":"ui_text_input","z":"efb9ade7.d85b3","name":"apellido","label":"apellido","tooltip":"","group":"356fac5d.d980c4","order":3,"width":0,"height":0,"passthru":false,"mode":"text","delay":"300","topic":"apellido","x":640,"y":1140,"wires":[["422c45cd.b237cc"]]},{"id":"90b8ddb9.a4009","type":"ui_button","z":"efb9ade7.d85b3","name":"save-button","group":"356fac5d.d980c4","order":7,"width":0,"height":0,"passthru":true,"label":"Guardar datos","tooltip":"","color":"","bgcolor":"","icon":"assignment_turned_in","payload":"{}","payloadType":"global","topic":"save-button","x":630,"y":1360,"wires":[["422c45cd.b237cc","10b37a2f.924d26"]]},{"id":"34eef85f.6094a8","type":"debug","z":"efb9ade7.d85b3","name":"node_switch","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","x":1190,"y":1140,"wires":[]},{"id":"422c45cd.b237cc","type":"join","z":"efb9ade7.d85b3","name":"","mode":"custom","build":"object","property":"payload","propertyType":"msg","key":"topic","joiner":"\\n","joinerType":"str","accumulate":true,"timeout":"","count":"1","reduceRight":false,"reduceExp":"","reduceInit":"","reduceInitType":"","reduceFixup":"","x":810,"y":1140,"wires":[["c1bd856f.3c4988","4c576493.4af37c"]]},{"id":"c1bd856f.3c4988","type":"switch","z":"efb9ade7.d85b3","name":"","property":"topic","propertyType":"msg","rules":[{"t":"eq","v":"save-button","vt":"str"}],"checkall":"true","repair":false,"outputs":1,"x":950,"y":1160,"wires":[["34eef85f.6094a8","ca9c02de.d5267"]]},{"id":"ec9816de.2696e8","type":"debug","z":"efb9ade7.d85b3","name":"node_bd_pac","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","x":1440,"y":1220,"wires":[]},{"id":"e984d589.591c48","type":"mysql","z":"efb9ade7.d85b3","mydb":"b0768069.53b72","name":"BD_PACIENTES","x":1190,"y":1180,"wires":[["ec9816de.2696e8","1539cb74.06b685"]]},{"id":"4c576493.4af37c","type":"debug","z":"efb9ade7.d85b3","name":"node_join","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","x":1080,"y":1080,"wires":[]},{"id":"ca9c02de.d5267","type":"function","z":"efb9ade7.d85b3","name":"select-query","func":"insert = {topic: \"INSERT INTO pacientes (nombre,apellido,cedula,edad,sexo) VALUES ('\" + msg.payload.name + \"','\" + msg.payload.apellido + \"','\" + msg.payload.cedula + \"','\" + msg.payload.edad + \"','\" + msg.payload.sexo + \"')\"};\nreturn insert; ","outputs":1,"noerr":0,"x":1010,"y":1220,"wires":[["e984d589.591c48","662867c2.605d48"]]},{"id":"662867c2.605d48","type":"debug","z":"efb9ade7.d85b3","name":"node_function","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","x":1200,"y":1240,"wires":[]},{"id":"1539cb74.06b685","type":"ui_toast","z":"efb9ade7.d85b3","position":"dialog","displayTime":"3","highlight":"","outputs":1,"ok":"OK","cancel":"","topic":"Ingreso correcto del paciente","name":"Ingreso correcto del paciente","x":1460,"y":1160,"wires":[[]]},{"id":"ca2a9972.abedf8","type":"ui_text_input","z":"efb9ade7.d85b3","name":"edad","label":"edad","tooltip":"","group":"356fac5d.d980c4","order":5,"width":0,"height":0,"passthru":false,"mode":"number","delay":"300","topic":"edad","x":630,"y":1240,"wires":[["422c45cd.b237cc"]]},{"id":"eb45b468.c1cde8","type":"ui_text_input","z":"efb9ade7.d85b3","name":"sexo","label":"sexo","tooltip":"","group":"356fac5d.d980c4","order":6,"width":0,"height":0,"passthru":false,"mode":"text","delay":"300","topic":"sexo","x":630,"y":1300,"wires":[["422c45cd.b237cc"]]},{"id":"9147ab60.bfe638","type":"ui_text_input","z":"efb9ade7.d85b3","name":"cedula","label":"cedula","tooltip":"","group":"356fac5d.d980c4","order":4,"width":0,"height":0,"passthru":true,"mode":"number","delay":300,"topic":"cedula","x":630,"y":1200,"wires":[["422c45cd.b237cc","d8882730.113668"]]},{"id":"ffc9830b.9778c","type":"function","z":"efb9ade7.d85b3","name":"Funcion almacenar","func":"\n\n//intento\nvar cont = global.get('cont') || 0;\ncont +=1;\nglobal.set('cont',cont);\n\nif (flow.get('numero') != msg.payload.ced && msg.payload.ced !== null){\n cont = 1;\n}\n\nif (cont == 1){\n //var cedula = flow.get('ced') || 0;\n var ced1 = msg.payload.ced;\n flow.set('numero',ced1);}\n\nif (cont < 5){ \nmsg.topic =\"INSERT INTO datos (Tamb, Tcorp, rcard, Dcaid, Acorp, Aritm, Ccaid, Ccorp, Critm, Pamb,Pcorp, cedula) VALUES ('\" + msg.payload.tamb + \"','\" +msg.payload.tcorp+ \"','\" +msg.payload.rcard+ \"','\" +msg.payload.dcaid+ \"','\" +msg.payload.acorp+ \"','\" +msg.payload.aritm+ \"','\" +msg.payload.ccaid+ \"','\" +msg.payload.ccorp+ \"','\" +msg.payload.critm+ \"','\" +msg.payload.pamb+ \"','\" +msg.payload.pcorp+ \"','\" +flow.get('numero')+ \"')\";\nreturn msg;}\n\nif (cont >= 5){\n global.set(cont, 2);\n msg.topic =\"INSERT INTO datos (Tamb, Tcorp, rcard, Dcaid, Acorp, Aritm, Ccaid, Ccorp, Critm, Pamb,Pcorp, cedula) VALUES ('\" + msg.payload.tamb + \"','\" +msg.payload.tcorp+ \"','\" +msg.payload.rcard+ \"','\" +msg.payload.dcaid+ \"','\" +msg.payload.acorp+ \"','\" +msg.payload.aritm+ \"','\" +msg.payload.ccaid+ \"','\" +msg.payload.ccorp+ \"','\" +msg.payload.critm+ \"','\" +msg.payload.pamb+ \"','\" +msg.payload.pcorp+ \"','\" +flow.get('numero')+ \"')\";\n return msg;\n}\n\n\n","outputs":1,"noerr":0,"x":870,"y":240,"wires":[["741933b9.fecd4c","d7e9fca7.51cd5"]]},{"id":"582d5c6d.ee5994","type":"change","z":"efb9ade7.d85b3","name":"set","rules":[{"t":"set","p":"payload","pt":"msg","to":"","tot":"str"}],"action":"","property":"","from":"","to":"","reg":false,"x":250,"y":1200,"wires":[["5fcfa5f0.f6833c"]]},{"id":"5fcfa5f0.f6833c","type":"ui_button","z":"efb9ade7.d85b3","name":"clear-button","group":"356fac5d.d980c4","order":8,"width":0,"height":0,"passthru":false,"label":"Borrar campos","tooltip":"","color":"","bgcolor":"","icon":"delete","payload":"[]","payloadType":"json","topic":"","x":430,"y":1200,"wires":[["c73e4ef6.370bb","8b1ce9c2.e32e18","9147ab60.bfe638","eb45b468.c1cde8","ca2a9972.abedf8","90b8ddb9.a4009"]]},{"id":"10b37a2f.924d26","type":"debug","z":"efb9ade7.d85b3","name":"node-button","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","x":850,"y":1360,"wires":[]},{"id":"d8882730.113668","type":"mqtt out","z":"efb9ade7.d85b3","name":"","topic":"ced","qos":"","retain":"","broker":"55d76ff9.f1284","x":840,"y":1200,"wires":[]},{"id":"916bf529.2bb1a8","type":"mqtt in","z":"efb9ade7.d85b3","name":"","topic":"ced","qos":"0","broker":"55d76ff9.f1284","x":410,"y":360,"wires":[["4b5cc1b9.78f03"]]},{"id":"55d76ff9.f1284","type":"mqtt-broker","z":"","name":"","broker":"localhost","port":"1883","clientid":"","usetls":false,"verifyservercert":true,"compatmode":true,"keepalive":"15","cleansession":true,"birthTopic":"","birthQos":"0","birthRetain":null,"birthPayload":"","closeTopic":"","closePayload":"","willTopic":"","willQos":"0","willRetain":null,"willPayload":""},{"id":"b0768069.53b72","type":"MySQLdatabase","z":"","host":"127.0.0.1","port":"3306","db":"tesis","tz":""},{"id":"56f79abd.6320c4","type":"ui_group","z":"","name":"Alarmas","tab":"16e86f12.023081","order":7,"disp":true,"width":"6","collapse":false},{"id":"b57a22fd.812da","type":"ui_group","z":"","name":"Contadores","tab":"16e86f12.023081","order":8,"disp":true,"width":"6","collapse":false},{"id":"293ba6c0.530d0a","type":"ui_group","z":"","name":"Tempamb","tab":"16e86f12.023081","order":3,"disp":true,"width":"6","collapse":false},{"id":"95bc1cba.c56de","type":"ui_group","z":"","name":"Tempcorp","tab":"16e86f12.023081","order":4,"disp":true,"width":"6","collapse":false},{"id":"9d168e43.85bdb","type":"ui_group","z":"","name":"Ritmocard","tab":"16e86f12.023081","order":5,"disp":true,"width":"6","collapse":false},{"id":"b7aab8a8.973748","type":"ui_group","z":"","name":"Promedio","tab":"16e86f12.023081","order":6,"disp":true,"width":"6","collapse":false},{"id":"356fac5d.d980c4","type":"ui_group","z":"","name":"Pacientes","tab":"16e86f12.023081","order":2,"disp":true,"width":"6","collapse":true},{"id":"16e86f12.023081","type":"ui_tab","z":"","name":"dispositivo","icon":"healing","order":2}]
How do you know what patient the MQTT data is for?
Thanks for answering
The mqtt data comes from an external device. While the personal data of the patient entered them manually
That still doesn't answer the question Ive been asking since the start of this topic:
When an mqtt message arrives in node-red, how do you know what patient it is for? Is it part of the message? Is it part of the topic string the message is published to?
- The device does not know to which patient the data belongs, I enter the data from the "patient" table manually and for this reason I only save one data. I'm looking for the data to be stored continuously.
- If it is part of the topics that are published but the identification data does it only once, attach the capture of the flow
Let me ask Nick's question another way
Will there ever be more than one paitent having the device(s) that send the mqtt attached?
If so, since the data is send automatically, what will indicate in the MQTT msg which paitent the msg is from?
