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

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">](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">](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 |

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