Copy link to clipboard
Copied
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!!
<body bgcolor="#FFFFFF" topmargin="2" leftmargin="2" rightmargin="2">
<cfform method="post" name="address_value_1" target="blank" action="results.cfm">
<table width="185" border=0 bordercolor="black" cellpadding="0" cellspacing="0" >
<tr>
<td>
<h2 class="surv">The Number: </h2>
</td>
<td align="left" >
<cfinput type="text" name="address_value_new" size="20" maxlength="20" class="whiteonblue">
</td>
<td align="left">
<cfselect name="ns" class="whiteonblack">
<option value="dir_n">N</option>
<option value="dir_s">S</option>
</cfselect>
</td>
<td align="left" >
<cfinput type="text" name="address_value_new_2" size="20" maxlength="20" class="whiteonblue">
</td>
<td align="left">
<cfselect name="EW" class="whiteonblack">
<option value="dir_e">E</option>
<option value="dir_w">W</option>
</cfselect>
</td>
</tr>
<tr>
<td align=center colspan="2">
<INPUT TYPE="submit" VALUE="go">
</td>
</tr>
</table>
</cfform>
results.cfm
<CFQUERY name="by_address" datasource="sire" dbtype="odbc">
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>
Copy link to clipboard
Copied
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.
Copy link to clipboard
Copied
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!
Copy link to clipboard
Copied
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.
Copy link to clipboard
Copied
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.
Copy link to clipboard
Copied
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