What does a query return when there's no match?

[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

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

[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?

[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 ;)