Bases de datos y PHP

Problema: Usar MySql directamente

Un error común en el código PHP que está obsoleto es el uso de la funcion “mysql” para acceder directamente a la base de dators.

Ejemplo de como acceder de manera directa a la base de datos.

Ejemplo 1

function get_user_id( $nombre)
{
$db = mysql_connect( 'localhost', 'root', 'password' );
mysql_select_db( 'usuarios' );

$res = mysql_query( "SELECT id FROM usuarios WHERE login='".$nombre."'" );
while( $row = mysql_fetch_array( $res ) ) { $id = $row[0]; }

return $id;
}

var_dump( get_user_id( 'jose' ) );
?>

Date cuenta que se utiliza la funcion mysql_connect para acceder a la base de datos. Tambien fíjate que utilizamos una cadena concatenada para añadir el parametro nombre a la consulta.

Esta técnica tiene dos buenas alternativas:

* El modulo PEAR DB y las clases PHP Data Objects.

Ambas proveen una capa de abstraccion para la elección de un tipo de base de datos, tu podrás ajustar tu código para funcionar con MySql ó PostgreSql o otros modelos sin demasiados problemas.

La otra ventaja del uso de capas de abstraccion como PEAR DB o PDO es que puedes usar el operador “?” en tus consultas SQL.

Haciendo el mantenimiento de seguridad de tu SQL mucho más sencillo.

El código alternativo a utilizar el módulo PEAR DB

Ejemplo 2

require_once("DB.php");

function get_user_id( $name )
{
$dsn = 'mysql://root:password@localhost/users';
$db =& DB::Connect( $dsn, array() );
if (PEAR::isError($db)) { die($db->getMessage()); }

$res = $db->query( ‘SELECT id FROM usuarios WHERE login=?’,
array( $nombre) );
$id = null;
while( $res->fetchInto( $row ) ) { $id = $row[0]; }

return $id;
}

var_dump( get_user_id( ‘jose’ ) );
?>

Mira cómo todas las menciones directas a MySql han desaparecido, excepto en la cadena de conexión $dsn.

Se utiliza la variable nombre en la sentencia SQL con el operador “?” por lo que los datos son enviados a traves del array al final de la consulta.

Problema: No utilizar la función autonumérica

La mayoria de bases de datos modernas, tienen la habilidad de auto-incrementar el identificador único de las tablas.

A pesar de ello aún sigo viendo consultas de búsqueda para encontrar el id más alto y de este modo incrementando el dato poder añadir un nuevo registro.

Código erróneo .sql

DROP TABLE IF EXISTS usuarios;
CREATE TABLE users (
id MEDIUMINT,
login TEXT,
password TEXT
);

INSERT INTO usuarios VALUES ( 1, ‘jose’, ‘pass’ );
INSERT INTO usuarios VALUES ( 2, ‘manu’, ‘pass’ );
INSERT INTO usuarios VALUES ( 1, ‘rafa’, ‘pass’ );

- El campo id declarado en el ejemplo utiliza un ismple entero. Por eso debería ser declarado único, se le puede asignar el valor que se desee, como se muestra en el ejemplo que sigue a la creación de la tabla.

Código necesario para añadir usuarios al esquema anterior

anadir_usuario.php

require_once("DB.php");

function add_user( $nombre, $pass )
{
$rows = array();

$dsn = 'mysql://root:password@localhost/bad_badid';
$db =& DB::Connect( $dsn, array() );
if (PEAR::isError($db)) { die($db->getMessage()); }

$res = $db->query( “SELECT max(id) FROM usuarios” );
$id = null;
while( $res->fetchInto( $row ) ) { $id = $row[0]; }

$id += 1;

$sth = $db->prepare( “INSERT INTO usuarios VALUES(?,?,?)” );
$db->execute( $sth, array( $id, $nombre, $pass ) );

return $id;
}

$id = add_user( ‘juan’, ‘pass’ );

var_dump( $id );
?>

- El código en anadir_usuario.php ejecuta primero una consulta para conseguir el número del id mayor.

- Entonces ejecuta el INSERT con el id+1.

- Este código podría fallar en servidores con alto tráfico ademas de ser ineficiente.

Por eso: ¿Cuál es la alternativa?

Usar la característica auto-incremento para crear IDs únicos por cada insercción automáticamente.

Código óptimo

DROP TABLE IF EXISTS usuarios;
CREATE TABLE usuarios (
id MEDIUMINT NOT NULL AUTO_INCREMENT,
login TEXT NOT NULL,
password TEXT NOT NULL,
PRIMARY KEY( id )
);

INSERT INTO users VALUES ( null, ‘jose’, ‘pass’ );
INSERT INTO users VALUES ( null, ‘manu’, ‘pass’ );
INSERT INTO users VALUES ( null, ‘rafa’, ‘pass’ );

- Se añade la etiqueta NOT NULL que indica que el campo no debe ser nulo.

- Tambien se añade la característica AUTO_INCREMENT que indica que el campo será incrementado automáticamente y por último PRIMARY KEY por lo que cada campo es un id.

- Estos cambios optimizan el uso del servidor y aumenta la eficiencia del código.

Código necesario para añadir usuarios al esquema anterior

anadir_usuario(óptimo).php

require_once("DB.php");

function add_user( $nombre, $pass )
{
$dsn = 'mysql://root:password@localhost/good_genid';
$db =& DB::Connect( $dsn, array() );
if (PEAR::isError($db)) { die($db->getMessage()); }

$sth = $db->prepare( “INSERT INTO usuarios VALUES(null,?,?)” );
$db->execute( $sth, array( $nombre, $pass ) );

$res = $db->query( “SELECT last_insert_id()” );
$id = null;
while( $res->fetchInto( $row ) ) { $id = $row[0]; }

return $id;
}

$id = add_user( ‘juan’, ‘pass’ );

var_dump( $id );
?>

En vez de utilizar una consulta para obtener el último id añadido solamente hace falta un INSERT y acto seguido un SELECT para la comprobación de la inserccion de datos.

Este código es mucho más simple y eficiente que el de los ejemplos anteriores.

Problema: Uso de varias bases de datos

De vez en cuando se ven aplicaciones en los que cada tabla estan en bases de datos separadas.

Hay razones para hacer esto en bases de datos extremadamente grandes, pero no cómo mejora de la aplicacion, no es necesario este nivel de segmentación.

Ademas no puedes mejorar las relaciones a traves de bases de datos, y sin contemplar que manejar tablas de distintas bases de datos es más complicado.

Veámoslo con ejemplos

archivos.sql:
CREATE TABLE archivos (
id MEDIUMINT,
user_id MEDIUMINT,
name TEXT,
path TEXT
);

carga_archivos.sql:
INSERT INTO archivos VALUES ( 1, 1, ‘test1.jpg’, ‘archivos/test1.jpg’ );
INSERT INTO archivos VALUES ( 2, 1, ‘test2.jpg’, ‘archivos/test2.jpg’ );

usuarios.sql:
DROP TABLE IF EXISTS usuarios;
CREATE TABLE usuarios (
id MEDIUMINT,
login TEXT,
password TEXT
);

carga_usuarios.sql:
INSERT INTO usuarios VALUES ( 1, ‘manu’, ‘pass’ );
INSERT INTO usuarios VALUES ( 2, ‘jose’, ‘pass’ );

Para el funcionamietno con este modelo de multiples bases de datos. Necesitamos cargar los datos de una base de datos y despues filtralos con una nueva conexión a otra base de datos.

require_once("DB.php");

function get_user( $nombre)
{
$dsn = 'mysql://root:password@localhost/bad_multi1';
$db =& DB::Connect( $dsn, array() );
if (PEAR::isError($db)) { die($db->getMessage()); }

$res = $db->query( “SELECT id FROM usuarios WHERE login=?”,
array( $nombre) );
$uid = null;
while( $res->fetchInto( $row ) ) { $uid = $row[0]; }

return $uid;
}

function get_files( $nombre)
{
$uid = get_user( $nombre);

$rows = array();

$dsn = ‘mysql://root:password@localhost/bad_multi2′;
$db =& DB::Connect( $dsn, array() );
if (PEAR::isError($db)) { die($db->getMessage()); }

$res = $db->query( “SELECT * FROM archivos WHERE user_id=?”,
array( $uid ) );
while( $res->fetchInto( $row ) ) { $rows[] = $row; }

return $rows;
}

$archivos= get_files( ‘juan’ );

var_dump( $archivos);
?>

- La función get_user conecta a la base de datos que contiene la tabla con la información relativa a los usuarios y devuelve el id para el usuario dado.
- La función get_files conecta a la tabla con la información acerca de los archivos y devuelve el registro asociado con el usuario dado.

Una manera mas eficiente de hacer todo esto es caragar la información en una única base de datos.

Código óptimo

require_once("DB.php");

function get_files( $nombre)
{
$rows = array();

$dsn = 'mysql://root:password@localhost/good_multi';
$db =& DB::Connect( $dsn, array() );
if (PEAR::isError($db)) { die($db->getMessage()); }

$res = $db->query(
“SELECT files.* FROM usuarios,archivos WHERE
users.login=? AND usuarios.id=archivos.user_id”,
array( $name ) );
while( $res->fetchInto( $row ) ) { $rows[] = $row; }

return $rows;
}

$archivos= get_files( ‘jose’ );

var_dump( $archivos);
?>

El código no es sólo más corto sino más fácil de comprender y mucho más eficiente.

Conclusión

Las bases de datos son herramientas poderosas y como todas las herramientas podesoras deben ser optimizadas apropiadamente.

El truco para el aprovechamiento máximo está en comprender cóm funciona y de ese modo poder aprovechar sus virtudes.

Enlaces

Puede que estos enlaces te ayuden a ello…

The Art of SQL, por Stephane Faroult y Peter Robson, es de lectura obligatoria para los programadores que utilizan bases de datos en sus aplicaciones.
PHP.net es el punto de comienzo y final de todo lo que quieras aprender sobre PHP.
La documentación de PEAR DB es una excelente librería.
La documentación de PDO Functions podrás realizar verdaderas mejoras en tu código.
MySQL.org excelente documentación con ejemplos que te muestra cómo mejorar el uso de las bases de datos.
Five common PHP database problems