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

**URL:** <https://discourse.nodered.org/t/how-to-check-whether-there-is-existing-data-before-allowing-users-to-insert-the-data-to-the-database/1754>\
**Category:** Dashboard\
**Created:** [23 July 2018 02:10 UTC](https://discourse.nodered.org/t/how-to-check-whether-there-is-existing-data-before-allowing-users-to-insert-the-data-to-the-database/1754 "2018-07-23T02:10:23Z")\
**Posts on this page:** 20\
**Page:** 1

<div class="post-metadata">

**Author:** ![rishanaziz](https://avatars.discourse-cdn.com/v4/letter/r/f14d63/32.png) [@rishanaziz](https://discourse.nodered.org/u/rishanaziz)\
**Post date:** [23 July 2018 02:10 UTC](https://discourse.nodered.org/t/how-to-check-whether-there-is-existing-data-before-allowing-users-to-insert-the-data-to-the-database/1754/1 "2018-07-23T02:10:23Z")

</div>

Hi,

![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/2X/2/273c5c3c83ebfd25bc00429205fbb7005097f5ab.png)

Database type: **MySQL**

There is no data available on the database and I should allow the user to enter the data to the database.

I'm trying to do if else statement with this empty msg.payload (e.g. if (msg.payload===null)), but I'm getting SQL Syntax Error. My SELECT and INSERT statements in the function are working fine individually, but once I come up with complete function, the error coming up.

The following is the function code:

```auto
var msgSelect = {};
var msgInsert = {};

msgSelect.topic = "SELECT name FROM test WHERE name='" + msg.payload.name + "'";

if (msgSelect.payload===null)
{
	msgInsert.topic="INSERT INTO test (name,lastname,code) VALUES ('"+ msg.payload.name +"','"+ msg.payload.lastname +"','"+ msg.payload.code +"')";
	msg.payload = "Data inserted successfully!";
}
else
{
	msg.payload = "Data insertion failed!";
}
return msg;

```

Thanks for helping.

---

<div class="post-metadata">

**Author:** ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)\
**Post date:** [23 July 2018 06:10 UTC](https://discourse.nodered.org/t/how-to-check-whether-there-is-existing-data-before-allowing-users-to-insert-the-data-to-the-database/1754/2 "2018-07-23T06:10:57Z")

</div>

An empty array is not the same as null. Test the length of the array instead.

---

<div class="post-metadata">

**Author:** ![rishanaziz](https://avatars.discourse-cdn.com/v4/letter/r/f14d63/32.png) [@rishanaziz](https://discourse.nodered.org/u/rishanaziz)\
**Post date:** [23 July 2018 08:59 UTC](https://discourse.nodered.org/t/how-to-check-whether-there-is-existing-data-before-allowing-users-to-insert-the-data-to-the-database/1754/3 "2018-07-23T08:59:07Z")

</div>

Give me an example to test the length of the array.

---

<div class="post-metadata">

**Author:** ![knolleary](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/knolleary/32/3_2.png) [@knolleary](https://discourse.nodered.org/u/knolleary)\
**Post date:** [23 July 2018 09:03 UTC](https://discourse.nodered.org/t/how-to-check-whether-there-is-existing-data-before-allowing-users-to-insert-the-data-to-the-database/1754/4 "2018-07-23T09:03:40Z")

</div>

@rishanaziz See [https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global\_Objects/Array/length](https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Array/length) for how to get the length of an array.

---

<div class="post-metadata">

**Author:** ![rishanaziz](https://avatars.discourse-cdn.com/v4/letter/r/f14d63/32.png) [@rishanaziz](https://discourse.nodered.org/u/rishanaziz)\
**Post date:** [23 July 2018 09:56 UTC](https://discourse.nodered.org/t/how-to-check-whether-there-is-existing-data-before-allowing-users-to-insert-the-data-to-the-database/1754/5 "2018-07-23T09:56:51Z")

</div>

I got the following output when I SELECT statement. I would like to compare the count in if else statement. How to do it? If the count is 0, then only I would like to allow the users to insert the data. I also would like to know whether we are able to have two SQL statements in a function node. If it's possible, I'm able to SELECT and INSERT statement in the same function node.

![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/2X/6/6b85d05102a3c0173bb6b62403b0450ad71c44b8.png)

---

<div class="post-metadata">

**Author:** ![knolleary](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/knolleary/32/3_2.png) [@knolleary](https://discourse.nodered.org/u/knolleary)\
**Post date:** [23 July 2018 10:00 UTC](https://discourse.nodered.org/t/how-to-check-whether-there-is-existing-data-before-allowing-users-to-insert-the-data-to-the-database/1754/6 "2018-07-23T10:00:48Z")

</div>

This [page in the docs](https://nodered.org/docs/user-guide/messages) tells you about the tools available in the Debug sidebar to find out how to access any part of a message.

In this instance, `msg.payload` is an Array with a single element. That element is an object with a `count` property. Putting that all together gives: `msg.payload[0].count`

So you could use a Switch node to branch your flow by testing that property for `0` or `otherwise`:

 ![Local_Node-RED](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/2X/1/1e4d087f3aa671dce2a1185be215b37e3c667120.png)

```auto
[{"id":"c5197dfc.82664","type":"switch","z":"c6daa392.ba6de","name":"","property":"payload[0].count","propertyType":"msg","rules":[{"t":"eq","v":"0","vt":"num"},{"t":"else"}],"checkall":"true","repair":false,"outputs":2,"x":213,"y":1308,"wires":[[],[]]}]

```

> [@rishanaziz](#):
>
> I also would like to know whether we are able to have two SQL statements in a function node. If it's possible, I'm able to SELECT and INSERT statement in the same function node.

A Function node doesn't do anything with SQL. You are setting properties on a message that gets passed to a mysql node. You can only do one query at a time.

---

<div class="post-metadata">

**Author:** ![PKGeorgiev](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/pkgeorgiev/32/1325_2.png) [@PKGeorgiev](https://discourse.nodered.org/u/PKGeorgiev)\
**Post date:** [23 July 2018 17:40 UTC](https://discourse.nodered.org/t/how-to-check-whether-there-is-existing-data-before-allowing-users-to-insert-the-data-to-the-database/1754/7 "2018-07-23T17:40:05Z")

</div>

I prefer to use the MySQL-ish way (I think it's called upsert =\> update if exists or insert when absent):

`msg.topic = "INSERT INTO `registry` (`key`, `value`) VALUES ('" + msg.topic + "', '" + msg.payload + "') ON DUPLICATE KEY UPDATE `value` = '" + msg.payload + "' ";`

For this to work, the `key` field (`name` in your case) need to be primary key.  
This eliminates the need for two queries i.e. less nodes in the flow.

---

<div class="post-metadata">

**Author:** ![rishanaziz](https://avatars.discourse-cdn.com/v4/letter/r/f14d63/32.png) [@rishanaziz](https://discourse.nodered.org/u/rishanaziz)\
**Post date:** [24 July 2018 01:21 UTC](https://discourse.nodered.org/t/how-to-check-whether-there-is-existing-data-before-allowing-users-to-insert-the-data-to-the-database/1754/8 "2018-07-24T01:21:43Z")

</div>

I'm getting "TypeError: Cannot read property '0' of undefined" once I used the switch node. I'm not sure where it went wrong.

Thanks for helping.

![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/2X/e/ed167a79287bd9d7ef8850e4705c036466560cfc.png)

```auto
[{"id":"b107d7d0.8bcf98","type":"function","z":"87eeee6.729851","name":"select-query","func":"//var name = {payload: (msg.payload.name)};\n//msg.payload = name;\nvar select = {topic: \"SELECT COUNT(*) AS count FROM test WHERE name='\" + msg.payload.name + \"'\"};\n\nreturn select;","outputs":1,"noerr":0,"x":670,"y":120,"wires":[["ce323c92.b8766","ea75af48.2081b"]]},{"id":"cb5c2136.c89d5","type":"function","z":"87eeee6.729851","name":"insert-query","func":"//var cntStr = {payload: (msg.payload[0])};\n//var count = {payload: (cntStr.payload.count)};\n\n//if (msg.payload[0].count === 0)\n//{\n\tinsert = {topic: \"INSERT INTO test (name,lastname,code) VALUES ('\"+ msg.payload.name +\"','\"+ msg.payload.lastname +\"','\"+ msg.payload.code +\"')\"};\n\tactionString = \"Data inserted successfully!\";\n\treturn [insert,actionString];\n/*}\nelse\n{\n\tactionString = \"Data insertion failed!\";\n\tmsg.payload = actionString;\n return msg;\n}*/","outputs":1,"noerr":0,"x":1070,"y":180,"wires":[["a91e26c5.d2ed48","c86b585b.736d78"]]},{"id":"a91e26c5.d2ed48","type":"debug","z":"87eeee6.729851","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","x":1290,"y":120,"wires":[]},{"id":"bd18f1d0.01967","type":"switch","z":"87eeee6.729851","name":"","property":"topic","propertyType":"msg","rules":[{"t":"eq","v":"save-button","vt":"str"}],"checkall":"true","repair":false,"outputs":1,"x":470,"y":120,"wires":[["b107d7d0.8bcf98"]]},{"id":"7e1ee6ef.c4ec08","type":"mui_button","z":"87eeee6.729851","name":"save-button","group":"862f3f87.367da","order":4,"width":0,"height":0,"passthru":false,"label":"Save","color":"","bgcolor":"","icon":"","payload":"{}","payloadType":"global","topic":"save-button","x":90,"y":220,"wires":[["4f6f5725.bf21d8"]]},{"id":"8f59d5e9.f9bf98","type":"mui_text_input","z":"87eeee6.729851","name":"name","label":"Name","group":"862f3f87.367da","order":1,"width":0,"height":0,"passthru":false,"mode":"text","delay":300,"topic":"name","x":70,"y":40,"wires":[["4f6f5725.bf21d8"]]},{"id":"17abf0bb.ba670f","type":"mui_dropdown","z":"87eeee6.729851","name":"lastname","label":"","place":"Select Last Name","group":"862f3f87.367da","order":2,"width":0,"height":0,"passthru":false,"options":[{"label":"Rishan","value":1,"type":"num"},{"label":"Login","value":2,"type":"num"}],"payload":"","topic":"lastname","x":80,"y":100,"wires":[["4f6f5725.bf21d8"]]},{"id":"a8d9a9b0.81f358","type":"mui_text_input","z":"87eeee6.729851","name":"code","label":"Code","group":"862f3f87.367da","order":3,"width":0,"height":0,"passthru":false,"mode":"text","delay":300,"topic":"code","x":70,"y":160,"wires":[["4f6f5725.bf21d8"]]},{"id":"4f6f5725.bf21d8","type":"join","z":"87eeee6.729851","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":290,"y":120,"wires":[["bd18f1d0.01967"]]},{"id":"ce323c92.b8766","type":"mysql","z":"87eeee6.729851","mydb":"272ea16.4569d5e","name":"database","x":880,"y":120,"wires":[["a91e26c5.d2ed48"]]},{"id":"ea75af48.2081b","type":"switch","z":"87eeee6.729851","name":"","property":"payload[0].count","propertyType":"msg","rules":[{"t":"eq","v":"0","vt":"num"},{"t":"else"}],"checkall":"true","repair":false,"outputs":2,"x":870,"y":240,"wires":[["cb5c2136.c89d5"],["617d5616.2edfb8"]]},{"id":"617d5616.2edfb8","type":"function","z":"87eeee6.729851","name":"failure-msg","func":"actionString = \"Data insertion failed!\";\nmsg.payload = actionString;\nreturn msg;","outputs":1,"noerr":0,"x":1070,"y":300,"wires":[["a91e26c5.d2ed48"]]},{"id":"c86b585b.736d78","type":"mysql","z":"87eeee6.729851","mydb":"272ea16.4569d5e","name":"database","x":1280,"y":180,"wires":[[]]},{"id":"862f3f87.367da","type":"mui_group","z":"","name":"Input Test - Multiple Users","tab":"29df8a3d.2c7ba6","order":1,"disp":true,"width":"6","collapse":false},{"id":"272ea16.4569d5e","type":"MySQLdatabase","z":"","host":"192.168.3.10","port":"3306","db":"ram_test","tz":""},{"id":"29df8a3d.2c7ba6","type":"mui_tab","z":"","name":"I4Inari","icon":"dashboard","order":1}]

```

---

<div class="post-metadata">

**Author:** ![rishanaziz](https://avatars.discourse-cdn.com/v4/letter/r/f14d63/32.png) [@rishanaziz](https://discourse.nodered.org/u/rishanaziz)\
**Post date:** [24 July 2018 02:51 UTC](https://discourse.nodered.org/t/how-to-check-whether-there-is-existing-data-before-allowing-users-to-insert-the-data-to-the-database/1754/9 "2018-07-24T02:51:49Z")

</div>

I already got the answer. Thank you for your help. Really appreciate it.

---

<div class="post-metadata">

**Author:** ![Tefita](https://avatars.discourse-cdn.com/v4/letter/t/a8b319/32.png) [@Tefita](https://discourse.nodered.org/u/Tefita)\
**Post date:** [7 February 2019 04:36 UTC](https://discourse.nodered.org/t/how-to-check-whether-there-is-existing-data-before-allowing-users-to-insert-the-data-to-the-database/1754/10 "2019-02-07T04:36:28Z")

</div>

I have tried to modify your shared flow but I can not see the error, you can help me with the flow @rishanaziz

---

<div class="post-metadata">

**Author:** ![zenofmud](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/zenofmud/32/316_2.png) [@zenofmud](https://discourse.nodered.org/u/zenofmud)\
**Post date:** [7 February 2019 10:13 UTC](https://discourse.nodered.org/t/how-to-check-whether-there-is-existing-data-before-allowing-users-to-insert-the-data-to-the-database/1754/11 "2019-02-07T10:13:29Z")

</div>

If you want to use the INSERT with the DUPLICATES KEY option I suggest you do a google search to see what the syntax is and the table definition requirements

---

<div class="post-metadata">

**Author:** ![Tefita](https://avatars.discourse-cdn.com/v4/letter/t/a8b319/32.png) [@Tefita](https://discourse.nodered.org/u/Tefita)\
**Post date:** [7 February 2019 15:27 UTC](https://discourse.nodered.org/t/how-to-check-whether-there-is-existing-data-before-allowing-users-to-insert-the-data-to-the-database/1754/12 "2019-02-07T15:27:47Z")

</div>

I have decided to continue working with the shared flow of @rishanaziz , I wish that there is no duplicate data, that is, if the name already exists in the table, the message "duplicate name, failure of data entry" is shown, otherwise a message "data entered correctly". but I can not store the data in the table even though "name" is new.  
I use mysql

Regards

---

<div class="post-metadata">

**Author:** ![zenofmud](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/zenofmud/32/316_2.png) [@zenofmud](https://discourse.nodered.org/u/zenofmud)\
**Post date:** [7 February 2019 16:02 UTC](https://discourse.nodered.org/t/how-to-check-whether-there-is-existing-data-before-allowing-users-to-insert-the-data-to-the-database/1754/13 "2019-02-07T16:02:09Z")

</div>

Well I that case, I would attach a debug node to the output of your sql node (displaying the complete msg object) Them you can do an insert of a row that doesn’t exist and an insert of a row that does exist. Comparing the debug output will give you the information you need to determine if tha insert worked or failed and you can send message based on that.

---

<div class="post-metadata">

**Author:** ![Tefita](https://avatars.discourse-cdn.com/v4/letter/t/a8b319/32.png) [@Tefita](https://discourse.nodered.org/u/Tefita)\
**Post date:** [7 February 2019 21:35 UTC](https://discourse.nodered.org/t/how-to-check-whether-there-is-existing-data-before-allowing-users-to-insert-the-data-to-the-database/1754/14 "2019-02-07T21:35:06Z")

</div>

Image 1 shows the msg debug of a repeated "name", where count: 1

![dato_repetido](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/2X/7/790679945236d81953a24a7cb09c38a089095791.png)

Image 2 shows a new "name", where count: 0  
 ![dato_nuevo](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/2X/5/5b0057a7f5cb451b706d4c876b7e4df1784393a4.png)

For this reason I have configured the node siwtch in the following way.

 ![NODE_SWITCH](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/2X/6/6d846d6710a65767016a1ead6ba7f84dd0838934.png)

I can not insert the data in the table.  
Can you help me please?

---

<div class="post-metadata">

**Author:** ![ukmoose](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/ukmoose/32/13_2.png) [@ukmoose](https://discourse.nodered.org/u/ukmoose)\
**Post date:** [7 February 2019 21:58 UTC](https://discourse.nodered.org/t/how-to-check-whether-there-is-existing-data-before-allowing-users-to-insert-the-data-to-the-database/1754/15 "2019-02-07T21:58:34Z")

</div>

a switch node can’t insert data, but in one of your tests  
you have it as a number (the small 0 9) and the other you have it as a string (small a z)

---

<div class="post-metadata">

**Author:** ![Tefita](https://avatars.discourse-cdn.com/v4/letter/t/a8b319/32.png) [@Tefita](https://discourse.nodered.org/u/Tefita)\
**Post date:** [7 February 2019 22:05 UTC](https://discourse.nodered.org/t/how-to-check-whether-there-is-existing-data-before-allowing-users-to-insert-the-data-to-the-database/1754/16 "2019-02-07T22:05:10Z")

</div>

Thanks for the observation, but I already modified it:

 ![SWITCH_1](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/2X/2/22620a97818cf8936aa583ffd936772094230da3.png)

But later I have configured the following in node function:  
insert:

```auto
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];

```

failure:

```auto
actionString = "Ya se ingresado ese nombre!";
msg.payload = actionString;
return msg;

```

I attach the capture of the flow

 ![FLUJO_1](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/2X/c/cf5cb6c1680896c91b17d82f999d252a445edd7f.png)

---

<div class="post-metadata">

**Author:** ![zenofmud](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/zenofmud/32/316_2.png) [@zenofmud](https://discourse.nodered.org/u/zenofmud)\
**Post date:** [8 February 2019 00:12 UTC](https://discourse.nodered.org/t/how-to-check-whether-there-is-existing-data-before-allowing-users-to-insert-the-data-to-the-database/1754/17 "2019-02-08T00:12:49Z")

</div>

so you build an insert statement and I see you have a debug attached to the output of the function that builds it. Make sure you have that debug node set to display the complete msg object then show us the output of that debug.

You also have a debug node attached to the database node - please set that to display the complete msg object and show up that output too.

---

<div class="post-metadata">

**Author:** ![Tefita](https://avatars.discourse-cdn.com/v4/letter/t/a8b319/32.png) [@Tefita](https://discourse.nodered.org/u/Tefita)\
**Post date:** [8 February 2019 01:10 UTC](https://discourse.nodered.org/t/how-to-check-whether-there-is-existing-data-before-allowing-users-to-insert-the-data-to-the-database/1754/18 "2019-02-08T01:10:39Z")

</div>

I only get the msg debug from the node insert:  
 ![node_insert](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/2X/8/85ea117b67784481d61e02dc72438985c866503f.png)

and I get this error.  
How can I solve it please?

---

<div class="post-metadata">

**Author:** ![ukmoose](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/ukmoose/32/13_2.png) [@ukmoose](https://discourse.nodered.org/u/ukmoose)\
**Post date:** [8 February 2019 07:20 UTC](https://discourse.nodered.org/t/how-to-check-whether-there-is-existing-data-before-allowing-users-to-insert-the-data-to-the-database/1754/19 "2019-02-08T07:20:49Z")

</div>

what function is giving you that error message?

what message are you sending to that function?

---

<div class="post-metadata">

**Author:** ![zenofmud](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/zenofmud/32/316_2.png) [@zenofmud](https://discourse.nodered.org/u/zenofmud)\
**Post date:** [8 February 2019 08:36 UTC](https://discourse.nodered.org/t/how-to-check-whether-there-is-existing-data-before-allowing-users-to-insert-the-data-to-the-database/1754/20 "2019-02-08T08:36:55Z")

</div>

That is saying you have a error in the function node. Since you haven’t provided your flow all anybody can do at this point is say ‘You need to fix the error’

[Next page](https://discourse.nodered.org/t/how-to-check-whether-there-is-existing-data-before-allowing-users-to-insert-the-data-to-the-database/1754.md?page=2)