Printable Version of Topic

Click here to view this topic in its original format

Dream.In.Code _ ColdFusion _ How do I verify that an email address does not already exists in DB

Posted by: cumupkid 18 Apr, 2009 - 06:18 PM

I want to make sure people can not enter their information if the email address they are trying to use already exists in the data base. with an error message letting them know the email address they are trying to use already exists.

Form Page Code

CODE

<cfif isdefined("form.firstname")>

    <cfif form.email eq form.emailverify>
    <cfinvoke
    component="cfcs.customers" method="insertcustomer">
    <cfinvokeargument name="formdata" value="#form#"/>
</cfinvoke>
<cflocation url="index.cfm?thankyou" addtoken="no">
    <cfelse>
        <cfset errormessage = "Your email address did not match">
    </cfif>


</cfif>


<cfparam name="form.firstname" default="">
<cfparam name="form.lastname" default="">
<cfparam name="form.email" default="">

<cfparam name="form.primaryphone" default="">
<cfparam name="form.secondaryphone" default="">
<cfparam name="form.address" default="">
<cfparam name="form.city" default="">
<cfparam name="form.state" default="">
<cfparam name="form.postalcode" default="">


<h2>Join our email list</h2>
<div id="DBTable">
<table border="0" cellspacing="0" cellpadding="2" align="center">
<cfform>


      <tr>
        <td>*First Name:</td>
        <td><cfinput type="text" name="firstname" value="#form.firstname#" required="yes" id="firstname" tabindex="1" maxlength="20"></td>
    </tr>
    <tr>
        <td>*Last Name:</td>
        <td ><cfinput type="text" name="lastname" value="#form.lastname#" required="yes" id="lastname" tabindex="2" maxlength="20"></td>
    </tr>
    <tr>
        <td>*Email:</td>
        <td ><cfinput type="text" name="email" value="#form.email#" validateat="onSubmit" validate="email" required="yes" id="email" tabindex="3" maxlength="50"></td>
    </tr>
    <tr>
        <td>
            <cfif isDefined('errormessage')><div id="errorMessage">*Verify Email:</div>
            <cfelse>*Verify Email:</cfif>
        </td>
        <td ><cfinput type="text" name="emailverify"  required="yes" id="emailverify" tabindex="4" maxlength="50"></td>
    </tr>
    <tr>
        <td>*Primary Phone:</td>
        <td ><cfinput type="text" name="primaryphone" value="#form.primaryphone#" validateat="onSubmit" validate="telephone" required="yes" id="primaryphone" tabindex="4" maxlength="12" typeahead="no" showautosuggestloadingicon="true"></td>
    </tr>
    <tr>
        <td>Secondary Phone:</td>
        <td><cfinput type="text" name="secondaryphone" value="#form.secondaryphone#" id="secondaryphone" tabindex="5" maxlength="12"></td>
    </tr>
    <tr>
        <td>Street:</td>
        <td ><cfinput type="text" name="address" value="#form.address#" id="address" tabindex="6" maxlength="50"></td>
    </tr>
    <tr>
        <td>City:</td>
        <td ><cfinput type="text" name="city" value="#form.city#" id="city" tabindex="7" maxlength="20"></td>
    </tr>
    <tr>
        <td>State:</td>
        <td ><cfselect name="State" id="State" tabindex="8" selected="#form.state#">
                    <option value=" " selected="selected"> </option>
                    <option value="AK">AK</option>
                    <option value="AL">AL</option>
                    <option value="AR">AR</option>
                    <option value="AZ">AZ</option>
                    <option value="CA">CA</option>
                    <option value="CO">CO</option>
                    <option value="CT">CT</option>
                    <option value="DC">DC</option>
                    <option value="DE">DE</option>
                    <option value="FL">FL</option>
                    <option value="GA">GA</option>
                    <option value="HI">HI</option>
                    <option value="IA">IA</option>
                    <option value="ID">ID</option>
                    <option value="IL">IL</option>
                    <option value="IN">IN</option>
                    <option value="KS">KS</option>
                    <option value="KY">KY</option>
                    <option value="LA">LA</option>
                    <option value="MA">MA</option>
                    <option value="MD">MD</option>
                    <option value="ME">ME</option>
                    <option value="MI">MI</option>
                    <option value="MN">MN</option>
                    <option value="MO">MO</option>
                    <option value="MS">MS</option>
                    <option value="MT">MT</option>
                    <option value="NC">NC</option>
                    <option value="ND">ND</option>
                    <option value="NE">NE</option>
                    <option value="NH">NH</option>
                    <option value="NJ">NJ</option>
                    <option value="NM">NM</option>
                    <option value="NV">NV</option>
                    <option value="NY">NY</option>
                    <option value="OH">OH</option>
                    <option value="OK">OK</option>
                    <option value="OR">OR</option>
                    <option value="PA">PA</option>
                    <option value="PR">PR</option>
                    <option value="RI">RI</option>
                    <option value="SC">SC</option>
                    <option value="SD">SC</option>
                    <option value="TN">TN</option>
                    <option value="TX">TX</option>
                    <option value="UT">UT</option>
                    <option value="VA">VA</option>
                    <option value="VT">VT</option>
                    <option value="WA">WA</option>
                    <option value="WI">WI</option>
                    <option value="WV">WV</option>
                    <option value="WY">WY</option>
        </cfselect></td>
    </tr>
    <tr>
        <td>Zip:</td>
        <td ><cfinput type="text" name="postalcode" value="#form.postalcode#" validateat="onSubmit" validate="zipcode" id="postalcode" tabindex="9" maxlength="5"></td>
    </tr>
    <tr>
        <td>&nbsp; </td>
        <td>&nbsp; </td>
    </tr>
    <tr>
        <td><div align="right"><cfinput type="submit" name="Submit" value="Submit" id="Submit" tabindex="10"></div></td>
        <td ><cfinput type="button" name="cancel" id="Reset" value="Cancel" onClick="history.back()" tabindex="11"></td>
    </tr>
</cfform>
</table>
</div>


CFC Code
CODE

    <cffunction name="insertcustomer" access="public" returntype="void">
    <cfargument name="formdata" type="struct" required="yes">
        
        <cfquery datasource="#application.datasource#">
            INSERT INTO customers
                (FIRSTNAME, LASTNAME, EMAIL, PRIMARYPHONE, ADDRESS, CITY, STATE, POSTALCODE, SECONDARYPHONE)
             VALUES
                ('#formdata.firstname#', '#formdata.lastname#', '#formdata.email#', '#formdata.primaryphone#', '#formdata.address#', '#formdata.city#', '#formdata.state#', '#formdata.postalcode#', '#formdata.secondaryphone#')
        </cfquery>
    </cffunction>

Posted by: xheartonfire43x 21 Apr, 2009 - 05:58 AM

[quote name='cumupkid' date='18 Apr, 2009 - 06:18 PM' post='611209']
I want to make sure people can not enter their information if the email address they are trying to use already exists in the data base. with an error message letting them know the email address they are trying to use already exists.
[quote]

You just need a simple select statement. SELECT email FROM table WHERE email = form.email

then check to see if the record count is gt 0... if it is then that email is already there.

Posted by: wiredwizard 21 Apr, 2009 - 06:41 AM

You just need a simple select statement. SELECT email FROM table WHERE email = form.email

you could also do a

CODE

select count(*) as entry
from table
where email = form.emai


then do a if statement

CODE

cfif query.entry GT 0
  give them the error message
cfelse
   add record to your database



Posted by: sansclue 21 Apr, 2009 - 01:52 PM

For most applications the select / check recordCount test is enough. But the only truly bullet proof way to prevent duplicates from creeping into the database is to add a constraint in your database as well.

Powered by Invision Power Board (http://www.invisionboard.com)
© Invision Power Services (http://www.invisionpower.com)