Running environment: IIS
Script language: VBScript
Database: Access/SQL Server
Database language: SQL
1. summary:
Whether in dynamic websites such as forums, news systems, or download systems, you often see search functions: search for posts, search for users, search for software (in short, search for keywords), etc. This article introduces how to build an efficient Practical, ASP-based site multi-value search.
This article is dealing with "multi-condition fuzzy matching search". After understanding multiple conditions, single-condition search is just a piece of cake. Generally speaking, there are two methods for multi-condition search: enumeration method and progressive method. When there are not too many search conditions ( n <=3), the enumeration method can be used. The frequency of statements is 2 to the nth power, growing exponentially. n is the condition number. Obviously, when the number of conditions increases, the progressive method should be adopted regardless of the efficiency or realizability of the program. The statement frequency is n and grows linearly. It should be pointed out that the idea of the enumeration method is very simple. It determines whether the conditions are empty one by one, and then searches according to the non-empty conditions. At the same time, the truth table technology can be used to deal with situations with extremely many conditions (I believe no one will do this kind of thing) , when there are 4 conditions, you have to write 16 groups of statements); the progressive method is more ingenious, focusing on understanding. The first is the use of flags (flag), and the second is the clever use of string connections in SQL symbol&. The following uses examples to explain the establishment of the engine.
2. Example:
We build an address book query engine. The database name is addressbook.mdb, the table name is address, and the fields are as follows:
ID | Name | Tel | School |
1 | Zhang San | 33333333 | Department of Computer Science, University of Electronic Science and Technology of China |
2 | Li Si | 44444444 | Department of Biology, Sichuan University |
3 | Wang Er | 22222222 | Department of Architecture, Southwest Jiaotong University |
… | … | … | … |
The web search interface is as follows:
Name: | Phone: | School: | Search button |
The source program using enumeration method is as follows:
<%@ CODEPAGE = "936" %>
'Connect to database
<%
dim conn
dim DBOath
dim rs
dim sql
Set conn=Server.CreateObject("ADODB.Connection")
DBPath = Server.MapPath ("addressbook.mdb")
conn.Open "driver={Microsoft Access Driver (*.mdb)};dbq=" & DBPath
Set rs=Server.CreateObject("ADODB.Recordset")
'Get the name and phone number from the Web page , the value of the school
dim Name
dim Tel
dim School
Name=request("Name")
Tel=request("Tel")
School=request("School")
'The search core of the enumeration method, because there are three conditions, so it must be written 8 sets of If judgment statements
if trim(Name)="" and trim(Tel)="" and trim(School)="" then
sql="select * from address order by ID asc"
end if
if trim(Name)= "" and trim(Tel)="" and trim(School)<>"" then
sql="select * from address where School like '%"&trim(School)&"%' order by ID asc"
end if
if trim (Name)="" and trim(Tel)<>"" and trim(School)="" then
sql="select * from address where Tel like '%"&trim(Tel)&"%' order by ID asc"
end if
if trim(Name)="" and trim(Tel)<>"" and trim(School)<>"" then
sql="select * from address where Tel like '%"&trim(Tel)&"%' and School like '%"&trim(School)&"%' order by ID asc"
end if
if trim(Name)<>"" and trim(Tel)="" and trim(School)="" then
sql=" select * from address where Name like '%"&trim(Name)&"%' order by ID asc"
end if
if trim(Name)<>"" and trim(Tel)="" and trim(School)<>" " then
sql="select * from address where Name like '%"&trim(Name)&"%' and School like '%"&trim(School)&"%' order by ID asc"
end if
if trim(Name)< >"" and trim(Tel)<>"" and trim(School)="" then
sql="select * from address where Name like '%"&trim(Name)&"%' and Tel like '%"&trim( Tel)&"%' order by ID asc"
end if
if trim(Name)<>"" and trim(Tel)<>"" and trim(School)<>"" then
sql="select * from address where Name like '%"&trim(Name)&"%' and Tel like '%"&trim(Tel)&"%' and School like '%"&trim(School)&"%' order by ID asc"
end if
rs.open sql,conn,1,1
'Display search results
if rs.eof and rs.bof then
response.write "There is no record in the current address book"
else
do while not rs.eof
response.write "Name:"&rs("Name" )&"Telephone:"&rs("Tel")&"School:"&rs("School")&"<br>"
rs.movenext
loop
end if
'Disconnect the database
set rs=nothing
conn.close
set conn=nothing
%>
When understanding the above program, focus on the core part. The 8 groups of statements correspond to the 8 states in the 3 search boxes one by one.
Name | Tel | School |
| |
Empty | | | Empty | Empty | Empty |
Non | |
| Empty |
Non | |
Empty | Empty | |
In addition, trim() is a VB function that removes the spaces before and after the input string; % is a multi-character wildcard in the SQL language (_ is a single-character wildcard), so it can be seen that %"&trim()&"% is very useful for the search box The keywords entered in are matched from left to right respectively; in SQL language, and connection is used to indicate that there is an "AND" relationship between non-empty conditions.
Let's take a look at the progressive method. Compared with the enumeration method, they only differ in the core part:
'The search core of the progressive method is to judge whether the condition is empty or not. If it is not empty, add it to the search condition
sql="select * from address where"
if Name<>"" then
sql=sql&" Name like '%"&Name&"%' "
flag=1
end if
if Tel<>"" and flag=1 then
sql=sql&" and Tel like '%"&Tel&"% '"
flag=1
elseif Tel<>"" then
sql=sql&" Tel like '%"&Tel&"%'"
flag=1
end if
if Company<>"" and flag=1 then
sql=sql&" and Company like ' %"&Company&"%'"
flag=1
elseif Company <>"" then
sql=sql&" Company like '%"&Company&"%'"
flag=1
end if
if flag=0 then
sql="select * from address order by ID asc"
end if
rs.open sql,conn,1,1
The progression method is a smart algorithm, as can be seen from the length of the statements alone. The difficulty and essence of this algorithm lies in flag and &. First of all, you should know that & is a string concatenation symbol in SQL, which splices the characters around the symbol together. Back to the program again, when Name is not empty, sql="select * from address where Name like '%"&Name&"%' "and flag=1; next, when Name is not empty and Tel is not empty, that is Tel<>"" and flag=1, sql="select * from address where Name like '%"&Name&"%' and Tel like '%"&Tel&"%' "while flag=1, otherwise when Name is empty Tel Not empty, sql="select * from address where Tel like '%"&Tel&"%' "and flag=1; and so on, it can be extended to search with n conditions. Of course, when the conditions are all empty, that is, flag=0 will select all items in all tables.
3． verify:
At this point, a search engine is established. Here are some usage examples:
Name: Zhang | Phone: | School: | Search button |
The search results are:
Name: Zhang San Tel: 33333333 Unit: Department of Computer Science, University of Electronic Science and Technology of China
Name: | Phone: | School: College | Search Button |
The search results are:
Name: Zhang San Tel: 33333333 Unit: Department of Computer Science, University of Electronic Science and Technology of China
Name Li Si Tel: 44444444 Unit: Department of Biology, Sichuan University
Name: Wang Er Tel: 22222222 Unit: Department of Architecture, Southwest Jiaotong University
Name: | Phone: 4444 | School: Sichuan | Search Button |
The search results are:
Name Li Si Phone: 44444444 Unit: Department of Biology, Sichuan University
Name: | Phone: | School: Pay% Big | search button |
The search results are:
Name: Wang Er Phone: 22222222 Unit: Department of Architecture, Southwest Jiaotong University
4. improve:
In fact, this engine still has some flaws. The problem mainly lies in the wildcard character %. On the one hand, this is because people are accustomed to using * as a wildcard character. On the other hand, if % appears in a hyperlink, % will be "eaten" when obtained through request, as follows:
--test.htm--
…
<a href=test.asp?content=test%the%sign>click here</a>
…
--test.asp--
<%
content=request("content")
response.write content
%>
When browsing test.htm in IE, click the hyperlink and it will display as:
testthesign
It can be seen that % is directly ignored by the hyperlink. How can we solve this problem? It's very simple, we do a little trick - subvert the beam and change the pillar.
Add the following code before the search core:
Name=replace(Name,"*","%")
Tel=replace(Tel,"*","%")
Company=replace(Company,"*","%")
Add the following code after the search core:
Name=replace(Name,"%","*")
Tel=replace(Tel,"%","*")
Company=replace(Company,"%","*")
Let's analyze these statements. replace() is a string replacement function in VB. replace(Name,"*","%") is to replace all * in Name with %. In other words, we replace all occurrences of * in the three conditions with %, so that the wildcard characters in the first three sentences are changed to *. The next three sentences can prevent % from being "eaten". All problems will be solved.
Name: | Phone: | School: Pay% Big | search button |
The search results are:
Name: Wang Er Tel: 22222222 Unit: Department of Architecture, Southwest Jiaotong University
Change the above statement again and replace * with spaces. Wouldn't it become the search engine that we commonly use spaces to separate search conditions in Google and Baidu?
Running environment: IIS
Script language: VBScript
Database: Access/SQL Server
Database language: SQL
1. summary:
Whether in dynamic websites such as forums, news systems, or download systems, you often see search functions: search for posts, search for users, search for software (in short, search for keywords), etc. This article introduces how to build an efficient Practical, ASP-based site multi-value search.
This article is dealing with "multi-condition fuzzy matching search". After understanding multiple conditions, single-condition search is just a piece of cake. Generally speaking, there are two methods for multi-condition search: enumeration method and progressive method. When there are not too many search conditions ( n <=3), the enumeration method can be used. The frequency of statements is 2 to the nth power, growing exponentially. n is the condition number. Obviously, when the number of conditions increases, the progressive method should be adopted regardless of the efficiency or realizability of the program. The statement frequency is n and grows linearly. It should be pointed out that the idea of the enumeration method is very simple. It determines whether the conditions are empty one by one, and then searches according to the non-empty conditions. At the same time, the truth table technology can be used to deal with situations with extremely many conditions (I believe no one will do this kind of thing) , when there are 4 conditions, you have to write 16 groups of statements); the progressive method is more ingenious, focusing on understanding. The first is the use of flags (flag), and the second is the clever use of string connections in SQL symbol&. The following uses examples to explain the establishment of the engine.
2. Example:
We build an address book query engine. The database name is addressbook.mdb, the table name is address, and the fields are as follows:
ID | Name | Tel | School |
1 | Zhang San | 33333333 | Department of Computer Science, University of Electronic Science and Technology of China |
2 | Li Si | 44444444 | Department of Biology, Sichuan University |
3 | Wang Er | 22222222 | Department of Architecture, Southwest Jiaotong University |
… | … | … | … |
The web search interface is as follows:
Name: | Phone: | School: | Search button |
The source program using enumeration method is as follows:
<%@ CODEPAGE = "936" %>
'Connect to database
<%
dim conn
dim DBOath
dim rs
dim sql
Set conn=Server.CreateObject("ADODB.Connection")
DBPath = Server.MapPath ("addressbook.mdb")
conn.Open "driver={Microsoft Access Driver (*.mdb)};dbq=" & DBPath
Set rs=Server.CreateObject("ADODB.Recordset")
'Get the name and phone number from the Web page , the value of the school
dim Name
dim Tel
dim School
Name=request("Name")
Tel=request("Tel")
School=request("School")
'The search core of the enumeration method, because there are three conditions, so it must be written 8 sets of If judgment statements
if trim(Name)="" and trim(Tel)="" and trim(School)="" then
sql="select * from address order by ID asc"
end if
if trim(Name)= "" and trim(Tel)="" and trim(School)<>"" then
sql="select * from address where School like '%"&trim(School)&"%' order by ID asc"
end if
if trim (Name)="" and trim(Tel)<>"" and trim(School)="" then
sql="select * from address where Tel like '%"&trim(Tel)&"%' order by ID asc"
end if
if trim(Name)="" and trim(Tel)<>"" and trim(School)<>"" then
sql="select * from address where Tel like '%"&trim(Tel)&"%' and School like '%"&trim(School)&"%' order by ID asc"
end if
if trim(Name)<>"" and trim(Tel)="" and trim(School)="" then
sql=" select * from address where Name like '%"&trim(Name)&"%' order by ID asc"
end if
if trim(Name)<>"" and trim(Tel)="" and trim(School)<>" " then
sql="select * from address where Name like '%"&trim(Name)&"%' and School like '%"&trim(School)&"%' order by ID asc"
end if
if trim(Name)< >"" and trim(Tel)<>"" and trim(School)="" then
sql="select * from address where Name like '%"&trim(Name)&"%' and Tel like '%"&trim( Tel)&"%' order by ID asc"
end if
if trim(Name)<>"" and trim(Tel)<>"" and trim(School)<>"" then
sql="select * from address where Name like '%"&trim(Name)&"%' and Tel like '%"&trim(Tel)&"%' and School like '%"&trim(School)&"%' order by ID asc"
end if
rs.open sql,conn,1,1
'Display search results
if rs.eof and rs.bof then
response.write "There is no record in the current address book"
else
do while not rs.eof
response.write "Name:"&rs("Name" )&"Telephone:"&rs("Tel")&"School:"&rs("School")&"<br>"
rs.movenext
loop
end if
'Disconnect the database
set rs=nothing
conn.close
set conn=nothing
%>
When understanding the above program, focus on the core part. The 8 groups of statements correspond to the 8 states in the 3 search boxes one by one.
Name | Tel | School |
| |
Empty | | | Empty | Empty | Empty |
Non | |
| Empty |
Non | |
Empty | Empty | |
In addition, trim() is a VB function that removes the spaces before and after the input string; % is a multi-character wildcard in the SQL language (_ is a single-character wildcard), so it can be seen that %"&trim()&"% is very useful for the search box The keywords entered in are matched from left to right respectively; in SQL language, and connection is used to indicate that there is an "AND" relationship between non-empty conditions.
Let's take a look at the progressive method. Compared with the enumeration method, they only differ in the core part:
'The search core of the progressive method is to judge whether the condition is empty or not. If it is not empty, add it to the search condition
sql="select * from address where"
if Name<>"" then
sql=sql&" Name like '%"&Name&"%' "
flag=1
end if
if Tel<>"" and flag=1 then
sql=sql&" and Tel like '%"&Tel&"% '"
flag=1
elseif Tel<>"" then
sql=sql&" Tel like '%"&Tel&"%'"
flag=1
end if
if Company<>"" and flag=1 then
sql=sql&" and Company like ' %"&Company&"%'"
flag=1
elseif Company <>"" then
sql=sql&" Company like '%"&Company&"%'"
flag=1
end if
if flag=0 then
sql="select * from address order by ID asc"
end if
rs.open sql,conn,1,1
The progression method is a smart algorithm, as can be seen from the length of the statements alone. The difficulty and essence of this algorithm lies in flag and &. First of all, you should know that & is a string concatenation symbol in SQL, which splices the characters around the symbol together. Back to the program again, when Name is not empty, sql="select * from address where Name like '%"&Name&"%' "and flag=1; next, when Name is not empty and Tel is not empty, that is Tel<>"" and flag=1, sql="select * from address where Name like '%"&Name&"%' and Tel like '%"&Tel&"%' "while flag=1, otherwise when Name is empty Tel Not empty, sql="select * from address where Tel like '%"&Tel&"%' "and flag=1; and so on, it can be extended to search with n conditions. Of course, when the conditions are all empty, that is, flag=0 will select all items in all tables.
3． verify:
At this point, a search engine is established. Here are some usage examples:
Name: Zhang | Phone: | School: | Search button |
The search results are:
Name: Zhang San Tel: 33333333 Unit: Department of Computer Science, University of Electronic Science and Technology of China
Name: | Phone: | School: College | Search Button |
The search results are:
Name: Zhang San Tel: 33333333 Unit: Department of Computer Science, University of Electronic Science and Technology of China
Name Li Si Tel: 44444444 Unit: Department of Biology, Sichuan University
Name: Wang Er Tel: 22222222 Unit: Department of Architecture, Southwest Jiaotong University
Name: | Phone: 4444 | School: Sichuan | Search Button |
The search results are:
Name Li Si Phone: 44444444 Unit: Department of Biology, Sichuan University
Name: | Phone: | School: Pay% Big | search button |
The search results are:
Name: Wang Er Phone: 22222222 Unit: Department of Architecture, Southwest Jiaotong University
4. improve:
In fact, this engine still has some flaws. The problem mainly lies in the wildcard character %. On the one hand, this is because people are accustomed to using * as a wildcard character. On the other hand, if % appears in a hyperlink, % will be "eaten" when obtained through request, as follows:
--test.htm--
…
<a href=test.asp?content=test%the%sign>click here</a>
…
--test.asp--
<%
content=request("content")
response.write content
%>
When browsing test.htm in IE, click the hyperlink and it will display as:
testthesign
It can be seen that % is directly ignored by the hyperlink. How can we solve this problem? It's very simple, we do a little trick - subvert the beam and change the pillar.
Add the following code before the search core:
Name=replace(Name,"*","%")
Tel=replace(Tel,"*","%")
Company=replace(Company,"*","%")
Add the following code after the search core:
Name=replace(Name,"%","*")
Tel=replace(Tel,"%","*")
Company=replace(Company,"%","*")
Let's analyze these statements. replace() is a string replacement function in VB. replace(Name,"*","%") is to replace all * in Name with %. In other words, we replace all occurrences of * in the three conditions with %, so that the wildcard characters in the first three sentences are changed to *. The next three sentences can prevent % from being "eaten". All problems will be solved.
Name: | Phone: | School: Pay% Big | search button |
The search results are:
Name: Wang Er Tel: 22222222 Unit: Department of Architecture, Southwest Jiaotong University
Change the above statement again and replace * with spaces. Wouldn't it become the search engine that we commonly use spaces to separate search conditions in Google and Baidu?
