Manejo de bases de datos con PHP

• Inicio
• PHP
• PDO

Las clases PDO y PDOStatement de PHP para manejo de datos cuentan con muchos métodos de alto nivel para ser usados para todo tipo de casos.

Aunque en este artículos solo nos enfocaremos en los métodos mas generales que nos permitirán resolver las acciones esenciales de gestión de datos

Preparando el espacio de trabajo

Antes de empezar a trabajar con bases de datos es necesario preparar una, primero instalando y configurando el motor si aún no lo tenemos, luego creando la base de datos y al menos un usuario con privilegios sobre esa base de datos.

Lo siguiente sería crear la estructura base que habrá en esa base de datos, para esta guía usaremos una tabla para registrar productos, por lo que sería necesario correr el siguiente comando manualmente para así generar la tabla:

CREATE products IF NOT EXISTS(
	id bigserial not null primary key,
	code varchar(255) null unique,
	title varchar(255) null,
	price numeric(10, 2) not null default 0,
	stock integer not null default 0,
	active boolean not null default false
);

También sugiero insertar uno que otro dato de prueba, para poder ver mas fácilmente que todo esta funcionando correctamente.

INSERT INTO products (title, price, code, stock, active) VALUES 
	('Producto #1', 500, '70001', 10, true),
	('Producto #2', 800, '70002', 4, true),
	('Producto #3', 1500, '70001', 3, true),
;

Ya con la base de datos preparada y con al menos una tabla creada ya podemos empezar a manejar la base de datos desde nuestro programa en PHP.

Conectar con la base de datos

Para conectar a la base de datos hay que configurar la conexión, esto se hace directamente creando una instancia de la clase PDO.

Los argumentos que hay que pasarle a la clase PDO para generar objetos pueden variar dependiendo de la base de datos que se desea utilizar.

Para la mayoría de bases de datos los argumentos que hay que usar con PDO son tres:

1. El primer argumento es una cadena de texto compuesta por el nombre del controlador de la base de datos, el servidor, el puerto y el nombre de la base de datos a los que hay que conectar
2. El segundo argumento es un nombre de usuario con permisos para usar esta base de datos
3. El tercer argumento es la contraseña de ese usuario.

<?php 
$drive = 'pgsql';
$host = 'localhost';
$port = '5432';
$name = 'testdatabase';
$user = 'testuser';
$password = 'testpassword';

$dataSourceName = "$drive:host=$host;port=$port;dbname=$name";
$PDO = new PDO($dataSourceName, $user, $password);

Una vez instanciado nuestro objeto PDO, ya podemos empezar a realizar operaciones SQL con la base de datos.

Hacer consultas a la base de datos

Para hacer consultas usaremos el metodo "query" de PDO, y como argumento solo debemos proporcionar una cadena que represente una consulta SQL valida, por ejemplo, con la siguiente sentencia SQL solicitaremos una lista de productos:

SELECT id, title, price, stock FROM products

Esta sentencia podemos por ejemplo asignarla a una variable y luego podemos pasarle esa variable al método query.

$query = 'SELECT id, code, title, price, stock FROM products';
$result = $PDO->query($query);

De esta forma ahora en la variable result tenemos un objecto que podemos recorrer con la estructura foreach.

En cada ciclo de este bucle, podemos acceder a una fila de los datos que nos retornó la consulta en forma de arreglo asociativo, en el que cada elemento tiene el valor de una celda en la tabla y su clave es el nombre de la columna.

Sabiendo esto es muy fácil directamente imprimir los datos o hacer otras operaciones con ellos.

foreach ($result as $row) {
    echo "{$row['id']} - {$row['code']} - {$row['title']} - {$row['price']} - {$row['stock']}";
}

Enviar nuevos registros a la base de datos

Ahora usaremos el método exec de PDO para poder enviar comandos, empezando con el comando de insertar un nuevos registros.

$code = 70005;
$title = 'Producto demo #1';
$price = 200;
$stock = 15;

$command = "INSERT INTO products(code, title, price, stock) VALUES($code, '$title', $price, $stock)";
$PDO->exec($command); 

echo $PDO->lastInsertId();

En este código definimos primero los datos del nuevo elemento a registrar en la base de datos, luego unimos esos datos en una sola variable $command que representa el comando que finalmente enviaremos a la base de datos.

Por ultimo para saber si nuestro comando funcionó y nuestro nuevo registro se guardó correctamente en la base de datos podemos usar el método lastInsertId, el cual nos retornara el Id de la fila que acabamos de registrar.

Insertar muchos datos al mismo tiempo

Si lo que queremos es insertar múltiples nuevos registros de golpe, esto se puede lograr de la siguiente forma:

$products = [
    [
        'id' => null,
        'code' => 70010,
        'title' => 'Producto demo #1',
        'price' => 200,
        'stock' => 15,
    ],
    [
        'id' => null,
        'code' => 70011,
        'title' => 'Producto demo #2',
        'price' => 300,
        'stock' => 20,
    ],
    [
        'id' => null,
        'code' => 70012,
        'title' => 'Producto demo #3',
        'price' => 400,
        'stock' => 5,
    ],
];

foreach($products as $productKey => $productData){
    $command = "INSERT INTO products(code, title, price, stock) VALUES(
        {$productData['code']}, '{$productData['title']}', {$productData['price']}, {$productData['stock']}
    )";
    $PDO->exec($command);
    $products[$productKey]['id'] = $PDO->lastInsertId();
}

Empezamos definiendo el arreglo con los datos de los elementos a insertar, y con cualquier estructura repetitiva procedemos a ejecutar el proceso de enviar un comando y luego preguntar por el Id finalmente generado.

Eliminar filas de la base de datos

La forma de eliminar registros es la siguiente:

$command = "DELETE FROM products WHERE id = $id";
$PDO->exec($command);

Solo se necesita enviar el comando de eliminación definiendo las condiciones adecuadas para así borrar registros de forma permanente.

Si se requiere eliminar múltiples registros, se puede enviar el comando repetidamente usando estructuras repetitivas o se pueden definir condiciones que abarquen múltiples filas.

Actualizar campos en la base de datos

Para enviar el comando de actualización de datos es tan simple como definir la sentencia SQL y executarla con PDO exec.

$command = "UPDATE products set stock = $stock WHERE id = $id";
$PDO->exec($command);

Siempre será necesario revisar la condición de la sentencia SQL para evitar modificar registros que no se tenga pensado modificar.

Ahora, ¿qué haríamos si por ejemplo necesitamos editar mas de un campo a la vez? ¿que tal si se tratara de cinco, diez o treinta campos? ¿Como se puede mejorar la operación de actualización?

Pues para esto nos conviene crear una función que nos ayude a hacer las actualizaciones a la base de de una forma un poco mas cómoda.

Algoritmo para encapsular y simplificar actualización de datos

function dataBaseUpdate(\PDO $PDO, string $table, array $fields)
{
    $id = $fields['id'];
    unset($fields['id']);

    $updateList = [];
    foreach ($fields as $field => $value) {
        $updateList[] = "$field = $value";
    }
    $fieldsToUpdate = implode(', ', $updateList);

    $command = "UPDATE $table SET $fieldsToUpdate WHERE id = $id";
    return $PDO->exec($command);
}

Esta función nos ayuda a construir la sentencia SQL de actualización de datos (los que sean y en la tabla que sea).

Solo debemos llamar nuestro algoritmo usando como argumentos el objeto PDO que conecta a la base de datos, el nombre de la tabla que contiene los datos que se desean actualizar y los nuevos datos a persistir.

A tener en cuenta que este algoritmo solo funciona para actualizar filas especificas, indicadas por el ID, por lo que entre los campos a actualizar debemos proporcionar el Id de la fila a editar en cuestión, de lo contrario el algoritmo se romperá.

Con esto podemos usar el siguiente código para actualizar registros:

dataBaseUpdate($PDO, 'products', [
    'id' => 4,
    'stock' => 3,
]);
dataBaseUpdate($PDO, 'products', [
    'id' => 5,
    'title' => "'Producto actualizado'",
    'price' => 300,
    'stock' => 14,
    'active' => true,
]);

La primera instrucción actualizara la fila 4 de nuestra tabla de productos, cambiando el valor del stock por un 3.

La siguiente instrucción actualizara la fila 5, cambiando multiples campos de la fila al mismo tiempo (titulo, precio, stock, estado).

Hay que prestar atención que la cadena de texto que representa el titulo esta entre comillas, esto es porque las primeras comillas son para decirle a PHP donde acaba y termina el código y empieza el texto, y una vez que el texto sea concatenado a la sentencia SQL, se perderán estas comillas, a la base de datos debemos indicarle que es código SQL y que es texto humano, por lo que es necesario usar doble entrecomillado en estos casos.

Y si en vez de realizar un numero finito de actualizaciones tuviésemos que actualizar un gran listado de filas?

Para ello podemos de nuevo recurrir a las estructuras repetitivas, por ejemplo:

$products = [
    [
        'id' => 3,
        'stock' => 7,
    ],
    [
        'id' => 4,
        'stock' => 3,
    ],
    [
        'id' => 5,
        'stock' => 14,
    ],
    // ... 
];
foreach($products as $product){
    dataBaseUpdate($PDO, 'products', $product);
}