当前位置： 移动技术网 > IT编程>开发语言>Asp > asp 多字段模糊搜索的函数

asp 多字段模糊搜索的函数

2018年05月29日  | 移动技术网IT编程  | 我要评论

比较简单直接的sql语句

recordset1.source = "select * from 表 where 字段 like '%" + replace(recordset1__mmcolparam, "'", "''") + "%' or 字段2 like '%" + replace(recordset1__mmcolparam, "'", "''") + "%' order by id desc"
recordset1.source = "select * from 表 where 字段 like '%" + replace(recordset1__mmcolparam, "'", "''") + "%' and 字段2 = 2 order by id desc"

下面是一些补充

函数名：keyword_sousuo
作 用：生成sql查询条件
参 数：table_field ------ 表的字段名（之间用逗号分开）
keyword ------ 搜索关键词（之间用空格分开）
返 回：sql查询条件

 核心代码

'================================================== 
'函数名：keyword_sousuo 
'作 用：生成sql查询条件 
'参 数：table_field ------ 表的字段名（之间用逗号分开） 
' keyword ------ 搜索关键词（之间用空格分开） 
'返 回：sql查询条件 
'================================================== 
function keyword_sousuo(byval table_field,byval keyword) 
 dim str01,str02,keywords,table_fields,i,j 
   
 table_fields=split(trim(table_field),",") 
 keywords=split(trim(keyword),",") 
  
 if table_field<>"" then 
  str01="("&table_fields(0)&" like '%"&keyword&"%'" 
  for j=0 to ubound(table_fields) 
  str01=str01&" or "&table_fields(j)&" like '%"&keyword&"%'" 
  next 
  str01=str01&")" 
 else 
  response.write("<script>alert('参数错误（不能为空）！')</script>") 
  response.end() 
 end if 

 '全角－－》半角　空格 
 'keyword=replace(keyword,"　"," ") 
 'while instr(keyword," ")>0 
 ' keyword=replace(keyword," "," ") 
 'wend 
 keywords=split(keyword," ") 
 if ubound(keywords)>0 then 
 for i=0 to ubound(keywords) 
  str02=str02&"("&table_fields(0)&" like '%"&keywords(i)&"%'" 
  for j=1 to ubound(table_fields) 
  str02=str02&" or "&table_fields(j)&" like '%"&keywords(i)&"%'" 
  next 
  str02=str02&")" 
 next 
 str02="("&replace(str02,")(",")and(")&")" 
 keyword_sousuo="(" & str01 & "or" & str02 & ")" 
 else 
 keyword_sousuo=str01 
 end if 
end function 
'================================================== 
'函数名：keyword_tag 
'作 用：将字符串里的关键词标记为红色 
'参 数：str ------ 字符串 
' keyword ------ 标记关键词（之间用空格分开） 
'返 回：字符串（html格式） 
'================================================== 
function keyword_tag(byval str,byval keyword) 
 dim keywords,str01,str02,i 
  '全角－－》半角　空格 
 'keyword=replace(keyword,"　"," ") 
 'while instr(keyword," ")>0 
 ' keyword=replace(keyword," "," ") 
 'wend 
 str01=replace(str,keyword,"<font color=""#ff0000"">"&keyword&"</font>") 
  keywords=split(keyword," ") 
 if ubound(keywords)>0 then 
  str02=str 
 for i=0 to ubound(keywords) 
 str02=replace(str02,keywords(i),"<font color=""#ff0000"">"&keywords(i)&"</font>") 
 next 
  keyword_tag=str02 
 else 
 keyword_tag=str01 
 end if 
end function 
以下是一个例子 http://www.zhouhaihe.com/blog/uploads/200604/recordread.asp 

<%@language="vbscript" codepage="936"%> 
<% 
 option explicit 
 dim conn,connstr,database 
 connstr="dbq="+server.mappath("record.mdb")+";defaultdir=;driver={microsoft access driver (*.mdb)};" 
 set conn=server.createobject("adodb.connection") 
 conn.open connstr 

dim n,rs,page,keyword,keywords,recordcount,pagecount,pos,i,sql_where 
n=50 
page=request("page") 
keyword=trim(request("keyword")) 
sql_where=keyword_sousuo("title,cz,name,time",keyword) 
set rs=server.createobject("adodb.recordset") 
rs.open "select * from record where "&sql_where&" order by id desc",conn,1,2 
if page=0 then 
 page=1 
 end if 
recordcount = 0 
do while not rs.eof 
 recordcount = recordcount +1 
 rs.movenext 
loop 
if not recordcount=0 then 
 rs.movefirst 
end if 
pagecount=recordcount/n 
pagecount=int(pagecount) 
if (recordcount mod n)>0 then 
 pagecount=pagecount +1 
end if 
%> 
<style type="text/css"> 
<!-- 
.style3 {color: #666666} 
--> 
</style> 
<table width="100%" border="0" cellspacing="0" cellpadding="0"> 
 <form id="form1" name="form1" method="post" action="?"><tr><td align="center"><input name="keyword" type="text" id="keyword" value="<%=keyword%>" size="30" /> 
   <input type="submit" name="submit" value="搜索" /> 
 <span class="style3">(关键词之间用空格分开) </span></td> 
 
 </tr></form> 
 <tr> 
 <td height="5"></td> 
</table> 

<table width="600" border="1" align="center" cellpadding="4"> 
 <tr align="center"> 
 <td width="131">标题</td> 
 <td width="131">动作</td> 
 <td width="131">用户名</td> 
 <td width="132">时间</td> 
 </tr> 
<%pos=0 
do while pos<(page-1)*n 
pos=pos+1 
rs.movenext 
loop 
i=0 
while not rs.eof and i<n 
i=i+1%> 
 <tr align="center"> 
 <td><%=keyword_tag(rs("title"),keyword)%></td> 
 <td><%=keyword_tag(rs("cz"),keyword)%></td> 
 <td><%=keyword_tag(rs("name"),keyword)%></td> 
 <td><%=keyword_tag(rs("time"),keyword)%></td> 
 </tr> 
 
 <%rs.movenext 
 wend 
 rs.close 
 set rs=nothing 
 conn.close 
 set conn=nothing 
 
'================================================== 
'函数名：keyword_sousuo 
'作 用：生成sql查询条件 
'参 数：table_field ------ 表的字段名（之间用逗号分开） 
' keyword ------ 搜索关键词（之间用空格分开） 
'返 回：sql查询条件 
'================================================== 
function keyword_sousuo(byval table_field,byval keyword) 
 dim str01,str02,keywords,table_fields,i,j 
   
 table_fields=split(trim(table_field),",") 
 keywords=split(trim(keyword),",") 
  
 if table_field<>"" then 
  str01="("&table_fields(0)&" like '%"&keyword&"%'" 
  for j=0 to ubound(table_fields) 
  str01=str01&" or "&table_fields(j)&" like '%"&keyword&"%'" 
  next 
  str01=str01&")" 
 else 
  response.write("<script>alert('参数错误（不能为空）！')</script>") 
  response.end() 
 end if 

 '全角－－》半角　空格 
 keyword=replace(keyword,"　"," ") 
 ' while instr(keyword," ")>0 
 ' keyword=replace(keyword," "," ") 
 'wend 
 keywords=split(keyword," ") 
 if ubound(keywords)>0 then 
 for i=0 to ubound(keywords) 
  str02=str02&"("&table_fields(0)&" like '%"&keywords(i)&"%'" 
  for j=1 to ubound(table_fields) 
  str02=str02&" or "&table_fields(j)&" like '%"&keywords(i)&"%'" 
  next 
  str02=str02&")" 
 next 
 str02="("&replace(str02,")(",")and(")&")" 
 keyword_sousuo="(" & str01 & "or" & str02 & ")" 
 else 
 keyword_sousuo=str01 
 end if 
end function 
'================================================== 
'函数名：keyword_tag 
'作 用：将字符串里的关键词标记为红色 
'参 数：str ------ 字符串 
' keyword ------ 标记关键词（之间用空格分开） 
'返 回：字符串（html格式） 
'================================================== 
function keyword_tag(byval str,byval keyword) 
 dim keywords,str01,str02,i 
  '全角－－》半角　空格 
 keyword=replace(keyword,"　"," ") 
 'while instr(keyword," ")>0 
 ' keyword=replace(keyword," "," ") 
 'wend 
 str01=replace(str,keyword,"<font color=""#ff0000"">"&keyword&"</font>") 
  keywords=split(keyword," ") 
 if ubound(keywords)>0 then 
  str02=str 
 for i=0 to ubound(keywords) 
 str02=replace(str02,keywords(i),"<font color=""#ff0000"">"&keywords(i)&"</font>") 
 next 
  keyword_tag=str02 
 else 
 keyword_tag=str01 
 end if 
end function 
dim y 
%> <tr align="center"> 
 <td colspan="4"> <%=recordcount%>个  <font color="#ff0000"><%=page%></font>/<%=pagecount%>页 
  <%=n%>个/页 转到： 
 <select name="select" onchange=window.open(this.options[selectedindex].value,'_self') > 
 <%y=1 
     do while y<pagecount+1%> 
 <option value=?page=<%=y%>&keyword=<%=server.urlencode(keyword)%> <%if cstr(page)=cstr(y) then response.write(" selected") end if%> >第<%=y%>页</option> 
 <%y=y+1 
 loop%> 
 </select></td> 
 </tr> 
</table>

文章就介绍到这了，希望大家以后多多支持移动技术网。

您可能感兴趣的文章:

如对本文有疑问， 点击进行留言回复！！

相关文章:

验证码：
最近更新的文章
大家感兴趣的文章
移动技术网