|
| |
|
|
hessfirm
Posts: 155
Joined: 9/15/2004
Status: offline
 |
search with multiple values in one text field - 4/7/2005 0:21:16
I've seen an old thread on this, but no solution was developed that i saw.
I am using a single text field to search a SQL table via the DRW. I want to allow users to input multiple values in the text field separated by commas...and the values are not numeric...
the sql in the DRW is county IN ::county::
But how do i get the single quotes around each word? Most people will just type eg. sample1, sample2....in the text field. This will produce no records returned. It needs to be 'sample1', 'sample2'....But users don't know to type in the single quotes.
So how do i automate the surrounding single quotes? HELP!!!
_____________________________
Steve Hess
|
|
|
|
rdouglass
Posts: 9280
From: Biddeford, ME USA
Status: offline
|
RE: search with multiple values in one text field - 4/7/2005 8:38:56
If you're sure they're being separated by commas, try using a REPLACE function:
<%
DIM myVar
myVar = "'" & Replace(Request.form("country"),",","','") & "'"
%>
Then in a dieted DRW use:
...country IN " & myVar & "...
However, there are more "accurate" ways by putting your words into an array and being more specific in your requests. If you're interested, let me know and I'll see if I can find a similar post. That's been done many times and it's actually quite straightforward IMO.
Anyways, hope it helps.
_____________________________
Don't take you're eye off your final destination.
ASP Checkbox Function Tutorial.
| |
|
|
|
BeTheBall
Posts: 6487
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
RE: search with multiple values in one text field - 4/7/2005 9:46:50
I may be wrong, but I believe IN only works with numeric fields. You will likely have to diet the code as shown here:
http://www.outfront.net/spooky/adv_drw_diet.htm
Once that is complete, go into the red code and replace the line that begins with:
fp_sQry =
with the following lines of code:
If trim(request.form("nameofsearchformfield"))&"" <> "" Then
varString = trim(replace(request.form("nameofsearchformfield"), "'","''"))
varString = Replace(varString, " ", ",")
arrString = Split(varString, ",")
mySQL = "SELECT * FROM tablebeingsearched WHERE fieldbeingsearched LIKE '%"&arrString(0)&"%' "
If Ubound(arrString)> 0 Then
For i = 1 to Ubound(arrString)
mySQL = mySQL & " AND fieldbeingsearched LIKE '%"&arrString(i)&"%' "
Next
End If
Else
mySQL = "SELECT * FROM tablebeingsearched"
End If
fp_sQry = mySQL
Replace the bold items with the actual names from your database and search form. The above will also replace spaces with a comma to cover users who may forget to add one.
< Message edited by BeTheBall -- 4/7/2005 10:07:35 >
_____________________________
Duane
Some people are like Slinkies . . . Not really good for anything . . . . . But they still bring a smile to your face when you push them down a flight of stairs.
| |
|
|
|
BeTheBall
Posts: 6487
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
RE: search with multiple values in one text field - 4/7/2005 10:42:10
Agreed.
_____________________________
Duane
Some people are like Slinkies . . . Not really good for anything . . . . . But they still bring a smile to your face when you push them down a flight of stairs.
| |
|
|
|
hessfirm
Posts: 155
Joined: 9/15/2004
Status: offline
|
RE: search with multiple values in one text field - 4/8/2005 2:43:46
the diet is working nicely. thanks!
The code, though, to insert single quotes isn't...Here is the DRW code after the diet and new code you gave. I am sure I am missing something.
The search form url is http://atlantanewhomesdirectory.com/TestPages/diettrialsearchform.htm
The results page is http://atlantanewhomesdirectory.com/TestPages/diettrial.asp
Also, if i am using multiple search form fields that query different fields in the table, how do i add them into this formula as well....For example, in the query below, i am searching using only 'builderbroker'...but what if i allow additional search form fields for different columns?
And does is matter that we use SQl versus mySQL?
<tbody>
<!--#include file="../_fpclass/fpdblib.inc"-->
<% if 0 then %>
<SCRIPT Language="JavaScript">
document.write("<div style='background: yellow; color: black;'>The Database Results component on this page is unable to display database content. The page must have a filename ending in '.asp', and the web must be hosted on a server that supports Active Server Pages.</div>");
</SCRIPT>
<% end if %>
<%
If trim(request.form("builderbroker"))&"" <> "" Then
varString = trim(replace(request.form("builderbroker"), "'","''"))
varString = Replace(varString, " ", ",")
arrString = Split(varString, ",")
mySQL = "SELECT * FROM Administration WHERE builderbrokerLIKE '%"&arrString(0)&"%' "
If Ubound(arrString)> 0 Then
For i = 1 to Ubound(arrString)
mySQL = mySQL & " AND builderbrokerLIKE '%"&arrString(i)&"%' "
Next
End If
Else
mySQL = "SELECT * FROM administration"
End If
fp_sQry =mySQL fp_sDefault=""
fp_sNoRecords="<tr><td colspan=2 align=left width=""100%"">No records returned.</td></tr>"
fp_sDataConn="DedServer"
fp_iMaxRecords=256
fp_iCommandType=1
fp_iPageSize=0
fp_fTableFormat=True
fp_fMenuFormat=False
fp_sMenuChoice=""
fp_sMenuValue=""
fp_iDisplayCols=2
fp_fCustomQuery=False
BOTID=0
fp_iRegion=BOTID
%>
<!--#include file="../_fpclass/fpdbrgn1.inc"-->
<tr>
<td><%=FP_FieldVal(fp_rs,"ID")%></td>
<td><%=FP_FieldVal(fp_rs,"Bookmark")%></td>
</tr>
<!--#include file="../_fpclass/fpdbrgn2.inc"-->
</tbody>
| |
|
 New Messages |
 No New Messages |
 Hot Topic w/ New Messages |
 Hot Topic w/o New Messages |
 Locked w/ New Messages |
 Locked w/o New Messages |
|
Post New Thread
Reply to Message
Post New Poll
Submit Vote
Delete My Own Post
Delete My Own Thread
Rate Posts
|
|
|
Printable Version 
). It may already be one but it's a common question.