After several studies have written the following code, there is a need to refer to the following
ASP Multi-Criteria Query Code Instance 2
ASP's multiple conditions in line with the query statement, write their own, take out to share, absolutely original. Please advise more!
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 conditional search: Enumeration method and progressive method. Search conditions are not too much (n<=3), you can use the enumeration method, its statement frequency of 2 of n-th, exponential growth, N for the number of conditions. Obviously, when the condition increases, whether from the efficiency of the program or the realization of the consideration should adopt the progressive method, the sentence frequency is n, 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:
ID Name Tel School
1 Computer Department of 333,333,333
2 Li 4,444,444,444 Department of Biology, Sichuan University
3 Wang 222,222,222 Architectural Department of Southwest Jiaotong University
... ... ... ...
The web search interface is as follows:
Name: Tel: School: Search button
The source program using the enumeration method is as follows:
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
Name Tel School
Empty space
Empty and not empty
Empty air and not emptiness
Empty non-empty
Not empty
Non-empty air
Non-empty or empty
Non-empty non-empty non-empty
In addition, the trim () is a function of VB, remove the spaces before and after the input string;% is the wildcard character in the SQL language (_ is the word wildcard), this shows that% "&trim () &" The keyword entered in the search box is left to right. The SQL language uses an and connection to describe the relationship between non-null conditions.
Let's look at the method of progression, which is only a core part of the enumeration method:
' The search core of the progressive method is to determine whether the condition is null and void, then add it to the search condition.
The progressive method is a sensible algorithm, the length of the statement can be seen. The difficulty and the essence of this algorithm are in flag and &. First you should be aware that & in SQL is a string connector that joins the characters around the symbol. Then go back to the program, when name is not empty sql= "SELECT * From address where name like '% ' &Name& '% '" 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& '% ' "fla G=1, otherwise, when name is empty, tel is not empty, sql= "select * FROM address where Tel like '%" &Tel& "%" is flag=1, and so on, and so on to the N-condition search. Of course, when all the conditions are empty, that is, Flag=0 selects all the items in all tables.
3. Verify:
So far, a search engine has been built up. Here are some examples of usage:
Name: Zhang Tel: school: Search button
Search results are:
Name: John Tel: 33333333 Unit: Computer Department of Science and Engineering
Name: Tel: School: University Search button
Search results are:
Name: John Tel: 33333333 Unit: Computer Department of Science and Engineering
Name Dick Tel: 44444444 Unit: Department of Biology, Sichuan University
Name: Wang Two Tel: 22222222 Unit: Department of Architecture, Southwest Jiaotong University
Name: Tel: 4444 School: Sichuan Search button
Search results are:
Name Dick Tel: 44444444 Unit: Department of Biology, Sichuan University
Name: Tel: School: Submit the big search button
Search results are:
Name: Wang Two Tel: 22222222 Unit: Department of Architecture, Southwest Jiaotong University
4. Improved:
In fact, there are some flaws in the engine, the main problem is the wildcard%. On the one hand because people are accustomed to * as a wildcard, on the other hand, if it appears in the hyperlink, through request to get the% will be "eat" out, as follows:
--test.asp--
<%
Content=request ("content")
Response.Write Content
%>
When browsing test.htm in IE, click on the hyperlink to display:
Testthesign
The visible% is directly ignored by the hyperlink. How can we solve this problem? Very simple, we do a little bit of hands and feet-cynical.
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 the * in the name of%. That is to say, we replace all occurrences of the 3 conditions with%, so that the first 3 sentences change the wildcard character to *. Then 3 sentences can prevent the% from being "eaten". All the problems will be solved.
Name: Tel: School: Submit the big search button
Search results are:
Name: Wang Two Tel: 22222222 Unit: Department of Architecture, Southwest Jiaotong University
The above statement to change, the * with a space instead, not to become our Google, Baidu commonly used in the space to separate search terms of the searching engine?
Supplemental function: If we want to implement the query of the same table in the title and content, but want to order in these two orders, such as found with the title of the first display and the content of the match is displayed after the title of how to achieve it?
Sql= "SELECT * from product where title like '% ' &keyword& '% '"
Sql=sql + "union SELECT * from product where content like '% ' &keyword& '% ' ORDER by id DESC"
Two SQL statements with one in the middle, Union can be combined query, but the column must be the same, as well as sorting criteria is also one.
We will use union query, which will achieve the above functions.
Analysis: The database query will be based on the data and the title of the query and then the implementation of data and content query so there are successively points.
Start building with 50+ products and up to 12 months usage for Elastic Compute Service
1 on 1 presale consultation
24/7 Technical Support 6 Free Tickets per Quarter Faster Response
Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.
A comprehensive suite of global cloud computing services to power your business
Payment Methods We Support
