Optimizar las consultas SQL en PHP: Select y Join

La mayoría de nosotros tiene que relacionarse con bases de datos en nuestros proyectos, y SQL es uno de los languajes más utlizados.
SQL (Structured Query Language) es un lenguaje de programación estándar e interactivo para la obtención de información desde una base de datos.

Las consultas toman la forma de un lenguaje de comandos que permite seleccionar, insertar, actualizar, averiguar la ubicación de los datos, y más. También hay una interfaz de programación. Sin embargo, trabajar con SQL en PHP puede ser complicado. Si sus consultas son complejas, tiene que codificarlas como cadenas de texto que pueden ser propensas a errores, y que sufren de problemas de formato.

Hoy os guiaremos a través de la escritura de largas y confusas consultas Select and Join con PHP. Para ayudarnos, utilizaremos el ejemplo proporcionado por Davison que podéis descargar aquí:

Todo lo que tenéis que hacer es copiar y pegar este archivo PHP y guardarlo en vuestro proyecto. El archivo contiene una clase PHP DbQuery con varios métodos/funciones.

Analizemos el contenido.

La clase DbQuery è un generador de queries que nos ayuda a crear consultas SQL. Por ejemplo:

$sql = new DbQuery();
$sql->select('*');
$sql->from('item', 'i');
$sql->where('i.item_id > 1');
$sql->where('i.item_id > 3');
$sql->orderBy('i.item_id');
$sql->limit(5, 10);

echo $sql;

De este código saldrá:

 SELECT * FROM item i WHERE ( i.item_id > 1 ) AND ( i.category_id > 3 ) ORDER BY i.item_id LIMIT 10, 5
  

Métodos principales

__toString() – Generar y obtener la consulta.

build() – Generar y obtener la consulta (devolver un string, cadena de carácteres).

from(string $table, mixed $alias = null) – Define una tabla desde la cláusola FROM .

groupBy(string $fields) – Añade una restricción GROUP BY.

having(string $restriction) – Añade una restricción en la cláusola HAVING (cada restricción estará separada por un AND).

innerJoin(string $table, string $alias = null, string $on = null) – Añade una cláusola INNER JOIN

E.g. $this->innerJoin('product p ON ...').

join(string $join) – Añade una cláusola JOIN

E.g. $this->join('RIGHT JOIN'.DB_PREFIX.'product p ON ...');.

leftJoin(string $table, string $alias = null, string $on = null) – Añade una cláusola LEFT JOIN.

leftOuterJoin(string $table, string $alias = null, string $on = null) – Añade una cláusola LEFT OUTER JOIN.

limit(string $limit, mixed $offset = 0) – Límita los resultados de una consulta.

naturalJoin(string $table, string $alias = null) – Añade una cláusola NATURAL JOIN.

orderBy(string $fields) –Añade una restricción ORDER BY.

select(string $fields) – Agregar campos en la consulta.

where(string $restriction) –Añade una restricción WHERE (cada restricción estará separada por un AND).

A continuación mostreremos algunos ejemplos de cómo utilizar el generador de consultas SQL. Primero empezaremos por conectarnos a nuestra base de datos. Tenéis que tener en cuenta que la constante DB_PREFIX debe estar incluida en el fichero, ya que es necesaria en la clase DbQuery.

require_once dirname(_FILE_) . '/db-query.php';

define( 'DB_HOST', 'localhost' );
define( 'DB_USER', 'root' );
define( 'DB_PASSWORD', 'password' );
define( 'DB_NAME', 'sql_builder' );

/** Must include this constant in your script since it's used by the DbQuery class */
define( 'DB_PREFIX', '' );

error_reporting(E_ALL); 
ini_set('display_errors', 1);

// Create connection
$conn = new mysqli(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME);

// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
} 

Tenéis que pegar todo el código de abajo después de la sección anterior.

Vamos a escribir algunos códigos de ejemplo y haremos un "echo" de la consulta SQL.

SELECT y ORDER BY

$sql = new DbQuery();
$sql->select('*');
$sql->from('item', 'i');
$sql->orderBy('i.item_id');

$result = $conn->query($sql);

if ($result->num_rows > 0) {
    while($row = $result->fetch_assoc()) {
        print_r($row);
    }
}

SELECT * FROM item i ORDER BY i.item_id

WHERE, OR y LIMIT

$sql = new DbQuery();
$sql->select('*');
$sql->from('item', 'i');
$sql->where('i.item_id = 3 OR i.item_id = 5');
$sql->orderBy('i.item_id');
$sql->limit(5, 10);

SELECT * FROM product p WHERE (p.category_id = 3 OR p.category_id = 5) ORDER BY p.product_id LIMIT 10, 5

COUNT y GROUP BY

$sql = new DbQuery();
$sql->select('COUNT(category_id) AS cat_sum, category_id');
$sql->from('product');
$sql->groupBy('category_id');

$result = $conn->query($sql);

if ($result->num_rows > 0) {
    while($row = $result->fetch_assoc()) {
        print_r($row);
    }
}

SELECT COUNT(category_id) AS cat_sum, category_id FROM product GROUP BY category_id

INNER JOIN

/** 
 * Sql Joins
 * INNER JOIN: devuelve las filas cuando hay una coincidencia en ambas tablas.
 * LEFT JOIN: devuelve todas las filas de la tabla de la izquierda, incluso si no hay coincidencias en la tabla de la derecha.
 * RIGHT JOIN: devuelve todas las filas de la tabla derecha, incluso si no hay coincidencias en la tabla izquierda.
 **/
$sql = new DbQuery();
$sql->select('i.item_id, i.name AS item_name, c.name');
$sql->from('item', 'i');
$sql->innerJoin('category', 'c', 'i.category_id = c.category_id');

$result = $conn->query($sql);

if ($result->num_rows > 0) {
    while($row = $result->fetch_assoc()) {
        print_r($row);
    }
}

SELECT i.tem_id, i.name AS item_name, c.name FROM item i INNER JOIN category c ON i.category_id = c.category_id

GROUP BY

$sql = new DbQuery();
$sql->select('COUNT(i.item_id) AS item_sum, i.category_id');
$sql->from('item', 'i');
$sql->having('COUNT(i.category_id) > 5');
$sql->groupBy('category_id');

$result = $conn->query($sql);

if ($result->num_rows > 0) {
    while($row = $result->fetch_assoc()) {
        print_r($row);
    }
}

SELECT COUNT(i.item_id) AS item_sum, i.item_id FROM item i GROUP BY category_id HAVING (COUNT(i.item_id) > 5)

Y eso es todo por hoy. Gracias por leer! Si os gustan los artículos como este, dejad un comentario y compartidlo en las redes.

Feliz desarrollo

Janeth Kent

Licenciada en Bellas Artes y programadora por pasión. Cuando tengo un rato retoco fotos, edito vídeos y diseño cosas. El resto del tiempo escribo en MA-NO WEB DESIGN AND DEVELOPMENT.