|
| |
|
|
jonance
Posts: 298
From: Louisville KY USA
Status: offline
 |
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
Posts: 2340
Joined: 6/8/2002
From: New York
Status: offline
|
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
Posts: 298
From: Louisville KY USA
Status: offline
|
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
Posts: 2340
Joined: 6/8/2002
From: New York
Status: offline
|
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
Posts: 298
From: Louisville KY USA
Status: offline
|
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
Posts: 2340
Joined: 6/8/2002
From: New York
Status: offline
|
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
Posts: 298
From: Louisville KY USA
Status: offline
|
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
Posts: 2340
Joined: 6/8/2002
From: New York
Status: offline
|
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
Posts: 298
From: Louisville KY USA
Status: offline
|
RE: search across multiple fields - 9/27/2003 19:51:22
Microsoft VBScript compilation error '800a0400'
Expected statement
/searchtest.asp, line 177
end if
^
| |
|
|
|
jonance
Posts: 298
From: Louisville KY USA
Status: offline
|
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
Posts: 298
From: Louisville KY USA
Status: offline
|
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
Posts: 2340
Joined: 6/8/2002
From: New York
Status: offline
|
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?
< Message edited by Long Island Lune -- 9/27/2003 11:37:00 PM >
_____________________________
| |
|
|
|
jonance
Posts: 298
From: Louisville KY USA
Status: offline
|
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
Posts: 2340
Joined: 6/8/2002
From: New York
Status: offline
|
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!!!
< Message edited by Long Island Lune -- 9/28/2003 1:47:11 AM >
_____________________________
| |
|
|
|
jonance
Posts: 298
From: Louisville KY USA
Status: offline
|
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>
| |
|
|
|
jonance
Posts: 298
From: Louisville KY USA
Status: offline
|
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
| |
|
|
|
jonance
Posts: 298
From: Louisville KY USA
Status: offline
|
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
Posts: 2340
Joined: 6/8/2002
From: New York
Status: offline
|
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.
< Message edited by Long Island Lune -- 9/28/2003 9:44:34 PM >
_____________________________
| |
|
|
|
jonance
Posts: 298
From: Louisville KY USA
Status: offline
|
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
Posts: 298
From: Louisville KY USA
Status: offline
|
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::
| |
|
 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 
