Generador de Consulta MySQL FullText para un buscador

La idea es dejar una clase que genere una consulta MySQL de busqueda utilizando los indices FULLTEXT.

Lo primero son las limitaciones o requerimientos:

• La consulta solo servirá para base de datos MySQL.
• La tabla debe tener su juegos de caracteres en Case Insensitive o sea que no distinga entre mayúscula o minúsculas en nuestras búsquedas. Son todos los juegos de caracteres que terminan en ci por ej.: utf8_general_ci
• Los campos que se utilizaran para realizar el indice FULLTEXT y la búsqueda deben ser del tipo
  1. CHAR
  2. VARCHAR
  3. TEXT
     o algunas de sus variantes
• Utilizaremos la opcion IN BOOLEAN MODE que nos traerá algunas ventajas.
  1. No usa el limite del 50% de los resultados, sin esta opción si la cantidad de resultados era mayor al 50% de los registros de la tabla no nos retornaba nada, con esta opción si.
  2. Puede funcionar incluso sin un indice FULLTEXT, aunque enlenteceria enormemente la búsqueda.
  3. Nos da soporte para usar los siguientes operadores:
     • + indica que la palabra debe estar presente en cada registro que se retorne.
     • - indica que la palabra no puede estar presente en los registros que se retornen.
     • sin operador cuando ni + ni – se especifica la palabra es opcional, pero se le da mas preferencia al registro que la contiene.
     • > o < estos operadores se usan para aumentar o bajar la relevancia de una palabra en un registro, pero no elimina registros por contener dicha palabra.
     • ( ) los paréntesis se usan para agrupar palabras en sub-expresiones. Se pueden anidar expresiones.
     • ~ operador de negación, da una relevancia negativa de la palabra en el registro, pero a diferencia del operador – no elimina el registro de los resultados de la búsqueda.
     • “ palabras entere comillas se buscaran literalmente, es decir debe existir una coincidencia exacta en las frases.
     • * sirve como operador de truncado, en el caso de este buscador, no sera necesario usarlo puesto que la generación de la consulta se encargara de hacerlo.

Primero veamos la estructura de una tabla de ejemplo, que podria ser la del siguiente codigo...

CREATE TABLE `buscador` ( `idNoticia` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT, `titulo` VARCHAR(100) NOT NULL, `desarrollo` TEXT NOT NULL, `categoria` SET('Noticias','Reportes','Revisiones') NOT NULL, PRIMARY KEY (`idNoticia`), FULLTEXT INDEX `titulo` (`titulo`, `desarrollo`) ) COLLATE='utf8_general_ci' ENGINE=MyISAM ROW_FORMAT=DEFAULT AUTO_INCREMENT=1

Una vez creada la tabla y si no creamos el indice FULLTEXT, que en este caso serian los campos titulo y desarrollo, creamos el indice. La consulta funcionara igual si no se crea dicho indice pero sera mucho mas lenta que si lo creáramos.

ALTER TABLE buscador ADD FULLTEXT(titulo, desarrollo);

Realizado estos dos pasos ya podemos ir al código de la clase:
Archivo: BuscadorFullText.php

<?php /** * Clase BuscadorFullText. * Es responsable de entregarnos el codigo de una consulta para la Base de datos * MySQL con motor MyIsam, basandose en los Indices FullText. * Ver http://dev.mysql.com/doc/refman/5.0/es/fulltext-search.html * Ver http://dev.mysql.com/doc/refman/5.0/es/fulltext-fine-tuning.html * * @package varias creado en el projecto opet * @copyright 2010 - ObjetivoPHP * @license Gratuito (Free) http://www.opensource.org/licenses/gpl-license.html * @author Marcelo Castro (ObjetivoPHP) * @link objetivophp@*****.***.** * @version 2.0.0 (27/12/2009 - 05/09/2010) * @since Continua serie BuscadorGenerico 4.4.6 del 21/05/2007. */ class BuscadorFullText { /** * Contiene la cadena de busqueda, generalmente su contenido se correlaciona * con un campo en el formulario de busqueda. * @var string */ private $_textoDeBusqueda; /** * Contiene la tabla de la base en la cual se realizara la consulta. * @var string */ private $_tablaMysql; /** * Coleccion de campos que se retornaran como resultado. * @var array */ private $_colCamposResultado = array(); /** * Coleccion de campos que se utilizan para la busqueda de tipo fulltext. * @var array */ private $_colCamposFullText = array(); /** * Coleccion de parametros adiccionales a incluirle a la consulta. * Todos se añaden con tipo AND. * @var array */ private $_colParametros = array(); /** * Contiene el tipo de consulta que se realizo. * @var string */ private $_tipoConsulta = 'FULLTEXT'; /** * Configura si utiliza SQL_CALC_FOUND_ROWS en la consulta mysql para luego extraer la * cantidad de resultados con limit. * @var boolean */ private $_SQL_CALC_FOUND_ROWS = true; /** * Contiene el metodo de envio del Formulario. * Utilizado en los metodos getValue y addParametros. * @var string */ private $_metodoEnvio = 'POST'; /** * Contiene los operadores para parametros variables permitidos. * @var array */ private static $_operadores = array('=', '<=>', '<>', '!=', '<=', '<', '>', 'IS', 'IS NOT', 'IS NULL', 'IS NOT NULL'); /** * Metodo __construct. * Crea un objeto BuscadorFullText * @param string $buscar Cadena de busqueda puede contener +-~>< * @param string $tabla Nombre de la Tabla MySQL a usar en la consulta. * @return void */ public function __construct($buscar, $tabla) { $this->_textoDeBusqueda = addslashes($buscar); $this->_tablaMysql = addslashes($tabla); } /** * Metodo addCamposResultado. * Ingresa los campos que se usaran como resultado de la consulta. * @param string/array $campo Contiene el o los nombres de los campos. * @return void */ public function addCamposResultado($campo) { if (is_string($campo)) { $campo = explode(',', $campo); } if (is_array($campo)) { foreach ($campo as $valor) { $this->_colCamposResultado[] = $valor; } } } /** * Metodo addCamposFullText. * Ingresa los campos que se usaran para la consulta. * @param string/array $campo Contiene el o los nombres de los campos. * @return void */ public function addCamposFullText($campo) { if (is_string($campo)) { $campo = explode(',', $campo); } if (is_array($campo)) { foreach ($campo as $valor) { $this->_colCamposFullText[] = $valor; } } } /** * Metodo addParametrosVariables. * Inserta un parametro a la consulta. * @param string $campo Nombre del campo en el formulario de consulta, debe * coincidir con el de la base de datos. * @param string $operador operador de consulta = > < etc. * @return void */ public function addParametrosVariables($campo, $operador) { $valor = ($this->_metodoEnvio == 'POST')? $_POST[$campo] : $_GET[$campo]; if (!in_array($operador, self::$_operadores)) { trigger_error('Operador de Consulta no valido en Metodo ' . __METHOD__, E_USER_ERROR); } $parametro = $campo . ' ' . $operador . " '" . addslashes($valor) . "'"; $this->_colParametros[] = $parametro; } /** * Metodo addParametrosFijos. * Agrega parametros fijos en la consulta. * @param string $parametros parametro tipo campo='valor' * @return void */ public function addParametrosFijos($parametros) { $this->_colParametros[] = addslashes($parametros); } /** * Metodo setSqlCalcFoundRows. * Configura si debe ponerse en la consulta MySQL la clausula SQL_CALC_FOUND_ROWS. * @param boolean $boolean true por si, false por no. * @return void */ public function setSqlCalcFoundRows($boolean) { $this->_SQL_CALC_FOUND_ROWS = ($boolean)? true:false; } /** * Metodo setMetodoEnvio. * Configura por que metodo se envia el formulario (GET, POST). * @param string $metodo Metodo a utilizarce para levantar las variables * del formulario. * @return void */ public function setMetodoEnvio($metodo = 'POST') { ($metodo == 'GET')? $this->_metodoEnvio = 'GET' : $this->_metodoEnvio = 'POST'; } /** * Metodo getValue. * Retorna una cadena que se puede colocar dentro del value de un campo * de busqueda. * @param string $campo Nombre del campo que se debe levantar. * @param string $metodo metodo POST o GET. Por defecto POST. * @return string Cadena tipo value. */ public function getValue($campo) { $valor = ($this->_metodoEnvio == 'POST')? $_POST[$campo] : $_GET[$campo]; return stripslashes(str_replace('"', "'", $valor)); } /** * Metodo getConsultaMysql. * Genera una consulta MySQL, para un buscador dependiendo de el largo de la * cadena utiliza like o modo fulltext. * @param string $texto * @return string Consulta MySQL para busqueda. */ public function getConsultaMysql($texto='') { // Veo si cambia el texto de busqueda o si lo mantiene. $this->_textoDeBusqueda = ($texto)? addslashes($texto) : $this->_textoDeBusqueda; // Comienzo a generar la consulta MySql $mySqlQuery = (strlen($this->_textoDeBusqueda) < 4)? $this->_getConsultaLike($this->_textoDeBusqueda) : $this->_getConsultaFullText($this->_textoDeBusqueda); // Agregamos los Parametros a la consulta if ($this->_colParametros) { $mySqlQuery .= ' && (' . implode(' && ', $this->_colParametros) . ' ) '; } // Cerramos la consulta $mySqlQuery = ($this->_tipoConsulta == 'FULLTEXT')? $mySqlQuery . " ORDER BY puntuacion DESC" : $mySqlQuery; $mySqlQuery = $mySqlQuery . ' LIMIT %d, %d'; return $mySqlQuery; } /** * Genera la consulta tipo Like. * @param string $texto Texto de busqueda. * @return string Cadena para consulta MySQL con LIKE. */ private function _getConsultaLike($texto) { $this->_tipoConsulta= 'LIKE'; $inicio = ($this->_SQL_CALC_FOUND_ROWS)? 'SELECT SQL_CALC_FOUND_ROWS ':'SELECT '; $camposResultados = (count($this->_colCamposResultado))? implode(', ', $this->_colCamposResultado) : '*'; $consulta = $inicio . $camposResultados . " FROM " . $this->_tablaMysql . " WHERE ( "; foreach($this->_colCamposFullText as $campo) { $consulta = $consulta . " " . $campo . " LIKE '%%" . $texto . "%%' OR "; } $consulta = substr($consulta,0,strlen($consulta)-3) ." ) "; return $consulta; } /** * Genera la consulta fulltext. * @param string $texto Texto de busqueda * @return string Cadena para consulta MySQL tipo FULLTEXT. */ private function _getConsultaFullText($texto) { $this->_tipoConsulta= 'FULLTEXT'; $camposFullText = implode(', ', $this->_colCamposFullText); $parsearTexto = $this->_cadenaFullText($texto); $inicio = ($this->_SQL_CALC_FOUND_ROWS)? 'SELECT SQL_CALC_FOUND_ROWS ':'SELECT '; $camposResultados = (count($this->_colCamposResultado))? implode(', ', $this->_colCamposResultado) : '*'; $consulta = $inicio . " " . $camposResultados . ", MATCH( $camposFullText ) AGAINST ('$parsearTexto' IN BOOLEAN MODE) AS puntuacion FROM $this->_tablaMysql WHERE MATCH( $camposFullText ) AGAINST ('$parsearTexto' IN BOOLEAN MODE) "; return $consulta; } /** * Metodo cadenaFullText. * Genera la cadena que ira en parte AGAINST, de la consulta MySQL. * @param string $texto Texto digitado por el usuario. * @return string */ private function _cadenaFullText($texto) { $patron = array('/\s\s+/', // Quito espacios en blanco de mas '/\'/', // Cambio comillas simples por dobles '/([\+\-\~\<\>])(\s)/', // junto los simbolos con las palabras de busqueda '/([\w]*)([\+\-\~\<\>\)])/', // Separo los simbolos de las palabras que los anteceden); '/\*/', '/\s\s+/'); // Vuelvo a sacar espacios en blanco por las dudas $remplazo = array(' ', '"','\1', '\1 \2', '', ' '); $texto = preg_replace($patron, $remplazo, $texto); // Separamos las cadenas entre comillas. $datos = explode('"', str_replace('\\', '', $texto)); $cantidad = count($datos); for ($index = 0; $index < $cantidad; $index++) { if(($index % 2) == 0 && $datos[$index] ) { $partes = explode(' ', trim($datos[$index])); $datos[$index] = implode('* ', $partes) . '* '; } elseif ($datos[$index]) { // Ordeno en caso que sea una cadena entrecomillada $simbolo = substr($datos[$index - 1], strlen($datos[$index - 1])-3,1); $datos[$index] = '"' . $datos[$index] .'"'; if (strpos("+-~<>", $simbolo) !== false) { $datos[$index] = $simbolo . $datos[$index]; $datos[$index-1]= substr($datos[$index-1], 0, strlen($datos[$index - 1])-3); } } } $texto = implode(' ', $datos); // Correcciones Finales $patron = array('/\(\*/', '/\)\*/', '/[\+\-\~\<\>]\*/'); $remplazo = array('(', ')', ''); $texto = preg_replace($patron, $remplazo, $texto); return $texto; } } ?>

Como se utiliza la clase:
El código de un ejemplo básico de utilización seria el siguiente.

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <html xmlns="http://www.w3.org/1999/xhtml"> <head> <meta http-equiv="Content-Type" content="text/html; charset=utf-8" /> <title>Documento sin título</title> </head> <body> <?php require_once 'BuscadorFullText.php'; $obj2 = new BuscadorFullText($_POST['buscar'], 'buscador'); ?> <form id="form1" name="form1" method="post" action=""> <input type="text" name="buscar" id="buscar" value="<?php echo $obj2->getValue('buscar'); ?>" /> <select name="categoria" id="categoria"> <option value="Noticias">Noticias</option> <option value="Reportes">Reportes</option> <option value="Revisiones">Revisiones</option> </select> <input type="submit" name="enviar" id="enviar" value="Enviar" /> </form> <?php $obj2->addCamposFullText('titulo, desarrollo'); $obj2->addCamposResultado(array('idNoticia', 'titulo', 'desarrollo')); $obj2->addParametrosVariables('categoria' , '='); $consulta = $obj2->getConsultaMysql(); echo 'Consulta:' . sprintf($consulta, 0,10); ?> </body> </html>

Explicación del Código:

linea 10> Incluimos la clase a ser usada.
linea 11> Instanciamos la clase pasándose como parámetros, el valor del campo de búsqueda de nuestro formulario y el nombre de la tabla que se usara.
linea 14> $obj2->getValue(‘buscar’); este método nos retorna una cadena que puede ser usado correctamente en un value de un campo encerrado entre comillas dobles.
linea 23> Le indicamos cuales son los campos que pertenecen al indice fulltext, en este caso tenemos dos formatos un string separados por coma o un arreglo conteniendo en cada indice del arreglo uno de los campos.
linea 24> Pasamos los campos que queremos como resultados en la consulta.
linea 25> En este caso el formulario dispone de un campo para la categoría de la noticia, que varia según lo que selecciona el usuario. A este método le pasamos el nombre del campo del formulario que deberá coincidir con el de la tabla y el tipo de operador = > < etc. También disponemos de un método addParametrosFijos donde se le pasa alguna condición fija que se quiera.
linea 26> Cargamos la consulta en la variable $consulta !!! ojo !!! pues se usa con sprintf pues se tiene que incorporar los datos para el limit.
linea 27> Cargamos los datos del limit y ya podremos usarla en nuestra consulta mysql.
Espero que les resulte útil y cualquier comentario o error por favor avisarme para así corregirla, pues salio con pocas pruebas.

Próximamente agregare otro articulo para usar de manera conjunta, esta clase y el paginador.

FUENTES:
Búsquedas booleanas de texto completo (FullText)
Afinar búsquedas de texto completo (FullText) con MySQL
Funciones de búsqueda de texto completo (Full-Text)