| Author |
 Topic  |
|
|
RaiderUK
Average Member
United Kingdom
577 Posts |
 Posted - 27 March 2001 : 08:08:01
|
I am trying to do a search through fields in a access database I have, but it needs to be containing words. I looked at the Snitz search.asp and found it quite scary.
Can anyone help please?
|
|
|
davemaxwell
Access 2000 Support Moderator
USA
3020 Posts |
Posted - 27 March 2001 : 11:29:02
|
It's basically
SELECT * from tablename where field1 like '%datatosearchfor%'
The % are wildcards for anything before and after it. If you want to ignore case (capital letters), then use:
SELECT * from tablename where lcase(field1) like lcase('%datatosearchfor%')
Dave Maxwell
--------------
Proud to be a "World Class" Knucklehead |
 |
|
|
RaiderUK
Average Member
United Kingdom
577 Posts |
Posted - 27 March 2001 : 14:17:44
|
thats how i would normally search, but it would not return words containing.
i.e. Search for Snitz Forum Database would not work if the record was: Snitz Database
|
|
|
|
davemaxwell
Access 2000 Support Moderator
USA
3020 Posts |
Posted - 28 March 2001 : 07:01:39
|
quote:
thats how i would normally search, but it would not return words containing.
i.e. Search for Snitz(<- Changed to be what I think you mean ) Forum Database would not work if the record was: Snitz Database
If you set it up to look for each word individually it would. What you would basically need to do is parse the field containing the string values into an array, then use the arrays as the value to search for. In other words, if you set it up like this it should work:
keywords = split(Request.Form("Search"), " ")
keycnt = ubound(keywords)
loopcnt = 0
strSql = "Select * from table"
For Each word in keywords
loopcnt = loopcnt + 1
if loopcnt = 1 then
strSql = strSql & " WHERE "
else
strSql = strSql & " OR "
end if
strSql = strSql & "field1 like '%" & word & "%'"
next
rs.Open strSql, my_Conn, 3,1
Dave Maxwell
--------------
Proud to be a "World Class" Knucklehead |
|
|
|
RaiderUK
Average Member
United Kingdom
577 Posts |
Posted - 28 March 2001 : 08:21:05
|
thanks Dave will have fun now try to put that into my page.
|
|
|
|
RaiderUK
Average Member
United Kingdom
577 Posts |
Posted - 28 March 2001 : 11:29:18
|
just put it in and it works well, thanks Dave.
Is there any way to list by best match?
i.e. at the moment if there was 2 records with the text:
1. Discription of the file
2. File contents
If you searched for the words " File Contents " it
displays Record 1 first.
|
|
|
|
davemaxwell
Access 2000 Support Moderator
USA
3020 Posts |
Posted - 28 March 2001 : 12:41:21
|
This is about the best way I can think of (note: I did NOT test this,
so syntax may or may not be correct):
keywords = split(Request.Form("Search"), " ")
keycnt = ubound(keywords)
loopcnt = 0
orcount = 0
do while orcount < keycnt
strSql = "Select * from table"
For Each word in keywords
loopcnt = loopcnt + 1
if loopcnt = 1 then
strSql = strSql & " WHERE "
elseif loopcnt > orcnt then
strSql = strSql & " AND "
else
strSql = strSql & " OR "
end if
strSql = strSql & "field1 like '%" & word & "%'"
next
rs.Open strSql, my_Conn, 3,1
:
: Code to display goes here.....
:
rs.Close
set rs = nothing
orcount = orcnt + 1
loop
What this SHOULD do is count the number of items in your array and then loop through the
tables looking for all of them, then one or, then two or, etc. In other words, if there
are three items in the list, the where statement will be:
where field1 like '%value1%' AND field1 like '%value2%' AND field1 like '%value3%'
then it should be use this where statement:
where field1 like '%value1%' AND field1 like '%value2%' OR field1 like '%value3%'
finally it should use this where statement:
where field1 like '%value1%' OR field1 like '%value2%' OR field1 like '%value3%'
You might want to add a response.write of the strsql to make sure this is doing what you want.....
Dave Maxwell
--------------
Proud to be a "World Class" Knucklehead |
|
|
|
RaiderUK
Average Member
United Kingdom
577 Posts |
Posted - 28 March 2001 : 18:56:32
|
thanks again Dave, i will give it a try now.
|
|
|
|
RaiderUK
Average Member
United Kingdom
577 Posts |
Posted - 30 March 2001 : 05:29:23
|
cant seem to get it to work, can anyone help please.
The code looks like this now:
<%
If Request.ServerVariables("REQUEST_METHOD") = "POST" then
Dim strShWords
strShWords = Request.Form("search")
If Request.Form("searchOption") = "cont" then
keywords = split(strShWords, " ")
keycnt = ubound(keywords)
loopcnt = 0
orcount = 0
do while orcount < keycnt
strSql = "Select * from tbl_Resources"
For Each word in keywords
loopcnt = loopcnt + 1
if loopcnt = 1 then
strSql = strSql & " WHERE "
elseif loopcnt > orcnt then
strSql = strSql & " AND "
else
strSql = strSql & " OR "
end if
strSql = strSql & "res_ShtDesc like '%" & word & "%'"
next
set rsSearch = Server.CreateObject("ADODB.Recordset")
rsSearch.ActiveConnection = strConString
rsSearch.Source = strSql
rsSearch.CursorType = 0
rsSearch.CursorLocation = 2
rsSearch.LockType = 3
rsSearch.Open()
rsSearch_numRows = 0
orcount = orcnt + 1
loop
else
set rsSearch = Server.CreateObject("ADODB.Recordset")
rsSearch.ActiveConnection = strConString
rsSearch.Source = "SELECT * FROM tbl_Resources WHERE res_ShtDesc like '%" & strShWords & "%'"
rsSearch.CursorType = 0
rsSearch.CursorLocation = 2
rsSearch.LockType = 3
rsSearch.Open()
rsSearch_numRows = 0
end if
end if
Dim Repeat1__numRows
Repeat1__numRows = 10
Dim Repeat1__index
<in the page>
<%
strCount = 0
If Not rsSearch.EOF Or Not rsSearch.BOF Then
%>
Records found. </font><br>
<br>
<table width="475" border="0" cellspacing="0" cellpadding="0">
<%
While ((Repeat1__numRows <> 0) AND (NOT rsSearch.EOF))
%>
<tr>
<td width="40">
<b> <i>
<%
strCount = strCount + 1
Response.Write strCount
%>
</i></b></td>
<td width="419"><%=(rsSearch.Fields.Item("res_ShtDesc").Value)%></td>
<td width="16"> </td>
</tr>
<%
Repeat1__index=Repeat1__index+1
Repeat1__numRows=Repeat1__numRows-1
rsSearch.MoveNext()
Wend
%>
</table>
<% end if %>
Repeat1__index = 0
rsSearch_numRows = rsSearch_numRows + Repeat1__numRows
|
|
|
|
davemaxwell
Access 2000 Support Moderator
USA
3020 Posts |
Posted - 30 March 2001 : 07:42:03
|
Add a Response.Write "SQL = " & StrSql Right after the next line and see what is in the SQL. Make sure it follows the logic I gave you earlier where if you've got three values, it does this:
Loop1:
Select & from table
Where Field1 like '%value1%' AND Field1 like '%value2%' AND Field1 like '%value3%'
Loop2:
Select & from table
Where Field1 like '%value1%' AND Field1 like '%value2%' OR Field1 like '%value3%'
Loop3:
Select & from table
Where Field1 like '%value1%' OR Field1 like '%value2%' OR Field1 like '%value3%'
<thought> if you want to be REAL exact, then loop2 should actually read:
Loop2:
Select & from table
Where (Field1 like '%value1%' AND Field2 like '%value2%') OR
(Field1 like '%value1%' AND Field3 like '%value2%') OR
(Field2 like '%value1%' AND Field3 like '%value2%')
</thought>
Dave Maxwell
--------------
Proud to be a "World Class" Knucklehead |
|
|
|
RaiderUK
Average Member
United Kingdom
577 Posts |
Posted - 30 March 2001 : 08:59:58
|
i tried it like this:
Dim strShWords
strShWords = "Hello this is a test"
keywords = split(strShWords, " ")
keycnt = ubound(keywords)
loopcnt = 0
orcount = 0
do while orcount < keycnt
strSql = "Select * from tbl_Resources"
For Each word in keywords
loopcnt = loopcnt + 1
if loopcnt = 1 then
strSql = strSql & " WHERE "
elseif loopcnt > orcnt then
strSql = strSql & " AND "
else
strSql = strSql & " OR "
end if
strSql = strSql & "res_ShtDesc like '%" & word & "%'"
next
orcount = orcount + 1
Response.Write "SQL = " & StrSql
loop
and the SQL look like this:
SQL = Select * from tbl_Resources WHERE res_ShtDesc like '%Hello%' AND res_ShtDesc like '%this%' AND res_ShtDesc like '%is%' AND res_ShtDesc like '%a%' AND res_ShtDesc like '%test%'SQL = Select * from tbl_Resources AND res_ShtDesc like '%Hello%' AND res_ShtDesc like '%this%' AND res_ShtDesc like '%is%' AND res_ShtDesc like '%a%' AND res_ShtDesc like '%test%'SQL = Select * from tbl_Resources AND res_ShtDesc like '%Hello%' AND res_ShtDesc like '%this%' AND res_ShtDesc like '%is%' AND res_ShtDesc like '%a%' AND res_ShtDesc like '%test%'SQL = Select * from tbl_Resources AND res_ShtDesc like '%Hello%' AND res_ShtDesc like '%this%' AND res_ShtDesc like '%is%' AND res_ShtDesc like '%a%' AND res_ShtDesc like '%test%'
|
|
|
|
davemaxwell
Access 2000 Support Moderator
USA
3020 Posts |
Posted - 30 March 2001 : 12:00:39
|
Move your loopcount=0 to after the do until
Dave Maxwell
--------------
Proud to be a "World Class" Knucklehead |
|
|
|
RaiderUK
Average Member
United Kingdom
577 Posts |
Posted - 30 March 2001 : 15:13:05
|
so it should now look like:
keywords = split(strShWords, " ")
keycnt = ubound(keywords)
orcount = 0
do while orcount < keycnt
loopcnt = 0
strSql = "Select * from tbl_Resources"
For Each word in keywords
loopcnt = loopcnt + 1
if loopcnt = 1 then
strSql = strSql & " WHERE "
elseif loopcnt > orcnt then
strSql = strSql & " AND "
else
strSql = strSql & " OR "
end if
strSql = strSql & "res_ShtDesc like '%" & word & "%'"
next
orcount = orcount + 1
Response.Write "SQL = " & StrSql
loop
|
|
|
|
RaiderUK
Average Member
United Kingdom
577 Posts |
Posted - 02 April 2001 : 04:19:18
|
the SQL looks like this now is this correct?
SQL = Select * from tbl_Resources WHERE res_ShtDesc like '%Hello%' AND res_ShtDesc like '%this%' AND res_ShtDesc like '%is%' AND res_ShtDesc like '%a%' AND res_ShtDesc like '%test%'SQL = Select * from tbl_Resources WHERE res_ShtDesc like '%Hello%' AND res_ShtDesc like '%this%' AND res_ShtDesc like '%is%' AND res_ShtDesc like '%a%' AND res_ShtDesc like '%test%'SQL = Select * from tbl_Resources WHERE res_ShtDesc like '%Hello%' AND res_ShtDesc like '%this%' AND res_ShtDesc like '%is%' AND res_ShtDesc like '%a%' AND res_ShtDesc like '%test%'SQL = Select * from tbl_Resources WHERE res_ShtDesc like '%Hello%' AND res_ShtDesc like '%this%' AND res_ShtDesc like '%is%' AND res_ShtDesc like '%a%' AND res_ShtDesc like '%test%'
Edited by - RaiderUK on 02 April 2001 04:19:48 |
|
|
|
RaiderUK
Average Member
United Kingdom
577 Posts |
Posted - 02 April 2001 : 04:46:52
|
if i use it like this it works fine except for when there is only 1 keyword entered.
Dim strShWords
strShWords = Request.Form("Search")
keywords = split(strShWords, " ")
keycnt = ubound(keywords)
orcount = 0
do while orcount < keycnt
loopcnt = 0
strSql = "Select * from tbl_Resources"
For Each word in keywords
loopcnt = loopcnt + 1
if loopcnt = 1 then
strSql = strSql & " WHERE "
elseif loopcnt > orcnt then
strSql = strSql & " AND "
else
strSql = strSql & " OR "
end if
strSql = strSql & "res_ShtDesc like '%%" & word & "%%'"
next
set rsSearch = Server.CreateObject("ADODB.Recordset")
rsSearch.ActiveConnection = connStr
rsSearch.Source = StrSql
rsSearch.CursorType = 0
rsSearch.CursorLocation = 2
rsSearch.LockType = 3
rsSearch.Open()
rsSearch_numRows = 0
orcount = orcount + 1
loop
|
|
|
| |
Topic |
|
Topic Locked
