Utilización de bases de datos mediante MySQLi

MySQLi

MySQLi es una extensión mejorada (la i final es de improved) de PHP para acceder a bases de datos MySQL. MySQL es, junto con Oracle y Microsoft SQL Server, uno de los sistemas de gestión de bases de datos (es decir, un Database Management System o DBMS) relacionales más populares a nivel mundial.


Conexión



Para poder conectarte a una base de datos con MySQLi desde un script PHP necesitamos los siguientes datos (en ese orden):


Y de forma opcional:


De este modo, suponiendo que hemos creado una base de datos y un usuario para la misma, puedo conectarme a la base de datos de tres maneras diferentes:


//Mediante el constructor de la clase
$db = new mysqli('localhost', 'amazonaws', 'amazonaws', 'amazonaws');

//Mediante el método connect
$db = new mysqli();
$db->connect('localhost', 'amazonaws', 'amazonaws', 'amazonaws');

// Mediante el estilo procedimental (deprecado próximamente
$db = mysqli_connect('localhost', 'amazonaws', 'amazonaws', 'amazonaws');


La tercera vía está considerada obsoleta, con lo cual debemos evitar utilizarla.

Por otra parte, es importante asegurarnos de que la conexión con la base de datos ha sido satisfactoria, ya que en caso contrario lo más probable es que no funcione nada más. Para ello, podemos utilizar un sistema de control de errores como el que muestra el siguiente código:


//@ delante de $db no es obligatorio pero me permite ignorar errores en la ejecución
@$db = new mysqli('localhost', 'amazonaws', 'amazonaws', 'amazonaws');
if ($db->connect_errno != null) {
   echo "Error número $db->connect_errno conectando a la base de datos.<br>Mensaje: $db->connect_error.";
   exit(); 
}


Si más adelante queremos cambiar de base de datos, podemos hacerlo mediante la instrucción:


//Cambiar de base de datos
$db->select_db("nombre_bbdd");


Además, es importante, ya que estamos en España y usamos tildes y caracteres como la ñ, configurar la conexión para el juego de caracteres utf8 de la siguiente forma:


//Configurar el juego de caracteres
$db->set_charset('utf8');


Y una vez terminadas las tareas a realizar con la base de datos, cerrar conexión con el comando:


//Cerrar la conexión a la base de datos
$db->close();


Consultas



Una vez que la conexión a la base de datos funciona correctamente, podemos realizar consultas a la misma de la siguiente manera (supongamos una tabla de ejemplo con varias filas de ejemplo):




//Insert
$query = $db->query('INSERT INTO personas (nombre) VALUES ("José"),("Luís")');

//Delete
$query = $db->query('DELETE FROM personas WHERE id>3');

//Update
$registros = $db->query('UPDATE personas SET activo=1 WHERE activo=0');
if ($registros){
    echo "Se han activado $db->affected_rows registros.";
}
echo "";


Como vemos, las consultas de tipo INSERT, DELETE o UPDATE retornan true si se ejecutan correctamente o false sise ha producido algún error.

Por contra, SELECT devuelve por defecto un conjunto de datos en forma de objeto resultado (MYSQLI_STORE_RESULT) que se almacenan de forma local. Existe una forma de hacer que los datos se vayan recuperando a medida que se vayan necesitando, cambiando la opción por defecto por MYSQLI_USE_RESULT:

Si no se pasa nada, el valor predeterminado es MYSQLI_STORE_RESULT.

Alguien dijo una oración así en phpmanul: si tenemos que recuperar una gran cantidad de datos, usamos MYSQLI_USE_RESULT

De hecho, la diferencia entre estos dos parámetros sigue siendo muy grande.

(1) La diferencia es que las filas del conjunto de resultados se recuperan del servidor.

(2) MYSQLI_USE_RESULT inicia la consulta, pero en realidad no obtiene ninguna fila

(3) MYSQLI_STORE_RESULT recupera todas las filas inmediatamente

(4) Cuando MYSQLI_STORE_RESULT recupera el conjunto de resultados del servidor, obtiene la fila y le asigna memoria y la almacena en el cliente. La siguiente llamada a mysqli_fetch_array () nunca devuelve un error, porque simplemente separa la fila de la estructura de datos que retuvo el conjunto de resultados. mysqli_fetch_array () devuelve NULL siempre indicando que se ha llegado al final del conjunto de resultados.

(5) MYSQLI_USE_RESULT en sí mismo no recupera ninguna fila, sino que solo inicia una recuperación fila por fila, lo que significa que se debe llamar a mysqli_fetch_array () en cada fila para hacerlo por sí mismo. En este caso, aunque mysqli_fetch_array () devuelve NULL normalmente indica que se ha llegado al final del conjunto de resultados, también puede indicar un error en la comunicación con el servidor.

Resumir

En comparación con MYSQLI_USE_RESULT, MYSQLI_STORE_RESULT tiene mayores requisitos de memoria y procesamiento. Debido a que todo el conjunto de resultados se mantiene en el cliente, el costo de la asignación de memoria y la creación de estructuras de datos es muy alto. Si desea recuperar varias filas a la vez, puede usar MYSQLI_USE_RESULT.

MYSQLI_USE_RESULT tiene requisitos de memoria bajos porque solo necesita asignar suficiente espacio para una sola fila por procesamiento. Esto es más rápido porque no hay necesidad de establecer estructuras de datos complejas para el conjunto de resultados. Por otro lado, MYSQLI_USE_RESULT supone una gran carga para el servidor, que debe mantener las filas en el conjunto de resultados hasta que el cliente considere que puede recuperar todas las filas.



//Select en un array con claves asociativas y numéricas (con MYSQLI_STORE_RESULT, da igual ponerlo que no)
$resultado = $db->query('SELECT * FROM personas');
$personas = $resultado->fetch_array(MYSQLI_BOTH); //O también $resultado->fetch_array()
while ($personas != null){ //Recorro el resultado
    echo $personas['id']." ".$personas[1]." ".$personas['activo']."";
    $personas = $resultado->fetch_array(MYSQLI_BOTH);
}
$resultado->free(); //Libero de la memoria
echo "";

//Select en un array con claves asociativas y numéricas (con MYSQLI_USE_RESULT)
$resultado = $db->query('SELECT * FROM personas', MYSQLI_USE_RESULT);
$personas = $resultado->fetch_array(MYSQLI_BOTH); //O también $resultado->fetch_array()
while ($personas != null){ //Recorro el resultado
    echo $personas['id']." ".$personas[1]." ".$personas['activo']."";
    $personas = $resultado->fetch_array(MYSQLI_BOTH);
}
$resultado->free(); //Libero de la memoria
echo "";

//Select en un array con claves asociativas
$resultado = $db->query('SELECT * FROM personas');
$personas = $resultado->fetch_array(MYSQLI_ASSOC); //O también $resultado->fetch_assoc()
while ($personas != null){ //Recorro el resultado
    echo $personas['id']." ".$personas['nombre']." ".$personas['activo']."";
    $personas = $resultado->fetch_array(MYSQLI_ASSOC);
}
$resultado->free(); //Libero de la memoria
echo "";

//Select en un array con claves numéricas
$resultado = $db->query('SELECT * FROM personas');
$personas = $resultado->fetch_array(MYSQLI_NUM); //O también $resultado->fetch_row()
while ($personas != null){ //Recorro el resultado
    echo $personas[0]." ".$personas[1]." ".$personas[2]."";
    $personas = $resultado->fetch_array(MYSQLI_NUM);
}
$resultado->free(); //Libero de la memoria
echo "";

//Select en un objeto
$resultado = $db->query('SELECT * FROM personas');
$personas = $resultado->fetch_object();
while ($personas != null){ //Recorro el resultado
    echo $personas->id." ".$personas->nombre." ".$personas->activo."";
    $personas = $resultado->fetch_object();
}
$resultado->free(); //Libero de la memoria
echo "";

//Select con un objeto, real_query y store_result
$booleano = $db->real_query('SELECT * FROM personas');
if ($booleano){
    $resultado = $db->store_result(); //Almaceno el resultado de la última consulta
    $personas = $resultado->fetch_object();
    while ($personas != null){ //Recorro el resultado
        echo $personas->id." ".$personas->nombre." ".$personas->activo."";
        $personas = $resultado->fetch_object();
    }
    $resultado->free(); //Libero de la memoria
    echo "";
}

//Select con un objeto, real_query y use_result
$booleano = $db->real_query('SELECT * FROM personas');
if ($booleano){
    $resultado = $db->use_result(); //Uso el resultado de la última consulta
    $personas = $resultado->fetch_object();
    while ($personas != null){ //Recorro el resultado
        echo $personas->id." ".$personas->nombre." ".$personas->activo."";
        $personas = $resultado->fetch_object();
    }
    $resultado->free(); //Libero de la memoria
    echo "";
}

//Cierro la conexión
$db->close();


Así, vemos que los datos se pueden recuperar de varias maneras en función de nuestras necesidades. Además, si quisiéramos que SELECT devolviera true o false, podríamos hacerlo utilizando el método real_query() en vez de query(), y posteriormente recuperar los datos de forma completa con store_result() o según los vaya necesitando con use_result().

Es importante liberar la memoria cuando hayamos terminado de trabajar con los datos. Para ello hemos utilizado el método free().

Transacciones



Al introducir las bases de datos indicamos que ciertos motores de almacenamiento, como InnoDB, permitían la ejecución de transacciones, esto es, ejecutar varias sentencias a la vez en una única conexión a la base de datos.

Por defecto, cada consulta a la base de datos es una transacción en si misma, pero este comportamiento se puede modificar de la siguiente forma:


//Deshabilitamos el autocommit par que no se ejecute cada una de ellas por separado
$db->autocommit(false);
//Declaramos todas las consultas
$resultado = $db->query('INSERT INTO personas (nombre) VALUES ("José"),("Luís")');
$resultado = $db->query('DELETE FROM personas WHERE id>3');
$resultado = $db->query('UPDATE personas SET activo=1 WHERE activo=0');
//Realizamos el commit para que se ejecuten todas las consultas
$db->commit();
//Mensaje
if ($resultado){
    echo "Se han activado $db->affected_rows registros.";
}
echo "";


Consultas preparadas



Otra forma de optimizar las consultas a la base de datos, de modo que además agilice las tareas más repetitivas como el UPDATE e INSERT es el uso de consultas preparadas o statements.

Esta técnica consiste básicamente en preparar la consulta antes de lanzarla y, ya de paso, aprovecharnos de ellos para rellenar los valores de la misma mediante variables en lugar de consultas, tal y como muestra el ejemplo 2 del siguiente código:


//Ejemplo 1 consulta (statement) preparada
$resultado = $db->stmt_init();
$resultado->prepare('INSERT INTO personas (nombre) VALUES ("José"),("Luís")');
$resultado->execute();
$resultado->close();
$db->close();

//Ejemplo 2 consulta preparada
$nombres = ['Jorgito', 'Juanito', 'Jaimito'];
$resultado = $db->stmt_init();
$resultado->prepare('INSERT INTO personas (nombre) VALUES (?)');
foreach ($nombres as $nombre){
    $resultado->bind_param('s', $nombre);
    $resultado->execute();
}
$resultado->close();
$db->close();



De esta forma, podemos tener la consulta siempre preparada para ejecutarla cuando tengamos las variables a nuestra disposición. Hay que tener en cuenta que con bind_param() siempre debo pasar el tipo y el nombre de la variable, ya que los parámetros del método se pasan por referencia y no de forma literal. Así en función del tipod e dato, la cadena de texto que precede a la variable será:



De manera similar a bind_param(), para consultas que devuelven conjuntos de datos podemos utilizar bind_result(), que asigna a variables los campos obtenidos en la consulta, como en el siguiente ejemplo:


//Consulta preparada con SELECT
$resultado = $db->stmt_init();
$resultado->prepare('SELECT * FROM personas');
$resultado->execute();
$resultado->bind_result($id, $nombre, $activo);
while ($resultado->fetch() != null){ //Recorre los registros devueltos
    echo $id." ".$nombre." ".$activo."";
}
$resultado->close();
$db->close();
echo "";