3.6 Consultas a bases de datos

Introducción

Las consultas SQL son una de las operaciones más comunes cuando trabajamos con bases de datos. Mediante las consultas SELECT, podemos recuperar datos de las tablas de la base de datos, lo que nos permite mostrar, analizar y manipular los datos según las necesidades de la aplicación.

En esta sección, nos centraremos en las consultas SELECT básicas y cómo podemos utilizarlas en PHP para interactuar con bases de datos. A medida que avanzamos, veremos cómo filtrar y ordenar los resultados de las consultas utilizando cláusulas como WHERE y ORDER BY.

Las consultas SELECT permiten extraer datos de una tabla y trabajar con ellos en nuestro código. Es fundamental aprender cómo filtrar los resultados utilizando la cláusula WHERE, cómo ordenar los resultados con ORDER BY, y cómo usar operadores comunes como AND, OR, IN y BETWEEN.

3.4.1 Consultas SELECT básicas

La instrucción SELECT es la más utilizada en SQL para obtener datos de una base de datos. Es importante entender cómo estructurarla de manera eficiente para obtener solo los datos necesarios, lo que mejora el rendimiento de la consulta.

Sintaxis básica de un SELECT:

SELECT columna1, columna2, ...
FROM nombre_de_tabla;

• SELECT: Especifica las columnas que queremos seleccionar.
• FROM: Indica la tabla de la que vamos a obtener los datos.

Ejemplo de consulta básica:

Vamos a hacer una consulta básica a la tabla employees de la base de datos employees para obtener los nombres y apellidos de los empleados.

Ejemplo de consulta básica con MySQLi (orientado a objetos):

En este ejemplo vamos a realizar una consulta para edtraer los id, nombres y apellidos de los empleados de la tabla employees utilizando MySQLi.

Ejemplo de consulta básica

MySQLi(OO)MySQLi(proc)

<?php
// Crear conexión
$conexion = new mysqli("localhost", "root", "", "employees");

// Comprobar conexión
if ($conexion->connect_error) {
    die("Conexión fallida: " . $conexion->connect_error);
}

// Consulta para obtener los nombres y apellidos de los empleados
$query = "SELECT emp_no, first_name, last_name FROM employees";
$resultado = $conexion->query($query);

// Mostrar los resultados
while ($fila = $resultado->fetch_assoc()) {
    echo "Nombre: " . $fila['emp_no'] . $fila['first_name'] . " - Apellido: " . $fila['last_name'] . "<br>";
}

// Cerrar la conexión
$conexion->close();
?>

<?php
// Crear conexión
$conexion = mysqli_connect("localhost", "root", "", "employees");

// Comprobar conexión
if (!$conexion) {
    die("Conexión fallida: " . mysqli_connect_error());
}

// Consulta para obtener los nombres y apellidos de los empleados
$query = "SELECT emp_no, first_name, last_name FROM employees";
$resultado = mysqli_query($conexion, $query);

// Mostrar los resultados
while ($fila = mysqli_fetch_assoc($resultado)) {
    echo "Nombre: " . $fila['emp_no'] . $fila['first_name'] . " - Apellido: " . $fila['last_name'] . "<br>";
}

// Cerrar la conexión
mysqli_close($conexion);
?>

Vamos a analizar el código y cer qué realiza cada método o función. Lo hacemos para la version orientada a objetos de MySQLi, la procedimental es similar.

1. Crear conexión: Se establece una conexión a la base de datos utilizando new mysqli(), donde se especifican el host, el usuario, la contraseña y el nombre de la base de datos.
2. Comprobar conexión: Se verifica si la conexión fue exitosa. Si no, se muestra un mensaje de error y se detiene la ejecución del script.
3. Consulta: Se define la consulta SQL que queremos ejecutar. En este caso, seleccionamos los campos emp_no, first_name y last_name de la tabla employees. En este caso la consulta no tiene parámetros, más adelante veremos cómo pasarlos.
4. Ejecutar consulta: Se ejecuta la consulta utilizando $conexion->query($query), que devuelve un objeto de resultado $resultado.
5. Mostrar resultados: Se itera sobre los resultados utilizando un bucle while, y se accede a cada fila de resultados con $fila = $resultado->fetch_assoc(). Luego, se muestran los nombres y apellidos de los empleados. Cada vez que llamamos a fetch_assoc(), obtenemos una fila de resultados en forma de array asociativo, y pasamos a el objeto $resultado apunta a la siguiente fila. Cuando no hay más filas, fetch_assoc() devuelve false y el bucle se detiene. Otra opciones:
   • fetch_row(): Devuelve un array numérico.
   • fetch_array(): Devuelve un array asociativo y numérico.
   • fetch_object(): Devuelve un objeto con propiedades que corresponden a los nombres de las columnas.
6. Cerrar conexión: Finalmente, se cierra la conexión a la base de datos utilizando $conexion->close().

Visto de esta manera, es como si para cada consulta objetenmos un array de registros. Donde cada elemento del array es una registro (fila) de la consulta. Podemos acceder a cada campo del registro como si fuera un elemento del array, utilizando el nombre de la columna como índice.

Ejemplo de consulta básica con PDO:

Ejemplo de consulta básica con PDO

PDO

<?php
try {
    // Crear conexión
    $conexion = new PDO("mysql:host=localhost;dbname=employees", "root", "");
    $conexion->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    // Consulta para obtener los nombres y apellidos de los empleados
    $query = "SELECT first_name, last_name FROM employees";
    $resultado = $conexion->query($query);

    // Mostrar los resultados
    while ($fila = $resultado->fetch(PDO::FETCH_ASSOC)) {
        echo "Nombre: " . $fila['first_name'] . " - Apellido: " . $fila['last_name'] . "<br>";
    }

} catch (PDOException $e) {
    echo "Conexión fallida: " . $e->getMessage();
}

$conexion = null;
?>

En este ejemplo, utilizamos PDO para conectarnos a la base de datos y realizar la consulta. La estructura es similar a la de MySQLi, pero con algunas diferencias en la forma de manejar la conexión y los errores.

1. Crear conexión: Se establece una conexión a la base de datos utilizando new PDO(), donde se especifican el DSN (Data Source Name), el usuario y la contraseña.
2. Comprobar conexión: Se utiliza un bloque try-catch para manejar excepciones. Si la conexión falla, se lanza una excepción y se muestra un mensaje de error.
3. Consulta: Se define la consulta SQL que queremos ejecutar. En este caso, seleccionamos los campos first_name y last_name de la tabla employees.
4. Ejecutar consulta: Se ejecuta la consulta utilizando $conexion->query($query), que devuelve un objeto de resultado $resultado.
5. Mostrar resultados: Se itera sobre los resultados utilizando un bucle while, y se accede a cada fila de resultados con $fila = $resultado->fetch(PDO::FETCH_ASSOC). Luego, se muestran los nombres y apellidos de los empleados. Otras opciones de fetch() permiten obtener los resultados en diferentes formatos:
   • PDO::FETCH_ASSOC: Devuelve un array asociativo.
   • PDO::FETCH_NUM: Devuelve un array numérico.
   • PDO::FETCH_BOTH: Devuelve un array asociativo y numérico (por defecto).
6. Cerrar conexión: Finalmente, se cierra la conexión a la base de datos estableciendo $conexion a null.

---

Filtrar resultados con WHERE

El WHERE se utiliza para filtrar los resultados y obtener solo aquellos que cumplan una condición específica. Esto es útil cuando necesitas recuperar datos que cumplen ciertos criterios.

Sintaxis de WHERE:

SELECT columna1, columna2, ...
FROM nombre_de_tabla
WHERE condicion;

Ejemplo de consulta con WHERE:

Queremos obtener los empleados que tiene el id 5000.

<?php
$query = "SELECT emp_no, first_name, last_name, salary FROM employees WHERE emp_no = 5000";

Ejemplo con MySQLi (orientado a objetos):

Ejemplo con WHERE

MySQLi(OO)MySQLi(proc)

<?php
$conexion = new mysqli("localhost", "root", "", "employees");
if ($conexion->connect_error) {
    die("Conexión fallida: " . $conexion->connect_error);
}
$stmt = $conexion->prepare("SELECT emp_no, first_name, last_name FROM employees WHERE emp_no = ?");
$stmt->bind_param("i", $emp_no);
$emp_no = 5000;
$stmt->execute();
$resultado = $stmt->get_result();
while ($fila = $resultado->fetch_assoc()) {
    echo "Nombre: " . $fila['first_name'] . " - Apellido: " . $fila['last_name'] . "<br>";
}
$stmt->close();
$conexion->close(); 
?>

<?php
$conexion = mysqli_connect("localhost", "root", "", "employees");
if (!$conexion) {
    die("Conexión fallida: " . mysqli_connect_error());
}
$emp_no = 5000;
$query = "SELECT emp_no, first_name, last_name FROM employees WHERE emp_no = $emp_no";
$resultado = mysqli_query($conexion, $query);   
while ($fila = mysqli_fetch_assoc($resultado)) {
    echo "Nombre: " . $fila['first_name'] . " - Apellido: " . $fila['last_name'] . "<br>";
}
mysqli_close($conexion);
?>

En este ejemplo, utilizamos una consulta preparada para evitar inyecciones SQL. La consulta se prepara con un marcador de posición ?, y luego se vincula el valor del parámetro utilizando bind_param().

Ejemplo con PDO:

Ejemplo de WHERE en PDO

PDO

<?php
try {
    $conexion = new PDO("mysql:host=localhost;dbname=employees", "root", "");
    $conexion->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    $query = "SELECT emp_no, first_name, last_name FROM employees WHERE emp_no = :emp_no";
    $stmt = $conexion->prepare($query);
    $stmt->bindParam(':emp_no', $emp_no);
    $emp_no = 5000;
    $stmt->execute();

    while ($fila = $stmt->fetch(PDO::FETCH_ASSOC)) {
        echo "Nombre: " . $fila['first_name'] . " - Apellido: " . $fila['last_name'] . "<br>";
    }

} catch (PDOException $e) {
    echo "Conexión fallida: " . $e->getMessage();
}

$conexion = null;
?>

En este ejemplo, utilizamos un marcador de posición :emp_no en la consulta y vinculamos el valor utilizando bindParam(). Esto ayuda a prevenir inyecciones SQL y mejora la seguridad de la aplicación.

---

Resumen de la sección 3.4.1

• Hemos cubierto la sintaxis básica de una consulta SELECT.
• Hemos aprendido a filtrar resultados utilizando la cláusula WHERE.
• Se presentaron ejemplos con MySQLi (procedimental y orientado a objetos) y PDO.
• Los ejercicios prácticos permitirán a los estudiantes practicar consultas básicas y filtrado de datos.

---

Ejercicios prácticos:

Estos ejercicios debes solucionarlos con MySQLi (OO) y PDO. Puedes elegir el que prefieras, pero es recomendable que practiques ambos. Recuerda que puedes utilizar la base de datos employees para realizar las consultas.

1. Ejercicio 1: Crea un formulario que permita al usuario ingresar un emp_no y muestre el nombre y apellido del empleado correspondiente.
   • Pista: Utiliza una consulta preparada para evitar inyecciones SQL.
   • Ejemplo de consulta: SELECT first_name, last_name FROM employees WHERE emp_no = ?
2. Ejercicio 2: Crea un formulario que pida el nombre de un empleado y muestre todos los empleados que contengan ese nombre en su first_name o en su last_name.
   • Pista: Utiliza el operador LIKE para buscar coincidencias parciales.
   • Ejemplo de consulta: SELECT first_name, last_name FROM employees WHERE first_name LIKE '%nombre%'
3. Ejercicio 3: Crea un formulario que permita al usuario ingresar un rango de fechas y muestre todos los empleados contratados dentro de ese rango.
   • Pista: Utiliza la cláusula BETWEEN para filtrar por fechas.
   • Ejemplo de consulta: SELECT first_name, last_name FROM employees WHERE hire_date BETWEEN 'fecha_inicio' AND 'fecha_fin'

---

3.4.2 Consultas complejas

Uso de JOIN para relaciones entre tablas

Las consultas complejas son aquellas que involucran múltiples tablas y relaciones entre ellas. En este caso, utilizamos JOIN para combinar datos de diferentes tablas en una sola consulta. Aunque no es un asunto a tratar en este módulo, decir, que las cosultas multiplataforma de pueden realizar básicamente de dos maneras:

• FROM Se especifican las tablas necesarias separadas por comas, luego en el WHERE se especifican las condiciones de unión (normalmente las claves ajenas).
• JOIN Se especifican las tablas necesarias y se utilizan las cláusulas JOIN, para definir cómo se deben unir las tablas.
  • INNER JOIN: Devuelve solo las filas que tienen coincidencias en ambas tablas.
  • LEFT JOIN: Devuelve todas las filas de la tabla izquierda y las filas coincidentes de la tabla derecha. Si no hay coincidencia, se devuelven valores nulos para la tabla derecha.
  • RIGHT JOIN: Devuelve todas las filas de la tabla derecha y las filas coincidentes de la tabla izquierda. Si no hay coincidencia, se devuelven valores nulos para la tabla izquierda.

Ejemplo de INNER JOIN con MySQLi utilizando parámetros:

En este ejmplo vamos a realizar una consulta que une varias tablas para obtener información relacionada. Vamos a obtener el nombre y apellido de los empleados junto con el nombre de su departamento. Parametrizaremos la consulta para que el usuario pueda filtrar por un dept_no específico.

Ejemplo de INNER JOIN - Parametrizado

MySQLi(OO)MySQLi(proc)

    <?php
    $conexion = new mysqli("localhost", "root", "", "employees");

    if ($conexion->connect_error) {
        die("Conexión fallida: " . $conexion->connect_error);
    }

    // Parámetro de ejemplo
    $departamento_id = 5;

    // Consulta con INNER JOIN y parámetros
    $query = "SELECT employees.first_name, employees.last_name, departments.dept_name 
            FROM employees
            INNER JOIN dept_emp ON employees.emp_no = dept_emp.emp_no
            INNER JOIN departments ON dept_emp.dept_no = departments.dept_no
            WHERE departments.dept_no = ?";

    $stmt = $conexion->prepare($query);
    $stmt->bind_param("i", $departamento_id); // "i" indica que el parámetro es un entero
    $stmt->execute();
    $resultado = $stmt->get_result();

    while ($fila = $resultado->fetch_assoc()) {
        echo "Nombre: " . $fila['first_name'] . " - Apellido: " . $fila['last_name'] . " - Departamento: " . $fila['dept_name'] . "<br>";
    }

    $stmt->close();
    $conexion->close();
    ?>

<?php
$conexion = mysqli_connect("localhost", "root", "", "employees");
if (!$conexion) {
    die("Conexión fallida: " . mysqli_connect_error());
}
// Parámetro de ejemplo
$departamento_id = 5;
// Consulta con INNER JOIN y parámetros
$query = "SELECT employees.first_name, employees.last_name, departments.dept_name 
        FROM employees
        INNER JOIN dept_emp ON employees.emp_no = dept_emp.emp_no
        INNER JOIN departments ON dept_emp.dept_no = departments.dept_no
        WHERE departments.dept_no = $departamento_id";
$resultado = mysqli_query($conexion, $query);
while ($fila = mysqli_fetch_assoc($resultado)) {
    echo "Nombre: " . $fila['first_name'] . " - Apellido: " . $fila['last_name'] . " - Departamento: " . $fila['dept_name'] . "<br>";
}
mysqli_close($conexion);
?>

En este ejemplo, aunque l la consulta es más compleja, la estructura es similar a la de una consulta básica. La diferencia principal es que estamos utilizando INNER JOIN para combinar datos de varias tablas.

• INNER JOIN: Se utiliza para combinar filas de dos o más tablas basadas en una condición de coincidencia. En este caso, estamos uniendo la tabla employees con dept_emp y departments para obtener información relacionada.
• ON: Especifica la condición de coincidencia entre las tablas. En este caso, estamos uniendo employees.emp_no con dept_emp.emp_no y dept_emp.dept_no con departments.dept_no. Normalmente se utilizan las claves ajenas para unir las tablas.
• WHERE: Se utiliza para filtrar los resultados según una condición específica. En este caso, estamos filtrando por departments.dept_no.
• ?: Es un marcador de posición que se utiliza en consultas preparadas. Se reemplaza por el valor del parámetro al ejecutar la consulta.

---

Ejemplo de INNER JOIN con PDO utilizando parámetros:

Ejemplo de INNER JOIN con PDO - Parametrizado

<?php
try {
    $conexion = new PDO("mysql:host=localhost;dbname=employees", "root", "");
    $conexion->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    // Parámetro de ejemplo
    $departamento_id = 5;

    // Consulta con INNER JOIN y parámetros
    $query = "SELECT employees.first_name, employees.last_name, departments.dept_name 
                FROM employees
                INNER JOIN dept_emp ON employees.emp_no = dept_emp.emp_no
                INNER JOIN departments ON dept_emp.dept_no = departments.dept_no
                WHERE departments.dept_no = :dept_no";

    $stmt = $conexion->prepare($query);
    $stmt->bindParam(':dept_no', $departamento_id, PDO::PARAM_INT);
    $stmt->execute();

    while ($fila = $stmt->fetch(PDO::FETCH_ASSOC)) {
        echo "Nombre: " . $fila['first_name'] . " - Apellido: " . $fila['last_name'] . " - Departamento: " . $fila['dept_name'] . "<br>";
}

} catch (PDOException $e) {
    echo "Conexión fallida: " . $e->getMessage();
}

$conexion = null;
?>

La explicación es similar a la de MySQLi. La diferencia principal es que estamos utilizando bindParam() para vincular el parámetro :dept_no a la consulta.

• prepare(): Prepara la consulta SQL para su ejecución. Esto ayuda a prevenir inyecciones SQL al separar la consulta de los datos.
• bindParam(): Se utiliza para vincular un parámetro a una variable. En este caso, estamos vinculando el parámetro :dept_no a la variable $departamento_id, que contiene el valor del departamento que queremos filtrar.
  • PDO::PARAM_INT: Especifica que el parámetro es un entero. Esto ayuda a PDO a manejar el tipo de dato correctamente. Otros tipos de parámetros:
    • PDO::PARAM_STR: Especifica que el parámetro es una cadena.
    • PDO::PARAM_BOOL: Especifica que el parámetro es un booleano.
    • PDO::PARAM_NULL: Especifica que el parámetro es nulo.
    • PDO::PARAM_LOB: Especifica que el parámetro es un objeto LOB (Large Object), como un archivo o una imagen.
• bindValue(): Se utiliza para vincular un valor a un parámetro. En este caso, estamos vinculando el parámetro :dept_no al valor de $departamento_id. La diferencia es que bindParam() vincula la variable por referencia, mientras que bindValue() vincula el valor directamente.

---

Uso de LEFT JOIN

Ejemplo de LEFT JOIN con MySQLi (orientado a objetos) utilizando parámetros:

Ejemplo de LEFT JOIN con MySQLi - Parametrizado

MySQLi(OO)MySQLi(proc)

<?php
$conexion = new mysqli("localhost", "root", "", "employees");

if ($conexion->connect_error) {
    die("Conexión fallida: " . $conexion->connect_error);
}

// Parámetro de ejemplo
$empleado_id = 3;

// Consulta con LEFT JOIN y parámetros
$query = "SELECT employees.first_name, employees.last_name, departments.dept_name 
        FROM employees
        LEFT JOIN dept_emp ON employees.emp_no = dept_emp.emp_no
        LEFT JOIN departments ON dept_emp.dept_no = departments.dept_no
        WHERE employees.emp_no = ?";

$stmt = $conexion->prepare($query);
$stmt->bind_param("i", $empleado_id); // "i" indica que el parámetro es un entero
$stmt->execute();
$resultado = $stmt->get_result();

while ($fila = $resultado->fetch_assoc()) {
    echo "Nombre: " . $fila['first_name'] . " - Apellido: " . $fila['last_name'] . " - Departamento: " . ($fila['dept_name'] ?? 'N/A') . "<br>";
}

$stmt->close();
$conexion->close();
?>

<?php
$conexion = mysqli_connect("localhost", "root", "", "employees");
if (!$conexion) {
    die("Conexión fallida: " . mysqli_connect_error());
}
// Parámetro de ejemplo
$empleado_id = 3;
// Consulta con LEFT JOIN y parámetros
$query = "SELECT employees.first_name, employees.last_name, departments.dept_name 
        FROM employees
        LEFT JOIN dept_emp ON employees.emp_no = dept_emp.emp_no
        LEFT JOIN departments ON dept_emp.dept_no = departments.dept_no
        WHERE employees.emp_no = $empleado_id";
$resultado = mysqli_query($conexion, $query);
while ($fila = mysqli_fetch_assoc($resultado)) {
    echo "Nombre: " . $fila['first_name'] . " - Apellido: " . $fila['last_name'] . " - Departamento: " . ($fila['dept_name'] ?? 'N/A') . "<br>";
}
mysqli_close($conexion);
?>

En este ejemplo, utilizamos LEFT JOIN para obtener todos los empleados, incluso aquellos que no están asignados a un departamento. Esto es útil cuando queremos mostrar todos los empleados, independientemente de si tienen un departamento asociado o no.

---

Ejemplo de LEFT JOIN con PDO utilizando parámetros:

Ejemplo de LEFT JOIN con PDO - Parametrizado

PDO

<?php
try {
    $conexion = new PDO("mysql:host=localhost;dbname=employees", "root", "");
    $conexion->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    // Parámetro de ejemplo
    $empleado_id = 3;

    // Consulta con LEFT JOIN y parámetros
    $query = "SELECT employees.first_name, employees.last_name, departments.dept_name 
            FROM employees
            LEFT JOIN dept_emp ON employees.emp_no = dept_emp.emp_no
            LEFT JOIN departments ON dept_emp.dept_no = departments.dept_no
            WHERE employees.emp_no = :emp_no";

    $stmt = $conexion->prepare($query);
    $stmt->bindParam(':emp_no', $empleado_id, PDO::PARAM_INT);
    $stmt->execute();

    while ($fila = $stmt->fetch(PDO::FETCH_ASSOC)) {
        echo "Nombre: " . $fila['first_name'] . " - Apellido: " . $fila['last_name'] . " - Departamento: " . ($fila['dept_name'] ?? 'N/A') . "<br>";
    }

} catch (PDOException $e) {
    echo "Conexión fallida: " . $e->getMessage();
}

$conexion = null;
?>

---

Uso de GROUP BY y HAVING

Ejemplo de GROUP BY con MySQLi utilizando parámetros:

Ejemplo de GROUP BY con MySQLi - Parametrizado

MySQLi(OO)MySQLi(proc)

<?php
$conexion = new mysqli("localhost", "root", "", "employees");

if ($conexion->connect_error) {
    die("Conexión fallida: " . $conexion->connect_error);
}

// Parámetro de ejemplo
$salario_minimo = 50000;

// Consulta con GROUP BY y HAVING
$query = "SELECT departments.dept_name, COUNT(*) AS num_employees
        FROM employees
        INNER JOIN dept_emp ON employees.emp_no = dept_emp.emp_no
        INNER JOIN departments ON dept_emp.dept_no = departments.dept_no
        GROUP BY departments.dept_name
        HAVING COUNT(*) > ?";

$stmt = $conexion->prepare($query);
$stmt->bind_param("i", $salario_minimo); // "i" indica que el parámetro es un entero
$stmt->execute();
$resultado = $stmt->get_result();

while ($fila = $resultado->fetch_assoc()) {
    echo "Departamento: " . $fila['dept_name'] . " - Número de empleados: " . $fila['num_employees'] . "<br>";
}

$stmt->close();
$conexion->close();
?>

<?php
$conexion = mysqli_connect("localhost", "root", "", "employees");
if (!$conexion) {
    die("Conexión fallida: " . mysqli_connect_error());
}
// Parámetro de ejemplo
$salario_minimo = 50000;
// Consulta con GROUP BY y HAVING
$query = "SELECT departments.dept_name, COUNT(*) AS num_employees
        FROM employees
        INNER JOIN dept_emp ON employees.emp_no = dept_emp.emp_no
        INNER JOIN departments ON dept_emp.dept_no = departments.dept_no
        GROUP BY departments.dept_name
        HAVING COUNT(*) > $salario_minimo";
$resultado = mysqli_query($conexion, $query);
while ($fila = mysqli_fetch_assoc($resultado)) {
    echo "Departamento: " . $fila['dept_name'] . " - Número de empleados: " . $fila['num_employees'] . "<br>";
}
mysqli_close($conexion);
?>

En este ejemplo, utilizamos GROUP BY para agrupar los resultados por departamento y HAVING para filtrar los grupos que tienen más de un cierto número de empleados. Esto es útil cuando queremos obtener estadísticas o resúmenes de datos.

• GROUP BY: Se utiliza para agrupar filas que tienen valores idénticos en columnas específicas. En este caso, estamos agrupando por departments.dept_name.
• COUNT(*): Se utiliza para contar el número de filas en cada grupo. En este caso, estamos contando el número de empleados en cada departamento.
• HAVING: Se utiliza para filtrar los grupos después de que se han creado. En este caso, estamos filtrando los grupos que tienen más de un cierto número de empleados.
• ?: Es un marcador de posición que se utiliza en consultas preparadas. Se reemplaza por el valor del parámetro al ejecutar la consulta.

---

Ejemplo de GROUP BY con PDO utilizando parámetros:

Ejemplo de GROUP BY con PDO - Parametrizado

PDO

<?php
try {
    $conexion = new PDO("mysql:host=localhost;dbname=employees", "root", "");
    $conexion->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    // Parámetro de ejemplo
    $salario_minimo = 50000;

    // Consulta con GROUP BY y HAVING
    $query = "SELECT departments.dept_name, COUNT(*) AS num_employees
            FROM employees
            INNER JOIN dept_emp ON employees.emp_no = dept_emp.emp_no
            INNER JOIN departments ON dept_emp.dept_no = departments.dept_no
            GROUP BY departments.dept_name
            HAVING COUNT(*) > :num_employees";

    $stmt = $conexion->prepare($query);
    $stmt->bindParam(':num_employees', $salario_minimo, PDO::PARAM_INT);
    $stmt->execute();

    while ($fila = $stmt->fetch(PDO::FETCH_ASSOC)) {
        echo "Departamento: " . $fila['dept_name'] . " - Número de empleados: " . $fila['num_employees'] . "<br>";
    }

} catch (PDOException $e) {
    echo "Conexión fallida: " . $e->getMessage();
}

$conexion = null;
?>

---

Resumen de la sección 3.4.2

En este punto, hemos cubierto cómo realizar consultas complejas utilizando JOINs para combinar datos de múltiples tablas y GROUP BY y HAVING para agrupar y filtrar los resultados. También hemos aprendido cómo parametrizar estas consultas para mejorar la seguridad y evitar SQL Injection.

---

Ejercicios prácticos:

1. Ejercicio 1: Realiza una consulta parametrizada que muestre el nombre de los empleados en activo junto con el nombre de su departamento, filtrado por un dept_no específico. Los empleados que están en activo deben tener el campo to_date en la tabla dept_emp como 9999-01-01.
2. Ejercicio 2: Realiza una consulta parametrizada utilizando LEFT JOIN para mostrar todos los departamentos y el número de empleados en cada uno. Si un departamento no tiene empleados, debe mostrarse con un conteo de 0.
3. Ejercicio 3: Realiza una consulta parametrizada que cuente cuántos empleados hay en cada departamento, mostrando solo aquellos con más de 5 empleados.
4. Ejercicio 4: Realiza una consulta parametrizada con GROUP BY y HAVING para mostrar los departamentos con más de 10 empleados.

---

3.4.3 Ejemplos prácticos

Ejemplo 1: Mostrar todos los empleados de un departamento

En este ejemplo, vamos a realizar una consulta que muestre todos los empleados de un departamento específico. Utilizaremos un INNER JOIN para combinar la información de la tabla employees y departments.

Ejemplo de código con MySQLi (orientado a objetos):

Ejemplo 1: Mostrar empleados de un departamento

MySQLi(OO)PDO

<?php
$conexion = new mysqli("localhost", "root", "", "employees");

if ($conexion->connect_error) {
    die("Conexión fallida: " . $conexion->connect_error);
}

// Parámetro de ejemplo
$departamento_id = 5;

// Consulta con INNER JOIN
$query = "SELECT employees.first_name, employees.last_name, departments.dept_name 
        FROM employees
        INNER JOIN dept_emp ON employees.emp_no = dept_emp.emp_no
        INNER JOIN departments ON dept_emp.dept_no = departments.dept_no
        WHERE departments.dept_no = ?";

$stmt = $conexion->prepare($query);
$stmt->bind_param("i", $departamento_id); // "i" indica que el parámetro es un entero
$stmt->execute();
$resultado = $stmt->get_result();

while ($fila = $resultado->fetch_assoc()) {
    echo "Nombre: " . $fila['first_name'] . " - Apellido: " . $fila['last_name'] . " - Departamento: " . $fila['dept_name'] . "<br>";
}

$stmt->close();
$conexion->close();
?>

    <?php
    try {
        $conexion = new PDO("mysql:host=localhost;dbname=employees", "root", "");
        $conexion->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

        // Parámetro de ejemplo
        $departamento_id = 5;

        // Consulta con INNER JOIN
        $query = "SELECT employees.first_name, employees.last_name, departments.dept_name 
                FROM employees
                INNER JOIN dept_emp ON employees.emp_no = dept_emp.emp_no
                INNER JOIN departments ON dept_emp.dept_no = departments.dept_no
                WHERE departments.dept_no = :dept_no";

        $stmt = $conexion->prepare($query);
        $stmt->bindParam(':dept_no', $departamento_id, PDO::PARAM_INT);
        $stmt->execute();

        while ($fila = $stmt->fetch(PDO::FETCH_ASSOC)) {
            echo "Nombre: " . $fila['first_name'] . " - Apellido: " . $fila['last_name'] . " - Departamento: " . $fila['dept_name'] . "<br>";
        }

    } catch (PDOException $e) {
        echo "Conexión fallida: " . $e->getMessage();
    }

    $conexion = null;
    ?>

---

Ejemplo 2: Ordenar empleados por salario

En este ejemplo, vamos a ordenar a los empleados por su salario. Utilizaremos la cláusula ORDER BY para ordenar los resultados de la consulta.

Ejemplo 2: Ordenar empleados por salario

MySQLi(OO)PDO

<?php
$conexion = new mysqli("localhost", "root", "", "employees");

if ($conexion->connect_error) {
    die("Conexión fallida: " . $conexion->connect_error);
}

// Consulta con ORDER BY
$query = "SELECT first_name, last_name, salary FROM employees 
        ORDER BY salary DESC";

$resultado = $conexion->query($query);

while ($fila = $resultado->fetch_assoc()) {
    echo "Nombre: " . $fila['first_name'] . " - Apellido: " . $fila['last_name'] . " - Salario: " . $fila['salary'] . "<br>";
}

$conexion->close();
?>

<?php
try {
    $conexion = new PDO("mysql:host=localhost;dbname=employees", "root", "");
    $conexion->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    // Consulta con ORDER BY
    $query = "SELECT first_name, last_name, salary FROM employees 
            ORDER BY salary DESC";

    $stmt = $conexion->query($query);

    while ($fila = $stmt->fetch(PDO::FETCH_ASSOC)) {
        echo "Nombre: " . $fila['first_name'] . " - Apellido: " . $fila['last_name'] . " - Salario: " . $fila['salary'] . "<br>";
    }

} catch (PDOException $e) {
    echo "Conexión fallida: " . $e->getMessage();
}

$conexion = null;
?>

---

Ejemplo 3: Filtrar empleados por fecha de contratación

En este ejemplo, vamos a mostrar los empleados contratados después de una fecha específica utilizando la cláusula WHERE.

Ejemplo de código con MySQLi (orientado a objetos):

Ejemplo 3: Filtrar empleados por fecha de contratación

MySQLi(OO)PDOMySQLi(proc)

<?php
$conexion = new mysqli("localhost", "root", "", "employees");

if ($conexion->connect_error) {
    die("Conexión fallida: " . $conexion->connect_error);
}

// Parámetro de ejemplo
$fecha_contratacion = '2015-01-01';

// Consulta con WHERE
$query = "SELECT first_name, last_name, hire_date FROM employees 
        WHERE hire_date > ?";

$stmt = $conexion->prepare($query);
$stmt->bind_param("s", $fecha_contratacion); // "s" indica que el parámetro es una cadena
$stmt->execute();
$resultado = $stmt->get_result();

while ($fila = $resultado->fetch_assoc()) {
    echo "Nombre: " . $fila['first_name'] . " - Apellido: " . $fila['last_name'] . " - Fecha de contratación: " . $fila['hire_date'] . "<br>";
}

$stmt->close();
$conexion->close();
?>

<?php
try {
    $conexion = new PDO("mysql:host=localhost;dbname=employees", "root", "");
    $conexion->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    // Parámetro de ejemplo
    $fecha_contratacion = '2015-01-01';

    // Consulta con WHERE
    $query = "SELECT first_name, last_name, hire_date FROM employees 
            WHERE hire_date > :hire_date";

    $stmt = $conexion->prepare($query);
    $stmt->bindParam(':hire_date', $fecha_contratacion);
    $stmt->execute();

    while ($fila = $stmt->fetch(PDO::FETCH_ASSOC)) {
        echo "Nombre: " . $fila['first_name'] . " - Apellido: " . $fila['last_name'] . " - Fecha de contratación: " . $fila['hire_date'] . "<br>";
    }

} catch (PDOException $e) {
    echo "Conexión fallida: " . $e->getMessage();
}

$conexion = null;
?>

<?php
$conexion = mysqli_connect("localhost", "root", "", "employees");
if (!$conexion) {
    die("Conexión fallida: " . mysqli_connect_error());
}
// Parámetro de ejemplo
$fecha_contratacion = '2015-01-01';
// Consulta con WHERE
$query = "SELECT first_name, last_name, hire_date FROM employees 
        WHERE hire_date > '$fecha_contratacion'";
$resultado = mysqli_query($conexion, $query);
while ($fila = mysqli_fetch_assoc($resultado)) {
    echo "Nombre: " . $fila['first_name'] . " - Apellido: " . $fila['last_name'] . " - Fecha de contratación: " . $fila['hire_date'] . "<br>";
}
mysqli_close($conexion);
?>

En este ejemplo, utilizamos la cláusula WHERE para filtrar los empleados contratados después de una fecha específica. La consulta se parametriza para evitar inyecciones SQL. - hire_date: Es la columna que contiene la fecha de contratación de los empleados.

---

Resumen de la sección 3.4.3

En este punto, hemos cubierto ejemplos prácticos de consultas SELECT que incluyen:

• Obtener todos los empleados de un departamento.
• Ordenar los empleados por salario.
• Filtrar empleados por fecha de contratación.

Cada ejemplo utiliza consultas parametrizadas para garantizar la seguridad y evitar problemas como SQL Injection.

---

Ejercicios prácticos:

Para este ejercicio trata de crear un fichero php que contenga todas las funciones que necesitarás pera resolver el ejercicio. De esta manera en el fichero php que contenga la vista (HTML) solo tendrás que incluir el fichero de funciones y llamar a las funciones necesarias. Para incluir el fichero de funciones puedes utilizar la función include o require de PHP.

• include: Este comando se utiliza para incluir el contenido de un archivo en otro archivo PHP. Si el archivo no se encuentra, se mostrará una advertencia, pero el script continuará ejecutándose.
• require: Este comando también se utiliza para incluir el contenido de un archivo en otro archivo PHP. Sin embargo, si el archivo no se encuentra, se generará un error fatal y el script se detendrá.

Ejemplo de uso:

<?php 
include 'funciones.php'; // Incluye el archivo funciones.php
require 'funciones.php'; // Requiere el archivo funciones.php
?>

1. Crea un formulario que permita al usuario elegir un departamento y una fecha de contratación.
2. Muestra todos los empleados de ese departamento que fueron contratados después de la fecha seleccionada y que estén activos.
3. Muestra también el nombre del responsable actual del departamento.
4. Discursión: En este ejercicio debes realizar diferentes consultas, podemos plantearnos utilizar una única conexión para todas las consultas o crear una conexión para cada consulta. ¿Qué ventajas e inconvenientes tiene cada opción? ¿Qué harías tú?

---