Member Avatar for earachefl@comca

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

  • 2 Contributors
  • 3 Replies
  • 113 Views
  • 8 Hours Discussion Span
  • Latest Post Latest Post by arrgh
Member Avatar for arrgh

filter the results in the query

It's better to filter in the db. The less info you return the better.

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

That's because no actual error occurred. The .cfm template only does what you tell it to do. So unless you're saying "do X when the query contains 0 records", nothing is going to happen.

If no matches were found, the query recordCount will be 0. Just use that value to do whatever it is you want to happen when "no match" is found.

(run the query)

<cfif results.recordCount gt 0>
records WERE found. do stuff here ...
<cfelse>
no match found. do something else...
</cfif>

Edited by arrgh because: n/a
Member Avatar for earachefl@comca

Yeah, I tried that... ran the query with the filter and then used a <cfif> to check the recordcount, and based on the results of that, redirected to either a success page or a failure page. But what happened was that if there was a match, the correct redirect happened, but if there wasn't a match, the template just exited. Hence my question as to what exactly a query returns if there is no match - null? False?

Member Avatar for arrgh

If the code is doing something different than you want .. it is probably wrong. Because checking the recordcount definitely works. I use it all the time ;)

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.