Return search by string in Table

Question

Return search by string in Table

Data and database queries have a very special place in the universe of computers. The popularization of the internet was thanks to the agility of searches implemented by the web Enginner Search engines.

Doing a simple search for a string using sql+mysql was not such an effective alternative to manage a sophisticated search that returns results closer to what you want to find. You can for example fetch a sentence literal:

busca('palavra');

busca('minha frase de busca deve retornar algo que tenha sentido com o que eu digitei');

Arriving at such a result should not be such a simple task. You need to scale the problem at its highest complexity.

which implementation path will help me get this result ?
what tools can I be using to build this result ?
using only PHP and MYSQL I can approach this result ?
is there any open project source that has some tooling that manage searches like this in a mysql database?

I am currently developing a collection system for PDFs files. First I thought of making a program that would make this query in the binaries of the PDF file. But I realized that this solution could bring me some extra difficulties and a lot of processing. So I thought I'd pull out this data with PDF Parse (text extractor) to write it to a table. To first way I found to map this object was using the single Table Inheritance.

Why did I have this choice? I have a collection of more than 300 magazines each with an average of 30 pages. I want to get with this the simple functionality of finding a string provided by the search in the content column and that returns me the other fields. Well that I know how to do. My main concern about all this is the speed and performance of this system. Since these search requests in the bank will be made very often. If there are clearer and more efficient ways to implement this functionality I would like to read reviews.

I have the following scenario: tabela = paginas(id, post_id, n_pagina , conteudo).

So my goal is just to locate where certain text appears in posts. And with this capture the other fields where the reference is found along with a portion of string that precedes and succeeds the search term. The post_id key will be a pointer to the object posts from my cms.

$consulta = 'Amazônia'; 

$results = metodoDeBusca($consulta){

    // código
    return $resultados;
};

$resultados = [    
    [
        'pagina_id' => 12,
        'pagina' => 5,
        'post_id' =>1,        
        'str_before'  => 'A viagem será com destino a',
        'str_after'  => 'Esse dia será muito explendoroso'
    ],
    [
        'pagina_id' => 74 ,
        'pagina' => 1,
        'post_id' => 4,
        'str_before'  => 'A',
        'str_after'  => 'Tem uma fauna rica em biodiversidade'        
    ]
];

How could a PHP script return me an array with all this information so I could retrieve it in my CMS?

I would mainly like to get some implications that the construction of this class can generate. How can I be improving my architecture from the ultilization of some external features. Tips on libraries or platforms that interact with wordpress will be very welcome.

3

php mysql table datatable design-pattern

Author: Atila Silva, 2018-04-05

Source

1 answers

score 0 · Answer 1

Could you add more information?

Do you need a code that would return those results from a bank search?

Something like (assuming MySQL):

<?php
$conexao = mysqli_connect("servidor","usuario","senha","banco");

$consulta = 'Amazônia'; 
$sql = "SELECT pagina_id, pagina, post_id, conteudo FROM Revista WHERE conteudo LIKE '%{$consulta}%'";
$result = mysqli_query($conexao, $sql);

if (mysqli_num_rows($resultados) > 0) {
    while($r = mysqli_fetch_assoc($resultados)) {
        list($str_before, $str_after) = explode(' $consulta ', $row['conteudo']);
    }
} else {
    echo "Consulta não encontrada";
}

mysqli_close($conexao);
?>

In terms of optimization, I would give to try using Redis-PHP.

My reputation does not allow comments yet. I will enlarge the answer through your comments.