How to Check whether There is Existing Data before Allowing Users to Insert the Data to the Database

Dashboard
21

Sorry @zenofmud , for not sharing the flow, I've actually tried to modify the flow but I could help.

[{"id":"663256c1.ef9618","type":"ui_text_input","z":"a0a2199f.f5e638","name":"name","label":"Name","tooltip":"","group":"356fac5d.d980c4","order":2,"width":0,"height":0,"passthru":false,"mode":"text","delay":"300","topic":"name","x":570,"y":280,"wires":[["9c430827.1649e8"]]},{"id":"8e6c4c9d.3b383","type":"ui_text_input","z":"a0a2199f.f5e638","name":"apellido","label":"apellido","tooltip":"","group":"356fac5d.d980c4","order":3,"width":0,"height":0,"passthru":false,"mode":"text","delay":"300","topic":"apellido","x":580,"y":340,"wires":[["9c430827.1649e8"]]},{"id":"12a585f6.e531ba","type":"ui_button","z":"a0a2199f.f5e638","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":570,"y":560,"wires":[["9c430827.1649e8","228f7175.69169e"]]},{"id":"9dbf8c03.4e568","type":"debug","z":"a0a2199f.f5e638","name":"node_switch1","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","x":1140,"y":340,"wires":[]},{"id":"9c430827.1649e8","type":"join","z":"a0a2199f.f5e638","name":"","mode":"custom","build":"object","property":"payload","propertyType":"msg","key":"topic","joiner":"\\n","joinerType":"str","accumulate":true,"timeout":"","count":"5","reduceRight":false,"reduceExp":"","reduceInit":"","reduceInitType":"","reduceFixup":"","x":750,"y":340,"wires":[["e4b9d7ca.a5ec88","60ccb3fe.4bcd8c"]]},{"id":"e4b9d7ca.a5ec88","type":"switch","z":"a0a2199f.f5e638","name":"switch_select","property":"topic","propertyType":"msg","rules":[{"t":"eq","v":"save-button","vt":"str"}],"checkall":"true","repair":false,"outputs":1,"x":910,"y":360,"wires":[["9dbf8c03.4e568","d084b396.0651d"]]},{"id":"7aa36605.3867e8","type":"debug","z":"a0a2199f.f5e638","name":"node_bd_pac","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","x":1380,"y":320,"wires":[]},{"id":"60ccb3fe.4bcd8c","type":"debug","z":"a0a2199f.f5e638","name":"node_join","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","x":1020,"y":280,"wires":[]},{"id":"d084b396.0651d","type":"function","z":"a0a2199f.f5e638","name":"select-query","func":"var select = {topic: \"SELECT COUNT(*) AS count FROM pacientes WHERE nombre='\" + msg.payload.name + \"'\"};\nreturn select;","outputs":1,"noerr":0,"x":950,"y":420,"wires":[["8a6f7674.c3ec98","8dd0ca48.b80278","bfb6b546.6fabd8"]]},{"id":"8a6f7674.c3ec98","type":"debug","z":"a0a2199f.f5e638","name":"node_select","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","x":1030,"y":500,"wires":[]},{"id":"1fa2ae8b.72e9c1","type":"ui_text_input","z":"a0a2199f.f5e638","name":"edad","label":"edad","tooltip":"","group":"356fac5d.d980c4","order":5,"width":0,"height":0,"passthru":false,"mode":"number","delay":"300","topic":"edad","x":570,"y":440,"wires":[["9c430827.1649e8"]]},{"id":"951ae802.19d948","type":"ui_text_input","z":"a0a2199f.f5e638","name":"sexo","label":"sexo","tooltip":"","group":"356fac5d.d980c4","order":6,"width":0,"height":0,"passthru":false,"mode":"text","delay":"300","topic":"sexo","x":570,"y":500,"wires":[["9c430827.1649e8"]]},{"id":"ade31e4f.08585","type":"ui_text_input","z":"a0a2199f.f5e638","name":"cedula","label":"cedula","tooltip":"","group":"356fac5d.d980c4","order":4,"width":0,"height":0,"passthru":true,"mode":"number","delay":300,"topic":"cedula","x":570,"y":400,"wires":[["9c430827.1649e8","e0ca343.98933c8"]]},{"id":"13b7e857.884ca8","type":"change","z":"a0a2199f.f5e638","name":"set","rules":[{"t":"set","p":"payload","pt":"msg","to":"","tot":"str"}],"action":"","property":"","from":"","to":"","reg":false,"x":230,"y":400,"wires":[["8b7d066a.c7e1a8"]]},{"id":"8b7d066a.c7e1a8","type":"ui_button","z":"a0a2199f.f5e638","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":370,"y":400,"wires":[["663256c1.ef9618","8e6c4c9d.3b383","ade31e4f.08585","951ae802.19d948","1fa2ae8b.72e9c1","12a585f6.e531ba"]]},{"id":"228f7175.69169e","type":"debug","z":"a0a2199f.f5e638","name":"node-button","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","x":790,"y":560,"wires":[]},{"id":"8dd0ca48.b80278","type":"mysql","z":"a0a2199f.f5e638","mydb":"698ecd0.1670e34","name":"BD_DATOS","x":1150,"y":400,"wires":[["7aa36605.3867e8"]]},{"id":"e0ca343.98933c8","type":"debug","z":"a0a2199f.f5e638","name":"cedula","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","x":800,"y":500,"wires":[]},{"id":"2480d039.f669e","type":"function","z":"a0a2199f.f5e638","name":"insert-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 + \"')\"};\nactionString = \"Dato ingresado satisfactoriamente!\";\nreturn [insert,actionString];\n\n\n//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 + \"')\"};\n//return insert;  \n","outputs":1,"noerr":0,"x":1410,"y":480,"wires":[["d63e1165.fd7fe","628ee543.03c4ac"]]},{"id":"bfb6b546.6fabd8","type":"switch","z":"a0a2199f.f5e638","name":"switch_insert","property":"payload[0].count","propertyType":"msg","rules":[{"t":"eq","v":"0","vt":"msg"},{"t":"eq","v":"1","vt":"msg"}],"checkall":"true","repair":false,"outputs":2,"x":1190,"y":460,"wires":[["975efbe.6731308","2480d039.f669e"],["db614880.d35f48","7150fef5.16df6"]]},{"id":"db614880.d35f48","type":"function","z":"a0a2199f.f5e638","name":"failure-msg","func":"actionString = \"Ya se ingresado ese nombre!\";\nmsg.payload = actionString;\nreturn msg;","outputs":1,"noerr":0,"x":1410,"y":540,"wires":[["add1cfb6.0b88e"]]},{"id":"d63e1165.fd7fe","type":"mysql","z":"a0a2199f.f5e638","mydb":"698ecd0.1670e34","name":"BD_DATOS","x":1590,"y":480,"wires":[["a6c52cd3.8dfe1"]]},{"id":"1583bc32.a442d4","type":"function","z":"a0a2199f.f5e638","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":1030,"y":620,"wires":[[]]},{"id":"628ee543.03c4ac","type":"debug","z":"a0a2199f.f5e638","name":"node_insert","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","x":1600,"y":420,"wires":[]},{"id":"add1cfb6.0b88e","type":"debug","z":"a0a2199f.f5e638","name":"node_fallo","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","x":1650,"y":580,"wires":[]},{"id":"a6c52cd3.8dfe1","type":"debug","z":"a0a2199f.f5e638","name":"node_bdpacientes","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","x":1800,"y":520,"wires":[]},{"id":"7150fef5.16df6","type":"debug","z":"a0a2199f.f5e638","name":"node_siwtch3","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","x":1410,"y":620,"wires":[]},{"id":"975efbe.6731308","type":"debug","z":"a0a2199f.f5e638","name":"node_siwtch2","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","x":1380,"y":400,"wires":[]},{"id":"a942a4fe.805278","type":"ui_toast","z":"a0a2199f.f5e638","position":"dialog","displayTime":"3","highlight":"","outputs":1,"ok":"OK","cancel":"","topic":"Ingreso correcto del paciente","name":"Ingreso correcto del paciente","x":1640,"y":340,"wires":[[]]},{"id":"87508a4f.47d1e8","type":"ui_toast","z":"a0a2199f.f5e638","position":"dialog","displayTime":"3","highlight":"","outputs":1,"ok":"OK","cancel":"","topic":"fallo","name":"fallo","x":1630,"y":640,"wires":[[]]},{"id":"356fac5d.d980c4","type":"ui_group","z":"","name":"Pacientes","tab":"16e86f12.023081","order":2,"disp":true,"width":"6","collapse":true},{"id":"698ecd0.1670e34","type":"MySQLdatabase","z":"","host":"","port":"","db":"","tz":""},{"id":"16e86f12.023081","type":"ui_tab","z":"","name":"dispositivo","icon":"healing","order":2}]
22

1)I have the error in node function "insert-query".
2) In this insert the data of the table (name, surname, age, identity card, sex)

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 + "')"};
actionString = "Dato ingresado satisfactoriamente!";
return [insert,actionString];
23

So add a debug after that and what is the message you are sending?

24

I always have this message when I enter a "name" already stored in the table or a new "name".
This I get at the exit of the node function "insert-query".
INSERT1

25

So if it isn't working you need to break the problem down to fid the bit that is broken.
Check that the message going into the function you posted

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 + "')"};
actionString = "Dato ingresado satisfactoriamente!";
return [insert,actionString];

and the messages that leave the function are what you think they are. You do this by adding debug nodes. If it can't read property 'name' check that where you are trying to use name it exists.

26

As @ukmoose said, you need to work backwards. If your 'insert-query' is getting an error, you need to look at what is going into that node. Looking at your flow the input comes from the switch node and the msg that enters the switch node comes from the 'select-query' function.

So what is coming out of that node and is it the data you are trying to access in the 'insert-query' node?

27

I appreciate your help @ukmoose , @zenofmud

I get the following:

  1. Debug: node name:
    name

  2. Debug output: node join:
    node_join

  3. Debug: switch_select
    node_switch1

  4. The error is presented in the node function "select-query", because I have a "msg.payload: undefined".
    node_select

  5. Debug: node_switch 2; this is the output of the switch_insert
    salida_s2

28

So your select-query function has an error saying that msg.payload is not defined. Take a step back and figure out where the data send to that function is coming from and what is in the msg being sent.

It is your responsibility to send data to that function node in a format that the function node will use.

29

The data comes from the "switch_select" node, and I get the following:
sw1_i3

I have verified the path of the name message and it is this:
payload.name

30 
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 + "')"};
actionString = "Dato ingresado satisfactoriamente!";
return [insert,actionString];

In this code you are returning the insert message on the first output of the Function node and you are returning actionString on the second output of the Function node.

But actionString is a string - not a message object. You must return messages from the Function, not plain strings.

var 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 + "')"};
var actionString = {payload:"Dato ingresado satisfactoriamente!"};
return [insert,actionString];
31

@knolleary Nick, that is the second error
@Tefita In the 'select query' function you are returning an object with only a msg.topic and no msg.payload. That is why you are getting the msg.payload: undefined

32

And that probably isn't an error, because the sql node uses msg.topic to define the query and doesn't use msg.payload.

33

At least cleaning that up will be a good learning exercise for @Tefita as she trys to learn how to use NR.

34

I have done the mentioned thing but now I have another error:

var select = {payload: "SELECT COUNT(*) AS count FROM pacientes WHERE nombre='" + msg.payload.name + "'"};
return select;

SELECT1

35

Then should consider what was said by @knolleary : sql node uses msg.topic

36

So by this last post of yours, can we assume you have it working?

37

No, I need your help please, I continue with the problem, I do not know how to solve it.
This error in is node function "select-query"
select2

38

As I said before look at the msg entering the function node. In the function node you are trying to get information out of the payload but there is no payload. You have to fix that!

39

Hi,
I'm also getting same problem
SELECT COUNT() AS count FROM scanner WHERE scannerdata='a' : msg.payload : undefined
[
{
"id": "b727ae2d17cd20f9",
"type": "tab",
"label": "Flow 6",
"disabled": false,
"info": "",
"env": []
},
{
"id": "161724188b20085f",
"type": "ui_text_input",
"z": "b727ae2d17cd20f9",
"name": "",
"label": "",
"tooltip": "",
"group": "21fe878da3854fff",
"order": 10,
"width": 0,
"height": 0,
"passthru": false,
"mode": "text",
"delay": "0",
"topic": "payload",
"sendOnBlur": false,
"className": "",
"topicType": "msg",
"x": 180,
"y": 1000,
"wires": [
[
"41989a70fc4eceed",
"32bb7ff6b2b4f975"
]
]
},
{
"id": "1d79023b9138099f",
"type": "debug",
"z": "b727ae2d17cd20f9",
"name": "",
"active": false,
"tosidebar": true,
"console": false,
"tostatus": false,
"complete": "payload",
"targetType": "msg",
"statusVal": "",
"statusType": "auto",
"x": 530,
"y": 980,
"wires": []
},
{
"id": "e2381e9472463e2f",
"type": "change",
"z": "b727ae2d17cd20f9",
"name": "",
"rules": [
{
"t": "set",
"p": "payload",
"pt": "msg",
"to": "",
"tot": "str"
}
],
"action": "",
"property": "",
"from": "",
"to": "",
"reg": false,
"x": 320,
"y": 1200,
"wires": [
[
"161724188b20085f"
]
]
},
{
"id": "32bb7ff6b2b4f975",
"type": "delay",
"z": "b727ae2d17cd20f9",
"name": "",
"pauseType": "delay",
"timeout": "1",
"timeoutUnits": "seconds",
"rate": "1",
"nbRateUnits": "1",
"rateUnits": "second",
"randomFirst": "1",
"randomLast": "5",
"randomUnits": "seconds",
"drop": false,
"allowrate": false,
"outputs": 1,
"x": 120,
"y": 1200,
"wires": [
[
"e2381e9472463e2f"
]
]
},
{
"id": "41989a70fc4eceed",
"type": "function",
"z": "b727ae2d17cd20f9",
"name": "",
"func": "if(msg.payload || 0)\n{\nglobal.set('scannerread',msg.payload);\nreturn msg;\n}",
"outputs": 1,
"noerr": 0,
"initialize": "",
"finalize": "",
"libs": [],
"x": 340,
"y": 1000,
"wires": [
[
"1d79023b9138099f",
"eafb48b2b763c2c8",
"0367dc0573d03a3c"
]
]
},
{
"id": "eafb48b2b763c2c8",
"type": "ui_text",
"z": "b727ae2d17cd20f9",
"group": "21fe878da3854fff",
"order": 10,
"width": 0,
"height": 0,
"name": "",
"label": "Last scanned barcode",
"format": "{{msg.payload}}",
"layout": "row-spread",
"className": "",
"x": 580,
"y": 1040,
"wires": []
},
{
"id": "0367dc0573d03a3c",
"type": "function",
"z": "b727ae2d17cd20f9",
"name": "",
"func": "var z=global.get('scannerread');\nmsg.payload=z;\n//msg.payload=z;\n//msg.payload={topic:"SELECT scannerdata,count(scannerdata = '"+z+"') FROM training.scanner;"};\n//var select = {topic: "SELECT COUNT() AS count FROM test WHERE name='" + msg.payload.name + "'"};\n\nvar select = {topic: "SELECT COUNT(*) AS count FROM scanner WHERE scannerdata='" + msg.payload + "'"};\nreturn select;",
"outputs": 1,
"noerr": 0,
"initialize": "",
"finalize": "",
"libs": ,
"x": 540,
"y": 1260,
"wires": [
[
"72f8055014c1dc6e",
"724a9f33193410af",
"ad9ac705413818c1"
]
]
},
{
"id": "72f8055014c1dc6e",
"type": "mysql",
"z": "b727ae2d17cd20f9",
"mydb": "7e23b89935e72915",
"name": "",
"x": 780,
"y": 1240,
"wires": [

]
},
{
"id": "724a9f33193410af",
"type": "debug",
"z": "b727ae2d17cd20f9",
"name": "",
"active": true,
"tosidebar": true,
"console": false,
"tostatus": false,
"complete": "true",
"targetType": "full",
"statusVal": "",
"statusType": "auto",
"x": 730,
"y": 1320,
"wires":
},
{
"id": "69e9418553ca469a",
"type": "debug",
"z": "b727ae2d17cd20f9",
"name": "",
"active": false,
"tosidebar": true,
"console": false,
"tostatus": false,
"complete": "true",
"targetType": "full",
"statusVal": "",
"statusType": "auto",
"x": 690,
"y": 1400,
"wires":
},
{
"id": "6cb5f64655eaf652",
"type": "function",
"z": "b727ae2d17cd20f9",
"name": "",
"func": "actionString = "Data insertion failed!";\nmsg.payload = actionString;\nreturn msg;\n",
"outputs": 1,
"noerr": 0,
"initialize": "",
"finalize": "",
"libs": ,
"x": 640,
"y": 1480,
"wires": [
[
"fa3bbe7bcccb4548"
]
]
},
{
"id": "fa3bbe7bcccb4548",
"type": "debug",
"z": "b727ae2d17cd20f9",
"name": "",
"active": false,
"tosidebar": true,
"console": false,
"tostatus": false,
"complete": "false",
"statusVal": "",
"statusType": "auto",
"x": 790,
"y": 1480,
"wires":
},
{
"id": "ad9ac705413818c1",
"type": "switch",
"z": "b727ae2d17cd20f9",
"name": "",
"property": "payload[0].count",
"propertyType": "msg",
"rules": [
{
"t": "eq",
"v": "0",
"vt": "num"
},
{
"t": "else"
}
],
"checkall": "true",
"repair": false,
"outputs": 2,
"x": 450,
"y": 1440,
"wires": [
[
"69e9418553ca469a"
],
[
"6cb5f64655eaf652",
"d30d854110c2756d"
]
]
},
{
"id": "d30d854110c2756d",
"type": "debug",
"z": "b727ae2d17cd20f9",
"name": "",
"active": true,
"tosidebar": true,
"console": false,
"tostatus": false,
"complete": "false",
"statusVal": "",
"statusType": "auto",
"x": 470,
"y": 1520,
"wires":
},
{
"id": "21fe878da3854fff",
"type": "ui_group",
"name": "Export Data",
"tab": "750a8b0a2f1e55a8",
"order": 3,
"disp": false,
"width": "6",
"collapse": false,
"className": ""
},
{
"id": "7e23b89935e72915",
"type": "MySQLdatabase",
"name": "",
"host": "127.0.0.1",
"port": "3306",
"db": "training",
"tz": "",
"charset": "UTF8"
},
{
"id": "750a8b0a2f1e55a8",
"type": "ui_tab",
"name": "Export",
"icon": "dashboard",
"disabled": false,
"hidden": false
}
]

40

In order to make flow or code importable it is necessary to surround your code with three backticks (also known as a left quote or backquote ```)

``` 
   code goes here 
```

You can edit and correct your post by clicking the pencil :pencil2: icon.

See this post for more details - How to share code or flow json

However, if the problem is that you are having difficulty getting the right query, and you are creating the query in a function node, then just post the code of the function node, and show us what is going into the function node, and what is coming out of it. There is no need to post a large flow.