RE: search across multiple fields (Full Version)

All Forums >> [Web Development] >> ASP and Database



Message

Long Island Lune -> RE: search across multiple fields (9/28/2003 21:55:51)

Actually I just saw something....
This is the code you just posted (below).
It is not the same as mine.
I see what is going on. You used my code to build the SQL, then bypassed it and used your own fp_sQry instead of the assmebled code the routine just built???

Locate the GREEN code below.

Replace the SQL line in your Dieted DRW with this:
fp_sQry=myquery


<%
Dim Keyw, keyt
Dim sort
Dim myquery
Dim myQuerystring
Keyt = " SELECT * FROM products WHERE "
Keyw = split(Request.Form("search" )," " )
myQuerystring = "(Itemname Like ' %" & trim(Keyw(0)) & " %' )"
myQuerystring = myQuerystring & " OR (brands Like ' %" & trim(Keyw(0)) & " %' )"
myQuerystring = myQuerystring & " OR (category Like ' %" & trim(Keyw(0)) & " %' )"
myQuerystring = myQuerystring & " OR (subcategory Like ' %" & trim(Keyw(0)) & " %' )"
if uBound(Keyw) > 1 Then
for i = 1 to uBound(Keyw)
myQuerystring = myQuerystring & " OR (Itemname Like ' %" & trim(Keyw(i)) & " %' )"
myQuerystring = myQuerystring & " OR (brands Like ' %" & trim(Keyw(i)) & " %' )"
myQuerystring = myQuerystring & " OR (category Like ' %" & trim(Keyw(i)) & " %' )"
myQuerystring = myQuerystring & " OR (subcategory Like ' %" & trim(Keyw(i)) & " %' )"
next
end if
Keyw = Keyt + myQuerystring
Keyw = Keyw + "ORDER BY ::SortColumn:: "
myquery = Keyw
%>
<!--#include file="_fpclass/fpdblib.inc"-->
<%
fp_sQry="SELECT * FROM products WHERE (itemname LIKE '%::search::%' OR brands LIKE '%::search::%' OR category LIKE '%::search::%' OR subcategory LIKE '%::search::%') ORDER BY ::SortColumn::"
fp_sDefault="search=&search=&search=&search=&SortColumn=brands"
fp_sNoRecords="<tr><td colspan=12 align=left width=""100%"">No products matched your search criteria...</td></tr>"
fp_sDataConn="louisvilleguide"
fp_iMaxRecords=0
fp_iCommandType=1
fp_iPageSize=20
fp_fTableFormat=True
fp_fMenuFormat=False
fp_sMenuChoice="itemname"
fp_sMenuValue="itemname"
fp_iDisplayCols=12
fp_fCustomQuery=True
BOTID=0
fp_iRegion=BOTID
%>
<!--#include file="_fpclass/fpdbrgn1.inc"-->
<tr>
<td bgcolor="#FFFFE6" width="85">
<p align="center"><a href="productdetail.asp?ID=<%=FP_FieldURL(fp_rs,"ID")%>"><img border="0" src="pics/<%=FP_FieldLink(fp_rs,"picture")%>" height="72"></a></p>
</td>
<td bgcolor="#FFFFE6">
<p align="center"><font size="2" color="#00004F">


<%=FP_FieldVal(fp_rs,"brands")%>
</font>


</td>
<td bgcolor="#FFFFE6">
<p align="center"><a href="productdetail.asp?ID=<%=FP_FieldURL(fp_rs,"ID")%>">
<%=FP_FieldVal(fp_rs,"itemname")%></a>
<%If FP_FieldVal(fp_rs,"activated") = "no" then%>
<img border="0" src="images/outofstock.jpg"><%Else%>
<%End If%>
<%If FP_FieldVal(fp_rs,"activated") = "preorder" then%>

<img border="0" src="images/preorder.jpg"><%Else%>
<%End If%>
</p>

</td>
<td bgcolor="#FFFFE6">
<p align="center"><font size="2" color="#00004F">
<%=FP_FieldVal(fp_rs,"category")%></font></td>
<td bgcolor="#FFFFE6" width="70">
<p align="center"><font size="2" color="#00004F"><%=FormatCurrency(FP_FieldVal(fp_rs,"price"))%></font></td>
<td bgcolor="#FFFFE6" width="64">
<p align="center"><a href="http://ww6.aitsafe.com/cf/add.cfm?product=Item+Number+<%=FP_FieldURL(fp_rs,"itemnumber")%>+--+<%=FP_FieldURL(fp_rs,"brands")%>+--+<%=FP_FieldURL(fp_rs,"itemname")%>&price=<%=FP_FieldURL(fp_rs,"price")%>&qty=1&userid=83100920&units=<%=FP_FieldURL(fp_rs,"weight")%>"><img border="0" src="images/addtocart.jpg"></a></td>
</tr>
<!--#include file="_fpclass/fpdbrgn2.inc"-->
</tbody>
</table>
<p align="center"> </p>



jonance -> RE: search across multiple fields (9/28/2003 22:27:16)

Oh...i had restructured the page for cleanliness' sake and forgot to put that back in. NOW we have a new prob....the page seems to work, but it isn't really returning results right for some reason.

For instance...just search the letter 'a'...only one item comes up...even though there are many more with the letter 'a'...as there are 1000's in the db. Even still..when you see that sipa sipa footbag result.....type in 'footbag'...it returns no results...here my current code. It just isn't searching quite right for some reason...if you type in "black label" you should see all the products you saw when i accidentally had the normal search in there...about 200 items. You only get 1 item...again, we are very close i think...

quote:


<%
Dim Keyw, keyt
Dim sort
Dim myquery
Dim myQuerystring
Keyt = " SELECT * FROM products WHERE "
Keyw = split(Request.Form("search" )," " )
myQuerystring = "(Itemname Like ' %" & trim(Keyw(0)) & " %' )"
myQuerystring = myQuerystring & " OR (brands Like ' %" & trim(Keyw(0)) & " %' )"
myQuerystring = myQuerystring & " OR (category Like ' %" & trim(Keyw(0)) & " %' )"
myQuerystring = myQuerystring & " OR (subcategory Like ' %" & trim(Keyw(0)) & " %' )"
if uBound(Keyw) > 1 Then
for i = 1 to uBound(Keyw)
myQuerystring = myQuerystring & " OR (Itemname Like ' %" & trim(Keyw(i)) & " %' )"
myQuerystring = myQuerystring & " OR (brands Like ' %" & trim(Keyw(i)) & " %' )"
myQuerystring = myQuerystring & " OR (category Like ' %" & trim(Keyw(i)) & " %' )"
myQuerystring = myQuerystring & " OR (subcategory Like ' %" & trim(Keyw(i)) & " %' )"
next
end if
Keyw = Keyt + myQuerystring
Keyw = Keyw + "ORDER BY ::SortColumn:: "
myquery = Keyw
%>
<!--#include file="_fpclass/fpdblib.inc"-->
<%
fp_sQry=myquery
fp_sDefault="search=&search=&search=&search=&SortColumn=brands"
fp_sNoRecords="<tr><td colspan=12 align=left width=""100%"">No products matched your search criteria...</td></tr>"
fp_sDataConn="louisvilleguide"
fp_iMaxRecords=0
fp_iCommandType=1
fp_iPageSize=20
fp_fTableFormat=True
fp_fMenuFormat=False
fp_sMenuChoice="itemname"
fp_sMenuValue="itemname"
fp_iDisplayCols=12
fp_fCustomQuery=True
BOTID=0
fp_iRegion=BOTID
%>
<!--#include file="_fpclass/fpdbrgn1.inc"-->
<tr>
<td bgcolor="#FFFFE6" width="85">
<p align="center"><a href="productdetail.asp?ID=<%=FP_FieldURL(fp_rs,"ID")%>"><img border="0" src="pics/<%=FP_FieldLink(fp_rs,"picture")%>" height="72"></a></p>
</td>
<td bgcolor="#FFFFE6">
<p align="center"><font size="2" color="#00004F">


<%=FP_FieldVal(fp_rs,"brands")%>
</font>


</td>
<td bgcolor="#FFFFE6">
<p align="center"><a href="productdetail.asp?ID=<%=FP_FieldURL(fp_rs,"ID")%>">
<%=FP_FieldVal(fp_rs,"itemname")%></a>
<%If FP_FieldVal(fp_rs,"activated") = "no" then%>
<img border="0" src="images/outofstock.jpg"><%Else%>
<%End If%>
<%If FP_FieldVal(fp_rs,"activated") = "preorder" then%>

<img border="0" src="images/preorder.jpg"><%Else%>
<%End If%>
</p>

</td>
<td bgcolor="#FFFFE6">
<p align="center"><font size="2" color="#00004F">
<%=FP_FieldVal(fp_rs,"category")%></font></td>
<td bgcolor="#FFFFE6" width="70">
<p align="center"><font size="2" color="#00004F"><%=FormatCurrency(FP_FieldVal(fp_rs,"price"))%></font></td>
<td bgcolor="#FFFFE6" width="64">
<p align="center"><a href="http://ww6.aitsafe.com/cf/add.cfm?product=Item+Number+<%=FP_FieldURL(fp_rs,"itemnumber")%>+--+<%=FP_FieldURL(fp_rs,"brands")%>+--+<%=FP_FieldURL(fp_rs,"itemname")%>&price=<%=FP_FieldURL(fp_rs,"price")%>&qty=1&userid=83100920&units=<%=FP_FieldURL(fp_rs,"weight")%>"><img border="0" src="images/addtocart.jpg"></a></td>
</tr>
<!--#include file="_fpclass/fpdbrgn2.inc"-->
</tbody>
</table>
<p align="center"> </p>



Long Island Lune -> RE: search across multiple fields (9/28/2003 22:46:05)

What is the "SortColumn" value?



Long Island Lune -> RE: search across multiple fields (9/28/2003 22:48:55)

Forget that last question. I got the answer off your code. "itemname".

Confusion. I went to your site and viewed the code. You have a hidden field that states sortcolumn = "itemname". Here is the line:

<input type="hidden" name="SortColumn" value="itemname">

But in your DRW it shows SortColumn = brands..

Which one is supposed to be SortColumn?



Long Island Lune -> RE: search across multiple fields (9/28/2003 23:07:24)

Okay, try this:

<%
Dim Keyw, keyt
Dim sort
Dim myquery
Dim myQuerystring
Keyt = " SELECT * FROM products WHERE "
Keyw = split(Request.Form("search" )," " )
myQuerystring = "(Itemname Like '%"&trim(Keyw(0))&"%')"
myQuerystring = myQuerystring & " OR (brands Like '%"&trim(Keyw(0))&"%')"
myQuerystring = myQuerystring & " OR (category Like '%"&trim(Keyw(0))&"%')"
myQuerystring = myQuerystring & " OR (subcategory Like '%"&trim(Keyw(0))&"%')"
if uBound(Keyw) > 1 Then
for i = 1 to uBound(Keyw)
myQuerystring = myQuerystring & " OR (Itemname Like '%"&trim(Keyw(i))&" %')"
myQuerystring = myQuerystring & " OR (brands Like '%"&trim(Keyw(i))&"%')"
myQuerystring = myQuerystring & " OR (category Like '%"&trim(Keyw(i))&"%')"
myQuerystring = myQuerystring & " OR (subcategory Like '%"&trim(Keyw(i))&" %')"
next
end if
Keyw = Keyt + myQuerystring
Keyw = Keyw + " ORDER BY ::SortColumn:: "
myquery = Keyw
%>

On my mach-up page, I had the routine print out the SQL code it generated.

I used: black label sweat as the test.

This is what it displayed to me:

SELECT * FROM products WHERE (Itemname Like '%black%') OR (brands Like '%black%') OR (category Like '%black%') OR (subcategory Like '%black%') OR (Itemname Like '%label %') OR (brands Like '%label%') OR (category Like '%label%') OR (subcategory Like '%label %') OR (Itemname Like '%sweat %') OR (brands Like '%sweat%') OR (category Like '%sweat%') OR (subcategory Like '%sweat %') ORDER BY ::SortColumn::

This is correct.



jonance -> RE: search across multiple fields (9/28/2003 23:09:58)

should be brands..i removed the hidden field. I had added it b/c i thought earlier that might be why i was getting the error about the parameter missing. It still isn't returning the results after removal though...ya know, one thing about this process...i'm learning some things about asp i didn't know before...thanks for that.



Long Island Lune -> RE: search across multiple fields (9/28/2003 23:11:44)

quote:

one thing about this process...i'm learning some things about asp i didn't know before...thanks for that.


That's fantastic. [;)] Some GOOD NEWS right? [:)]



jonance -> RE: search across multiple fields (9/28/2003 23:22:46)

Wow...that made a huge difference. It is finding them...alot of them. If i type in "antihero hoody"...you get alot of results...the only problem i see my client having is that it doesn't return just results containing "antihero" AND "hoody". It returns all results containing "antihero" OR "hoody"...i think this is great, but is there a way to have the latter...to make it a more descrimating search...since there are 1000's of items...it think that is what he'll want, and it's what he described.

PS...i hope you're getting something out of this as well...it's amazing how you never quit learning. I play banjo and it is the same thing with that...no matter how good at it i get, there's always something to learn.

John



Long Island Lune -> RE: search across multiple fields (9/28/2003 23:36:32)

GREAT!!! FINALLY!!!
[:)][:)][:)][:)][:)][:)][:)][:)][:)][:)][:)][:)][:)][:)][:)][:)][:)][:)][:)][:)][:)][:)][:)][:)][:)][:)][:)]

I NEEDED to hear some good news [;)]

What we could do is change OR to AND.

In Boolean Logic:
OR will return anything that contains EITHER word.
AND will return anything that contains BOTH words.

Which would you prefer?
Let me know which way you want it and I'll fix the code first thing in the morning.
Time for bed. [;)]



Long Island Lune -> RE: search across multiple fields (9/28/2003 23:38:43)

I DID an AND version for you if you want to try it out now. See how this works.

<%
Dim Keyw, keyt
Dim sort
Dim myquery
Dim myQuerystring
Keyt = " SELECT * FROM products WHERE "
Keyw = split(Request.Form("search" )," " )
myQuerystring = "(Itemname Like '%"&trim(Keyw(0))&"%')"
myQuerystring = myQuerystring & " AND (brands Like '%"&trim(Keyw(0))&"%')"
myQuerystring = myQuerystring & " AND (category Like '%"&trim(Keyw(0))&"%')"
myQuerystring = myQuerystring & " AND (subcategory Like '%"&trim(Keyw(0))&"%')"
if uBound(Keyw) > 1 Then
for i = 1 to uBound(Keyw)
myQuerystring = myQuerystring & " AND (Itemname Like '%"&trim(Keyw(i))&" %')"
myQuerystring = myQuerystring & " AND (brands Like '%"&trim(Keyw(i))&"%')"
myQuerystring = myQuerystring & " AND (category Like '%"&trim(Keyw(i))&"%')"
myQuerystring = myQuerystring & " AND (subcategory Like '%"&trim(Keyw(i))&" %')"
next
end if
Keyw = Keyt + myQuerystring
Keyw = Keyw + " ORDER BY ::SortColumn:: "
myquery = Keyw
%>

Going to bed now. [:)][;)]



Long Island Lune -> RE: search across multiple fields (9/28/2003 23:42:18)

quote:

PS...i hope you're getting something out of this as well...it's amazing how you never quit learning. I play banjo and it is the same thing with that...no matter how good at it i get, there's always something to learn.


Yeah - High Blood Pressure... [:D]
Only Kidding. [:)][;)]

It's given me a few ideas on how to refine my original code for my own usage.
That's for sure... [8|]

I hear banjo is 10-times harder to play than guitar...

LLLuneeeee [:)]



jonance -> RE: search across multiple fields (9/28/2003 23:52:39)

Not if you play guitar first [:)]

I put in the and version and am not really getting any results anymore...my search for 'a' turns up alot of results but search for a word in one of those results and you find nothing. Weird [:-]

I mean...the OR version was 100X better than what i had before...but i would like to know for my benefit if it can work with the and way...for some reason it seems to be discriminating way too much now...and only single letters or something return results...any idea? Have a good night...i'll be around the computer tomorrow working on this site some more...check out the main www.axiomskatesales.com site and see what you think about it overall...i've done alot of programming for it...this is really the final (i hope) hangup. Thanks again for the assistance...

John



Long Island Lune -> RE: search across multiple fields (9/29/2003 0:19:48)

John,

I just logged-on for a quick second and saw your note.
I think I have the solution.
I'll fill you in tomorrow.



Long Island Lune -> RE: search across multiple fields (9/29/2003 12:32:59)

Here's an AND and OR version.

Here's the test words: black label

Here's the logic:

SELECT * FROM products WHERE (Itemname Like '%black%') OR (brands Like '%black%') OR (category Like '%black%') OR (subcategory Like '%black%') AND (Itemname Like '%label%') OR (brands Like '%label%') OR (category Like '%label%') OR (subcategory Like '%label%') ORDER BY ::SortColumn::

<%
Dim Keyw, keyt
Dim sort
Dim myquery
Dim myQuerystring
Keyt = " SELECT * FROM products WHERE "
Keyw = split(Request.Form("search" )," " )
myQuerystring = "(Itemname Like '%"&trim(Keyw(0))&"%')"
myQuerystring = myQuerystring & " OR (brands Like '%"&trim(Keyw(0))&"%')"
myQuerystring = myQuerystring & " OR (category Like '%"&trim(Keyw(0))&"%')"
myQuerystring = myQuerystring & " OR (subcategory Like '%"&trim(Keyw(0))&"%')"
if uBound(Keyw) > 1 Then
for i = 1 to uBound(Keyw)
myQuerystring = myQuerystring & " AND"
myQuerystring = myQuerystring & " (Itemname Like '%"&trim(Keyw(i))&"%')"
myQuerystring = myQuerystring & " OR (brands Like '%"&trim(Keyw(i))&"%')"
myQuerystring = myQuerystring & " OR (category Like '%"&trim(Keyw(i))&"%')"
myQuerystring = myQuerystring & " OR (subcategory Like '%"&trim(Keyw(i))&"%')"
next
end if
Keyw = Keyt + myQuerystring
Keyw = Keyw + " ORDER BY ::SortColumn:: "
myquery = Keyw
response.write myquery
%>

It's just a matter of fine-tuning adjustments now.
Give this a try. [;)]



jonance -> RE: search across multiple fields (9/29/2003 13:51:17)

I just tested the new code....it seems to be kind of doing the same as before: If you search for "black label"...it returns records with "black" OR "label" in there. The first record is an Antihero brand, the word 'black' is in there but 'label' is not, so logically it shouldn't retrieve that record.

I definately see where you are going now with the logic but for some reason it still isn't excluding those records...it also shows the SQL above the table but i'm guessing that code is meant to show that so we could see it....

Here's the link: http://www.axiomskatesales.com/searchtest.asp

Thanks,
John



Long Island Lune -> RE: search across multiple fields (9/29/2003 14:25:27)

Yeah, the last line in the code displays the actual SQL. I'll remove that.

Let's try to approach this from a different angle.
Do you know how would you like the logic?

See the SQL in my last post, use that and show me where you would like the ANDs and ORs inside the SQL. Then I'll assemble it for you. Then you test. And we'll keep doing that until you home in on what is best.

This is really the best way to solve the problem. Because I've been only guessing.



jonance -> RE: search across multiple fields (9/29/2003 14:41:38)

Right now it returns all results with EITHER word in them.

Below is the sql displayed above the table when i do a search for 'black label'. I don't see anywhere that it is actually looking for the word 'label', just 'black'. Is that assumption correct? I guess it should be looking for both....like you described in your second to last post...but it doesn't seem to be performing that part:

Here is the actual SQL being produced by a search for 'black label'
quote:

SELECT * FROM products WHERE (Itemname Like '%black%') OR (brands Like '%black%') OR (category Like '%black%') OR (subcategory Like '%black%') ORDER BY ::SortColumn::



Here is the SQL you said it should produce from your other post, which seems to be correct...
quote:

SELECT * FROM products WHERE (Itemname Like '%black%') OR (brands Like '%black%') OR (category Like '%black%') OR (subcategory Like '%black%') AND (Itemname Like '%label%') OR (brands Like '%label%') OR (category Like '%label%') OR (subcategory Like '%label%') ORDER BY ::SortColumn::



Thanks,
John



Long Island Lune -> RE: search across multiple fields (9/29/2003 15:23:00)

John,

I found the problem why only one of the words was appearing in the finished SQL. Try this now and see what happens:


<%
Dim Keyw, keyt
Dim sort
Dim myquery
Dim myQuerystring
Keyt = " SELECT * FROM products WHERE "
Keyw = split(Request.Form("search" )," " )
myQuerystring = "(Itemname Like '%"&trim(Keyw(0))&"%')"
myQuerystring = myQuerystring & " OR (brands Like '%"&trim(Keyw(0))&"%')"
myQuerystring = myQuerystring & " OR (category Like '%"&trim(Keyw(0))&"%')"
myQuerystring = myQuerystring & " OR (subcategory Like '%"&trim(Keyw(0))&"%')"
if uBound(Keyw) > 0 Then
for i = 1 to uBound(Keyw)
myQuerystring = myQuerystring & " AND"
myQuerystring = myQuerystring & " (Itemname Like '%"&trim(Keyw(i))&"%')"
myQuerystring = myQuerystring & " OR (brands Like '%"&trim(Keyw(i))&"%')"
myQuerystring = myQuerystring & " OR (category Like '%"&trim(Keyw(i))&"%')"
myQuerystring = myQuerystring & " OR (subcategory Like '%"&trim(Keyw(i))&"%')"
next
end if
Keyw = Keyt + myQuerystring
Keyw = Keyw + " ORDER BY ::SortColumn:: "
myquery = Keyw
response.write myquery
%>

[;)]



Long Island Lune -> RE: search across multiple fields (9/29/2003 15:24:47)

P.S.: I left the actual visual display of the finished SQL in the code so you can see what it is building. Once we're done I'll remove the line that generates it.

If you want it out NOW, let me know.



jonance -> RE: search across multiple fields (9/29/2003 15:39:57)

Okay...i think we're one step away...I think i can see what the sql needs to be now. It is now searching for both words. Here is the SQL it displayed above the table:
quote:


SELECT * FROM products WHERE (Itemname Like '%black%') OR (brands Like '%black%') OR (category Like '%black%') OR (subcategory Like '%black%') AND (Itemname Like '%label%') OR (brands Like '%label%') OR (category Like '%label%') OR (subcategory Like '%label%') ORDER BY ::SortColumn::


BUT...this still returns all results with 'Black' OR 'Label' in them. I think this would be the proper outputted SQL to complete the AND and group it properly..correct me if i'm wrong:

quote:


SELECT * FROM products WHERE ((Itemname Like '%black%') OR (brands Like '%black%') OR (category Like '%black%') OR (subcategory Like '%black%')) AND ((Itemname Like '%label%') OR (brands Like '%label%') OR (category Like '%label%') OR (subcategory Like '%label%')) ORDER BY ::SortColumn::


What i did was add two extra sets of parentheses....one around the first "group" for the word 'black' and one around the second for the word 'label'...it seems that would be the correct logic. Am i right? If you can send me code that outputs that i think that will do it.

If you want to leave the line in that prints the code until we get it finalized that might make it easier for me to see if it's being output right...then just tell me which line to remove to take that out.

Thanks again for all the help...i smell victory!

Thanks,
John



jonance -> RE: search across multiple fields (9/29/2003 15:43:15)

Actually i think i got it...i edited your last code to the following

quote:


<%
Dim Keyw, keyt
Dim sort
Dim myquery
Dim myQuerystring
Keyt = " SELECT * FROM products WHERE "
Keyw = split(Request.Form("search" )," " )
myQuerystring = "((Itemname Like '%"&trim(Keyw(0))&"%')"
myQuerystring = myQuerystring & " OR (brands Like '%"&trim(Keyw(0))&"%')"
myQuerystring = myQuerystring & " OR (category Like '%"&trim(Keyw(0))&"%')"
myQuerystring = myQuerystring & " OR (subcategory Like '%"&trim(Keyw(0))&"%'))"
if uBound(Keyw) > 0 Then
for i = 1 to uBound(Keyw)
myQuerystring = myQuerystring & " AND"
myQuerystring = myQuerystring & " ((Itemname Like '%"&trim(Keyw(i))&"%')"
myQuerystring = myQuerystring & " OR (brands Like '%"&trim(Keyw(i))&"%')"
myQuerystring = myQuerystring & " OR (category Like '%"&trim(Keyw(i))&"%')"
myQuerystring = myQuerystring & " OR (subcategory Like '%"&trim(Keyw(i))&"%'))"
next
end if
Keyw = Keyt + myQuerystring
Keyw = Keyw + " ORDER BY ::SortColumn:: "
myquery = Keyw
response.write myquery
%>


It now seems to be working GREAT! Check it out! I'm going to run some tests to make sure. Do i just take out the response.write line to make that text disappear?

Thanks,
John



Long Island Lune -> RE: search across multiple fields (9/29/2003 15:46:30)

quote:


Do i just take out the response.write line to make that text disappear?


Yes, just remove the last line: response.write myquery from the code.

So it's working pretty good?



Long Island Lune -> RE: search across multiple fields (9/29/2003 15:47:54)

Yes, the extra parenthesis could definitely make a difference. It isolates the different layers. Good thinking. Someone is learning ASP !!!!!!! [;)]



jonance -> RE: search across multiple fields (9/29/2003 15:48:38)

THANKS A MILLION!

This is it....i have tested it and it works PERFECT! I can't begin to thank you enough because this result would have been impossible w/o your help and just my knowledge. I should now be able to use this code to implement into my other sites as well...plus i have learned a bit about how asp works. Thanks for your time and dedication to helping me solve this problem. The outfront forums have alot of GREAT PEOPLE who are willing to help us new asp guys.

Thanks, again,
John



Long Island Lune -> RE: search across multiple fields (9/29/2003 15:52:33)

YOUR WELCOME
[:)][:)][:)][:)][:)][:)][:)][:)][:)][:)][:)][:)][:)][:)][:)][:)][:)][:)][:)][:)][:)][:)]

Glad I could be of assistance. [;)]
And I'm very glad it worked out for you.

LLLuneeeee [:)]



simonw -> RE: search across multiple fields (9/30/2003 6:30:24)

You could try a different approach, one I have used with success in Microsoft Access in a FrontPage web. Instead of having lots of fields inwhich to search, have one field. So your table would be like this

tblItems
ItemDesc i.e. Brands, Category, Sub Category
Data

Instead of creating separate fields, you have one field * that contains the field description as data

i.e.

Desc * Data
Symptoms Coughing
Remedy Hot Lemon Drink
Side Effects Runny Nose
Toxic Effects Acidic

You only have to search one field (Data).

It is also a dynamic solution. New fields can be added to the Desc column

Simon (New Member)



jonance -> RE: search across multiple fields (9/30/2003 9:29:38)

Okay....all the testing shows me the search is working fine. The only problem i have found is when more than 20 results are found. I had the page set up to show 20 results and have the buttons at the bottom to go 'start' 'previous' 'next' 'end'. When you click that you get this error:

Microsoft VBScript runtime error '800a0009'
Subscript out of range: '[number: 0]'

/search.asp, line 165


It doesn't seem to pass the search paramater to it for some reason...is there any way to fix this or will i have to just display all of the results on one page. The reason we want them split is for load time as up to 500 records could be found in a simple search...and those pics take a while to download.

Go to www.axiomskatesales.com and do a keyword search for "deck" at the top once inside the site. Then on the results page click "next"...you'll see the error.

Thanks,
John



jonance -> RE: search across multiple fields (9/30/2003 9:39:46)

By the way, here is code for the results page...

quote:


<%
Dim Keyw, keyt
Dim sort
Dim myquery
Dim myQuerystring
Keyt = " SELECT * FROM products WHERE "
Keyw = split(Request.Form("search" )," " )
myQuerystring = "((Itemname Like '%"&trim(Keyw(0))&"%')"
myQuerystring = myQuerystring & " OR (brands Like '%"&trim(Keyw(0))&"%')"
myQuerystring = myQuerystring & " OR (category Like '%"&trim(Keyw(0))&"%')"
myQuerystring = myQuerystring & " OR (subcategory Like '%"&trim(Keyw(0))&"%'))"
if uBound(Keyw) > 0 Then
for i = 1 to uBound(Keyw)
myQuerystring = myQuerystring & " AND"
myQuerystring = myQuerystring & " ((Itemname Like '%"&trim(Keyw(i))&"%')"
myQuerystring = myQuerystring & " OR (brands Like '%"&trim(Keyw(i))&"%')"
myQuerystring = myQuerystring & " OR (category Like '%"&trim(Keyw(i))&"%')"
myQuerystring = myQuerystring & " OR (subcategory Like '%"&trim(Keyw(i))&"%'))"
next
end if
Keyw = Keyt + myQuerystring
Keyw = Keyw + " ORDER BY ::SortColumn:: "
myquery = Keyw
%>
<!--#include file="_fpclass/fpdblib.inc"-->
<%
fp_sQry=myquery
fp_sDefault="search=&search=&search=&search=&SortColumn=brands"
fp_sNoRecords="<tr><td colspan=12 align=left width=""100%"">No products matched your search criteria...</td></tr>"
fp_sDataConn="louisvilleguide"
fp_iMaxRecords=0
fp_iCommandType=1
fp_iPageSize=20
fp_fTableFormat=True
fp_fMenuFormat=False
fp_sMenuChoice="itemname"
fp_sMenuValue="itemname"
fp_iDisplayCols=12
fp_fCustomQuery=True
BOTID=0
fp_iRegion=BOTID
%>
<!--#include file="_fpclass/fpdbrgn1.inc"-->
<tr>
<td bgcolor="#FFFFE6" width="85">
<p align="center"><a href="productdetail.asp?ID=<%=FP_FieldURL(fp_rs,"ID")%>"><img border="0" src="pics/<%=FP_FieldLink(fp_rs,"picture")%>" height="72"></a></p>
</td>
<td bgcolor="#FFFFE6">
<p align="center"><font size="2" color="#00004F">


<%=FP_FieldVal(fp_rs,"brands")%>
</font>


</td>
<td bgcolor="#FFFFE6">
<p align="center"><a href="productdetail.asp?ID=<%=FP_FieldURL(fp_rs,"ID")%>">
<%=FP_FieldVal(fp_rs,"itemname")%></a>
<%If FP_FieldVal(fp_rs,"activated") = "no" then%>
<img border="0" src="images/outofstock.jpg"><%Else%>
<%End If%>
<%If FP_FieldVal(fp_rs,"activated") = "preorder" then%>

<img border="0" src="images/preorder.jpg"><%Else%>
<%End If%>
</p>

</td>
<td bgcolor="#FFFFE6">
<p align="center"><font size="2" color="#00004F">
<%=FP_FieldVal(fp_rs,"category")%></font></td>
<td bgcolor="#FFFFE6" width="70">
<p align="center"><font size="2" color="#00004F"><%=FormatCurrency(FP_FieldVal(fp_rs,"price"))%></font></td>
<td bgcolor="#FFFFE6" width="64">
<p align="center"><a href="http://ww6.aitsafe.com/cf/add.cfm?product=Item+Number+<%=FP_FieldURL(fp_rs,"itemnumber")%>+--+<%=FP_FieldURL(fp_rs,"brands")%>+--+<%=FP_FieldURL(fp_rs,"itemname")%>&price=<%=FP_FieldURL(fp_rs,"price")%>&qty=1&userid=83100920&units=<%=FP_FieldURL(fp_rs,"weight")%>"><img border="0" src="images/addtocart.jpg"></a></td>
</tr>
<!--#include file="_fpclass/fpdbrgn2.inc"-->



Long Island Lune -> RE: search across multiple fields (9/30/2003 11:32:02)

quote:


Microsoft VBScript runtime error '800a0009'
Subscript out of range: '[number: 0]'

/search.asp, line 165



I only count 75 lines in the code you posted.



Long Island Lune -> RE: search across multiple fields (9/30/2003 11:36:05)

This is a DIFFERENT site isn't it? [8|]

I'm not getting an error when I test.
Nothing is happening when I press the button. [;)]

Post the whole page.



Page: <<   < prev  1 [2] 3 4 5   next >   >>

Valid CSS!




Forum Software © ASPPlayground.NET Advanced Edition 2.4.5 ANSI
0.125