Ejemplo de duplicado de búsquedas eficientes

[vart001]

Ejemplo de duplicado de búsquedas eficientes
 Esto es un ejemplo de duplicado de campos en bases de datos para búsquedas eficientes
en idiomas que usen acentos, como es el caso del castellano.

Supongamos que tenemos una tabla con datos de clientes. Algunos de esos datos, como
nombre, apellidos, domicilio, ciudad, provincia, teléfono, email, etc. son idóneos
para efectuar una búsqueda de cadenas de texto, y otros campos, como facturación,
identificativo de cliente, etc, no lo son. Bién, trabajaremos con esa tabla, a la que
llamaremos 'clientes'.

Supongamos también que tenemos un formulario buscador de clientes en donde entramos un
texto para buscar a la vez en todos los campos mencionados, y que especificamos una de
las tres típicas opciones: 'alguna de las palabras', 'todas las palabras' o 'la frase
exacta'. El duplicado de campos es ideal para esta situación.

El ejemplo está referido a una base de datos MySQL, pero es perfectamente trasladable a
cualquier base de datos que soporte campos de datos grandes (BLOB de MySQL) y el
equivalente a la función locate() de MySQL. Esta función locate() localiza una subcadena
dentro de otra cadena, de forma simple y eficiente, sobre todo si el campo en el que se
hará la búsqueda es de formato binario (si distingue mayúsculas de minúsculas).

El campo de tipo BLOB contendrá la concatenación de todos los otros campos de la tabla en
un formato listo para hacerle consultas rápidas. Es importante que el campo sea de tipo
BLOB, no TEXT, que repercutiría en consultas mucho más lentas. Llamaremos a ese campo
'sin_acentos'.

Ante todo, cada vez que REPLACEemos un cliente nuevo o modifiquemos uno ya existente,
debemos recrear el campo que contendrá los datos duplicados. Este es el handicap de esta
técnica. Pero cuando REPLACEemos o modifiquemos un registro, no nos importará esperarnos
una décima de segundo más para que se actualize, si en contrapartida, cuando realizamos
una búsqueda sobre unos pocos miles de registros, obtenemos el máximo rendimiento.

O sea, que cada vez que hagamos un inser o un update en la tabla 'clientes', actualizaremos
el campo 'sin_acentos' con una función parecida a esta:

<?
function recrea_cliente($id) {
   
   $result = mysql_query(
   "select * from clientes where id='$id'");

   $row = mysql_fetch_ro w($result);

   $str = ' ' .
   sin_acentos($row['nombre'])    . ' ' .
   sin_acentos($row['apellidos']) . '  ' .
   sin_acentos($row['domicilio']) . ' ' .
   sin_acentos($row['ciudad'])    . ' ' .
   sin_acentos($row['provincia']) . '  ' .
   sin_acentos($row['telefono'])  . '  ' .
   sin_acentos($row['email'])     . '  ' .
   sin_acentos($row['etc'])       . ' ';

   mysql_query(
   "Update clientes set sin_acentos='$str'
   where id='$id'");
   
   return;
}

/*
Observen, en la función recrea_cliente(), que cuando creo el campo 'sin_acentos', separo
'nombre' y 'apellidos' con un solo espacio, pero 'apellidos' y 'domicilio' los separo
con dos. Esto lo hago así para que cuando se realice una búsqueda de tipo
'la frase exacta', no se encuentre por casualidad la coincidencia de la última palabra de
'apellidos' y la primera de 'domicilio', pero sí que se encuentre 'nombre' + 'apellidos'.
Lo mismo con 'domicilio' + 'ciudad' + 'provincia'. Al gusto de cada uno.

Vamos ahora con la función sin_acentos(), una pequeña maravilla que convierte todo a
mayúsculas sin acentos, respetando únicamente letras, números y los signos '-.    ·@_ÑÇ' (Cada
uno que elija a su gusto qué simbolos son susceptibles de búsquedas), y que convierte
todo lo demás en espacios en blanco (sin duplicar):
*/
function sin_acentos($str) {
   
   $let =
   "[^-.    ·@_ÑÇ0123456789" .
    ONT>"ABCDEFGHIJKLMN OPQRSTUVWXYZ]+";
   
   $min =
   "abcdefghijklmn opqrstuvwxyz" .
   "àáöäåôãõèéêëìíîïÿðñéóæâüûçøýùú" .
   "ÀÀÖÄÅÔÃÕÈÉÃÅ ËÌÍÎÏÒÓÂÜÛØÝÙÚ    ·";
   
   $may =
   "ABCDEFGHIJKLMN OPQRSTUVWXYZ" .
   "AAOAAOAOEEEEII IIYOÑOOÆAUUÇOYUU" .
   "AAOAAOAOEEEEII IIOOAUUOYUU.";
   
   $str = split($let, strtr($str, $min, $may));
   
   return implode(' ', $str);
}
/*
Y aquí tenemos la reina de la fiesta, la función buscar(), a la que le pasaremos el
$texto a buscar, el $tipo de búsqueda ('T' todas las palabras, 'A' alguna de las
palabras y 'F' la frase exacta), en nombre de la $tabla en que realizaremos la
búsqueda, y el nombre del $campo de la tabla que contiene el resumen sin acentos de
los otros campos.

También podemos especificarle qué haremos con los prefijos y los sufijos. Si
$prefijos = true, 'José' coincidirá con 'alejóse', pero si $prefijos = false, no. Y
si $sufijos = true, 'José' coincidirá con 'Josefa', pero si $sufijos = false, no.

Esta función nos devolverá un array con dos campos: el query SQL de la búsqueda y una
descripción de lo que se va a buscar:
*/

function buscar($texto,
                $tipo,
                $tabla,
                $campo,
                $prefijos = false,
                $sufijos = true) {
   
   $pre = $prefijos ? '' : ' ';
   $suf = $sufijos  ? '' : ' ';
   
   $cc = sin_acentos(trim($texto));
   
   if (! $cc) {
     
      $descri = "    ¿Que busque qué?";
      $buscar = '';
   
   }elseif ($tipo == 'F') { // Frase exacta
     
      $descri = "Buscando la frase $cc";
     
      $query =
      "select * from $tabla where ".
      "locate('$pre$cc$suf', $campo)";
   
   }else {
     
      $ccs = explode(' ', $cc);
     
      if (($sizecc = sizeof($ccs)) == 1) {
         // Solo hay una palabra.
         
         $descri = "Buscando la palabra $cc";
         
         $query =
         "select * from $tabla where ".
         "locate('$pre$cc$suf', $campo)";
     
      }elseif ($tipo == 'T') { // Todas las palabras
         
         $descri = "Buscando las palabras ";
         $coma = '';
         
         $query = "select * from $tabla";
         $where = 'where(';
         
         for ($n = 0; $n < $sizecc; $n++) {
           
            if ($n == $sizecc - 1) $coma = ' y ';
           
            $descri .= "$coma$ccs[$n]";
            $coma = ', ';
           
            $query .= " $where locate('$pre$ccs[$n]$suf', $campo)";
            $where = " and";
         }
         $query .= ')';
     
      }elseif ($tipo == 'A') { // Alguna de las palabras
         
         $descri = "Buscando las palabras ";
         $coma = '';
         
         $query = "select * from $tabla";
         $where = "where(";
         
         for ($n = 0; $n < $sizecc; $n++) {
           
            if ($n == $sizecc - 1) $coma = ' o ';
           
            $descri .= "$coma$ccs[$n]";
            $coma = ', ';
           
            $query .= " $where locate('$pre$ccs[$n]$suf', $campo)";
            $where = " or";
         }
         $query .= ')';
     
      }else{
         
         die('$tipo debe ser T/A/F');
     
      }
   }

   return array($query, $descri);
}
/*
 
El query está listo para hacer una búsqueda, pero pueden añadírsele otras cláusulas,
como por ejemplo ORDER BY o LIMIT, y si se quieren ampliar las condiciones de la
búsqueda también se le puedesn añadir.

Creo que se entenderá toto mucho mejor con una simulación/ejemplo:

*/

$tabla = 'clientes';
$campo = 'sin_acentos';

$textos[] = 'José';
$textos[] = 'José hotmail';
$textos[] = 'José hotmail Barcelona ';

$tipos['todas las palabras']  = 'T';
$tipos['alguna de las palabras'] = 'A';
$tipos['la frase exacta']  = 'F';

$limit = 0;

echo '
<html>
<head></head>
<body bgcolor="#ffffff">
<font size=2>

';

foreach($textos as $texto) {
   
   foreach($tipos as $tp => $tipo) {
     
      list($query, $descri) =
      buscar($texto, $tipo, $ tabla, $campo);
     
      $query .= " and facturacion > 100000";
      // Ampliamos las condiciones de la búsqueda
     
      $query .= " order by facturacion";
      // añadimos un order by

      $query .= " limit $limit, 30";
      // y también como ejempo, una cláusula limit distinta cada vez
     
      echo "------------------------------ ";
      echo "Si intruducen '$texto' " .
           "para buscar $tp, obtenemos: ";
      echo " Descripción: $descri ";
      echo "Query: $query ";
     
      $limit += 30;
   }
}

echo '

</font>
</body>
</html>';

exit;
?>