Check for duplicate username before processing a form?

On your process page you add a query to check the database for this username if the result is empty you keep going with the insert if not you go back to the previous page with a message saying this username is already taken.

Javascript would not help in this situation because you have to query the database to see if the username exist already.

At the top of the processing page you could have a query with an cfif statement.  Something like this

<cfquery name ="checkUser" datasource = "yourDSN">
SELECT username
From yourtable
WHERE username ='#FORM.UserName#'
</cfquery>

<! --- Write your if statement to check if that user name is taken --->

<cfif checkuser.recordcount GT 0>
<p align="center">That UserName is already taken. </p>
<p align="center"><a href="registration.cfm">Please click  
go back to select a new username</a></p>
</cfif>

nmarano


This will work but the problem is that when it goes to the process page, I get a coldfusion duplicates error also.

I need to process this on the form page becuase also when I click the link to go back to registration, everything is cleared. How can I set this to happen before the page is submitted to the process page?

I guess is ther ea way to do this query on submit and stay ion the form if that username is already taken???

Thanks

Judson

Could you put a cfif on the form action or something?

I would think you could do something in Javascript, but I am unsure of how to do it.  You could possible have a query on your reg page, that pulls all usernames from your db.  Then you could have a javascript function on submit that will check if that username is taken.  I don't know enough about JS to give you a solution, but believe it can be done.  

Yes you could do a query before hand and put all the username in a javascript array and use that array to perform the check but if you have a huge amount of username this is not the best solution, another way would be to use AJAX to query the db in the background, but I see this is coldfusion and I know exactly nothing about coldfusion, I will not be able to help, sorry.

there is a JS way to do this...but involves ajax, cfc's and a quick trip to the server... might be a bit advanced.. can post if you want

Nick's got the right idea but you need a bit more... if you don;t have your insert query within the if else logic the insert will still happen and you'll have a dupe..

your process page structure should e something like this

<cfif isdefined('username')>

<cfquery name="UNCheck" datasource="yourdsn">
select username from mytbl where username="<cfqueryparam cfsqltype="cf_sql_varchar" value="#form.username">
</cfquery>

<cfif UNCheck.recordcount NEQ 0><!--- then exists...throw error --->
<cflocation url="http://www.youserver.com/signup.cfm>
<cfelse>

Process the sign up as normal

</cfif>

if you want to retain for data you'll have to either pass the values as url variables

<cflocation url="http://www.youserver.com/signup.cfm?error=1&address1=#form.address1&phone=Form.phone#>

or as session variables

<cflock timeout="30" type="exclusive scope="session">
<cfset session.address1 = Form.address1>
<cfset session.phone = Form.phone>
<cfset session.error = 1>
</cflock>

then your signup page would look something like this

if using url variables
<cfparam name="url.address1 default="">
<cfparam name="url.phone default="">
<cfparam name="url.error" default="">

<form...>

<div style="color:##0000CC;font-weight:bold;">
<cfif url.error eq 1>
Sorry, that username is already taken.
</cfif>
</div>
<input type="text" value="" name="username">
<input type="text" value="#url.Address1#" name="address1">
<input type="text" value="#url.Phone#" name="phone">
<input type="submit">
</form>



if using session variables


<cflock timeout="30" type="readonly" scope="session">
<cfset vAddress1 = session.address1 >
<cfset vPhone = session.phone>
<cfset vError = session.error>
</cflock>


<form...>

<div style="color:##0000CC;font-weight:bold;">
<cfif vError eq 1>
Sorry, that username is already taken.
</cfif>
</div>
<input type="text" value="" name="username">
<input type="text" value="#vAddress1#" name="address1">
<input type="text" value="#vPhone#" name="phone">
<input type="submit">
</form>

judsonmusic,

Look at sid's solution.  It seems that will be the best fit for what you're trying to do.  Sid I agree he is going to have to pass the form variables as either session or url.  

 Here's another method.   This is an outline of a simple framework that you can use on any form page.   The page should submit to itself, if successful it will redirect to the next page (perhaps a thank you page).   If it fails, the same page will redraw showing all the same values as entered by the user, along with an error message.  

 The jist of this framework is simply that all values pulled from the database are converted to form variables before displaying the form.   If there is an error, do not pull the values from the database, just use the posted form variables.

 Note that you really need to use server-side validation, even if you choose to use javascript as an initial validation, ultimately on post, you need to validate it before processing in your database.  Its very easy to get around javascript validation, doing so would allow bad data into your database unless you validated just prior to inserting/updating.


 This example uses the idea of a simple contact ('owner') update.


<!--- this is the primary key for the data on this page, expect it to be passed,
      if it is empty, the page will create a new record ----->
<cfparam name="form.contact_id" default="">  
             
<cfset variables.error = "">  <!---- this variable will hold an error message, if a problem occurs ---->

<cfif IsDefined('form.submit')>  <!-- the page has been submitted, process action --->
  <cftransaction action="BEGIN"> <!--- a transaction ensure everything on page is saved together or rolledback on error ---->
  <cftry>  <!---- catch any errors in the cfcatch below ----->

    <!---- do all validation checks here
             including query to check for duplicates ---->
    <!--- if any problems, throw an error ---->  
          <cfthrow message="Please supply all required fields">

      <!--- all is well, perform your update ---->
    <cfinclude template="owner_upd.cfm"> <!--- this file inserts or updates the data --->

    <cftransaction action="COMMIT">
  <cfcatch type="Any"> <!---- catch all errors, whether you cfthrow them or if happens in your sql statements ---->
     <cftransaction action="ROLLBACK">
     <cfset variables.error = cfcatch.message> <!---- set your error flag message variable ---->
  </cfcatch>
  </cftry>
  </cftransaction>
  <cfif len(variables.error) eq 0> <!--- if no error, go to your next page ---->
     <cflocation url="/nextpage.cfm?ID=AddVariablesHere" addtoken="no">
  </cfif>
</cfif>


<cfif len(variables.error) eq 0>
   <!--- Do this Only the first time opening the page,
         this will not run there is an error
             ---->
   <cfquery name="getContact" datasource="#request.datasource#">
     select * from contacts
       where contact_id = #val(form.contact_id)#
   </cfquery>
   <!---- the next line will copy every column from the query into a FORM
          variable.  This will allow you to use the form scope below instead
              of the query name.  In case of an error, this allows your page
              to reload the entered values without saving them ---->
   <cfloop index="ii" list="#getContact.columnList#">
      <cfset form[ii] = getContact[ii][getcontacts.currentRow]>
   </cfloop>
   
</cfif>

<cfif len(variables.error) gt 0> <!--- if an error, show it ---->
  <cfoutput><span style="color:red">#variables.error#</span></cfoutput>
</cfif>


<!--- here is your form to get the data ---->

<cfoutput>
<form method="post">  <!---- No Action in form, page will post to itself ---->

 First Name: <input type="Text" name="firstName" value="#form.firstName#"><br><br>
 
 Last Name: <input type="Text" name="LastName" value="#form.lastName#"><br><br>

 Email: <input type="Text" name="email" value="#form.email#"><br><br>

 <input type="Submit" name="submit" value="Save">

</form>
</cfoutput>

The proble is that I am using the form as an add or update form so there are already values assigned such as value="mytable.username"

Is ther a way to put a cfif around the values???

> The proble is that I am using the form as an add or update form

That's just what this framework is used for.  Creating and modifying records.  

> so there are already values assigned such as value="mytable.username

Sorry, not sure what you mean..

Are you saying that some of the input fields have values already populated in them, that already exist in the datbase?  For example, the record already exists with the person's first and last name and should be prepopulated in the form?

All values from the database are assigned into form variables.  Then they are used in the INPUT tags as value="#form.username#"    When you submit the page, the username is submitted and become FORM.USername if processed it will be part of your update username = '#form.username#'  

If it fails, it will drop back down into the input tag  with value="#form.username#"   Perfect !


This is a very common framework, where a page is posted to itself for processing.  Errors are handled locally and the page can be drawn with the same information.   I think you (everyone) should give it a try if they don't have something like this.  

So in other words, you are setting the form values at the top of the page like:

<cfparam name=#FORM.username# default ="mytable.username">

Then in the bottom or in the form, you set the value for input feild "username" to what ever #FORM.username# is?


Judson

Pretty close, instead of using CFPARAM, which would conditionally set the value, I am using CFSET.  Also, it's not done at the top, its done here...

 <cfloop index="ii" list="#getContact.columnList#">
      <cfset form[ii] = getContact[ii][getcontacts.currentRow]>
  </cfloop>

 This will loop for every column of the query and assign it to a FORM. variable, its the same as saying this..

 <cfset form.firstName  = getContact.firstName>
 <cfset form.lastName  = getContact.lastName>
 <cfset form.username = getContact.usernmae>


>  Then in the bottom or in the form, you set the value for input feild "username" to what ever #FORM.username# is?

Yes, that's right, as shown here..

 First Name: <input type="Text" name="firstName" value="#form.firstName#">

So the input tag will have the data from the table the first time, but on error will instead show what the user entered instead.   This is handled by this CFIF...

<cfif len(variables.error) eq 0>
   <cfquery name="getContact" datasource="#request.datasource#">
     select * from contacts
       where contact_id = #val(form.contact_id)#
   </cfquery>
   <cfloop index="ii" list="#getContact.columnList#">
      <cfset form[ii] = getContact[ii][getcontacts.currentRow]>
   </cfloop>
</cfif>

If no error, pull from database and assign to form variables.  If there is an error, slip this step and use the form variables we get from the form when it posted.

So this builds a form dynamically huh?

Right now I have a form built on a table with the form feilds defined the same aas the feilds in the database.


Judson

> So this builds a form dynamically huh?

No, no.  You have to build the form.  See the INPUT tags at the bottom?   The only dynamic part is populating the data tables into the form variables.  

> Right now I have a form built on a table with the form feilds defined the same aas the feilds in the database.

Perfect, just the way you want it !

Try placing this block of code under your query...
 ( change "MyQuery" to your query name )

   <cfloop index="ii" list="#MyQuery.columnList#">
      <cfset form[ii] = MyQuery[ii][1]>
   </cfloop>

   <cfdump var="#form#">

 Then, make sure all your value="" parameters use the FORM scope, NOT your query name

So instead of this..
<input type="Text" name="email" value="#myQuery.email#">

Do this...
<input type="Text" name="email" value="#form.email#">

Now try to run your page...

I hate to ask, but if I give you the form, can you alter it and show me how it should be???

Or just re-explain your code step, by step. im sorry. Im alittle confused

Just to explain my situation. I have a blank form that is used for updating or adding a new member.

If you are adding a new member, I want the username feild to take whatever is enterd and check to see if there is already a record in the database with that username, if so, display an error.


Thanks

judson

 I understand Judson, no problem.  Please post the full contents of your form file.

Before i do that, is there not a way to out a javascript that ould sya like, "ONSUBMIT" run the query to check for the exsistence of the username and if that username exists, display error, but dont do the form action yet???


Thanks

Take a look at this, it seems to work just fine. What do you think?

<cfparam name="URL.test" default="0">
<cfif IsDefined("FORM.submit") AND FORM.submit EQ "add_user">

<cfquery name ="checkUser" datasource = "hgram_med">
SELECT username
FROM dbo.users
WHERE username ='#FORM.username#'
</cfquery>

<cfif checkuser.recordcount GT 0>
<cfoutput>That username already exists!</cfoutput>
</cfif>
</cfif>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>Untitled Document</title>
</head>

<body>

<form action="formtest.cfm?test=1" method="post" name="form1" id="form1">

  <label>
  <input name="fstnam" type="text" id="fstnam" <cfif #url.test# EQ 1>value="<cfoutput>#FORM.fstnam#</cfoutput>"</cfif>/>
  </label>
  name
  <p>&nbsp; </p>
  <label>
  <input name="username" type="text" id="username" />
  </label>
  username
  <p>
    <label>
    <input type="submit" name="Submit" value="Add_user" />
    </label>
</p>
</form>
</body>
</html>

 You could do that through remote services such as AJAX.  Its more complex than regular javascript because you have to go all the way back to the server to run a database query.  Typically, javascript just runs right on the client's browser and can do simple things like  - you didn't enter the field.   But in order to check the database (which is over the internet), javascript has to take a full trip back to the server which means either a form submit or something like ajax.

 But keep in mind that you really really want to get to the point for server-side validation (meaning validating by coldfusion).  You want this for many reasons.  Javascript can be tricked or turned off easily.  But if you're not worried about security, what about the "accidental" errors.  Someone types a name too long for the field?  The database will throw an error and you want to trap it elegantly and return to the same page.   Also, there are tons of validation that you will do that will require the database or more sophisticated processing. this username check is one of them.  

 So, unless this is your last form and not one of your first, I would charge forward into this territory, master it and learn to love it :)