Running environment: IIS scripting language: VBScript database: Access/sql Server database language: SQL 1. Profile:
Whether it is in the forum, or the news system, or download the system, such as dynamic Web sites, we often see search function: Search posts, search users, search software (in short search keywords), etc., this article is about how to build an efficient and practical, based on the ASP site multivalued search.
This article is facing the "multi-conditional fuzzy matching search", understanding the multiple conditions, a single condition search is a piece of cake. Generally speaking, there are two methods for multiple criteria search:
Enumeration MethodAnd
Progressive Method。 Search conditions are not too long (
N<=3), you can use the enumeration method, which has a statement frequency of 2
NThe second side, the exponential growth,
NAs the number of conditions. Obviously, when the conditions are increased, the progressive method should be adopted for both the efficiency and the realization of the program, and the statement frequency is
N, into linear growth. It should be pointed out that the enumeration method is very simple, one judge whether the condition is empty, and then search by the non-empty condition, at the same time can use the truth table technology to deal with the conditions of a lot of situations (believe that nobody to do this kind of thing, 4 when the condition has to write 16 groups of statements); Its skillful in one is the use of the logo bit (flag), the second is the magical character of SQL string Connector &. Here is an example to illustrate the engine building.
2. Instance:
We set up a directory query engine, the database named Addressbook.mdb, table name address, the fields are as follows:
IDNameTelSchool1 Zhang 333,333,333 Computer Department 2 Li 4,444,444,444 Sichuan University Biology Department 3 Wang 222,222,222 Southwest Jiaotong Construction Department ...
The web search interface is as follows:
Name: Tel: School: Search button
Using
Enumeration MethodThe source program is as follows: <%@ CODEPAGE = "936"%> ' connection database <%dim conn Dim dboathdim Rsdim SQL Set conn=server.createobject ("ADODB. Connection ") DBPath = Server.MapPath (" Addressbook.mdb ") Conn. Open "Driver={microsoft Access driver (*.mdb)};d bq=" & Dbpathset rs=server.createobject ("ADODB. Recordset ")" Gets the name, phone, school value from the Web page Dim Namedim Teldim schoolname=request ("Name") tel=request ("Tel") school=request (" School ")" enumeration method of
Search Core, because there are 3 conditions to write 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) <> "" " n 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) <&G t; "" and Trim (School) <> "then sql=" select * From address where Tel like '% ' &trim (tel) & amp; " % ' 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 ' Endif 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 ' &N Bsp 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 Li Ke '% ' &trim (School) & "% ' ORDER by ID ASC" End Ifrs.open sql,conn,1,1 ' show search results if rs.eof and Rs.bof THEN&NBSP;&NB sp; response.write "There is no record in the current address book" else do, not rs.eof& nbsp; ResponSe.write "Name:" &rs ("name") & "Tel:" &rs ("tel") & "School:" &rs ("School") & "<br>" rs.movenext LoopEnd If ' disconnects the database set rs=nothing conn.close Set conn=nothing%>
When you understand the above program, focus on the core, 8 group statement one by one corresponds to 3 of the 8 states in the search box
Nametelschool empty space air empty empty non-empty empty air non-empty air empty
