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

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.


WapplerPrevious   WapplerNext


4 Likes

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:”

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 ?

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.

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.

2019-10-04_1803

1 Like

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

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

1 Like

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.

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;

3 Likes

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 ? :slight_smile:

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

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]

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.

1 Like

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 :slight_smile:

2 Likes

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

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.

2 Likes