| Author |
 Topic  |
|
|
skynet104
VP-CART New User
94 Posts |
 Posted - January 24 2004 : 00:28:04
|
I have been using VPasp for quite a long time now.
And I would recommend this product no matter what.
I am using VPasp 5.0 with an Access database with 44,000 products.
Many of my visitors to my page complaint that they can not find certain artists ( I sell music ), and I believe that the problem resides in the way the name of these artists are recorded.
The cname is setup as "lastname" followed by an space and then the "FirstName". For example:
When customers look for Elton John, they won't be able to find it, because the cname is setup as John Elton.
or some times when the name is compaund of 3 names it will be imposible to display this names with the search.
the only way they can find anything is just by typing just one name, then they will get results(ej. John).
Is there some code I can add to the default search asp that could help me to let my customers find anything, ignoring spaces and/or anything in between, how can i make the search to pick up the names even if the order of words is not the same as the name they are looking for?
Please help, I will appreciate your response
|
|
|
siraj
VP-CART New User
USA
194 Posts |
Posted - January 24 2004 : 00:44:11
|
I am sure your search need to a minor tweeking. Friend of mine, had the same issue and she sells pictures and she tweek the code by Support for a little fee. You can ask support or buzz me I'll direct you. [email protected]
GOOD LUCK.
SIRAJ. |
 |
|
|
Jill
VP-CART Super User
USA
249 Posts |
Posted - January 24 2004 : 05:58:09
|
Would it be possible to switch the names so they would make more sense? Do they come to you in the form of lastname,firstname? If so, it would be easy enough to run an update expression to switch them.
Jill
|
|
|
|
skynet104
VP-CART New User
94 Posts |
Posted - January 24 2004 : 17:06:04
|
Hi there;
Yes, the names come in that format (lastname firstname), it is the way the data was designed. Switch? how can I do that?
Thanks in Advance;
|
|
|
|
jonmadrid
VP-CART New User
USA
192 Posts |
Posted - January 24 2004 : 17:48:11
|
I just read this thread and thought of a quick and easy way to overcome your problem. Here goes...
In Shopsearch.asp, around line 257, change this:
*******
strKeyword = Request("Keyword")
*******
TO this
*******
strKeyword = Replace(Request("Keyword")," ",",")
*******
What that does is instead of searching for the string as a whole, it breaks it into different items so that they can each be searched on by replacing any spaces with commas. I tested it by renaming one of the products in my cart to "John Elton" (as the example you mentioned). I then performed a search for "Elton John" and the record was found. Worked like a charm. I haven't really tested it beyond that but it seemed to work just fine.
Does that solve your problem?
All the best,
Jon Madrid
--------------------
Madrid Communications
Web Design, Development, and Hosting
www.madridcom.com
|
|
|
|
Jill
VP-CART Super User
USA
249 Posts |
Posted - January 24 2004 : 23:03:03
|
Skynet,
I use this expression in an update query in Access 2000. It works if your firstname, lastname has the comma. Otherwise, the expression would have to be modified:
Mid([Contrib],InStr([Contrib],",")+1,Len([contrib])) & " " & Left([Contrib],InStr([Contrib],",")-1)
Replace "Contrib" with the name of the field that the names are in. If you have a name like Smith, Jr., John or Musicland, Inc you'll end up with Inc Musicland and something like Jr., John Smith. I just run a query beforehand for *,*,* to pull everything with 2 commas, and then for "inc" afterwards. Not terribly efficient, but it works, especially since there are usually only 4 or 5 records like that.
If you are using Access, but you're not familiar with queries, and want to use this, let me know and I'll try to walk you through it. And I'd copy the field contents into another field before running the expression, just in case!
Jill 
|
|
|
|
skynet104
VP-CART New User
94 Posts |
Posted - January 25 2004 : 02:02:17
|
Hi Jill and Jon:
Thanks a lot for your perfect response, however i am faced with another problem when I made this changes, it does find the artist name, however it will also search all the words in the database that are similar and it will trigger a huge list of items, also since my customers look also by song name (Hotel California) , this option will not work if i do the changes.
I came out with a query that solves the problem of the name too, however it will not find the names with 3 words or more example "kool and the gang", it will just list the ones with only one first name and one last name.
and the code is as follows:
*******
if Request("wname") <>"" then
wname = request("wname")
fname = mid(wname,1,instr(wname," ")-1)
lname = mid(wname,instr(wname," ") + 1,len(wname))
else
fname = ucase(Request("fname"))
response.write("fname=" & fname & "] <br>")
lname = ucase(Request("lname"))
response.write("lname=" & lname & "] <br>")
end if
********
My question is: how can I combine the ability of this query with the same power as shopsearch.asp and have this query find also names that contain 3 words or more? and the songs too.
Sorry for the confusion, if you need the entire query file, it is at the right here at the end.
Thanks in Advance
__________________________
<!--#include file="shop$db.asp"-->
<%
'***********************************************************
' VP-ASP General Query of Products based on any field
' and field=value can come from a form or hyperlink
' If using a form, the Form field name must be SHOPQUERY
' if numbers < 0 then they are ignore
' if fieldvalue="ALL" then all fieldvalues match
' VP-ASP 3.0
'*************************************************************
const cMaxProductFields=100
dim allvaluues
allvalues="ALL"
Dim keys(100)
dim keyvalues(100)
dim keycount
dim firsttime
dim Words(10)
Dim wordcount
Dim ProductFields
dim ProductTypes
Dim ProductFieldCount
dim fieldtype
'
GetProductFields
GetInput ' see what user wants
GenerateSQL ' generate SQL
SetSess "wordcount",keycount
SetSess "Words",keys
Setsess "SQL",SQL
'debugwrite sql
Response.Redirect "ShopDisplayproducts.asp?Search=Yes"
'
Sub GetInput
dim key
dim strname, strvalue
keycount=0
wordcount=0
For Each key in Request.form
strname = key
strvalue = Request.Form(key)
if strvalue<>"" Then
KeyValues(keycount)=strvalue
Keys(keycount)=strname
keycount=keycount+1
end if
next
For Each key in Request.Querystring
strname = key
strvalue = Request.querystring(key)
if strvalue<>"" Then
KeyValues(keycount)=strvalue
Keys(keycount)=strname
keycount=keycount+1
end if
next
end sub
Sub GenerateSQL
dim i
dim rc
on error resume next
firsttime="TRUE"
SQL = "SELECT * FROM Products "
if xstocklow<>"" then
lngCStock= clng(xStockLow)
AddPrefix
SQL = SQL & " cStock> " & lngCStock
end if
for i=0 to keycount-1
AddSQL Keys(i), keyvalues(i), SQL
Next
sql = sql & " ORDER BY " & xSortProducts
if xdebug="yes" then
debugwrite sql
end if
if Request("wname") <>"" then
wname = request("wname")
fname = mid(wname,1,instr(wname," ")-1)
lname = mid(wname,instr(wname," ") + 1,len(wname))
else
fname = ucase(Request("fname"))
response.write("fname=" & fname & "] <br>")
lname = ucase(Request("lname"))
response.write("lname=" & lname & "] <br>")
end if
sql = "select * from products where cname like '" & lname & "%' and rtrim(cname) like '%" & fname & "'"
'sql = sql & " ORDER BY " & xSortProducts
'response.write ("sql=" & sql & "<br>")
'response.end
end sub
'
SUB AddSQL (strname,strvalue, SQL)
dim fieldtype
strname=Ucase(strname)
CheckValidField strname, rc, fieldtype
if rc>0 then
exit sub
end if
if Fieldtype ="Number" or FieldType="Currency" then
end if
if fieldtype="Text" or fieldtype="Memo" then
If ucase(strvalue)=allvalues then ' make all really mean all
strvalue=""
end if
AddPrefix
SQL=SQL & " " & strname & " like '" & strvalue & "%'"
exit sub
end if
If Fieldtype="DateTime" then
addPrefix
SQL=SQL & " " & strname & "=#" & cdate(strvalue) & "#"
exit sub
end if
If Fieldtype="Currency" then
if strvalue<0 then
exit sub
end if
If not IsNumeric(strvalue) then
exit sub
end if
if xConvertEuropeanNumbers="Yes" then
strvalue=replace(strvalue,",",".")
end if
'strvalue=Formatnumber(strvalue,2)
addPrefix
SQL=SQL & " " & strname & "<=" & strvalue
exit sub
end if
if Fieldtype ="Number" then
If strvalue<0 then
exit sub
end if
If not IsNumeric(strvalue) then
exit sub
end if
addPrefix
SQL=SQL & " " & strname & "=" & strvalue
exit sub
end if
AddPrefix
SQL=SQL & " " & strname & " like '" & strvalue & "%'"
end sub
'
Sub addPrefix
if firsttime="TRUE" then
SQL=SQL & " WHERE "
firsttime="FALSE"
else
SQL = SQL & " AND "
end if
end sub
' don't allow bad fields to get into query
Sub GetProductFields
dim fSql
dim rs
dim fldname
dim fieldtype
ProductFieldCount=GetSess("ProductFieldCount")
If ProductFieldCount="" then
ShopOpendatabase Dbc
ProductFieldCount=0
ReDim ProductFields(cMaxProductFields)
ReDim ProductTypes(cMaxProductFields)
FSQL = "SELECT * FROM Products"
Set rs = dbc.Execute(fSQL)
For each fldName in rs.Fields
ProductFields(productfieldcount)=ucase(fldName.Name)
fieldtype=GetTypeName(fldname.type)
ProductTypes(productfieldcount)=fieldtype
' debugwrite fieldtype
Productfieldcount=Productfieldcount+1
next
rs.close
shopclosedatabase dbc
SetSessA "ProductFields",ProductFields
SetSessA "ProductTypes",ProductTypes
SetSess "ProductFieldCount",ProductFieldCount
end if
ProductFields=GetsessA("ProductFields")
ProductTypes=GetsessA("ProductTypes")
ProductFieldCount=GetSess("ProductFieldCount")
End Sub
'
Sub CheckValidfield (fieldname, rc, fieldtype)
rc =0
for i = 0 to ProductFieldcount-1
if fieldname=ProductFields(i) then
fieldtype=ProductTypes(i)
exit sub
end if
next
rc=4
end sub
Function GetTypeName(id)
Select Case id
Case "3"
GetTypeName = "Number"
Case "200"
GetTypeName = "Text"
Case "129"
GetTypeName = "Text"
Case "201"
GetTypeName = "Memo"
Case "6"
GetTypeName = "Currency"
Case "11"
GetTypeName = "YesNo"
Case "5"
GetTypeName = "Number"
Case "7", "133","134","135"
GetTypeName = "DateTime"
Case Else
GetTypeName = "Text"
End Select
End Function
%>
|
|
|
|
jonmadrid
VP-CART New User
USA
192 Posts |
Posted - January 26 2004 : 12:07:00
|
How are artists like 'Kool and the Gang' (or artists with more than just FName LName) listed in your database?
All the best,
Jon Madrid
--------------------
Madrid Communications
Web Design, Development, and Hosting
www.madridcom.com
|
|
|
|
skynet104
VP-CART New User
94 Posts |
Posted - January 27 2004 : 10:30:02
|
Yes, we have artist wich include groups names wich are usually more than 2 names. The code that i have will search only for one fname and one lname.
Thanks in advance
|
|
|
| |
Topic |
|
All Forums
Search Problem
New Topic
Printer Friendly
TrustGuard - PCI Security Scanner
