Skip to main content
E
earachefl
Known Participant
May 24, 2010
Question

Searching for email match

  • May 24, 2010
  • 1 reply
  • 779 views

When using a small Access database, is it better to return the results of a query and search the result, or to try to filter the results in the query? I tried the latter first, like so:

<cfquery datasource="cpac" name="results">

     SELECT Email

     FROM Subscribers

     WHERE Email='#FORM.Email#'

</cfquery>

and am puzzled by the results when no match is found... the template just exits, and no error message is given. I then tried removing the WHERE filter and was able to use <cfif> to find the lack of a match... but this just seems backwards to me. Is there a better way of doing it?

Thanks

This topic has been closed for replies.

1 reply

-
-__cfSearching__-
Inspiring
May 24, 2010

When using a small Access database, is it better to return

the results of a query and search the result, or to try to

filter the results in the query?

Generally it is better to filter the result in the query. Searching is the job of a database. Plus it makes no sense to incur the overhead of returning 1000 records, when you only need one (1) of them.

.... and am puzzled by the results when no match is found...

the template just exits, and no error message is given.

How the CF template behaves when no records are found is entirely up to you. A query returning zero (0) records is not an error.  So if you want the template to behave differently, then you need to code it that way.  That is about as specific as we can be without seeing any code.

Message was edited by: -==cfSearching==-

E
earacheflAuthor
Known Participant
May 25, 2010

OK, my first try was this:

<cfquery datasource="ds" name="result">

     SELECT Email

     FROM Subscribers

     WHERE Email='#FORM.Email#'

</cfquery>

<cfoutput query="result">

     <cfif result.RecordCount = 0>

          <cfinsert datasource="ds" tablename="Subscribers">

          <cflocation url="subscribeSuccess.cfm">

     <cfelse>

          <cflocation url="subscribeFailure.cfm">

     </cfif>

</cfoutput>

And the result was that if a match was found, the proper redirect to subscribeFailure.cfm occurred, but if no match was found, instead of the insert and redirect occurring (and I also tried commenting out the <cfinsert> statement to be sure) the template would just exit with no error, and the insert wouldn't happen.

-
-__cfSearching__-
Inspiring
May 25, 2010

but if no match was found, instead of the insert and redirect occurring (and I also tried commenting out the <cfinsert> statement to be sure) the template would just exit with no error, and the insert wouldn't happen.

That is because you placed the code within a cfoutput query loop. The loop will only execute if the query contains one or more records.  So if no records were found, the code inside the loop never executes.  You need to remove the cfoutput loop.  Cfoutput is for _displaying_ data.  You do not need it to check the query recordcount.

<cfif result.RecordCount = 0>

          <cfinsert datasource="ds" tablename="Subscribers">

          <cflocation url="subscribeSuccess.cfm">

<cfelse>

          <cflocation url="subscribeFailure.cfm">

</cfif>

Also, the comparison operator is incorrect. Use "EQ" (not "=").

Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices