Ordenar y filtrar resultados de consultas MySQL con PHP, sin saturar la base de datos

• Tweet
• 

El truco: hacer una única consulta MySQL y luego manipular los datos con tranquilidad, utilizando las funciones PHP para manipulación de arrays.

¿Cuántas veces abres y cierras conexiones o ejecutas consultas a MySQL dentro de un bucle solo para mostrar la información de manera “bonita”? ¿Te has puesto a pensar que hacer eso es como hacer tres viajes al supermercado (para comprar alimentos, artículos de limpieza y artículos de papelería) para traer así tus compras ordenadas? Tú no haces tres viajes al super ¿o sí? Pues ¿para qué abrir y cerrar conexiones a la base de datos más de una vez? Créeme: no sólo tú te cansas… MySQL también.

Es frecuente encontrar código fuente como éste:

<?php
$conn = mysql_connect("host","usuario","contraseña");
mysql_select_db("productos",$conn); 

$query_categorias = "SELECT id, categoria FROM categorias ORDER BY categoria ASC";
$result_categorias = mysql_query($query_categorias,$conn);

while($row_categorias = mysql_fetch_array($result_categorias)) {
  print ''.$row_categorias['categoria'].'
';
  $query_prods = "SELECT producto, precio
  FROM productos WHERE categoria=".$row_categorias['id']."
  ORDER BY producto ASC ";
  $result_prods = mysql_query($query_prods, $conn);
  while($row_prods = mysql_fetch_array($result_prods)) {
    print $row_prods['producto'].' ($'.$row_prods['precio'].')
';
  }
  mysql_free_result($result_prods);
}

mysql_free_result($result_categorias);
?>

Podemos hacer algo más complejo, y ésto nos dará la ventaja de:

- Optimizar el rendimiento de MySQL (ya que haremos una única consulta).
- Facilitar la legibilidad del código evitando embeber código HTML en PHP.

¿Cómo haremos ésto? Lo vemos paso a paso:

1) Hacemos una única consulta SQL. Las consultas del ejemplo anterior pueden resumirse en una sóla, obteniendo exactamente la misma cantidad de datos:

$query = " SELECT c.categoria, p.producto, p.precio FROM categorias AS c INNER JOIN productos AS p ON p.categoria = c.id ";

La consulta explicada:

SELECT c.categoria, p.producto, p.precio

Aquí estoy indicando a MySQL que debe retornarme el nombre de la categoría, del producto y su precio.

FROM categorias AS c

Le estoy diciendo a MySQL que debe consultar la tabla llamada categorias y (para escribir menos) le asigno como alias c. Es decir, referirme a la tabla categorias es lo mismo que hacerlo sobre c, MySQL interpretará que estoy haciendo referencia a la misma tabla.

INNER JOIN productos AS p

Le digo a MySQL que “combine/junte internamente” la tabla anterior (categorias) con la tabla productos (a quien le asigno el alias p).

La diferencia entre decir: FROM categorias AS c, productos AS p y FROM categorias AS c INNER JOIN productos AS p es sumamente radical: en la primera forma le pido simplemente datos de ambas tablas, mientras que en la segunda forma le estoy pidiendo a MySQL que relaciones ambas tablas.

ON p.categoria = c.id

Aquí le digo a MySQL cómo debe relacionar ambas tablas. Donde el valor del campo categoria de la tabla producto debe coincidir con el valor del campo id de la tabla categorias.

Si nos fijamos, veremos que le estoy ahorrando a MySQL el trabajo de ordenar los datos.

2) Seguidamente abriremos conexión con la base de datos, ejecutaremos la consulta almacenando todos los resultados en un array, e inmediatamente liberaremos a MySQL de trabajo.

$conn = mysql_connect("host", "usuario", "contraseña");
mysql_select_db("productos", $conn);
$result = mysql_query($query, $conn);
//aquí almaceno todos los resultados en el array $row
while($row[] = mysql_fetch_assoc($result));
mysql_free_result($result);
mysql_close($conn);
array_pop($row);

Con ésto, hemos liberado a MySQL, logrando lo que necesitábamos de ella: los resultados de la consulta. A partir de ahora, manipularemos los datos “sin molestar” a MySQL.

3) Ordenar los datos:

Nuestro array $row será un array multidimensional, semejante al siguiente:

Array (
  [0] => array (
    [categoria] = 'Chocolates',
    [producto] = 'Chocolate Bagley Semi-Amargo',
    [precio] = 9,70
  ),
  [1] => array (
    [categoria] = 'Goma de mascar',
    [producto] = 'Chiclets Beldent menta fuerte',
    [precio] = 2,50
  ),
  [2] => array (
    [categoria] = 'Chocolates',
    [producto] = 'Chocolate Amargo Águila',
    [precio] = 8,25
  ),
)

Es posible ordenar los datos de un array multidimensional gracias a la función de manejo de arrays de PHP array_multisort().

Para ordenar el array en forma ascendente, por categoría y producto, bastará con llamar a la función array_multisort(), pasándole como parámetros nuestro array $row y la constante global de PHP SORT_ASC :

array_multisort($row, SORT_ASC);

Y nuestros resultados ya están ordenados. Si los queremos en orden descendente, la constante a utilizar será SORT_DESC .

Es importante resaltar que, si en vez de utilizar la función array_multisort(), utilizamos sort(), los resultados no se ordenarán como esperamos, ya que utilizará los índices para efectuar el ordenamiento.

4) Filtrar el array (para acomodar los datos de manera “bonita”):

Ésto significa que, tal y como recordarás, nuestro array $row, en cada registro encontrado, repite el nombre de la categoría. Pues haremos que la categoría sea una clave del array $row, cuyo valor sea otro array con el listado de los productos (y así facilitamos el trabajo para crear la vista HTML).

El objetivo es que nuestro array filtrado pase a tener un formato similar al siguiente:

Array (
..[Chocolates] => array (
....array(
....  [producto] = 'Chocolate Bagley Semi-Amargo',
....  [precio] = 9,70
....),
....array(
....  [producto] = 'Chocolate Amargo Águila',
....  [precio] = 8,25
....),
..),
..[Goma de mascar] => array (
....array(
....  [producto] = 'Chiclets Beldent menta fuerte',
....  [precio] = 2,50
....),
..),
)

Nuestra función recibirá sólo dos parámetros:

- $array : nuestro array $row

- $clave_orden : una cadena conteniendo el nombre de la columna por la cual deben agruparse los datos (en nuestro caso será categorias)

Notese que el parámetro $array posee el signo ampersand (&) a la izquierda. Ésto significa que será pasado como referencia, para modificar automáticamente nuestro $row global.

function filtrar_array(&$array, $clave_orden) {
  $array_filtrado = array(); // inicializamos un nuevo array
  // creamos un bucle foreach para recorrer el array original y “acomodar” los datos
  foreach($array as $index=>$array_value) {
    // guardamos temporalmente el nombre de la categoría
    $value = $array_value[$clave_orden];
    // eliminamos la categoria del registro, ya no la necesitaremos
    unset($array_value[$clave_orden]);
    // creamos una clave en nuestro nuevo array, con el nombre de la categoria
    // y como valor le sumamos el array conteniendo producto y precio
    $array_filtrado[$value][] = $array_value;
    /* en cada iteración, si el nombre de la categoría ya figura como clave, será
       sobreescrito y se le agregará como nuevo valor, solo los datos de producto
       y precio. Si la categoria no existe, ahí sí, creará la nueva clave */
  }
  $array = $array_filtrado; // modificamos automáticamente nuestro array global $row
}

5) Finalmente, para acceder a los datos ordenados y filtrados bastará llamar a esta función, y luego recorrer el array para generar la vista HTML.

//llamamos a la función para filtrar los datos
filtrar_array($row, 'categoria');

//recorremos el array para imprimirlo con formato HTML
foreach($row as $categoria=>$productos) {
  print ''.$categoria.'
'; // imprimimos la categoría
  // recorremos los productos
  foreach($productos as $producto) {
    // imprimimos producto y precio
    print $producto['producto'].' ($'.$producto['precio'].')
';
  }
}

Así, hemos llegado al mismo resultado (idéntica vista HTML) que en el ejemplo del principio, habiendo optimizado el rendimiento de MySQL.

En un próximo artículo veremos cómo paginar los resultados desde un array y cómo independizar el código HTML de PHP.

¡Éxitos!