Solved

Check for duplicate username before processing a form?

Posted on 2007-04-04
23
606 Views
Last Modified: 2009-12-16
I have a member sign up form on a page that when a user submits, goes to a process page with an insert.


How can I set it up to first check to see if that user name has already been taken before it goes to the process page???

I am assuming that i would do some kind of "On Submit " function or something but I need to know the easiest way to do this. basicallly avoiding duplicates being stored. I think about 99 percent of all member type sites have this function, i just dont know how to do it in this situation.

Judson
0
Comment
Question by:judsonmusic
  • 9
  • 8
  • 3
  • +2
23 Comments
 
LVL 28

Expert Comment

by:gamebits
ID: 18850009
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.
0
 
LVL 2

Expert Comment

by:nmarano
ID: 18850101
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>
0
 
LVL 3

Author Comment

by:judsonmusic
ID: 18850178
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?
0
 
LVL 3

Author Comment

by:judsonmusic
ID: 18850192
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?
0
 
LVL 2

Expert Comment

by:nmarano
ID: 18850297
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.  
0
 
LVL 28

Expert Comment

by:gamebits
ID: 18850327
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.
0
 
LVL 36

Expert Comment

by:SidFishes
ID: 18850378
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>

















0
 
LVL 2

Expert Comment

by:nmarano
ID: 18850566
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.  
0
 
LVL 39

Expert Comment

by:gdemaria
ID: 18850617

 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>

0
 
LVL 3

Author Comment

by:judsonmusic
ID: 18850776
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???
0
 
LVL 39

Expert Comment

by:gdemaria
ID: 18851063

> 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.  

0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 3

Author Comment

by:judsonmusic
ID: 18851965
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
0
 
LVL 39

Expert Comment

by:gdemaria
ID: 18852236

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.




0
 
LVL 3

Author Comment

by:judsonmusic
ID: 18852283
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
0
 
LVL 39

Expert Comment

by:gdemaria
ID: 18852409
> 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#">



0
 
LVL 39

Expert Comment

by:gdemaria
ID: 18852419

 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...
0
 
LVL 3

Author Comment

by:judsonmusic
ID: 18858939
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
0
 
LVL 3

Author Comment

by:judsonmusic
ID: 18858963
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
0
 
LVL 39

Expert Comment

by:gdemaria
ID: 18859061

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

Author Comment

by:judsonmusic
ID: 18859087
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
0
 
LVL 3

Author Comment

by:judsonmusic
ID: 18859190
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>
0
 
LVL 39

Expert Comment

by:gdemaria
ID: 18859223

 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 :)
0
 
LVL 39

Accepted Solution

by:
gdemaria earned 500 total points
ID: 18859494

<cfparam name="form.userID" default="">

<cfset variables.error = "">
<cfif IsDefined("FORM.submit") AND FORM.submit EQ "add_user">
  <cftry>

    <cfif len(form.fstnam) eq 0>
        <cfthrow message="First Name is required">
      </cfif>
    <cfif len(form.username) eq 0>
        <cfthrow message="Username is required">
    <cfelseif len(form.username) lt 6 or len(form.username) gt 20>
        <cfthrow message="Username must be between 6 and 20 characters>
      </cfif>
      <cfquery name ="checkUser" datasource = "hgram_med">
        SELECT username FROM dbo.users
        WHERE username ='#FORM.username#'
        <cfif len(form.userID)> <!---- for users who are changing their username ---->
          and  userID != #form.userID#
        </cfif>
      </cfquery>
      <cfif checkuser.recordcount GT 0>
         <cfthrow message="The username is already taken">
      </cfif>

      <!--- if you made it here, update the database ---->
      <cfif len(form.userID)> <!--- update existing user --->
            <cfquery name="UpdUser">
             update users
               set username = '#form.username#'
                 , fstname  = '#form.fstnam#'
             where userID = #form.userID#
            </cfquery>
      <cfelse> <!---- insert to create a new user ---->
            <cfquery name="insUser">
              insert users (fstnam,username)
              values ('#form.fstnam#','#form.username#')
            </cfquery>
            <cfquery name="GetPkey" datasource="hgram_med">
          select SCOPE_IDENTITY( ) as ID
        </cfquery>  
        <cfset form.userID = GetPkey.ID>
      </cfif>
      
      <!--- everything went ok, no to next page --->
      <cflocation url="#cgi.script_name#?userID=#form.userID#" addtoken="No">
      
  <!-- this will trap any errors- the ones you threw
   or just regular database issues --->
  <cfcatch type="Any">
    <cfset variables.error = cfcatch.error>
  </cfcatch>
  </cftry>
</cfif>
<cfif len(variables.error) eq 0>
  <!--- get data and convert to form variables --->
  <cfquery name="getUsers" datasource="hgram_med">
    select * from users where userID = #val(form.userID)#
  </cfquery>
  <cfset form.fstname = getUsers.fstname>
  <cfset form.userID = getUsers.userID>
  <cfset form.username = getUsers.username>
</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>
<cfif len(variables.erorr)> <!-- if an error, show it --->
 <cfoutput>
 <div style="border: 1px solid red; padding: 5px 10px 5px 10px; width:400px;">#variables.error#</div>
 </cfoutput>
</cfif>
<cfoutput>
<input name="userID" type="text" value="#form.userID#">
<form method="post">
 <p>
  <label for="fstnam">First Name</label>
  <input name="fstnam" type="text" value="#form.fstname#">
  </p><p>
  <label for="username">Login Name</label>
  <input name="username" type="text" value="#form.username#">
  </label>
  </p><p>
  <label>
    <input type="submit" name="Submit" value="Add_user">
  </label>
  </p>
</form>
</cfoutput>
</body>
</html>

0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

I spent nearly three days trying to figure out how incorporate OAuth in Coldfusion for the Eventful API. Hopefully, this article will allow Coldfusion Programmers to buzz through the API when they need to. Basically, what this script does is authori…
JavaScript can be used in a browser to change parts of a webpage dynamically. It begins with the following pattern: If condition W is true, do thing X to target Y after event Z. Below are some tips and tricks to help you get started with JavaScript …
The viewer will learn the basics of jQuery, including how to invoke it on a web page. Reference your jQuery libraries: (CODE) Include your new external js/jQuery file: (CODE) Write your first lines of code to setup your site for jQuery.: (CODE)
The viewer will learn the basics of jQuery including how to code hide show and toggles. Reference your jQuery libraries: (CODE) Include your new external js/jQuery file: (CODE) Write your first lines of code to setup your site for jQuery…

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now