CFQUERY Using Multiple 'and' Statements

Report · Mar 27, 2012

Hello,

I am working on a form in which the user inputs 4 values and then the results are returned in a table. The trouble I am running into is that I am getting no results when using multiple 'and' statements. There are no errors thrown, just empty results. If I use the 'or' operator, I get a set resturns, just not as narrowed down as I would like. I have included the relevant code below and any suggestions would be greatly appreciated!!

<tr>

<td>

<h2 class="surv">The Number: </h2>

</td>

</td>

</cfselect>

</td>

</td>

</cfselect>

</td>

</tr>

<tr>

</td>

</tr>

</table>

</cfform>

results.cfm

select *
FROM sire.dbo.control_doc

WHERE sire.dbo.control_doc.publish = 'yes'
and

   sire.dbo.control_doc.grid_ns like '%#form.address_value_new#%'
   and sire.dbo.control_doc.direction_ns like '%#form.ns#%'
   and sire.dbo.control_doc.grid_ew like '%#form.address_value_new_2#%'
   and sire.dbo.control_doc.direction_ew like '%#form.ew#%'
</CFQUERY>

Report · Mar 27, 2012

If there's no data found but no error, then quite simply there's no data to find. What happens if you just run the query in the normal database tools? I suspect you'll find no rows there either.

Also, you *really* need to read up on and use cfqueryparam, a form like the one you have there is a SQL Injection attack waiting to happen.

Report · Mar 27, 2012

Thanks so much for your answer Owain. You are right, I need to use cfqueryparam, this is just a test form.

The thing is that I know there is data in the database because I can see the values I enter in there. Does the cfquery look correct using multiple 'and' statements?

Thanks!

Report · Mar 27, 2012

If the query wasn't correct, you'd get an error - therefore if you're expecting results I'd guess your form fields aren't exactly what you think they are. Try adding the result="x" attribute to the cfquery tag, then dumping it out just after - you can then see the exact query being run.

Is you database case-sensitive? Have you trimmed your form fields so they don't contain whitespace? Just a couple of ideas.

O.

Report · Mar 27, 2012

In addition to the other suggestions:

The thing is that I know there is data in the database because I can see the values I enter in there.

Well obviously something is different than you are thinking: either the actual values or the logic. The best way to find the problem is print out the generated sql as suggested. If you do not see anything obviously wrong with the sql, run it directly against your database. If it still returns zero records, comment out one AND clause at a time to determine which part is eliminating the records. Then go from there. That is what I do.

Report · Mar 27, 2012

Thanks for both of your help! I am utilizing your suggestions and eliminating one 'and' at a time in hopes of solving the issue.

Emily

Adobe Community

CFQUERY Using Multiple 'and' Statements