Consulta por relevancia MYSQL

[fermel 0]

Olá pessoal tudo bem?

 

Galera é o seguinte, tenho uma tabela chamada SITE e nessa tabela eu tenho as seguintes colunas:

 

 

site_id = campo onde armazeno o id do site

site_url = campo onde armazeno a url do site

site_title = campo onde armazeno o conteudo das tags TITLE do site

site_desc = campo onde armazeno o conteudo das Tags DESCRIPTION do site

site_keywords = campo onde armazeno o conteudo das Tags KEYWORDS do site

h1 = campo onde armazeno o conteudo das Tags H1 do site

h2 = campo onde armazeno o conteudo das Tags H2 do site

h3 = campo onde armazeno o conteudo das Tags H3 do site

h4 = campo onde armazeno o conteudo das Tags H4 do site

h5 = campo onde armazeno o conteudo das Tags H5 do site

p = campo onde armazeno o conteudo das Tags P do site

preciso fazer uma consulta por relevancia pois estou fazendo um mini buscador (parecido com o google)

então queria consulta a palavra que foi digitada no campo de pesquisa e varrer no banco de dados e na hora de exibir ele me trazer a linha que apareceu mais vezes as palavras que foi buscada....

tentei com o seguinte codigo mas não funciono, ele traz a consulta mas não por relevancia

	if ($buscar<>" "){ 
   //CONTA O NUMERO DE PALAVRAS 
   $pedacos = explode(" ",$buscar); 
   $numero = count($pedacos); 
  if ($numero==1) { 
   //SE SO HA UMA PALAVRA DE BUSCA SE ESTABELECE UMA INSTRUCAO COM LIKE 
   $sql = mysql_query("SELECT * FROM site WHERE site_url LIKE ('%$buscar%') OR site_title LIKE ('%$buscar%') OR site_desc LIKE ('%$buscar%') OR site_keywords LIKE ('%$buscar%')
	OR h1 LIKE ('%$buscar%') OR h2 LIKE ('%$buscar%') OR h3 LIKE ('%$buscar%') OR h4 LIKE ('%$buscar%')	OR h5 LIKE ('%$buscar%') OR p LIKE ('%$buscar%')"); 
  } elseif ($numero>1) { 
  //SE HA UMA FRASE SE UTILIZA O ALGORTIMO DE BUSCA AVANCADO DE MATCH AGAINST 
  //busca de frases com mais de uma palavra e um algoritmo especializado 
  $sql = mysql_query("SELECT *, MATCH (site_url, site_title, site_desc, site_keywords, h1,h2,h3,h4,h5,p) AGAINST ('$buscar' in boolean mode) AS rank FROM site WHERE MATCH (site_url, site_title, site_desc, site_keywords, h1,h2,h3,h4,h5,p) AGAINST ('$buscar' in boolean mode) ORDER BY rank");
}
}

 

 

 

[Prog 183]

Qual o modelo da tabela? Você esta usando indece fulltext?

Não entendi o por que das 2 consultas, no meu entendimento, apenas a segunda é suficiente caso os requisitos sejam atendidos.

 

Explique melhor a situação.

[Motta 645]

Tente isto, mas sei que MySql tem ferramentas melhores para relevãncia.

mas como disse o Prog tente definir melhor o problema.

...
order by       (sign((instr(h1,'%$buscar%')) +
                sign((instr(h2,'%$buscar%')) +
                sign((instr(h3,'%$buscar%')) +
                sign((instr(h4,'%$buscar%')) +
                sign((instr(h5,'%$buscar%')))

[fermel 0]

Então, as duas consultas é porque vi no site onde me mostro o tutorial como usa o against e o match que eles não funcionam se eu for buscar apenas com uma palavra entao verifico se o que o usuario ta pesquisando tem mais de uma palavra, se tiver ele usa o match e against para fazer a consulta no banco de dados....

 

uso o phpmyadmin do cpanel da hospedagem e não sei como colocar os campos como fulltext mas pelo que vi se eu colocar o in boolean mode ele não precisa....

 

queria trazer a consulta por relevancia para não ter que criar uma função para criar um outro campo na tabela para fazer a relevancia atraves de quantidades de clicks no link de resultado....

 

o que eu estou tentando montar é um mini buscador parecido com o google para um trabalho academico.... só ta faltando fazer essa consulta trazer o resultado de acordo com a quantidade de vezes que os termos buscados apareceram na consulta em cada linha....

 

 

alguem tem alguma ideia?

[Motta 645]

#3 ?!

[Prog 183]

Vai aí...

 

Seguinte, para usar MATCH e AGAINST tem que ser indice FULLTEXT, ok?

 

Taí um exemplo:

 

create table site(

site_id integer primary key,

site_url varchar(255),

site_title varchar(255),

site_desc TEXT,

site_keywords varchar(255)

) ENGINE MYISAM;

 

create fulltext index idx_busca on site (site_url, site_title, site_desc, site_keywords);

 

insert into site values

(1,'www.imasters.com.br','Portal iMasters','Portal de tecnologia','forum tecnologia gadgets banco de dados'),

(2,'www.uol.com.br','UOL Tecnologia','O maior conteúdo da Internet','tecnologia variedades entretenimento'),

(3,'www.shopto.net','ShopTo.net','A maior loja de games do Reino Unido','games ps3 xbox360 nintendo tecnologia');

 

Consulta com relavância e peso, vai funcionar independente da quantidade de palavras e você deve parametrizar conforme a sua necessidade.

Para o exemplo a seguir, os campos site_url e site_title tem peso maior que site_desc e site_keyworks, isto quer dizer que se o termo buscado for encontrado em algum dos campos de maior peso, estes virão antes dos demais.

 

SELECT *, 

(MATCH (site_url, site_title) AGAINST ('tecnologia forum portal' in boolean mode) * 2) +

(MATCH (site_desc, site_keywords) AGAINST ('tecnologia forum portal' in boolean mode) * 1 ) AS rank

FROM site

WHERE MATCH (site_url, site_title, site_desc, site_keywords) AGAINST ('tecnologia forum portal' in boolean mode)

ORDER BY rank DESC

 

Outro exemplo:

 

SELECT *, 

(MATCH (site_url, site_title)

AGAINST ('tecnologia forum portal' in boolean mode) * 2) +

(MATCH (site_desc, site_keywords)

AGAINST ('tecnologia forum portal' in boolean mode) * 1 ) AS rank

FROM site

WHERE MATCH (site_url, site_title, site_desc, site_keywords)

AGAINST ('tecnologia forum portal' in boolean mode)

ORDER BY rank DESC

 

Bons estudos.

[fermel 0]

puts meww show de bola... vou testar aqui e depois publico e posto aqui no fórum o meu trabalho final do buscador....

 

só uma perguntinha o que significa essa parte index idx_busca on site?

create fulltext index idx_busca on site (site_url, site_title, site_desc, site_keywords);

 

 

Valeu mesmo galera pela força.... se eu tirar nota boa vou pagar um sorvete pra todo mundo que ajudo hehe

[Prog 183]

O meu eu quero de flocos...

 

Essa parte do script é a criação do indice FullText, é baseado neste indice que a consulta SQL faça as buscas textuais.

Como você pode ver eu usei apenas alguns campos, para fazer uma demostração, mas você pode incluir todos os que forem necessários.

[fermel 0]

hehe

tamo chegando no resultado bom :D ahsushauahu

 

usei o exemplo que o PROG mandou e agora ele começo a fazer a consulta..... vou realizar mais alguns testes de ver como esta saindo os resultados....

 

percebi que nessa consulta ele procura pegar o termo exato digitado certo? para eu digitar uma frase e ele pesquisar tanto a frase exata quando cada palavra que tem na frase eu teria que concatenar essa busca com o LIKE?

[Prog 183]

Aí as possibilidades são bem diversificadas...

 

SELECT *,
   (MATCH (site_url, site_title) AGAINST ('tecnolog* -forum -conteúdo' in boolean mode) * 2) +
   (MATCH (site_desc, site_keywords) AGAINST ('tecnolog* -forum -conteúdo' in boolean mode) * 1 ) AS rank
FROM site
WHERE MATCH (site_url, site_title, site_desc, site_keywords) AGAINST ('tecnol* -forum -conteúdo' in boolean mode)
ORDER BY rank DESC

Para mais informações, leia aqui:

http://dev.mysql.com/doc/refman/5.0/en/fulltext-boolean.html

 

E aqui algumas dicas para configurar seu servidor para ajustar as suas necessidades ou melhorar a performance:

http://dev.mysql.com/doc/refman/5.0/en/fulltext-fine-tuning.html

[fermel 0]

Entendi só que no meu caso estou usando uma variavel $busca então ficaria dessa forma?

SELECT *,   (MATCH (site_url, site_title) AGAINST ('$busca*' in boolean mode) * 2) +   (MATCH (site_desc, site_keywords) AGAINST ('$busca*' in boolean mode) * 1 ) AS rankFROM siteWHERE MATCH (site_url, site_title, site_desc, site_keywords) AGAINST ('$busca*' in boolean mode)ORDER BY rank DESC

haha e o seu sorvete de flocos eu vo manda via sedex 10 ashuashuash

 

 

por enquanto olhem só como esta ficando o buscador que estou desenvolvendo...

 

 

 

http://www.vingadorbuscador.tk

[Motta 645]

Não achou imasters ... :)

[fermel 0]

Não acho pke só tem 2sites add no banco, estou com problemas para grava as páginas no bd..... Aquele exemplo que vc me passou na resposta #10 esta certo da forma que eu postei na resposta #11???????

[Motta 645]

Tava te pilhando (#13) ...

[Prog 183]

O ideal seria fazer um pré-processamento do conteúdo de $busca antes de colocar na consulta.

 

Vamos supor que o conteúdo de $busca seja " macaco velho ", a busca seria por " macaco velho* ". Acho que não é isso o que você deseja.

[fermel 0]

O ideal seria fazer um pré-processamento do conteúdo de $busca antes de colocar na consulta.

 

Vamos supor que o conteúdo de $busca seja " macaco velho ", a busca seria por " macaco velho* ". Acho que não é isso o que você deseja.

 

então nesse caso, eu quero tratar a busca como o google faz, ele busca a frase inteira mas traz todos os resultados que contenha qualquer parte da frase, no caso do seu exemplo ele iria buscar MACACO velho, ou tudo que tenha MACACO e/ou tudo que tenha VELHO. entendestes?

[Prog 183]

Tratar como o Google faz vai ser um pouco mais trabalhoso.

 

Dicionários são desejáveis para várias coisas. (Spellcheck, Lematização, Sinônimos).

Você também vai precisar usar algoritmos de aproximação (Fuzzy e/ou Levenshtein).

Provavelmente você também vai precisar usar Tokenização.

 

O que você precisa agora? Você tem que definir um escopo para o seu trabalho, fazer um Google não pode ser seu escopo! :-)

 

Na busca por MACACO VELHO, como esta, faz exatamente como o Google faria sem dicionários, lematização, tokenização, algoritmos de aproximação ou busca fonética.

 

Se você fizer a consulta:

SELECT *,   (MATCH (site_url, site_title) AGAINST ('maca* exti* aran*' in boolean mode) * 2) +   (MATCH (site_desc, site_keywords) AGAINST ('maca* exti* aran*' in boolean mode) * 1 ) AS rankFROM siteWHERE MATCH (site_url, site_title, site_desc, site_keywords) AGAINST ('maca* exti* aran*' in boolean mode)ORDER BY rank DESC

Para buscar "macaco extinção arannha"... Não é a mesma coisa que buscar "maca exti aran" no Google.

 

Bons estudos.

[fermel 0]

entendi, mas no exemplo que você me passou você ta inserindo um texto e adicionando o * apos o texto, no meu caso eu tenho a variavel $busca então como devo proceder??

[Prog 183]

Você pode manipular o conteúdo de $busca antes de submeter a consulta.

[ivanferrer 5]

Primeiro você precisa gravar (usando insert) em uma tabela do banco de dados as palavras que foram buscadas e o valor somatorio=1 das encontradas no site... se a cada palavra que existir nesta tabela (usando uma consulta - select) em vez de usar insert, vc faz um (update) neste campo somatário, gravando somatorio = somatorio + 1, mantendo a palavra do banco...

 

depois vc conta o total agrupado por palavra, ordena do maior para o menor e pega o registro de maior relevância:

SELECT campo_palavra AS palavra_mais_relevante,  COUNT(campo_somatorio) AS total_somado_relevante FROM tabela WHERE 1 GROUP BY campo_palavra ORDER BY COUNT(campo_somatorio) DESC LIMIT 0,1;