Search within Search Results

[Maria Kovacs]

I am trying to do a search within query results:

My first search executes fine, but my second search uses the WHERE
parameters of my first search.

For Instance if my first search was "Keyword: stein" (which company
manufactures them), and I would like to see which of those companies
would be in "State: PA" then my sql should look like this:
select * from DB where (State like '%%PA%%') and (Keywords like
'%%stein%%')

Instead, for the search within reults I get this:
select * from DB where (State like '%%PA%%') and (State like
'%%stein%%')

What am I missing?

Here is my code:
<title>Search within Results</title>
<%
u_where=request.form("c_where")
u_search=request.form("c_search")
u_prev_search=request.form("c_prev_search")
u_search_within=request.form("c_search_within")

if c_search <> "" then
if c_prev_search = "" then
c_prev_search=c_search
else

c_prev_search=c_prev_search &","& c_search
g_prev_search=split(c_prev_search,",")
num_inputted=ubound(g_prev_search)
end if

sql= "select * from DB where (" & c_where &" like '%%"& c_search &
"%%') "

if c_search_within = "Yes" then
for counter =0 to num_inputted-1
sql=sql& "and (" & c_where & " like '%%"& g_prev_search(counter) &
"%%') "
next
end if



accessdb="DB"
cn="DRIVER={Microsoft Access Driver (*.mdb)};"
cn=cn & "DBQ=" & server.mappath(accessdb)
Set rs = Server.CreateObject("ADODB.Recordset")
rs.Open sql, cn

if rs.eof then
%>
No records were found
<%
else
rs.movefirst
do while Not rs.eof
%>
<table> <td width="200" valign="top" align="left">
<font face="Arial" size="1">
<a href="<%=rs("Web_Address")%>"
target="_blank"><%=rs("Supplier")%></a></font></td></table><br>
<%
rs.movenext
loop
end if
end if
%>
<!-- Begin Form Input Area -->
<form action="<%= request.servervariables("script_name") %>"
method="post">
<select size="1" name="c_where">
<option value="Keywords" selected>Keywords</option>
<option value="State">State</option>
</select>
<input type="text" name="c_search" value="<%= u_search %>">
<br>
<%
if c_search <> "" then %>
<input type = "radio" name="c_search_within" checked value="No">
Search &nbsp;
<input type = "radio" name="c_search_within" value="Yes"> Search
within results
<%
if c_search_within = "Yes" then %>
<input type = "hidden" name="c_prev_search" value="<%= c_prev_search
%>">
<%
else %>
<input type = "hidden" name="c_prev_search" value="<%= c_search %>">
<% end if %>
<br>
<% end if %>
<input type="submit" value="Search">
</form>
<!-- End Form Input Area -->
<p>&nbsp;</p>
<%= sql %>
<br>

[Hannibal]

jus a quick response

take a look at the variables you're using

if you're using c_where for the first search i.e
c_where=Keywords

make sure that the search within loks something like:
cwhere=Keywords&c_where2=State

just so you differentiate between the two fields you're querying... and
obviously the same goes for their search text(PA and Stein)

sorry if its in there, but i'm outta time and it would be the obvious answer
to a problem like that

laterz


"Maria Kovacs" <mariakovacs@lycos.com> wrote in message
news:6f76be22.0310020516.7d44e999@posting.google.c om...

> I am trying to do a search within query results:
>
> My first search executes fine, but my second search uses the WHERE
> parameters of my first search.
>
> For Instance if my first search was "Keyword: stein" (which company
> manufactures them), and I would like to see which of those companies
> would be in "State: PA" then my sql should look like this:
> select * from DB where (State like '%%PA%%') and (Keywords like
> '%%stein%%')
>
> Instead, for the search within reults I get this:
> select * from DB where (State like '%%PA%%') and (State like
> '%%stein%%')
>
> What am I missing?
>
> Here is my code:
> <title>Search within Results</title>
> <%
> u_where=request.form("c_where")
> u_search=request.form("c_search")
> u_prev_search=request.form("c_prev_search")
> u_search_within=request.form("c_search_within")
>
> if c_search <> "" then
> if c_prev_search = "" then
> c_prev_search=c_search
> else
>
> c_prev_search=c_prev_search &","& c_search
> g_prev_search=split(c_prev_search,",")
> num_inputted=ubound(g_prev_search)
> end if
>
> sql= "select * from DB where (" & c_where &" like '%%"& c_search &
> "%%') "
>
> if c_search_within = "Yes" then
> for counter =0 to num_inputted-1
> sql=sql& "and (" & c_where & " like '%%"& g_prev_search(counter) &
> "%%') "
> next
> end if
>
>
>
> accessdb="DB"
> cn="DRIVER={Microsoft Access Driver (*.mdb)};"
> cn=cn & "DBQ=" & server.mappath(accessdb)
> Set rs = Server.CreateObject("ADODB.Recordset")
> rs.Open sql, cn
>
> if rs.eof then
> %>
> No records were found
> <%
> else
> rs.movefirst
> do while Not rs.eof
> %>
> <table> <td width="200" valign="top" align="left">
> <font face="Arial" size="1">
> <a href="<%=rs("Web_Address")%>"
> target="_blank"><%=rs("Supplier")%></a></font></td></table><br>
> <%
> rs.movenext
> loop
> end if
> end if
> %>
> <!-- Begin Form Input Area -->
> <form action="<%= request.servervariables("script_name") %>"
> method="post">
> <select size="1" name="c_where">
> <option value="Keywords" selected>Keywords</option>
> <option value="State">State</option>
> </select>
> <input type="text" name="c_search" value="<%= u_search %>">
> <br>
> <%
> if c_search <> "" then %>
> <input type = "radio" name="c_search_within" checked value="No">
> Search &nbsp;
> <input type = "radio" name="c_search_within" value="Yes"> Search
> within results
> <%
> if c_search_within = "Yes" then %>
> <input type = "hidden" name="c_prev_search" value="<%= c_prev_search
> %>">
> <%
> else %>
> <input type = "hidden" name="c_prev_search" value="<%= c_search %>">
> <% end if %>
> <br>
> <% end if %>
> <input type="submit" value="Search">
> </form>
> <!-- End Form Input Area -->
> <p>&nbsp;</p>
> <%= sql %>
> <br>