Check if a Database Record Already Exists Before Inserting a New Entry

[George]

Check the data in your Database before inserting possible duplicate data

Using server-side data validation from server connect you can check if a record already exists in the database. This is useful when new users register to your site so you can check if the email is already used and if it is display an error message, while at the same time stopping the server insert action from inserting duplicate data.

---

 
Step 1: Select your Insert Action(1) and right click the step before the insert record step(2):

---

 
Step 2: Select Validator:

---

 
Step 3: Select Add Validate Data:

---

 
Step 4: From the validate date properties panel click validate options:

---

 
Step 5: Click the add new(+) button:

---

 
Step 6: Double click the expression field to display the dynamic data picker icon, then click the dynamic data picker:

---

 
Step 7: Select the POST variable (form input) who’s value you want to validate. In our case this is the email input field in our pages form, click select when done:

---

 
Step 8: Then add a new validation rule for the selected expression:

---

 
Step 9: Open the Database category:

---

 
Step 10: And select Does not exist in database:

---

 
Step 11: Select your database connection:

---

 
Step 12: Select the database table you want to insert your data into:

---

 
Step 13: And select the database table column you want to use for the existing record check:

---

 
Step 14: Customize the error message that will display if a record does already exist:

---

 
Step 15: In the linked field enter the “name” of the form field, which is on your page. If you don’t do this, the error message won’t appear on the page:

---

 
Step 16: Click Save:

---

 
Step 17: Then save your server action and you are done. The insert will not run if the record already exists in the database.

---

   

---

[mpardini]

Hello everyone! Happy memorial day for those in the US.

Can someone help me figure out why the error message is not showing up on my website for checking if an ID is in the database?

I am getting the right error in my database debugger: "UserID: “User does not exist in database.”, but nothing on the client side.

I think it has something to do with Step 15 below, but I believe that I am using the right form field name.

“Step 15: In the linked field enter the “name” of the form field, which is on your page. If you don’t do this, the error message won’t appear on the page:”

[jeoff75]

Thanks for this tutorial George.
I followed the steps described.
But it seems to stop the repeat action from processing the next entries when an error is met.

How can I let wappler know to continue processing the next entries in the repeat loop ?

2019-10-04_1556

[Teodor]

Hello @jeoff75
If you need to do this, don’t use a validation step.
The validation step will always stop the server action if a record is found.

In your case you need to add a query (in the place of validation step), filtered by the value you are checking. Then add condition step, and use the query as an expression. If the query has a value, the then steps of the conditions will run, if a query has no value, i.e. record not found the else steps will run.

Also - you can directly drag and drop images in your post, no need to link to a third party website.

[jeoff75]

Thanks for your help !

I tried what you described.
The logic didn’t work in the beginning because I had checked the “debug” option in the query.

But now everything works fine.
Both with {{transactionUnicity}} or {{transactionUnicity.length}} as a condition.

[nshkrsh]

can this be made to work when we are to insert bulk records not a single record?

[Hyperbytes]

Just drop the condition section into a repeat based on the initial query, should work fine.

[nshkrsh]

Yeah, this is possible. But of we have, say, a 10,000 records. This would be considerably slow given the looping that’ll have to be done.

[Hyperbytes]

Then I would suggest you use a custom query using “Insert on Duplicate key update” like this:

INSERT INTO mytable ( id, field_name)
VALUES
(Value1, Value2)
ON DUPLICATE KEY UPDATE field_name =Value2;

[jeoff75]

On step 14, in the message field, is there a way to put a key (instead of a text string for the error) that will be evaluated dynamically on the front end ?

I’m trying to localize completely my applications.
When I need to display error messages on the front end directly, it works just fine.

For instance, to validate a required field, I would add on my input :
dmx-bind:required=“true” dmx-bind:data-msg-required="{{translation.value.main_login__missing_login_label}}"

But I’m stuck on this one, to pass the error key from server connect to the front end and then make the front end load the key.
All variants of key, {{key}} or dmx.parse(key) I could try in step 14 failed so far.

I’m not even sure if this is supported ?

Sidenote : In case someone else runs into the “error not displaying on frontend” problem after following this tutorial, head here for the solution :

"Check if a Database Record Already Exists" not showing error message

Assuming you have set the linked field correct Have you any validation set in app connect for the same input?. [image] If there is no validation set such as “required” etc in app connect for that field then there is no error container in app connect to display the message

[salvuccicarlos]

Hello! Happy new year! How do I show the validation message on a notification? I used the “if invalid” but when i put, “show last error message” the notification says [Object object]

[psweb]

Just to try and clear up some confusion with this, for people that are not seeing the validation error message after performing the steps in this tutorial, it is more than likely because you have not added any type of client side form validation.

In other words if my client side form was built with no validation set to any field at all, and i performed the steps above, no error message would show at all, because the head of your document needs the dmxValidator.css and dmxValidator.js otherwise it will not work.

Try adding a client side validation to any field, such as required, and try again, and the error should display as expected.

[JonL]

Just some security/privacy good practice.

Depending on how sensitive your app is you may not want to inform the browser user if an email exists in the database. This information can be exploited in several ways(spam, brute force attacks, etc).

You may however want to do something yourself with that information like sending a message to the email entered(if it exists in database) informing that there has been an attempt of registering their email again and send them a password reset link(in case it’s the legitimate user) and/or adding information about the person that tried to do register (ip address, browser, location, etc).

So for other fields and other use cases this might a very good approach. Just be careful what information you provide to strangers

[magdy.reda]

JonL, good suggestion.

Then, how can I capture the server side validation output? As far as I understand it only has a message option as output.

Even if I activate its output, I cannot see how to capture it in a variable to be used in a conditional step (i.e. if true send email - else … ).

Thanks.

Mag

[Hyperbytes]

Th validation component is for validating input only. If you want to perform extended operations then I suggest you use a query instead and query the parameter. You can then perform conditional actions or return information back to app connect.

[tomkomin]

Can a link be used in the message. If so, what is the syntax.

It just shows the code:
This email is already registered. If you have forgotten your login info, <a href="forgot_password.php">Click Here</a>.
instead of:
This email is already registered. If you have forgotten your login info, Click Here.

Thanks,
TOm

[guptast]

Hi @George,

I have followed the steps in your post above to set up a validation to check and avoid duplicate records in the database. However, this is not working correctly for me. I can insert duplicate records in the database table with the validation rule set as ‘Does not Exist in Database’.

The POST request is returning Status Code: 200 in the dev tools for the duplicate records as well. The input form field has been set up with Required validation.

The setup for inserting records and validation looks like this:

I’m not sure if I’m missing a step in the setting up of validation rule. Any help or ideas of where to look would be greatly appreciated.

[guptast]

Hi @George,

The validation is working correctly now. The error messages are also showing when duplicate values are found in the database.

I resolved this issue by renaming the file extensions from .html to .apsx (suggested by @psweb in reference to another issue I had when setting up the login system, based on his video Full Login Procedure).

I don’t know whether there’s a way to resolve this issue by keeping the .html extensions. But if there’s any way, then it would be really helpful to know that as well.

Thank you for your continued support

[psweb]

I doubt there will ever really be a way around this, anything that requires a server action script to run, is going to perform checks on the server side, and the only way to pass values from server side to client side is by using .aspx or .php depending on your server environment. If the validator was checking only with client side validation then you could keep the .html but it can not then look at all the database records to ensure no duplicate exists.
I really do not think there is anything that can be done, as it’s not a Wappler related problem as such, but more of a current web server technologies order of operation.

[thines]

i have same issues, i did validator name both same, and have the right field name and database field. also have required in form, but still this check is not working. anyone help?

i’m a validating the email not exist check, i have email field same as the database field. also i have required and email validation on the form field. my validator name is “validateUserEmail” on both.

Note: im using nodejs.

please see the screenshot below: