Chris__T
Posts: 339
|
| Posted: 12/08/2008, 10:28 AM |
|
Awhile back I found this topic below for multiple keyword search:
http://forums.codecharge.com/posts.php?post_id=94083
I modified it to work in ASP (VBscript) and it works great.
dim term
dim x
dim clause
dim Lnamestring
dim keywords
dim temp
dim tempe
Lnamestring = CCGetFromGet("s_Job_Site", Empty)
Lnamestring = Lnamestring & " ."
If Len(Lnamestring) > 0 Then
term = Trim(Lnamestring)
term = Replace(term,"'","' '")
If InStr(term," ") > 0 Then
term = Split(term," ")
'temp = Ubound(term)
'tempe = temp + 1
'term(4) = "."
For x=0 to Ubound(term)
If x > 0 Then
clause = clause & " OR"
End If
clause = clause & " LName LIKE '%" & term(x) & "%'"
Next
Else
clause = " LName LIKE '%" & term & "%'"
End If
'sql = " WHERE " & clause
Employee2.DataSource.Where = Employee2.DataSource.Where & clause
'sql = "SELECT * FROM location WHERE" & clause
'Response.Write sql
End If
Now I'm trying to take it a step further. The code above does a multiple keyword search on one field. I would like it to do multiple keyword search on multiple fields.
I've been playing around with adding code to "clause = " lines to add more fields, but nothing is working right. It probably has something to do with it cycling through the for loop.
|
 |
 |
Oper
Posts: 1195
|
| Posted: 12/09/2008, 4:56 AM |
|
chris explain what those more fields mean?
could you post a sample SQL of what are you expecting?
something like:
select * from mytable where F01 like '%here%' or F01 like '%ok%' .................... what else?
_________________
____________________________
http://www.7bz.com (Free CMS,CRM Developed in CCS)
http://www.PremiumWebTemplate.com
Affiliation Web Site Templates
Please do backup first |
|
|
|
Chris__T
Posts: 339
|
| Posted: 12/09/2008, 6:18 AM |
|
Sorry, I should have explained that part. Right now with the code above, if I type (no quotes) "Davis Smith Walker Johnson" in the search box and hit the button, it takes that string and breaks it down into 4 different search words, so it will pull all records with a last name (LName) of Davis or Smith or Walker or Johnson. If you did that with regular search, it wouldn't return anything, because it would be looking for one last name of all 4 words.
Now what I want to do, is if I type (no quotes) "Johnson Elm Sewer " it breaks these down into 3 search words and not only searches on the LName field, but also searches over a Street field and a Project Description field. So it will search the LName field (in table) for Johnson or Elm or Sewer, will look in the street field for Johnson or Elm or Sewer and finally look in the Project Description field for either Johnson or Elm or Sewer. So it would pull records that match these criteria and some might be related in some way. Like a Google search. I'm having problems trying to figure out how to add the extra fields into the code above.
I'm thinking I might need another for loop for each field.
|
|
|
|
Chris__T
Posts: 339
|
| Posted: 12/09/2008, 7:54 AM |
|
I'm also trying this code:
dim keywords(20)
dim Lnamestring
dim tempy
dim search_columns(4)
search_columns(0)="Permit_No"
search_columns(1)="Job_Site"
search_columns(2)="Project_Type"
search_columns(3)="Contractor_Name"
search_columns(4)="Contractor_Firm_Name"
Lnamestring = CCGetFromGet("s_Job_Site", Empty)
tempy = trim(Lnamestring)
keywords = split(tempy)
Permit1.datasource.Where = " "
dim p
p = 0
Permit1.DataSource.Where = Permit1.DataSource.Where & "("
dim word
dim m
dim column
for each word in keywords
if (len(word) > 2) then
if (p <> 0) then
Permit1.DataSource.Where = Permit1.DataSource.Where & ") AND ("
p=1
m=0
end if
for each column in search_columns
if (m <> 0) then
Permit1.DataSource.Where = Permit1.DataSource.Where & " OR "
Permit1.DataSource.Where = Permit1.DataSource.Where & " search_columns(column) LIKE '%keywords(word)%' "
m=1
end if
next 'end foreach column
end if
next ' foreach word
Permit1.DataSource.Where = Permit1.DataSource.Where & ")"
But I can't get this one working either.
|
|
|
|
Oper
Posts: 1195
|
| Posted: 12/09/2008, 7:58 PM |
|
ok i going nut i have reply this few times.
Testing........
Testing..........
_________________
____________________________
http://www.7bz.com (Free CMS,CRM Developed in CCS)
http://www.PremiumWebTemplate.com
Affiliation Web Site Templates
Please do backup first |
|
|
|
Oper
Posts: 1195
|
| Posted: 12/09/2008, 8:00 PM |
|
for each word in keywords
if (len(word) > 2) then
if (p <> 0) then
Permit1.DataSource.Where = Permit1.DataSource.Where & ") AND ("
p=1
m=0
end if
for each column in search_columns
if (m <> 0) then
Permit1.DataSource.Where = Permit1.DataSource.Where & " OR "
End if
Permit1.DataSource.Where = Permit1.DataSource.Where & " search_columns(column) LIKE '%keywords(word)%' "
m=1
next 'end foreach column
end if
next ' foreach word
Permit1.DataSource.Where = Permit1.DataSource.Where & ")"
at at the end
response.write Permit1.DataSource.Where
response.end
so we coudl check what's goign on
i did a small change on the if take a look and test
but please post response.write
_________________
____________________________
http://www.7bz.com (Free CMS,CRM Developed in CCS)
http://www.PremiumWebTemplate.com
Affiliation Web Site Templates
Please do backup first |
|
|
|
Chris__T
Posts: 339
|
| Posted: 12/10/2008, 6:49 AM |
|
I had this same problem posted in the ASP section:
http://forums.codecharge.com/posts.php?post_id=102417
I guess we could just keep it to one section instead of replying to both.
tried the modifications above, and still get a blank white screen.
|
|
|
|
|
