Running environment: IIS
Script language: VBScript
Database: Access/SQL Server
Database language: SQL
1. summary:
Regardless of dynamic websites such as the forum, the news system, or the download system, everyone often sees the search function: search posts, search users, search software (in short search keywords), etc. This article introduces how to establish an efficient efficiency Practical, search for multi -value search based on ASP.
This article is facing multiple conditions and vague matching search, which understands multi -condition, and a single condition search is not a piece of cake. Generally speaking, there are two ways to search for multiple conditions: enumeration and progressive methods. The search condition is not too long (n <= 3), and the enumeration method can be used. The frequency of the sentence is 2. Obviously, when conditions are increased, the progress method should be adopted in both procedures and can be achieved. It should be pointed out that the enumeration method is very simple, whether the condition of the judgment is empty, and then searched according to non -empty conditions. At the same time, you can use the true value table technology to deal with the conditions with many conditions (I believe no one goes to do this kind of thing In 4 conditions, we already have to write 16 sets of sentences); the ideological method of the progress method is relatively clever, focusing on understanding, and it is a coincidence that one is to use the logo position (FLAG), and the second is to use the string in SQL string connection connection. symbol&. Let's explain the establishment of the engine by example.
2. Example:
We have established a address book query engine. The database is named addressbook.mdb, the table is named address, and the field is as follows:
Id name tel school
1 three 333333333 University of Electronic Science and Technology University of Electronic Science and Technology
2 Li 444444444 Sichuan University Biology Department
3 Wang 222222222 Southwest Jiaotong University Department of Architecture
… ...… ...
The web search interface is as follows:
Name: Tel: School: Search Button
The source procedure of the enumeration method is as follows:
< %@ CodePage = 936 %>
'Connection 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.oldSet)
'Get the name, telephone, school value from the web page
dim name
Dim Tel
DIM SCHOL
Name = request (name)
Tel = request (tel)
School = request (school)
'The core of the enumeration method, because there are 3 conditions, you need to write 8 groups 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 Ad SC
end if
RS.Oopen SQL, CONN, 1,1
'Show search results
if rs.eof and rs.bof then
response.write does not record in the address book currently
else
do while not rs.eof
Response.write Name: & RS (name) & Tel: & RS (Tel) & School: & RS (SCHOOL) & <br>
rs.movenext
loop
end if
'Break the database
set rs = Nothing
conn.close
set conn = nothing
%>
When understanding the above procedures, focus on the core part, 8 sets of sentences one by one corresponding to the 8 states of the 3 search boxes one by one
Name tel school
Empty
Empty and empty
Empty and empty
Empty non -empty non -empty
Non -empty
Non -empty non -empty
Non -empty non -empty
Non -empty non -empty non -empty
In addition, trim () is a function of VB, remove the space before and after the input string;%is the multi -character passage of the SQL language (_ is a single character pass), which shows that%& trim () &%input in the search box input The keywords are matched to the left and right; the SQL language is used to explain the relationship between non -empty conditions in the SQL language.
Let's take a look at the progress method. Compared with the enumeration method, they only have the core parts:
'The core of the search method, the judgment condition is empty, and the non -empty will 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 The
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.Oopen SQL, CONN, 1,1
The progress method is a wise algorithm, which can be seen alone from the length of the statement. The difficulty and essence of this algorithm lies on Flag and &. First of all, you should be clear & in SQL is a string connection symbol, stitching the characters around the symbol together. Back to the program, when the name is not empty, SQL = Select * from Address where name like '%& name &%' simultaneously flag = 1; then when the name is not empty and the Tel is not empty, that is, Tel <> and and of When flag = 1, SQL = Select * From Address where name like '%& name &%' and tel like '%& tel &%' at the same time, otherwise when the name is empty, SQL = SELECT * From Address w here tel LIKE '%& Tel &%' at the same time flag = 1; and so on. Of course, when the conditions are empty, that is, Flag = 0 will select all the items in the table.
3. verify:
At this point, a search engine is established. Here are some examples of use:
Name: Zhang Tel: School: Search Button
The search results are:
Name: Zhang San Tel: 33333333 Unit: Computer Department of the University of Electronic Science and Technology University
Name: Tel: School: University Search Button
The search results are:
Name: Zhang San Tel: 33333333 Unit: Computer Department of the University of Electronic Science and Technology University
Name Li Si Tel: 44444444 Unit: Sichuan University Biology Department
Name: Wang Er Tel: 22222222 Unit: Department of Architecture, Southwest Jiaotong University
Name: Tel: 4444 School: Sichuan Search button
The search results are:
Name Li Si Tel: 44444444 Unit: Sichuan University Biology Department
Name: Telephone: School: Pay%search button
The search results are:
Name: Wang Er Tel: 22222222 Unit: Department of Architecture, Southwest Jiaotong University
4. improve:
In fact, this engine still has some defects. The problem is mainly through the matching%. On the one hand, it is because people usually use*as a channel, and on the other hand, if it appears in the hyperlink, it will be eaten when obtaining it through the request, as follows:
--Test.htm--
Mowing
<A HREF = TEST.ASP? Content = TEST%The%Sign> Click Here </a>
Mowing
--Test.asp--
<%
content = request (content)
response.write content
%>
When you browse test.htm in IE, click the hyperlink, and it is displayed as:
testthesign
It can be seen that%is directly ignored by the hyperlink. How can we solve this problem? It's very simple, let's do a little hand and feet-stealing beams and changing columns.
Add the following code before the search core:
Name = replace (name,*,%)
Tel = replace (tel,*,%)
Company = Replace (Company,*,%)
Add the following code to the core of the search:
Name = Replace (name,%,*)
Tel = replace (tel,%,*)
Company = Replace (Company,%,*)
Let's analyze these sentences. Replace () is a string replacement function in VB. Replace (name,*,%) is to replace all*in name to%. In other words, we replaced all of the three conditions of the three conditions to%, so that the first three sentences will be changed to*. The last 3 sentences can prevent%from being eaten. All the problems will be solved.
Name: Telephone: School: Pay%search button
The search results are:
Name: Wang Er Tel: 22222222 Unit: Department of Architecture, Southwest Jiaotong University
Change the above statements and replace*with the empty grid. Isn't it a search engine that uses spaces that we often use in Google and BAIDU to separate the search conditions?
Supplementary function: If we want to query the title and content in the same table, but want to arrange them in the order of these two, such as finding out the first display that meets the title, how can it be realized behind the title? Intersection
SQL = SELECT * From PRODUCT WHERE TITE LIKE '%& Keyword &%'
SQL = SQL + Union Select * From Product WHERE Content Like '%& Keyword &%' Order by ID Desc
Add one in the middle of the two SQL statements, and Union can be inquiring in combination, but the column must be the same, and there are also one.
We will use Union's joint queries, which will achieve the above functions.
Analysis: The database query will be queried according to the data with the TITLE, and then the realization of the data query of the CONTENT is therefore divided.