Home / Questions / 395509
Accepted
scorpions
Asked: 2020-10-09 03:50:34 +0800 CST 2020-10-09 03:50:34 +0800 CST

Search for a string in a database query

  • 772

I am trying to create a search engine for a website, but I need to search for specific words in that search engine, such as "i5 laptop".

I've tried with LIKEbut it doesn't do it right. I have to say that maybe the string I'm looking for is not at the beginning or at the end, but rather it is contained.

I have tried this MySQL function:

SELECT * FROM comparador WHERE MATCH (titulo) AGAINST ('+"portatil i5"' IN BOOLEAN MODE)

It does not return anything, but if I put a 14, it does it well.

And also this one:

SELECT * FROM comparador WHERE MATCH (titulo) AGAINST ('+portatil +i5' IN BOOLEAN MODE)

The one that returns similar results is the first one, but putting i5it annoys the query.

Then aside, this travels from a post with ajax.

EDITION

Records in the Example DB

(34528, 0, '', '', 0, '', '', 0, 0, '', '', 0, '', 'PORTATIL ASUS X509JA-BR491T i3-1005G1 15.6HD 8GB 512SSD W10 COLOR PLATA', '', '', '', '', '90NB0QE1-M10120', 'DESYMAN', '', 369, 0, '0', '', 0, 'https://desyman.com/articulos/artporta asus x509ja-br491t_1.jpg\r', 0, 0, 0, 0, 0, 0, 0, 0, 0, '', '0000-00-00 00:00:00', 0, 0, '2020-10-08 07:10:40'),
    (34529, 0, '', '', 0, '', '', 0, 0, '', '', 0, '', 'PORTATIL ASUS X509JA-BR252T i3-1005G1 15.6HD 8GB 256SSD W10 COLOR PLATA', '', '', '', '', '90NB0QE1-M09760', 'DESYMAN', '', 339, 0, '0', '', 0, 'https://desyman.com/articulos/artporta asus x509ja-br252t_1.jpg\r', 0, 0, 0, 0, 0, 0, 0, 0, 0, '', '0000-00-00 00:00:00', 0, 0, '2020-10-08 07:10:40'),

Table in DB

CREATE TABLE `comparador` (


`id` int(11) NOT NULL,
  `id_categoria` int(11) NOT NULL,
  `categoria` text COLLATE utf8_spanish2_ci NOT NULL,
  `ruta_categoria` text COLLATE utf8_spanish2_ci NOT NULL,
  `id_subcategoria` int(11) NOT NULL,
  `subcategoria` text COLLATE utf8_spanish2_ci NOT NULL,
  `marca` text COLLATE utf8_spanish2_ci NOT NULL,
  `id_marca` int(11) NOT NULL,
  `id_proveedor` int(11) NOT NULL,
  `tipo` text CHARACTER SET utf8 COLLATE utf8_spanish_ci NOT NULL,
  `ruta` text CHARACTER SET utf8 COLLATE utf8_spanish_ci NOT NULL,
  `estado` int(11) NOT NULL,
  `url_producto` text COLLATE utf8_spanish2_ci NOT NULL,
  `titulo` text COLLATE utf8_spanish2_ci NOT NULL,
  `titular` text CHARACTER SET utf8 COLLATE utf8_spanish_ci NOT NULL,
  `descripcion` text COLLATE utf8_spanish2_ci NOT NULL,
  `youtube` text COLLATE utf8_spanish2_ci NOT NULL,
  `multimedia` text CHARACTER SET utf8 COLLATE utf8_spanish_ci NOT NULL,
  `codigo` text COLLATE utf8_spanish2_ci NOT NULL,
  `proveedor` text COLLATE utf8_spanish2_ci NOT NULL,
  `ean` text COLLATE utf8_spanish2_ci NOT NULL,
  `costo` float NOT NULL,
  `canon` int(11) NOT NULL,
  `stock` text COLLATE utf8_spanish2_ci NOT NULL,
  `detalles` text CHARACTER SET utf8 COLLATE utf8_spanish_ci NOT NULL,
  `precio` float NOT NULL,
  `portada` text CHARACTER SET utf8 COLLATE utf8_spanish_ci NOT NULL,
  `vistas` int(11) NOT NULL,
  `ventas` int(11) NOT NULL,
  `vistasGratis` int(11) NOT NULL,
  `ventasGratis` int(11) NOT NULL,
  `ofertadoPorCategoria` int(11) NOT NULL,
  `ofertadoPorSubCategoria` int(11) NOT NULL,
  `oferta` int(11) NOT NULL,
  `precioOferta` float NOT NULL,
  `descuentoOferta` int(11) NOT NULL,
  `imgOferta` text CHARACTER SET utf8 COLLATE utf8_spanish_ci NOT NULL,
  `finOferta` datetime NOT NULL,
  `peso` float NOT NULL,
  `entrega` float NOT NULL,
  `fecha` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_spanish2_ci;

php code of the application

// Incluimos nuestro archivo CONEXION
require_once "../app/conexion.php";

$articuloAbuscar = $_POST["articulo"];


$articulo = DB::queryExecute("SELECT * 
                                FROM comparador
                                WHERE MATCH (titulo)
                                AGAINST ('".$articuloAbuscar."' IN NATURAL LANGUAGE MODE)
                                LIMIT 50;
                            ");



echo "<div class='row'>";
    for($i=0; $i<count($articulo); $i++){

        echo "<div class='col-md-2'>";

            if($articulo[$i]["portada"] != null){
                echo "<img class='imagenProducto' src='".$articulo[$i]['portada']."'/>" . "<br/>";
            }else{
                echo "No tiene imagen" ."<br/>" ;
            }

            $id = $articulo[$i]['id'];

            echo "<br/>";
            echo "<span> Titulo: ".$articulo[$i]["titulo"] ."</span>". "<br/>";
            echo "<span> Proveedor: ".$articulo[$i]["proveedor"] ."</span>". "<br/>";
            echo "<span> PN: " . $articulo[$i]["codigo"] . "</span>" . "<br/><br/>";
            echo "<span class='bg-danger text-white'> Costo: " . $articulo[$i]["costo"] . "</span>" . "<br/><br/>";
            echo "<span class='bg-danger text-white'> Stock: " . $articulo[$i]["stock"] . "</span>" . "<br/><br/>";
            echo "<a href='controladores/generarOfertaEmseel.php?id=$id'>
                    <button class='btn btn-success mb-5'>Crear Oferta EMSEEL</button>
                  </a>";
            echo "<a href='controladores/generarOfertaIntegra.php?id=$id'>
                    <button class='btn btn-success mb-5'>Crear Oferta Informatica</button>
                  </a>";
            
        echo "</div>";
    }
echo "</div>";

json_encode($articulo);

class connection

class DB {


public static function connect() {
    try{
        $connection = new PDO("mysql:host=10.48.0.19;dbname=bbdd-tienda-integra","root","pass");
        $connection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    }catch(PDOException $e){
        echo "Error: ". $e->getMessage();
    }

return $connection;
}


// EXECUTE QUERY MYSQL TYPE SELECT
public static function queryExecute($sql){
    $arrayData = array();

    try{

        $connection = DB::connect();

        $stmt = $connection->query($sql);
        $stmt->setFetchMode(PDO::FETCH_ASSOC);
        $stmt->execute();

        // SAVE THE DATA IN ARRAY
        $arrayData = $stmt->fetchAll();


    }catch(PDOException $e){
        echo 'Error: '. $e->getMessage();
    }
return $arrayData;
}

INDEX query

//generamos la consulta
$resultado = DB::queryExecute("SELECT DISTINCT id, titulo, codigo, proveedor, costo, stock, portada FROM comparador ORDER BY id ASC LIMIT 10");

I have to say that it is an application made with pure php, no MVC, and I am only modifying it, I have not made it myself and I am going crazy with many things....

Thanks in advance

mysql

1 Answers

  1. Best Answer

    With the new information you provide I can help you better.

    To begin with, you should protect your code from the serious security issues associated with SQL injection which must be fixed with prepared queries or using PDO::quote().

    Since you establish a connection for each SQL query (something that I don't recommend and that is very bad in terms of performance), the simplest thing is to prepare your function to receive the values ​​of the markers to make use of a prepared query:

    $articulo = DB::queryExecute("
    SELECT * 
    FROM comparador
    WHERE
      MATCH (titulo) AGAINST (:articulo IN NATURAL LANGUAGE MODE)
    LIMIT 50;
", [
    ':articulo' => $_POST["articulo"]
]);

    Your class's method implementation would change to:

    class DB {
    /* ... */

    public static function queryExecute($sql, $marcadores = []) {
        try {
            $connection = DB::connect();
            /* No hacemos la consulta, solo la preparamos */
            $stmt = $connection->prepare($sql);
            $stmt->setFetchMode(PDO::FETCH_ASSOC);
            /* Ejecutamos la consulta usando los marcadores proporcionados */
            $stmt->execute($marcadores);
            /* Devolvemos el resultado, no es necesario almacenarlo */
            return $stmt->fetchAll();
        } catch(PDOException $e) {
            /* Finalizamos la ejecución del script */
            die(htmlspecialchars('Error: '. $e->getMessage()));
        }
    }
}

    On the other hand I'm seeing that your PHP generates HTML and then seems to try to return a JSON with json_encode($articulo);, but obviously the latter won't work without an output function to the browser (like echo).

    Problems that you suffer with your two searches:

    • +"portatil i5": Quotation marks are used to search for exactness in the sequence of terms. You are looking for the term portátilfollowed immediately by the term i5. If there is no record that has both words immediately after each other, then no results will appear.

    • +portatil +i5: The symbol +indicates that that word should be found and the -one that should not be. Note that the term i5 does not reach the default minimum length (3 characters) for it to be indexed, so the query will ignore it completely.

    To fix the latter you'll need to change the MySQL server configuration permanently and rebuild the full-text search index as shown below.

    MySQL server configuration

    To modify the minimum size of the terms that will be added to the full-text index, add the following lines to the section [mysqld]of the MySQL server configuration file (usually /etc/mysql/my.cnf):

    [mysqld]
innodb_ft_min_token_size=1
ft_min_word_len=1

    Restart the MySQL server for the changes to take effect.

    Full-Text Search Index Rebuild

    Now we need to rebuild the full-text search index to include the new terms using the following SQL query:

    OPTIMIZE TABLE comparador

    From now on you can use the term i5in a search to limit the results obtained.

    You can see the original answer at this link .

    • 4