Ranking de búsquedas con PHP y MySQL

baluart10 Agosto 2007 - 9:11am 4 comentarios
Enviar por Email Imprimir

Hace unos días vimos cómo crear un buscador interno con Google Custom Search Engine, herramienta recomendada por dos motivos:

  1. Conocer los intereses de nuestros visitantes, y
  2. Ahorrar recursos del servidor.

Pero, si lo que uno busca es conocer a fondo todas las búsquedas que se realizan en nuestro sitio web, la mejor opción es almacenar los datos en nuestra propia BD y luego reaizar análisis a nuestro gusto.

Los datos que proporciona Google Coop son demasiado estrechos. Coop sólo muestran un listado con las principales búsquedas, más no toda la información al detalle. Sin embargo, si nosotros decidimos almacenar los datos de las búsquedas, estaremos utilizaremos muchos más recursos, pero a su vez, nos beneficiaremos con mejores análisis.

Ahora vamos a crear nuestro ranking de búsquedas con PHP y MySQL, similar a lo que es Google Zeitgeist, para nuestro sitio (script incluido).

Zeitgeist - Terminos buscados en MySQL

La idea del Zeitgeist que vamos a desarrollar, es uno que almacene datos mínimos, para no agrandar nuestra base de datos muy rápidamente. Sin embargo, se pueden ampliar los campos, por ejemplo, añadiendo ips para distinguir nuestro ranking por países (el código fuente esta al final del artículo).

El SQL de la estructura de la tabla Zeitgeist, es como sigue:

CREATE TABLE `zeitgeist` (
  `id` int(11) NOT NULL auto_increment,
  `terminos` varchar(100) NOT NULL default '',
  `timestamp` int(11) NOT NULL default '0',
  PRIMARY KEY  (`id`)
) TYPE=MyISAM PACK_KEYS=0;

Como ven, estamos creando una tabla llamada zeitgeist, que contiene 3 campos, id, terminos y timestamp. Este último lo añadimos, para luego obtener los rankings en determinados periodos de tiempo. Ejm. Lo más buscado del día, lo más buscado de la semana, etc.

Una vez creada la tabla en nuestra base de datos, creamos las funciones PHP que almacenen y actualicen los datos, cada vez que un visitante realice una búsqueda.

Almacenar búsquedas con PHP: update_zeitgeist()

Nuestra función, llamada Update Zeitgeist, requiere dos parámetros: Los términos buscados y la fecha. Sin embargo, no hay problema en dejar nulo el último parámetro, ya que por defecto, la variable fecha tomará el valor de time().

function update_zeitgeist($terminos, $fecha){

if(!$fecha) $fecha = time();
if (!($conn = db_connect()))
return false;

// formateamos a minúsculas, retiramos acentos e inutilizamos comillas
$path = strtolower($terminos);
$find = array('á', 'é', 'í', 'ó', 'ú', 'ñ');
$repl = array('a', 'e', 'i', 'o', 'u', 'n');
$terms = str_replace ($find, $repl, $terminos);
$terminos = addslashes($terms);

// Inserta términos y hora a la BD
$result = @mysql_query( "insert into zeitgeist (terminos, timestamp) values
('$terminos', '$fecha')");

@mysql_free_result($result);
}

Nota: Las arrobas evitan la impresión de errores en pantalla. Pueden suprimirlas, para realizar las pruebas.

Con update_zeitgeist(), lo único que debemos hacer para ponerla en práctica, es añadirla a nuestro script de búsquedas. Por ejemplo, si nuestro buscador esta search.php, en la parte final de la página debemos colocar el include y la llamada a la función.

// requerimos paquete de funciones
require_once ("includes/zeitgeist.php");
// Recuperamos términos de búsqueda
$terminos = $_GET['q'];
// Insertamos términos en la BD
update_zeitgeist($terminos, $fecha);

De esta manera, lograremos almacenar cada búsqueda que se realice en nuestra web, en el momento en que ella se ha produzca. Ahora, vamos a ver como procesar y mostrar los datos, creando nuestro ranking.

Rankings por fechas: get_zeitgeist() y display_zeitgeist()

Get_zeitgeist() es la función que va a parsear los datos almacenados en la BD y nos devolvera un listado con los terminos más buscados según los parámetros que nosotros le coloquemos.

Esta función trabaja con 3 parámetros:

  • Param string $time, define el tiempo del ranking (día, semana, mes, año)
  • Param int $ts Parámetro UNIX, reemplaza a time(). Es nulo por defecto.
  • Param int $count Limita el número de resultados a mostrar. Si es (0) no se muestran resultados.

function get_zeitgeist($time, $ts = NULL, $count = 0)
{
if (!isset($ts))
{
$ts = time();
}
// definimos la constante inicial
$ZDate = getdate($ts);

switch ($time)
{
case day:
$ts1 = mktime(0, 0, 0, $ZDate['mon'], $ZDate['mday'], $ZDate['year']);
$ts2 = $ts1 + 86400;
break;
case week:
$tsB = $ts - $ZDate['wday'] * 86400;
$ZDateB = getdate($tsB);
$ts1 = mktime(0, 0, 0, $ZDateB['mon'], $ZDateB['mday'], $ZDateB['year']);
$ts2 = $ts1 + 7 * 86400;
unset($ZDateB);
break;
case month:
$ts1 = mktime(0, 0, 0, $ZDate['mon'], 1, $ZDate['year']);
$ts2 = mktime(0, 0, 0, $ZDate['mon'] + 1, 1, $ZDate['year']);
break;
case year:
$ts1 = mktime(0, 0, 0, 1, 1, $ZDate['year']);
$ts2 = mktime(0, 0, 0, 1, 1, $ZDate['year']+1);
break;
}

if (!($conn = db_connect()))
return false;

$result = mysql_query("
SELECT id, terminos, count(timestamp) cnt
FROM zeitgeist
WHERE (timestamp >= $ts1)
AND(timestamp < $ts2)
GROUP BY 2
ORDER BY 3 DESC, 2 ASC
LIMIT 0, $count
");

if (!$result)
return false;
//si hay consulta, crea el listado, lo almacena en value
do {
$value[] = $row;
} while ($row = mysql_fetch_assoc($result));

return $value;

}

Esta función es especialmente interesante, porque realiza el cálculo (ranking) tomando en cuenta el tiempo en que se ha realizado. Por ejemplo, uno podría añadir un case last_week y mostrar lo más buscado la semana pasada.

Display_zeitgeist(), es la plantilla que toma el valor devuelto por get_zeitgeist() e imprime el html.

function display_zeitgeist($new_array){
  if (is_array($new_array) && count($new_array)>1)
  {
  echo "<br />";
   echo "ZeitGeist: ";
 // muestra el listado de noticias
    foreach ($new_array as $new)
    {
 //eliminamos algún valor vacío
     if (empty($new)) {
         echo "";
 //mostramos los datos  
     } else {
   $terminos = stripslashes($new['terminos']);
   $url =  "index.php?q=".urlencode($terminos);
   echo "<a href=\"".$url."\">".$terminos." (".$new['cnt'].")</a> ";
  }
 }
  } else {
 echo "ZeitGeist: ";
    echo "Lo sentimos, estamos procesando los datos.";
  }

}

Finalmente, luego de realizar el include al archivo que las contiene, la manera de utilizar estas funciones es la siguiente:

//muestra el zeitgeist
if ($search_array = get_zeitgeist(year,$ts = NULL, $count = 5)){
display_zeitgeist($search_array);
}

En este caso, estaremos mostrando los 5 términos más buscados de todo el año. Si queremos que sean más terminos, cambiamos el valor de $count. Además, el primer parametro puede ser day, week, month o year, según querramos el ranking sea del día, la semana, el mes o el año (éste último por defecto).

Para dejar todo más claro, pueden descargar este código, con el SQL para crear la tabla, además de un par de funciones adicionales. Sólo hay que configurar la primera función (de conexión a la BD) y tendremos todo listo para trabajar.

Descargar | Zeitgeist con PHP y MySQL

Comentarios

Imagen de Nestor

Excelente post andaba buscando uno asi. Es decir un script capaz de guardar las busquedas que realizan nuestros visitantes a nuestras paginas. Mil gracias....

Imagen de Txaranga Eghoak

Muy bueno, estaba en ello y me ha inspirado la idea para poder hacer ranking de diferentes intervalos!!
muchas gracias!!
Un saludo!

Imagen de Txaranga Eghoak

Gracias!!
Me ha inspirado para hacer intervalos de ranking!!
Saludos!

Imagen de Angelinho
Angelinho

Buenas... Estuve leyendo todo este post y me ha gustado y deseo descargar el archivo pero me dice que ya no existe en la dirección indicada. Como lo puedo obtener?
Saludos

Tutoriales

Cómo descargar videos de VK.com
En este artículo voy a explicar como descargar videos y películas...
Descargar Facebook Móvil Gratis
Por si aún no lo han hecho, es posible descargar Facebook Móvil...
Cómo generar tráfico web con las redes sociales - Paso a Paso
Muchas empresas están publicando contenidos como la forma de crear...

Artículo Recomendado

3 Tips cruciales para recuperar archivos eliminados
¿Te imaginas perder el trabajo de toda una semana en tan solo unos segundos? Todos hemos pasado por este problema. Quizás eliminamos por error un archivo importante o lo borramos sin pensar que era valioso para otro... más