search across multiple fields (Full Version)

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



Message

jonance -> search across multiple fields (9/26/2003 12:04:07)

Hi, I have a database of skateboard items that i have a search set up for. The search box is called 'search'...here is the SQL code i have:

SELECT * FROM products WHERE (itemname LIKE '%::search::%' OR brands LIKE '%::search::%' OR category LIKE '%::search::%' OR subcategory LIKE '%::search::%') ORDER BY ::SortColumn::

It works just fine, but IF the user types in more than one word....and those two words are not found in the same field, but instead one is found in, say, "itemname" and the other is found in "category"...it does't return any results...is there a way to alter this code so it will sort of combine the search and return results even if the keywords are in diff. fields?

Thanks in advance,
John



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

I'm not sure I completely understand. But I do have a post from a long time ago where I needed to know if someone had typed something in and automatically adjust for this in a multiple search capacity. And I had to be able to handle MULTIPLE WORDS typed into a single field. Look the post over, it might answer your question. Shoot to the bottom of the post. That's where the mechanics are. It works great for me. I'm still using it today on one of my sites.

http://www.frontpagewebmaster.com/tm.asp?m=111335&appid=&p=&mpage=1&key=&language=&tmode=1&smode=1&s=#111361



jonance -> RE: search across multiple fields (9/26/2003 16:51:26)

This may be on track to what i am trying to do, but i can't seem to pull anything out of it as i am not sure the problem is the same....I just need to find a way to have two or more search words typed in my search box, separated by a space, be able to be found in multiple fields (the ones above). In other words, one may appear in the itemname and another in the category and it will still retrieve that record. Right now both words must all be in one field or it wont retrieve it.

Thanks,
John



Long Island Lune -> RE: search across multiple fields (9/26/2003 20:13:17)

1): You really need to put your DRW on a Spooky diet:
http://www.outfront.net/spooky/index.htm

2): Place the code (below) ABOVE the dieted DRW code on your page:

<%
Dim Keyw
dim sort
Dim myquery
Dim myQuerystring
sort = Request.form("SortColumn")
Keyw = " SELECT * FROM products WHERE "
Keyw = split(Request.Form("search" )," " )
if uBound(Keyw) > 0 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
else
myQuerystring = "(itemname LIKE '%::search::%' OR
brands LIKE '%::search::%' OR category LIKE '%::search::%' OR
subcategory LIKE '%::search::%')"
end if
Keyw = Keyw + myQuerystring
Keyw = Keyw + "ORDER BY sort"
end if
myquery = Keyw
%>

3): Replace the SQL line in your Dieted DRW with this:

fp_sQry=myquery



jonance -> RE: search across multiple fields (9/27/2003 19:06:39)

Thanks...i pasted that in and got this error. I wish i knew more about asp so i could troubleshoot but i'll have to ask for more help i'm afraid. Maybe by the time we figure this out i'll know more asp...thanks, JOhn :


The page is at http://axiomskatesales.com/searchtest.asp


Microsoft VBScript compilation error '800a0409'

Unterminated string constant

/searchtest.asp, line 173

myQuerystring = "(itemname LIKE '%::search::%' OR
------------------------------------------------------------------^



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

<%
Dim Keyw
dim sort
Dim myquery
Dim myQuerystring
sort = Request.form("SortColumn")
Keyw = " SELECT * FROM products WHERE "
Keyw = split(Request.Form("search" )," " )
if uBound(Keyw) > 0 Then
for i = 1 to uBound(Keyw)
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
else
myQuerystring = "(itemname LIKE '%::search::%' OR brands LIKE '%::search::%' OR category LIKE '%::search::%' OR subcategory LIKE '%::search::%')"
end if
Keyw = Keyw + myQuerystring
Keyw = Keyw + "ORDER BY sort"
end if
myquery = Keyw
%>

Note the changes in green above. The error was in the second green line segment. Try to place that entire line on a single line on your page. It was reading it as 3 separate lines.

The reason for the first green line was because concantenation was not really required for the first line. Assign it first, then concantenate the remaining.



jonance -> RE: search across multiple fields (9/27/2003 19:36:10)

Thanks again, but I have a new error now....Expected Statement.

http://axiom.nt3.npsis.com/searchtest.asp



Long Island Lune -> RE: search across multiple fields (9/27/2003 19:43:11)

I tried your page. I'm getting:
The page cannot be displayed

You mentioned "Expected Statement". Does it give a line number?



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

Microsoft VBScript compilation error '800a0400'

Expected statement

/searchtest.asp, line 177

end if
^



Long Island Lune -> RE: search across multiple fields (9/27/2003 19:52:11)

I'm taking a blind guess:

<%
Dim Keyw
dim sort
Dim myquery
Dim myQuerystring
sort = Request.form("SortColumn")
Keyw = " SELECT * FROM products WHERE "
Keyw = split(Request.Form("search" )," " )
if uBound(Keyw) > 0 Then
for i = 1 to uBound(Keyw)
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
else
myQuerystring = "(itemname LIKE '%::search::%' OR brands"
myQuerystring = myQuerystring & " LIKE '%::search::%' OR category"
myQuerystring = myQuerystring & " LIKE '%::search::%' OR subcategory" myQuerystring = myQuerystring & " LIKE '%::search::%')"
end if
Keyw = Keyw + myQuerystring
Keyw = Keyw + "ORDER BY sort"
end if
myquery = Keyw
response.write myquery
%>

The first green segment breaks the line up and concantenates it together.

The second green segment is a TEST line so you can see what is actually being built on the screen. Once we have the problem figured out, this line would be REMOVED.

It's Saturday night. I will be going out in a short time. If you still have problems, post it, I won't be able to check until later on tonight. If it's still giving you problems I'll take this code and put it in a test scenario on my site and test it until I get the correct procedure. It will get figured out. [:)][:D]



Long Island Lune -> RE: search across multiple fields (9/27/2003 19:58:02)

To make it look NEATER...:

<%
Dim Keyw
dim sort
Dim myquery
Dim myQuerystring
sort = Request.form("SortColumn")
Keyw = " SELECT * FROM products WHERE "
Keyw = split(Request.Form("search" )," " )
if uBound(Keyw) > 0 Then
for i = 1 to uBound(Keyw)
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
else
myQuerystring = "(itemname LIKE '%::search::%' OR"
myQuerystring = myQuerystring & " brands LIKE '%::search::%' OR"
myQuerystring = myQuerystring & " category LIKE '%::search::%' OR"
myQuerystring = myQuerystring & " subcategory LIKE '%::search::%')"
end if
Keyw = Keyw + myQuerystring
Keyw = Keyw + "ORDER BY sort"
end if
myquery = Keyw
response.write myquery
%>

Use this instead. It's just to make it look more uniform and neater. Make sure your putting the spaces in where they are required - unless your cut, copy and pasting. Then it doesn't matter. [;)]



Long Island Lune -> RE: search across multiple fields (9/27/2003 20:05:10)

Last advice before I go:

If you are cutting, copying and pasting this, make sure you take the contents from this post, run it through MS-Notepad first to get all the formatting out, then cut, copy and paste it from MS-Notepad into your page.

And make sure each line is exactly as you see it here (on your page). [;)]

Your probably know this already right? [;)]



jonance -> RE: search across multiple fields (9/27/2003 20:12:11)

yeah...i'm doing that...but it never hurts to point out. Thanks again for your help...i look forward to getting this solved. I have a client happy with EVERYTHING but how the search works...and i didn't know where to go with it. I do now...talk to you later.



jonance -> RE: search across multiple fields (9/27/2003 20:15:12)

One more thing while we are working on this....will this have the capability to match if the user types in more than one word and it isn't the "exact" phrase in the db. For example...ther is a product called:

Grind King Custom Union Jack, 5.0

I would like for a user to be able to type "Grind King Jack" and still have it come up....right now, it only matches if it is the exact phrase...is this possible?

Thanks,
John



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

Absolutely. Even if you just typed in an "a", everything with an "a" in it would be returned. Or "King Custom Union Jack, 5.0", or "Custom Union Jack, 5.0", or even "5.0". It doesn't matter. If it finds what you typed in, whether 1, 2, 3... words, and it will return it no matter what the full title is. It's working on my site now. This customer of mine was very insistant that it return anything that a user typed in no matter what it was, or how many words.

Did you try the code I left you with?
How did it go?



jonance -> RE: search across multiple fields (9/28/2003 0:06:49)

Sorry..i thought i posted this...maybe it didn't take. I tried both..this is the error the latter gave below. On my previous question, just to clarify, the keywords you mentioned were all exact phrases found in the title:

King Custom Union Jack, 5.0

right now i can get it to retrieve the record if 'king custom' is typed or 'union jack' is typed...but what if they type 'king jack'...where the words are not right together in the title. Right now on my search (the one that i have working on the site)...i can't get that to retrieve the result...that is important to the client. What is the url of your search page? I'd like to see if yours does that. Anyhow, here is the error....thanks!

Microsoft VBScript compilation error '800a0400'

Expected statement

/searchtest.asp, line 180

end if
^



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


<%
Dim Keyw, keyt
Dim sort
Dim myquery
Dim myQuerystring
sort = Request.form("SortColumn")
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 sort"
end if
myquery = Keyw
response.write myquery
%>

Notice the green changes I made. I made a mistake. Sorry, I was in a rush before. Now that I can relax I noticed I did not give a base for the creation of the myQuerystring string prior to concantening additional loops based on what your user typed in. This should also remove the error you have been getting.

Microsoft VBScript compilation error '800a0400'
Expected statement
/searchtest.asp, line 180
end if
^

quote:


What is the url of your search page? I'd like to see if yours does that.


Sorry, I can't do that unless I check with the owner first because I would have to give you his username and password. The site is private for a paying clientel.

I just went to the site and looked at all the products in the database. I found one with a long title:

Patented Eagleglove Golf Glove

I typed in: Glove Golf and it returned the product.
I typed in: Golf Glove and it returned the product.
I typed in: Golf and it returned the product.
I typed in: Patented Golf and it returned the product.

I just edited this area. IT WORKS!!!



Long Island Lune -> RE: search across multiple fields (9/28/2003 1:03:08)

John,

Refresh this page before copying my latest code segment. I just made an edit and you might not have the latest version. [;)]



Long Island Lune -> RE: search across multiple fields (9/28/2003 1:33:31)

John,

It worked:

"Patented Eagleglove Golf Glove"

I typed in: Glove Golf and it returned the product.
I typed in: Golf Glove and it returned the product.
I typed in: Golf and it returned the product.
I typed in: Patented Golf and it returned the product.
I typed in: Patented Glove and it returned the product.
I typed in: Golf Patented and it returned the product.

I was wrong. My client's site has 15 different search features and I used the wrong one. Once I realized this, I re-did the test. And no matter what I typed in, it worked perfect. So the answer is YES. My code will handle any word, or any group of words in any combination, even if they are typed in backwards. [:)][:)][:)][:)][:)]

Put the code in from my second-to-last post and see if you don't get the error now.
If it goes in error-free, start testing it and let me know your results. [:)][:)][:)][:)][:)]



jonance -> RE: search across multiple fields (9/28/2003 9:35:34)

I think we must be close...but still get a similar error. The error is below followed by all the code from the table i am displaying the results in. I won't be home until later tonight, so i can't test till then, but i look forward to trying out what you come up with. The last thing you mentioned is EXACTLY what i'm looking for! Thanks for all your help again:

Microsoft VBScript compilation error '800a0400'

Expected statement

/searchtest.asp, line 179

end if
^


Here is the code for that chunk of my page if it helps....

quote:


<p align="center"><font size="2" color="#800000"><b>Cart</b></font></td>
</tr>
</thead>
<tbody>
<%
Dim Keyw, keyt
Dim sort
Dim myquery
Dim myQuerystring
sort = Request.form("SortColumn")
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 sort"
end if
myquery = Keyw
response.write myquery
%>
<!--#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")%>">
</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>

<p align="left"><font face="Arial" size="2"><strong>If you didn't find what
you're looking for, </strong><strong><a href="contact.asp">send us a message</a>
and we will try our best to help. </strong></font></p>




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

I'm doing a mach-up of the code on my machine right now. [;)]



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

John,

Here you go. I tested this code and it produces the SQL's perfectly. My apologies on not literally testing it from the very beginning. Regardless, I found the error. So cut, copy, paste it in, and start testing. [:)][:D][;)]


<%
Dim Keyw, keyt
Dim sort
Dim myquery
Dim myQuerystring
sort = Request.form("SortColumn")
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 sort"
myquery = Keyw
%>



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

quote:

response.write myquery

P.S.
I took this line OUT so you will not be able to see the actual SQL's this routine is building.
If you want to SEE them as your testing, just put the line back in.
[;)]



jonance -> RE: search across multiple fields (9/28/2003 17:33:36)

Thanks again for the dedication...we must be getting VERY close. The page displays now but i am getting a "too few parameters...expected 1" error when i try the search. check it out at:

http://axiomskatesales.com/searchtest.asp

The search is at the top of the page (it posts to that same page)

Thanks,
John



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

Let's keep your sort column the way you originally had it. Cut, copy and paste this version in. The green is the area I changed.

<%
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
%>

In your next post, give me a word that I can use to search through your skateboard database with. [;)]



jonance -> RE: search across multiple fields (9/28/2003 20:56:13)

Okay...it isn't giving the error anymore...and the search is working to a degree and we are almost there i think...again THANKS A MILLION!

Go to http://axiomskatesales.com/searchtest.asp and do a search for "black label". It will bring up a bunch of results...that first item should be:

Black Label Hooded Youth Sweat, Never Forget, One Size

It shows there, but if you then type in "black label sweat"...it returns no results...even though all of those words are in the itemname. That is what i am after....it seems they still need to be an EXACT phrase...let me know if you need any of my code, I copied and pasted it exactly though....

Thanks,
John



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

Now I'm lost.

I have the exact same thing on my site and it works perfect.
It just so happens that my client has another site that I did for him about 6 months ago that is just sitting there unused. He mentioned it on the phone this morning. I had forgotten about it.

It has the exact same system on it and I can let you go there now and test the code to see how it works on mine. The site is domant now becasue we only have temporary graphics on it. I just tested the site with the examples I gave you:

"Patented Eagleglove Golf Glove"

And it works...

Here are the instructions:
(Maybe you should print this post out for testing purposes.)

1): Goto: XXXXXXXXXXX DELETED OUT XXXXXXXXXXX
2): On the left side of the home page you will see a giant button labeled: "Search for Specials and Closeouts NOW", CLICK it.
3): Once your on the search page look for:

"A): Use this search form if you want to SEARCH BY Category: "

4): Using the drop down listbox, select "GOLF GLOVES", then click the [SUBMIT THIS CATEGORY] button directly below it.

5): Once your on the next page you will see Keyword(s). Here is the segment from one of my previous posts to you:

I typed in: Glove Golf and it returned the product.
I typed in: Golf Glove and it returned the product.
I typed in: Golf and it returned the product.
I typed in: Patented Golf and it returned the product.
I typed in: Patented Glove and it returned the product.
I typed in: Golf Patented and it returned the product.

Cut, copy and paste each of the examples in (from above), and click the [SUBMIT MY INFO NOW BUTTON].

After you conduct each search, use your browser BACK button to return to the KEYWORD SEARCH page. Then cut, copy and paste the next example (above) and do it again.

There is only one product of this type in the database.

See?
It works fine in this database.
Tomorrow I'm editing THIS post to remove the site address I gave you so PLEASE check it out tonight. I really should have done this in IM.

Let's VERIFY that you see that it is working.



jonance -> RE: search across multiple fields (9/28/2003 21:38:42)

Hmmmm....that really stumps me....why would that happen? Any ideas....Here is the code i have on the site now on that page. Is it the same as your site? You can delete it now as i see it works...but it puzzles me. Just when i think we have it licked, a new problem!!

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="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 21:41:53)

For what it's worth...i had asked a question just about this EXACT PHRASE subject...spooky answered with this. It is just for a one field search, but the idea is the same...i wasn't sure where to put it or how to use it though...:

quote:


Search=Replace(Request("Search")," ","%")
SELECT * FROM products WHERE (itemname LIKE '%"&Search&"%') ORDER BY ::SortColumn::



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

John,

I'm glad you see it worked.
I just wanted you to see that I'M NOT CRAZY!!! It does work...
The address has now been edited from my last post.

There has to be a reason for this.
I'm going to look over Spooky's segment and see how it would work in mine.

Sit tight. [;)]



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

Valid CSS!




Forum Software © ASPPlayground.NET Advanced Edition 2.4.5 ANSI
0.140625