Consulta a la base de datos MYSQL a través de la extensión del controlador MySQLi en PHP

Hay varias extensiones de controladores que nos permiten consultar MySQL. MySQLi es uno de ellos.
Para usar MySQLi en la consola, debemos asegurarnos de tener instalada la CLI de PHP y la extensión del controlador mysql:

sudo apt-get -y install php7.0-cli php7.0-mysql

Tenga en cuenta que el nombre de la extensión carece del sufijo i. Una vez que se instala la extensión del controlador mysql, podemos continuar y comenzar a consultar el servidor MySQL.

Conectando

Podemos usar las funciones o clases de MySQLi para interactuar con MySQL. En el espíritu de OOP, utilizaremos el enfoque de clase para todos nuestros ejemplos. Usando la clase mysqli, podemos establecer una conexión MySQL desde PHP, de la siguiente manera:

$mysqli = new mysqli('127.0.0.1', 'root', 'mL08e!Tq', 'sakila');

Esta expresión de línea única buscará MySQL en el host 127.0.0.1 e intentará conectarse a su base de datos sakila utilizando el nombre de usuario root y mL08e!Tq como contraseña.

Manejo de errores

El manejo de errores alrededor de mysqli es relativamente fácil ya que podemos usar un simple bloque try…catch, de la siguiente manera:

<?php
mysqli_report(MYSQLI_REPORT_ALL);
try {
$mysqli = new mysqli('127.0.0.1', 'root', 'mL08e!Tq', 'sakila');
} catch (Throwable $t) {
exit($t->getMessage());
}

Idealmente, nos gustaría usar mysqli_sql_exception para un manejo más orientado solo de excepciones de MySQL:

<?php
mysqli_report(MYSQLI_REPORT_ALL);
try {
$mysqli = new mysqli('127.0.0.1', 'root', 'mL08e!Tq', 'sakila');
} catch (mysqli_sql_exception $e) {
exit($e->getMessage());
}

Podemos pasar uno de los siguientes niveles de informe a la función mysqli_report():

  • MYSQLI_REPORT_INDEX: esto informa si se utilizó un índice incorrecto o ningún índice en una consulta
  • MYSQLI_REPORT_ERROR: esto informa errores de las llamadas de función MySQL
  • MYSQLI_REPORT_STRICT: esto informa mysqli_sql_exception en lugar de posibles advertencias
  • MYSQLI_REPORT_ALL: esto informa todo
  • MYSQLI_REPORT_OFF: esto no informa nada

Si bien MYSQLI_REPORT_ALL puede parecer una exageración, su uso puede que localice los errores de MySQL que no son obvios en el nivel de la aplicación, como la falta de un índice en una columna.

Seleccionando

Podemos seleccionar datos de MySQL usando el método query() de una instancia de mysqli, de la siguiente manera:

<?php
try {
// Informar sobre todo tipo de errores
mysqli_report(MYSQLI_REPORT_ALL);
// Abra una nueva conexión al servidor MySQL
$mysqli = new mysqli('127.0.0.1', 'root', 'mL08e!Tq', 'sakila');
// Realizar una consulta en la base de datos
$result = $mysqli->query('SELECT * FROM customer WHERE email LIKE "MARIA.MILLER@sakilacustomer.org"');
// Devuelve la fila actual de un conjunto de resultados como un objeto
$customer = $result->fetch_object();
// Cerrar conexión de base de datos abierta
$mysqli->close();
// Salida de información del cliente
echo $customer->first_name, ' ', $customer->last_name, PHP_EOL;
} catch (mysqli_sql_exception $e) {
// Error de salida y salida en caso de excepción
echo $e->getMessage(), PHP_EOL;
exit;
}

El ejemplo anterior da el siguiente error:

No index used in query/prepared statement SELECT * FROM customer WHERE
email = "MARIA.MILLER@sakilacustomer.org"

Si hubiéramos usado MYSQLI_REPORT_STRICT en lugar de MYSQLI_REPORT_ALL, no habríamos recibido el error. Sin embargo, el uso de informes de errores menos restrictivos no es una solución para mitigar el error. Aunque es posible que no estemos a cargo de la arquitectura y el mantenimiento de la base de datos, es nuestro deber como desarrollador informar sobre aspectos como estos, ya que definitivamente afectarán el rendimiento de nuestra aplicación. Una solución, en este caso, es crear un índice en la columna de correo electrónico. Podemos hacerlo fácilmente a través de la siguiente consulta:

ALTER TABLE customer ADD INDEX idx_email (email);

idx_email es el nombre dado libremente del índice que estamos creando, mientras que el correo electrónico es la columna para la que estamos creando un índice. El prefijo idx_ es simplemente una cuestión de convención que usan algunos desarrolladores; el índice puede llamarse fácilmente xyz o simplemente correo electrónico.
Con el índice en su lugar, si ahora intentamos ejecutar el código anterior, debería mostrar MARIA MILLER, como se muestra en la siguiente captura de pantalla:

El método query() devuelve el objeto mysqli_result o el valor booleano verdadero y falso, según el siguiente tipo:

  • SELECT el tipo de consulta: objeto mysqli_result o falso booleano
  • SHOW tipo de consulta: objeto mysqli_result o falso booleano
  • DESCRIBE el tipo de consulta: objeto mysqli_result o falso booleano
  • EXPLAIN el tipo de consulta: objeto mysqli_result o falso booleano
  • Otros tipos de consultas: booleanas Verdadero o Falso

La instancia del objeto mysqli_result tiene varios métodos diferentes de obtención de resultados:

  • fetch_object(): recupera la fila actual de un conjunto de resultados como un objeto y permite que se llame repetidamente
  • fetch_all(): recupera todas las filas de resultados como MYSQLI_ASSOC, MYSQLI_NUM o MYSQLI_BOTH
  • fetch_array(): recupera una única fila de resultados como MYSQLI_ASSOC, MYSQLI_NUM o MYSQLI_BOTH
  • fetch_assoc(): recupera una única fila de resultados como una matriz asociativa, y permite ser llamado repetidamente
  • fetch_field(): recupera el siguiente campo en el conjunto de resultados y permite que se llame repetidamente
  • fetch_field_direct(): recupera metadatos para un solo campo
  • fetch_fields(): recupera metadatos para campos en un conjunto de resultados completo
  • fetch_row(): recupera una única fila de resultados como una matriz enumerada y permite ser llamado repetidamente

Parámetros de enlace

La mayoría de las veces, la consulta de datos viene con enlace de datos. En cuanto a la seguridad, el enlace de datos es el camino a seguir, ya que nunca debemos concatenar la cadena de consulta con variables por nuestra cuenta. Esto lleva a ataques de inyección SQL. Podemos vincular datos en una consulta utilizando el método prepare() y métodos bind_param() de las respectivas instancias mysqli y mysqli_stmt, de la siguiente manera:

<?php
try {
// Informar sobre todo tipo de errores
mysqli_report(MYSQLI_REPORT_ALL);
// Abra una nueva conexión al servidor MySQL
$mysqli = new mysqli('127.0.0.1', 'root', 'mL08e!Tq', 'sakila');
$customerIdGt = 100;
$storeId = 2;
$email = "%ANN%";
// Prepare una instrucción SQL para su ejecución.
$statement = $mysqli->prepare('SELECT * FROM customer WHERE customer_id >? AND store_id = ? AND email LIKE ?');
// Vincula variables a una declaración preparada como parámetros
$statement->bind_param('iis', $customerIdGt, $storeId, $email);
// Ejecuta una consulta preparada
$statement->execute();
// Obtiene un conjunto de resultados de una declaración preparada
$result = $statement->get_result();
// Obtener objeto de fila/entrada en el conjunto de resultados
while ($customer = $result->fetch_object()) {
// Salida de información del cliente
echo $customer->first_name, ' ', $customer->last_name, PHP_EOL;
}
// Cerrar una declaración preparada
$statement->close();
// Cerrar conexión de base de datos
$mysqli->close();
} catch (mysqli_sql_exception $e) {
// Error de salida y salida en caso de excepción
echo $e->getMessage();
exit;
}

Esto debería darnos el siguiente resultado:

El método bind_param() tiene una sintaxis interesante. Acepta dos o más parámetros. El primer parámetro, la cadena $types, contiene uno o más caracteres. Estos caracteres especifican los tipos para las variables de enlace correspondientes:

  • i: Esta es la variable de un tipo entero
  • d: Esta es la variable de un tipo doble
  • s: Esta es la variable de una cadena de tipo
  • b: Esta es la variable de un tipo blob

El segundo y todos los siguientes parámetros representan las variables de enlace. Nuestro ejemplo usa ‘iis’ para el parámetro $types, que básicamente lee el método bind_param() y sus parámetros como: tipo entero de enlace ($customerIdGt), tipo entero ($storeId) y tipo de cadena ($ email).

Insertar

Ahora que hemos aprendido cómo preparar una consulta y vincular datos a ella, insertar nuevos registros se vuelve bastante fácil:

<?php
try {
// Informar sobre todo tipo de errores
mysqli_report(MYSQLI_REPORT_ALL);
// Abra una nueva conexión al servidor MySQL
$mysqli = new mysqli('127.0.0.1', 'root', 'mL08e!Tq', 'sakila');
// Prepara algunos datos de dirección
$address = 'The street';
$district = 'The district';
$cityId = 135; // Coincide con la ciudad de Dallas en Sakila DB
$postalCode = '31000';
$phone = '123456789';
// Prepare una instrucción SQL para su ejecución.
$statement = $mysqli->prepare('INSERT INTO address (
address,
district,
city_id,
postal_code,
phone
) VALUES (
?,
?,
?,
?,
?
);
');
// Vincula las variables a una declaración preparada como parámetros
$statement->bind_param('ssiss', $address, $district, $cityId,
$postalCode, $phone);
// Ejecutar una consulta preparada
$statement->execute();
// Cerrar una declaración preparada
$statement->close();
// Manera rápida y "sucia" de buscar la identificación de dirección recién creada
$addressId = $mysqli->insert_id;
// Cerrar conexión de base de datos
$mysqli->close();
} catch (mysqli_sql_exception $e) {
// Error de salida y salida en caso de excepción
echo $e->getMessage();
exit;
}

El ejemplo aquí sigue más o menos el anterior donde introdujimos el enlace. La diferencia obvia simplemente radica en la expresión INSERT INTO SQL real. No hace falta decir que mysqli no tiene clases o métodos PHP separados para manejar la selección, inserción o cualquier otra acción.

Actualización

Al igual que al seleccionar e insertar, también podemos usar los métodos prepare(), bind_param() y execute() para manejar la actualización de registros, de la siguiente manera:

<?php
try {
// Informar sobre todo tipo de errores
mysqli_report(MYSQLI_REPORT_ALL);
// Abra una nueva conexión al servidor MySQL
$mysqli = new mysqli('127.0.0.1', 'root', 'mL08e!Tq', 'sakila');
// Prepara algunos datos de dirección
$address = 'The new street';
$addressId = 600;
// Prepare una instrucción SQL para su ejecución.
$statement = $mysqli->prepare('UPDATE address SET address = ? WHERE
address_id = ?');
// Vincula las variables a una declaración preparada como parámetros
$statement->bind_param('si', $address, $addressId);
// Ejecutar una consulta preparada
$statement->execute();
// Cerrar una declaración preparada
$statement->close();
// Cerrar conexión de base de datos
$mysqli->close();
} catch (mysqli_sql_exception $e) {
// Error de salida y salida en caso de excepción
echo $e->getMessage();
exit;
}

Borrado

Nuevamente, podemos usar los métodos prepare(), bind_param() y execute() para manejar la eliminación de registros, como se muestra aquí:

<?php
try {
// Informar sobre todo tipo de errores
mysqli_report(MYSQLI_REPORT_ALL);
// Abra una nueva conexión al servidor MySQL
$mysqli = new mysqli('127.0.0.1', 'root', 'mL08e!Tq', 'sakila');
// Prepara algunos datos de dirección
$paymentId = 500;
// Prepare una instrucción SQL para su ejecución.
$statement = $mysqli->prepare('DELETE FROM payment WHERE payment_id =
?');
// Vincula las variables a una declaración preparada como parámetros
$statement->bind_param('i', $paymentId);
// Ejecutar una consulta preparada
$statement->execute();
// Cerrar una declaración preparada
$statement->close();
// Cerrar conexión de base de datos
$mysqli->close();
} catch (mysqli_sql_exception $e) {
// Error de salida y salida en caso de excepción
echo $e->getMessage();
exit;
}

Transacciones

Si bien los métodos SELECT, INSERT, UPDATE y DELETE nos permiten manipular los datos paso a paso, la verdadera fortaleza de MySQL reside en las transacciones. Usando los métodos begin_transaction(), commit() y rollback() de una instancia de mysqli, podemos controlar las características de transacción de MySQL:

<?php
mysqli_report(MYSQLI_REPORT_ALL);
$mysqli = new mysqli('127.0.0.1', 'root', 'mL08e!Tq', 'sakila');
try {
// Iniciar nueva transacción
$mysqli->begin_transaction(MYSQLI_TRANS_START_READ_WRITE);
// Crear nueva dirección
$result = $mysqli->query('INSERT INTO address (
address,
district,
city_id,
postal_code,
phone
) VALUES (
"The street",
"The district",
333,
"31000",
"123456789"
);
');
// Obtener la identificación de la dirección recién creada
$addressId = $mysqli->insert_id;
// Crear nuevo cliente
$statement = $mysqli->prepare('INSERT INTO customer (
store_id,
first_name,
last_name,
email,
address_id
) VALUES (
2,
"John",
"Doe",
"john@test.it",
?
)
');
$statement->bind_param('i', $addressId);
$statement->execute();
// Recuperar ID de cliente recién creado
$customerId = $mysqli->insert_id;
// Seleccione la información del cliente recién creada
$statement = $mysqli->prepare('SELECT * FROM customer WHERE customer_id
= ?');
$statement->bind_param('i', $customerId);
$statement->execute();
$result = $statement->get_result();
$customer = $result->fetch_object();
// Confirmar transacción
$mysqli->commit();
echo $customer->first_name, ' ', $customer->last_name, PHP_EOL;
} catch (mysqli_sql_exception $t) {
// DEBEMOS tener cuidado con las operaciones de bloque de prueba que no son db que arrojan  excepciones
// Como ellos podrían causar una reversión inadvertida
$mysqli->rollback();
echo $t->getMessage(), PHP_EOL;
}
// Cerrar conexión de base de datos
$mysqli->close();

Los indicadores de transacción válidos son los siguientes:

  • MYSQLI_TRANS_START_READ_ONLY: Esto coincide con MySQL START TRANSACTION READ ONLY query
  • MYSQLI_TRANS_START_READ_WRITE: Esto coincide con MySQL START TRANSACTION READ WRITE query
  • MYSQLI_TRANS_START_WITH_CONSISTENT_SNAPSHOT: esto coincide con MySQL START TRANSACTION WITH CONSISTENT SNAPSHOT query

Echa un vistazo a https://dev.mysql.com/doc/refman/5.7/en/commit.html para obtener más información sobre la sintaxis y el significado de la transacción MySQL.

Comparte