[Resolvido] Busca diferente usando a função SOUNDEX

[jrcardozo 4]

Que tal oferecer em seu site uma busca que varre todos os módulos do site exibindo tudo em um único resultado?

Além disso sugere ao usuário os links prováveis de acordo com as palavras digitadas.

Essa primeira versão fiz para o banco MySql, mas pode ser usada em outros bancos bastando pequenas modificações no SQL.

 

A partir desta estrutura é possível criar as tags mais populares

 

A inclusão desta funcionlidade se resume a uma pequena alteração nas páginas de cadastro e edição dos itens administráveis do seu site.

 

Na url de exemplo pesquise por "carro" ou "IPVA" ou "carro ipva" ou "horrario carro ipva"(perceba que a palavra Horário está escrita errada de propósito) e veja a diferença na exibição da consulta

URL Exemplo: http://allmarketweb.com/jonathandj/exemplos/busca/busca.asp

 

Caso queira baixar o exemplo da Url acima acesse http://www.allmarketweb.com/jonathandj/cod...mp;idCategoria= e baixe os arquivos

 

-- MySQL Administrator dump 1.4
--
-- ------------------------------------------------------
-- Server version 5.0.27-community-nt


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;


--
-- Create schema db_jonathandj
--

CREATE DATABASE /*!32312 IF NOT EXISTS*/ db_jonathandj;
USE db_jonathandj;

--
-- Table structure for table `db_jonathandj`.`jdj_modulo`
--

DROP TABLE IF EXISTS `jdj_modulo`;
CREATE TABLE `jdj_modulo` (
 `idModulo` int(10) unsigned NOT NULL auto_increment,
 `tabela` varchar(45) NOT NULL default '',
 `id` varchar(45) NOT NULL default '',
 `pagina` varchar(45) NOT NULL default '',
 `titulo` varchar(45) NOT NULL default '',
 `modulo` varchar(45) NOT NULL default '',
 PRIMARY KEY  (`idModulo`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `db_jonathandj`.`jdj_modulo`
--

/*!40000 ALTER TABLE `jdj_modulo` DISABLE KEYS */;
INSERT INTO `jdj_modulo` (`idModulo`,`tabela`,`id`,`pagina`,`titulo`,`modulo`) VALUES 
(1,'noticia','idNoticia','noticia.asp?idNoticia=','tituloNoticia','Notícias'),
(2,'produto','idProduto','produto.asp?idProduto=','nomeProduto','Produtos');
/*!40000 ALTER TABLE `jdj_modulo` ENABLE KEYS */;


--
-- Table structure for table `db_jonathandj`.`jdj_palavra`
--

DROP TABLE IF EXISTS `jdj_palavra`;
CREATE TABLE `jdj_palavra` (
 `idPalavra` int(10) unsigned NOT NULL auto_increment,
 `palavra` varchar(45) NOT NULL default '',
 PRIMARY KEY  (`idPalavra`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `db_jonathandj`.`jdj_palavra`
--

/*!40000 ALTER TABLE `jdj_palavra` DISABLE KEYS */;
/*!40000 ALTER TABLE `jdj_palavra` ENABLE KEYS */;


--
-- Table structure for table `db_jonathandj`.`jdj_palavramodulo`
--

DROP TABLE IF EXISTS `jdj_palavramodulo`;
CREATE TABLE `jdj_palavramodulo` (
 `idPalavraModulo` int(10) unsigned NOT NULL auto_increment,
 `idModulo` int(10) unsigned NOT NULL default '0',
 `idPalavra` int(10) unsigned NOT NULL default '0',
 `idRegistro` int(10) unsigned NOT NULL default '0',
 PRIMARY KEY  (`idPalavraModulo`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `db_jonathandj`.`jdj_palavramodulo`
--

/*!40000 ALTER TABLE `jdj_palavramodulo` DISABLE KEYS */;
/*!40000 ALTER TABLE `jdj_palavramodulo` ENABLE KEYS */;


--
-- Table structure for table `db_jonathandj`.`noticia`
--

DROP TABLE IF EXISTS `noticia`;
CREATE TABLE `noticia` (
 `idNoticia` int(10) unsigned NOT NULL auto_increment,
 `tituloNoticia` varchar(45) NOT NULL default '',
 `noticia` varchar(500) NOT NULL default '',
 `tags` varchar(100) NOT NULL default '',
 `fonte` varchar(45) NOT NULL default '',
 PRIMARY KEY  (`idNoticia`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `db_jonathandj`.`noticia`
--

/*!40000 ALTER TABLE `noticia` DISABLE KEYS */;
/*!40000 ALTER TABLE `noticia` ENABLE KEYS */;


--
-- Table structure for table `db_jonathandj`.`produto`
--

DROP TABLE IF EXISTS `produto`;
CREATE TABLE `produto` (
 `idProduto` int(10) unsigned NOT NULL auto_increment,
 `tags` varchar(100) NOT NULL default '',
 `nomeProduto` varchar(45) NOT NULL default '',
 `descricao` varchar(500) NOT NULL default '',
 `valor` float NOT NULL default '0',
 PRIMARY KEY  (`idProduto`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `db_jonathandj`.`produto`
--

/*!40000 ALTER TABLE `produto` DISABLE KEYS */;
/*!40000 ALTER TABLE `produto` ENABLE KEYS */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;

conexao.asp

<%
Const BD_LOCAL_USER_ID = "root" 'nome de usuário do banco de dados
Const BD_LOCAL_PASSWORD = "root" 'senha do usuário do banco de dados
Const BD_LOCAL_INITIAL_CATALOG = "db_jonathandj" 'nome da base de dados
Const BD_LOCAL_DATA_SOURCE = "localhost" 'nome ou IP do seridor
Const BD_PROVIDER = "{MySQL ODBC 3.51 Driver}"'provider utilizado
dim conexao, stringConexao
stringConexao = "Driver="&BD_PROVIDER&";" & _
"UID=" & BD_LOCAL_USER_ID & ";" & _
"PWD=" & BD_LOCAL_PASSWORD & ";" & _
"Database=" & BD_LOCAL_INITIAL_CATALOG & ";" & _
"Server=" & BD_LOCAL_DATA_SOURCE &";" &_
"Option=35"
set conexao = Server.CreateObject("ADODB.Connection") 
conexao.open stringConexao%>

funcoes.asp

<%
Response.Charset = "utf-8"
'função para fechar objetos criados
sub fechaObj(ByRef obj)
 on error resume next
 if obj.status <>0 then
  obj.close
  set obj = nothing
 end if
 if err.number<>0 then
  set obj = nothing
 end if
end sub

'função para tratar as aspas e possiveis ataques de sql injection
function replaceString(str)
 str	  = str&""
 str	  = replace(str,"  "," ")
 str		 = replace(str,"'","'")
 str		 = replace(str,"""",""") 
 str		 = replace(str,"<","<")
 str		 = replace(str,">",">")
 str		 = replace(str,"\","\\")  
 replaceString = str
end function

function atualizaPalavraTabela(IDMODULO,id,tag)
 'IDMODULO = id da tabela do módulo
 'id	= id do registro do módulo
 'tag   = texto contendo as tags a serem gravadas
 dim palavra, arrayBusca, sql, rs, idPalavra, i
 'verifica se os ids são validos evitando erros
 if IDMODULO <> "" and id <> "" then
  'exclui todos os itens relacionados na tabela palavraModulo referente ao id que estamos trabalhando
  sql = ""&_
	 "DELETE "&_
	 "FROM jdj_palavraModulo "&_
	 "WHERE idRegistro = "&id&" AND idModulo = "&IDMODULO&";" 
  conexao.execute(sql)
  'trata o conjunto de palavras e forma-se um array para tratar um a um
  palavra   = replace(tag,","," ")
  palavra   = replace(palavra,"+"," ")
  arrayBusca  = split(palavra," ")
  'varre o array em busca de palavras existentes
  for i = 0 to uBound(arrayBusca)
   palavra = arrayBusca(i)
   sql = ""&_
	  "SELECT p.idPalavra "&_
	  "FROM jdj_palavra p "&_
	  "WHERE UPPER(p.palavra) = UPPER('"&palavra&"');"
   set rs = conexao.execute(sql)
   if rs.eof then
   'se a palavra não existe na tabela então gravamos
	sql = ""&_
	   "INSERT INTO jdj_palavra ("&_
	   "palavra "&_
	   ")VALUES("&_
	   "LOWER('"&palavra&"'));"
	conexao.execute(sql) 
	'recupera-se o idPalavra do registro que acabou de ser inserido
	sql = ""&_
	   "SELECT LAST_INSERT_ID(idPalavra) AS idPalavra "&_
	   "FROM jdj_palavra "&_
	   "ORDER BY idPalavra DESC "&_
	   "LIMIT 1;" 
	set rs = conexao.execute(sql)	  
   end if
   'armazena-se na variavel o idPalavra
   idPalavra = rs("idPalavra") 
   'gravamos os ids palavra, modulo e registro
   sql = ""&_
	  "INSERT INTO jdj_palavraModulo ("&_
	  "idPalavra, idModulo, idRegistro "&_
	  ")VALUES("&_
	  ""&idPalavra&","&IDMODULO&","&id&");"
   conexao.execute(sql)
  next 
  atualizaPalavraTabela = true
 else
  atualizaPalavraTabela = false
 end if 
end function
%>

busca.asp

<%
option explicit
%>
<!--#include file="include/conexao.asp"-->
<!--#include file="include/funcoes.asp"-->
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>Formulário de busca jonathandj</title>
</head>
<%
dim sql, palavra, rs, rsBusca, chave, i, sqlWhere, tabelaHtml, arrayBusca, idModulo, relevancia
chave = replaceString(Request.QueryString("chave"))
%>
<body>
<!--#include file="include/menu.asp"-->
<form action="busca.asp" method="get">
 <table>
  <tr>
   <td><input type="text" name="chave" id="chave"  value="<%=chave%>" /><input type="submit" name="submit" value="Procurar" /></td>
  </tr>
 </table>
</form>
<%
if len(chave)>0 then
 'preparamos a busca quebrando as palavras em array
 palavra = replace(chave,","," ")
 palavra = replace(palavra,"+"," ")
 palavra = replace(palavra,"("," ")
 palavra = replace(palavra,")"," ")
 arrayBusca = split(palavra," ")
 'montamos o critério da busca usando a função SOUNEX
 for i = 0 to uBound(arrayBusca)
  palavra = arrayBusca(i)
  if i = 0 then
   sqlWhere = " AND ("
  else
   sqlWhere = sqlWhere & " OR "
  end if
  sqlWhere = sqlWhere & " SOUNDEX(p.palavra) = SOUNDEX('"&palavra&"') " 
 next
 sqlWhere = sqlWhere & ")"
 'executa-se a consulta para pegarmos os dados do módulo
 sql = "SELECT DISTINCT m.idModulo, m.modulo, m.titulo, m.pagina, m.id, m.tabela, pm.idRegistro "&_
	"FROM jdj_modulo m "&_
	"INNER JOIN jdj_palavraModulo pm ON pm.idModulo = m.idModulo "&_
	"INNER JOIN jdj_palavra p ON p.idPalavra = pm.idPalavra "&_
	"WHERE 1=1 "&sqlWhere&" "&_
	"ORDER BY m.modulo;"	
 set rs = conexao.execute(sql)
 if not rs.eof then
  idModulo = ""
  'percorre cada registro do recorset
  do while not rs.eof 
   'executa nova busca para montar os links do resultado da busca
   sql = ""&_
	  "SELECT m."&rs("titulo")&" AS titulo, m."&rs("id")&" AS id "&_
	  "FROM "&rs("tabela")&" AS m "&_
	  "WHERE m."&rs("id")&" = "&rs("idRegistro")&" "&_
	  "ORDER BY m."&rs("titulo")&";"
   set rsBusca = conexao.execute(sql)
   if not rsBusca.eof then
	'verifica o id do módulo para não duplicar o cabeçalho
	if idModulo <> rs("idModulo") or idModulo = "" then
	 tabelaHtml =  tabelaHtml & ""&_
			"<table border=""0"" width=""100%"">"&vbNewLine&_
			" <tr>"&vbNewLine&_
			"  <td style=""background-color:#cccccc;"">"&vbNewLine&_
			"   "&rs("modulo")&""&vbNewLine&_
			"  </td>"&vbNewLine&_
			" </tr>"&vbNewLine
	end if
	'faz o loop para listar os registros de cada módulo	  
	do while not rsBusca.eof
	 tabelaHtml = tabelaHtml & ""&_
		   " <tr>"&vbNewLine&_
		   "  <td>"&vbNewLine&_
		   "   <a href="""&rs("pagina")&rsBusca("id")&""">"&rsBusca("titulo")&"</a>"&vbNewLine&_
		   "  </td>"&vbNewLine&_
		   " </tr>"&vbNewLine
	rsBusca.movenext
	'necessário para verificação do próximo cabeçalho a ser criado
	idModulo = rs("idModulo")
	loop
   end if
   fechaObj(rsBusca)
   rs.movenext
   'quando acabou a busca adiciona a tag de fechamento de table
   if not rs.eof then
	if idModulo <> rs("idModulo") then
	 tabelaHtml = tabelaHtml & "</table>"&vbNewLine
	end if
   end if
  loop
  'fecha a ultima tag table da busca
  tabelaHtml = tabelaHtml & "</table>"
  'inicia a exibição de palavras relevantes
  '**** Obs.: esta parte é opcional, caso deseje não precisa fazer
  'faz a mesma consulta realizada no inicio da página
  sql = "SELECT DISTINCT p.palavra "&_
	 "FROM jdj_modulo m "&_
	 "INNER JOIN jdj_palavraModulo pm ON pm.idModulo = m.idModulo "&_
	 "INNER JOIN jdj_palavra p ON p.idPalavra = pm.idPalavra "&_
	 "WHERE 1=1 "&sqlWhere&";"
  set rs = conexao.execute(sql)
  if not rs.eof then
   'se achou alguma palavra com a fonética semelhante então monta o resultado concatenando na variavel
   do while not rs.eof
	'verifica se a palavra do registro atual já não está na variavel	
	if inStr(uCase(chave),uCase(rs("palavra")))=0 then
	 relevancia = relevancia & rs("palavra") &" | "
	end if
	rs.movenext
   loop
   'se a variavel tiver algo então adiciona o codigo html a variavel
   if relevancia <> "" then
	relevancia =  "A busca utilizou as seguintes palavras de relevância para encontrar estes resultados: "&_
		   "<span style=""color:red;"">"&_
		   mid(relevancia,1,len(relevancia)-2)&_
		   "</span>"
	tabelaHtml =  ""&_
		   "<table border=""0"" width=""100%"">"&vbNewLine&_
		   " <tr>"&vbNewLine&_
		   "  <td style=""background-color:#FFCCCC;"" align=""left"">"&vbNewLine&_
		   "   "&relevancia&""&vbNewLine&_
		   "  </td>"&vbNewLine&_
		   " </tr>"&vbNewLine&_
		   "</table>"&vbNewLine&_
		   tabelaHtml
   end if		  
  end if
 else
  tabelaHtml = "<table border=""0"" width=""100%"">"&vbNewLine&_
		 " <tr>"&vbNewLine&_
		 "  <td style=""background-color:#FFCCCC;"" align=""center"">"&vbNewLine&_
		 "   Nenhum registro encontrado na busca por '<b>"&chave&"</b>'"&vbNewLine&_
		 "  </td>"&vbNewLine&_
		 " </tr>"&vbNewLine&_
		 "</table>"&vbNewLine
 end if
 fechaObj(rs)  
 'fechamos o objeto de conexão
 call fechaObj(conexao)
end if
'imprimimos o resultado da busca e da relevância9quando existir)
Response.Write(tabelaHtml)
%>
</body>
</html>

noriciaCadastro.asp

<%@LANGUAGE="VBSCRIPT" CODEPAGE="65001"%>
<%
option explicit
%>
<!--#include file="include/conexao.asp"-->
<!--#include file="include/funcoes.asp"-->
<%
'numero do ID do Módulo correspondente a tabela
Const ID_MODULO = 1 
dim tituloNoticia, noticia, fonte, tags, sql, rs, idNoticia
'resgata os valores dos inputs
tituloNoticia  = replaceString(Request.Form("tituloNoticia"))
noticia	 = replaceString(Request.Form("noticia"))
fonte	  = replaceString(Request.Form("fonte"))
tags	  = replaceString(Request.Form("tags"))
'verifica se houve o Post do formulário
if Request.ServerVariables("REQUEST_METHOD") = "POST" and len(tituloNoticia)>1 and len(noticia)>1 then
 'executa a gravação do novo registro
 sql = ""&_
	"INSERT INTO noticia ("&_
	"tituloNoticia, noticia, fonte, tags "&_
	")VALUES("&_
	"'"&tituloNoticia&"','"&noticia&"','"&fonte&"','"&tags&"');"
 conexao.execute(sql)
 'recupera o idNoticia que acabou de ser inserido
 sql = ""&_
	"SELECT LAST_INSERT_ID(idNoticia) AS idNoticia "&_
	"FROM noticia "&_
	"ORDER BY idNoticia DESC "&_
	"LIMIT 1;" 
 set rs = conexao.execute(sql)
 idNoticia  = rs("idNoticia")
 fechaObj(rs)
 'chamamos a função que fará o trabalho de criar as palavras de busca
 call atualizaPalavraTabela(ID_MODULO,idNoticia,tags)
 Response.Redirect("noticiaCadastro.asp")
end if
%>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>Cadastro de notícias jonathandj</title>
</head>

<body>
<!--#include file="include/menu.asp"-->
<form action="noticiaCadastro.asp" method="post">
 <table align="center">
  <tr>
   <td colspan="2" align="center">Cadastro de notícia</td>
  </tr>
  <tr>
   <td>Título: </td>
   <td><input type="text" name="tituloNoticia" id="tituloNoticia" /></td>
  </tr>
  <tr>
   <td>Tags de busca: </td>
   <td><input type="text" name="tags" id="tags" /></td>
  </tr>
  <tr>
   <td>Notícia: </td>
   <td><textarea name="noticia" id="noticia" cols="45" rows="6"></textarea></td>
  </tr>
  <tr>
   <td>Fonte: </td>
   <td><input type="text" name="fonte" id="fonte" /></td>
  </tr>
  <tr>
   <td colspan="2" align="center"><input type="submit" name="submit" value="Cadastrar" /></td>
  </tr>
 </table>
</form>
</body>
</html>
<%
fechaObj(conexao)
%>

- na tabela JDJ_MODULO cadastre os módulos do seu site para que a busca possa interagir, veja o que cada coluna representa abaixo:

 

|-----------------------------------------------------------------------------------------------------------------------|

|COLUNA |INFORMAÇÃO |

|-----------------------------------------------------------------------------------------------------------------------|

|tabela |Nome da tabela do seu módulo na base de dados |

|id |Nome da coluna que contém a chave primária do seu módulo |

|pagina |Nome da página que exibe as informações completas do seu módulo (veja exemplo que está no banco) |

|titulo |Nome da coluna do módulo que servirá para exibir o texto no link para o visitante do site |

|modulo |Nome amigável a exibir para o visitante do site que irá separar os módulos no resultado da busca |

|-----------------------------------------------------------------------------------------------------------------------|