Hi All, I'm trying to force a uniq 3

[PTmon]

Hi All,
I'm trying to force a unique username during a web sign-up. Needless to say, it isn't working. Here's what I tried:

<!-- content starts here -->
<CFQUERY NAME=&quot;getemail&quot; DATASOURCE=&quot;productlocator&quot;>
SELECT *
FROM admin
</CFQUERY>

<!--- make user username is unique --->
<cfoutput><cfif #getemail.username# CONTAINS #Form.username#>
<div class=&quot;maintext&quot;>Please go back and try a different username.<br>
The username &quot;#Form.Username#&quot; is already in use.</div>
<cfabort>
</cfif></cfoutput>

 

[Jami]

You have to first loop through your query output, which with your original code you are not yet doing, in order to test every record in your table. Try this:

<cfoutput query = &quot;getemail&quot;>
<cfif #username# contains &quot;#form.username#>
<div class=&quot;maintext&quot;>Please go back and try a different username.<br>
The username &quot;#Form.Username#&quot; is already in use.</div>
<cfabort>
</cfif>
</cfoutput>

 

[tleish]

The way you've called #getemail.username# will only return the first record in the query, not a list of the usernames.

You could do a search in the database first for the username, and if it finds a record then it's allready been used, if not then they can use it.

<!--- content starts here --->
<CFQUERY NAME=&quot;getemail&quot; DATASOURCE=&quot;productlocator&quot;>
SELECT *
FROM admin
WHERE username = '#Form.username#'
</CFQUERY>

<!--- make user username is unique --->
<cfoutput>
<!--- If there was more than one record returned, then tell them to retry --->
<cfif #getemail.recordcount#>
<div class=&quot;maintext&quot;>
Please go back and try a different username.<br>
The username &quot;#Form.Username#&quot; is already in use.</div>
<cfabort>
</cfif>
</cfoutput> - tleish

 

[PTmon]

Thanks alot, both ideas worked, with a few slight modifications. The double quotes didn't work with <cfoutput> but once that was fixed all was good. I ended up using Jamis, but thank you BOTH for your time. Here's the finished (working) version, in case anyone else is interested:

<!-- content starts here -->
<!--- get list of usernames --->
<CFQUERY NAME=&quot;getemail&quot; DATASOURCE=&quot;foo&quot;>
SELECT *
FROM admin
</CFQUERY>
<!--- check for username in use --->
<cfoutput query = &quot;getemail&quot;>
<cfif #username# contains #form.username#>
<div class='maintext'>Please go back and try a different username.<br>
The username '#Form.Username#' is already in use.</div>
<cfabort>
</cfif>
</cfoutput>
<!--- Process if unique --->
<cfinsert datasource=&quot;foo&quot; tablename=&quot;admin&quot; dbtype=&quot;ODBC&quot; formfields=&quot;username, password, email, Firstname, Lastname, modified, status&quot;>
<div class=&quot;highlights&quot;>Success!</div>
<cfoutput><span class=&quot;maintext&quot;>#Form.firstname# #Form.Lastname# was added to the admin section.</span>
</cfoutput>
<!-- content ends here -->

Thanks again!
Paul

 

[philcha]

PTmon,

There's a small weakness in searching the DB for a unique name - imagine this sequence of events:
1 User A's &quot;thread&quot; searches for user name &quot;John Smith&quot; and find it's not already in use.
2 User B's &quot;thread&quot; searches for user name &quot;John Smith&quot; and find it's not already in use.
3 User A's thread adds a record with user name &quot;John Smith&quot;, successfully.
4 User B's thread adds a record with user name &quot;John Smith&quot;. If the user name field does not have a &quot;must be unique&quot; index, the DB now contains 2 John Smiths. If the user name field has a &quot;must be unique&quot; index, user B's thread gets an error condition.

The only safe way I know to ensure uniqueness is:
* create a &quot;must be unique&quot; index on the field.
* in the code, just add the record, trap the &quot;duplicate key&quot; error if it occcurs, and make the error-handler redisplay the form with an error message.