Index Fulltext is not filtering in select Where

Asked

Viewed 51 times

1

I have a table called boletos, and created a FULLTEXT index for the column categoria, which is the column that will go the most in the search for the Where.

I did everything right, only when it’s time to search, he’s not filtering the result on Where. It simply brings all the results of the table and what I sought it puts at the top of the table, and I would like it to bring only the result I sought.

I don’t know if it’s mine select which is correct, but I will post here my code.

billets:

id int auto increment
boleto mediumblob//arquivo pdf,jpeg
nomeBoleto varchar,//nome do arquivo
tipoBoleto varchar,//tipo do arquivo(.pdf ou .jpeg)
categoria varchar,//só tem 2: Pagamento Mensal e Pagamento Anual
primary key (post_id),
FULLTEXT (categoria)//indice pra pesquisar por categoria

My PHP is like this:

<?php
include "conexão.php";

 //aqui puxa minha categoria do campo
$categoria = $_POST['comboB'];

//e meu select
$stmt = $db->prepare("select id,nomeBoleto,categoria from boletos WHERE MATCH (categoria) AGAINST ('$categoria' in boolean mode)");

$stmt->execute();
while($row = $stmt->fetch()){
  if($row > 0){ 

<tr>                
  <td><?php echo $row['id']?></td>
  <td><?php echo $row['nomeBoleto']?></td>
  <td><?php echo $row['categoria']?></td>                 
</tr>

  }
?>

It simply brings all the records putting what I sought at the top of the table, being that it is to bring only what I put to bring.

What am I doing wrong?

php mysql full-text

  • Put , if possible , the data.

  • As well as the data?

  • The data you entered and tried to search for may have an impact.

  • The data I seek enters the category column, and that is in the Where of my select...in this category column only has two options that is: Monthly Payment and Annual Payment, I do not know if it influences why the words are equal.. I’ve read something about, but I can’t fix it :(

  • @Motta edited the question and put in front of the columns the types of data that are entered, if you can help me.. I’m cracking my head to fix it..

  • The problem may be in the data , which "category" is in the table and which category you searched ? I don’t know "fulltext" but it can be this

  • In this category column only has two options that is: Monthly Payment and Annual Payment, fulltext is table mysql indices, I never worked with it, first time to mechendo tb.. :(

  • I believe that what will demand in the case is the "payment", perhaps it was the case to use an exact search, no ?

  • is that the search is coming from a variable that takes the value of a select for the post, because the user who selects the type he wants to search for... : $category = $_POST['comboB'];.. but the conflict is or the words are iguias.. I will research better here...

Show 4 more comments

1 answer

2


How Fulltext Index Search Works

For each record, Mysql assigns a relevance value, which represents the similarity of the search string to the line in question. A 0 (zero) value means no similarity, causing the record not to be displayed.

This relevance calculation is done through an algorithm designed to search large text masses, making the search unsuitable for small tables. Among the variables that are taken into account in this calculation, Mysql considers the number of words found in each index field, the number of words found per line, the number of occurrences of the same word in all lines, among others. The rarer the word, the greater its weight in the calculation of relevance.

About the modifier IN BOOLEAN MODE

Mysql can perform full-text boolean searches using IN BOOLEAN MODE. With this modifier, certain characters have a special meaning at the beginning or end of the search string words. Where, the + and - operators indicate that a word must be present or absent, respectively, for a match to occur.

Taking these facts into account, it seems that your query:

SELECT id, nomeBoleto, categoria FROM boletos WHERE MATCH (categoria) AGAINST ('$categoria' in boolean mode)

It should be: (note the plus sign +)

SELECT id, nomeBoleto, categoria FROM boletos WHERE MATCH (categoria) AGAINST ('+$categoria' in boolean mode)

References:

FULLTEXT indexes in Mysql

Boolean full text searches

Mysql Match Against In Boolean Mode Returns Nothing on Middle word

Browser other questions tagged php mysql full-text

You are not signed in. Login or sign up in order to post.