like e full text search

Search Like and Full Text

Using the Like operator

Like operator is used in a Where Clause to search for a standard specified in a column.

 $ conn = require __DIR__ . ' /utils/connection.php ' ;

$ term = $ argv [ 1 ] ?? null ;
$ term = ' % ' . $ term . ' % ' ;

** $ stmt = $ conn -> prepare ( ' SELECT * FROM posts WHERE body LIKE ?; ' );
$ stmt -> bind_param ( ' s ' , $ term );
$ stmt -> execute ();**

$ result = $ stmt -> get_result ();
$ posts = $ result -> fetch_all ( MYSQLI_ASSOC );

foreach ( $ posts as $ post ) {
    echo $ post [ ' title ' ]. PHP_EOL ;
    echo $ post [ ' body ' ]. PHP_EOL ;
    echo PHP_EOL ;
}

Use of % Example:

We want to select the people who live in a city that starts with 'S':

   1:SELECT * FROM Pessoas
   2:WHERE cidade LIKE 'S%'

The % symbol can be used to define a pattern (letters missing in the standard) both before and after the standard. The result of the research above will be:

Now we want to select the people who live in a city whose name ends with 'S':

   1:SELECT * FROM Pessoas
   2:WHERE cidade LIKE '%s'

The result for this research will be:

Using the Full-Text operator

To conduct research through a fulltext index we use the Match and Against functions, which are named after the fields and the value to be researched, respectively. See the example:

 $ stmt = $ conn -> prepare ( ' SELECT *, MATCH(title, body) AGAINST(? IN BOOLEAN MODE) as score FROM posts ORDER BY score DESC ; ' );
$ stmt -> bind_param ( ' s ' , $ term );
$ stmt -> execute ();

Match: A special construction used to conduct a full text search on a full text index. When match () is used in a clause, as in the example shown earlier, the returned lines are automatically classified with the greatest relevance first . Relevance values ​​are non -negative floating points numbers. No relevance means any similarity. Relevance is calculated based on the number of words on the line (document), the number of unique words on the line, the total number of words in the collection, and the number of lines containing a specific word .

Search Content:

 INSERT INTO posts (title, body) VALUES
    ( " Laravel framework " , " O laravel é muito utilizado hoje em dia " ),
    ( " CakePHP " , " Framework de desenvolvimento rápido " ),
    ( " Slim Framework " , " Micro framework, podemos utilizar o Eloquent do laravel nele " )