Skip to content

7. Uso de SGBD y MySql

Vamos a escribir scripts PHP utilizando una base de datos MySQL:

El SGBD MySQL está incluido en el paquete WampServer (véase el párrafo 2.1.1). Mostramos cómo crear una base de datos y un usuario MySQL.

  • Una vez iniciado, WampServer se puede administrar desde un ícono de [1] ubicado en la parte inferior derecha de la barra de tareas.
  • En [2], se inicia la herramienta de administración de MySQL

Se crea una base de datos [dbpersonnes]:

Image

Se crea un usuario [admpersonnes] con la contraseña [nobody]:

  • en [1], el nombre de usuario
  • en [2], la máquina de SGBD en la que se le otorgan derechos
  • en [3], su contraseña
  • en [4], lo mismo
  • en [5], no se le otorgan derechos a este usuario
  • en [6], se crea
  • en [7], se regresa a la página de inicio de phpMyAdmin
  • en [8], se utiliza el enlace [Privileges] de esta página para modificar los del usuario [admpersonnes] y [9].

1011

Image

  • en [10], se indica que se desea otorgar al usuario [admpersonnes] derechos sobre la base de datos [dbpersonnes]
  • en [11], se confirma la selección
  • con el enlace [12]. En [Tout cocher], se le otorgan al usuario [admpersonnes] todos los derechos sobre la base de datos [dbpersonnes] [13]
  • Se valida en [14]

Ahora tenemos:

  • una base de datos MySQL [dbpersonnes]
  • un usuario [admpersonnes / nobody] que tiene todos los derechos sobre esta base de datos

Vamos a escribir scripts PHP para trabajar con la base de datos. PHP cuenta con diversas bibliotecas para administrar bases de datos. Utilizaremos la biblioteca PDO (PHP Data Objects), que actúa como interfaz entre el código PHP y el SGBD:

La biblioteca PDO permite que el script PHP se abstraiga de la naturaleza exacta del SGBD utilizado. Así, en el ejemplo anterior, el SGBD MySQL puede sustituirse por el SGBD de Postgres con un impacto mínimo en el código del script PHP. Esta biblioteca no está disponible de forma predeterminada. Se puede verificar su disponibilidad de la siguiente manera:

  • 1: desde el ícono de administración de WampServer, se selecciona la opción [PHP / PHP extensions]
  • 2: se ven las diferentes extensiones PDO disponibles y las que están activas: [PHP_pdo_mysql] para el SGBD MySQL, [PHP_pdo_sqlite] para el SGBD SQL Lite. Para activar una extensión, solo hay que hacer clic en ella. El intérprete PHP se reinicia entonces con la nueva extensión activada.

7.1. Conexión a una base MySQL – 1 (mysql_01)

La conexión a un SGBD se realiza mediante la creación de un objeto PDO. El constructor admite diferentes parámetros:

$dbh=new PDO($dsn,$user,$passwd,$driver_options)

El significado de los parámetros es el siguiente:

$dsn
(Data Source Name) es una cadena que especifica la naturaleza del SGBD y su ubicación en Internet. La cadena
«mysql:host=localhost» indica que se trata de un SGBD MySQL que opera en el servidor local. Esta cadena
puede incluir otros parámetros, como el puerto de escucha del SGBD y el nombre de la base a la que
desea conectarse: «mysql:host=localhost:port=3306:dbname=dbpersonnes».
$user
identificador del usuario que se conecta
$passwd
su contraseña
$driver_options
una matriz de opciones para el controlador del SGBD

Solo el primer parámetro es obligatorio. El objeto así creado servirá luego como base para todas las operaciones realizadas en la base de datos a la que se ha conectado. Si no se pudo crear el objeto PDO, se lanza una excepción de tipo PDOException.

A continuación se muestra un ejemplo de conexión:


<?php

// conexión a una base de datos MySql
// la identidad del usuario es (admpersonnes, nobody)
$ID = "admpersonnes";
$PWD = "nobody";
$HOTE = "localhost";

try {
  // inicio de sesión
  $dbh = new PDO("mysql:host=$HOTE", $ID, $PWD);
  print "Connexion réussie\n";
  // cierre
  $dbh = null;
} catch (PDOException $e) {
  print "Erreur : " . $e->getMessage() . "\n";
  exit();
}

Resultados:

Connexion réussie

Comentarios

  • línea 11: la conexión a un SGBD se realiza mediante la creación de un objeto PDO. El constructor se utiliza aquí con los siguientes parámetros:
    • una cadena que especifica la naturaleza del SGBD y su ubicación en Internet. La cadena «mysql:host=localhost» indica que se trata de un SGBD MySQL que opera en el servidor local. No se ha especificado el puerto. Por lo tanto, se utiliza el puerto 3306 por defecto. Tampoco se indica el nombre de la base de datos. En ese caso, se establecerá una conexión con el SGBD MySQL, y la selección de una base de datos específica se realizará más adelante.
    • un nombre de usuario
    • su contraseña
  • línea 14: el cierre de la conexión se realiza mediante la eliminación del objeto PDO creado inicialmente.
  • línea 15: la conexión a un SGBD puede fallar. En ese caso, se lanza una excepción de tipo PDOException.

7.2. Creación de una tabla MySQL (mysql_02)


<?php

// conexión a la base de datos MySql
// la identidad del usuario
$ID = "admpersonnes";
$PWD = "nobody";
// identidad de la base
$DSN = "mysql:host=localhost;dbname=dbpersonnes";

// conexión
list($erreur, $connexion) = connecte($DSN, $ID, $PWD);
if ($erreur) {
  print "Erreur lors de la connexion à la base [$DSN] sous l'identité ($ID,$PWD) : $erreur\n";
  exit;
}

// eliminación de la tabla «personas» si existe
$requête = "drop table personnes";
$erreur = exécuteRequête($connexion, $requête);
//¿Hubo algún error?
if ($erreur)
  print "$requête : Erreur ($erreur)\n";
else
  print "$requête: Exécution réussie\n";
// creación de la tabla «personas»
$requête = "create table personnes (prenom varchar(30) NOT NULL, nom varchar(30) NOT NULL, age integer NOT NULL, primary key(nom,prenom))";
$erreur = exécuteRequête($connexion, $requête);
//¿Hubo algún error?
if ($erreur)
  print "$requête : Erreur ($erreur)\n";
else
  print "$requête: Exécution réussie\n";
// Nos desconectamos y salimos
déconnecte($connexion);
exit;

// ---------------------------------------------------------------------------------
function connecte($dsn, $login, $pwd) {
  // se conecta ($login,$pwd) a la base de datos $dsn
  // devuelve el ID de la conexión y un código de error
  try {
    // conexión
    $dbh = new PDO($dsn, $login, $pwd);
    // respuesta sin error
    return array("", $dbh);
  } catch (PDOException $e) {
    // respuesta con error
    return array($e->getMessage(), null);
  }
}

// ---------------------------------------------------------------------------------
function déconnecte($connexion) {
  // cierra la conexión identificada por $connexion
  $connexion = null;
}

// ---------------------------------------------------------------------------------
function exécuteRequête($connexion, $sql) {
  // ejecuta la consulta $sql en la conexión $connexion
  try {
    $connexion->exec($sql);
    // respuesta sin error
    return "";
  } catch (PDOException $e) {
    // respuesta con error
    return $e->getMessage();
  }
}

Resultados:

drop table personnes: Exécution réussie
create table personnes (prenom varchar(30) NOT NULL, nom varchar(30) NOT NULL, age integer NOT NULL, primary key(nom,prenom)): Exécution réussie

En PHPMyAdmin, se puede observar la presencia de la tabla:

 

Comentarios

  • líneas 38-50: la función connecte establece una conexión con un SGBD. Devuelve una matriz ($erreur, $connexion) donde $connexion es la conexión creada o null si no se pudo crear. En este último caso, $erreur es un mensaje de error.
  • líneas 53-56: la función déconnecte cierra una conexión
  • línea 59: la función exécuteRequête permite ejecutar una orden SQL en una conexión. La conexión es un objeto PDO. El método utilizado para ejecutar una orden SQL en un objeto PDO es el método exec (línea 63). La ejecución de la consulta puede iniciar un PDOException. Por lo tanto, este también se gestiona. La función devuelve un mensaje de error en caso de error; de lo contrario, devuelve una cadena vacía.

7.3. Llenado de la tabla de personas (mysql_03)

El siguiente script ejecuta los comandos SQL que se encuentran en el siguiente archivo de texto [creation.txt]:

drop table personnes
create table personnes (prenom varchar(30) not null, nom varchar(30) not null, age integer not null, primary key (nom,prenom))
insert into personnes values('Paul','Langevin',48)
insert into personnes values ('Sylvie','Lefur',70)
insert into personnes values ('Pierre','Nicazou',35)
insert into personnes values ('Geraldine','Colou',26)
insert into personnes values ('Paulette','Girond',56)


<?php

// conexión a la base de datos MySql
// identidad del usuario
$ID = "admpersonnes";
$PWD = "nobody";
// identidad de la base de datos
$DSN = "mysql:host=localhost;dbname=dbpersonnes";
// identidad del archivo de texto de comandos SQL a ejecutar
$TEXTE = "creation.txt";

// conexión
list($erreur, $connexion) = connecte($DSN, $ID, $PWD);
if ($erreur) {
  print "Erreur lors de la connexion à la base [$DSN] sous l'identité ($ID,$PWD) : $erreur\n";
  exit;
}

// creación y llenado de la tabla
$erreurs = exécuterCommandes($connexion, $TEXTE, 1, 0);
//visualización del número de errores
print "il y a eu $erreurs[0] erreurs\n";
for ($i = 1; $i < count($erreurs); $i++)
  print "$erreurs[$i]\n";

// se desconecta y se sale
déconnecte($connexion);
exit;

// ---------------------------------------------------------------------------------
function connecte($dsn, $login, $pwd) {
...
}

// ---------------------------------------------------------------------------------
function déconnecte($connexion) {
 ...
}

// ---------------------------------------------------------------------------------
function exécuteRequête($connexion, $sql) {
  // ejecuta la consulta $sql en la conexión $connexion
  // devuelve 1 mensaje de error si hay un error; de lo contrario, devuelve la cadena vacía
  ...
}

// ---------------------------------------------------------------------------------
function exécuterCommandes($connexion, $SQL, $suivi=0, $arrêt=1) {
  // utiliza la conexión $connexion
  // ejecuta los comandos SQL contenidos en el archivo de texto $SQL
  // este archivo es un archivo de comandos SQL que se ejecutarán a razón de uno por línea
  // si $suivi=1, entonces cada vez que se ejecuta un comando SQL se muestra un mensaje indicando si se realizó con éxito o falló
  // si $arrêt=1, la función se detiene ante el primer error encontrado; de lo contrario, ejecuta todos los comandos SQL
  // la función devuelve un array (número de errores, error1, error2, ...)
  
  // se verifica la presencia del archivo $SQL
  if (! file_exists($SQL))
    return array(1, "Le fichier $SQL n'existe pas");

  // ejecución de las consultas SQL contenidas en $SQL
  // se colocan en una matriz
  $requêtes = file($SQL);
  // se ejecutan; al principio no hay errores
  $erreurs = array(0);
  for ($i = 0; $i < count($requêtes); $i++) {
    //¿Hay alguna consulta vacía?
    if (preg_match("/^\s*$/", $requêtes[$i]))
      continue;
    // ejecución de la consulta $i
    $erreur = exécuteRequête($connexion, $requêtes[$i]);
    //¿Hubo algún error?
    if ($erreur) {
      // otro error
      $erreurs[0]++;
      // mensaje de error
      $msg = "$requêtes[$i] : Erreur ($erreur)\n";
      $erreurs[] = $msg;
      // ¿Seguimiento en pantalla o no?
      if ($suivi)
        print "$msg\n";
      // ¿Nos detenemos?
      if ($arrêt)
        return $erreurs;
    } else
    if ($suivi)
      print "$requêtes[$i] : Exécution réussie\n";
  }//para
  // regreso
  return $erreurs;
}

Resultados en pantalla:

drop table personnes : Exécution réussie
create table personnes (prenom varchar(30) not null, nom varchar(30) not null, age integer not null, primary key (nom,prenom)) : Exécution réussie
insert into personnes values('Paul','Langevin',48) : Exécution réussie
insert into personnes values ('Sylvie','Lefur',70) : Exécution réussie
insert into personnes values ('Pierre','Nicazou',35) : Exécution réussie
insert into personnes values ('Geraldine','Colou',26) : Exécution réussie
insert into personnes values ('Paulette','Girond',56) : Exécution réussie
il y a eu 0 erreurs

Las inserciones realizadas se pueden ver con PhpMyAdmin:

 

Comentarios

La novedad radica en la función exécuterCommandes de las líneas 48 a 90. Esta función ejecuta en la conexión $connexion las órdenes SQL que se encuentran en el archivo de texto denominado $SQL. Devuelve una tabla de errores ($nbErreurs, $msg1, $msg2, …) donde $nbErreurs es el número de errores, $msgi es el mensaje de error n.º i.. Si no hay errores, la tabla devuelta es la tabla array(0).

7.4. Ejecución de cualquier consulta SQL (mysql_04)

El siguiente script muestra la ejecución de las órdenes SQL del siguiente archivo de texto [sql.txt]:

select * from personnes
select nom,prenom from personnes order by nom asc, prenom desc
select * from personnes where age between 20 and 40 order by age desc, nom asc, prenom asc
insert into personnes values('Josette','Bruneau',46)
update personnes set age=47 where nom='Bruneau'
select * from personnes where nom='Bruneau'
delete from personnes where nom='Bruneau'
select * from personnes where nom='Bruneau'
xselect * from personnes where nom='Bruneau'

Entre estas órdenes SQL, se encuentra la orden «select», que devuelve resultados de la base de datos; las órdenes «insert», «update» y «delete», que modifican la base de datos sin devolver resultados; y, por último, órdenes erróneas como la última (xselect).


<?php

// conexión a la base de datos MySql
// la identidad del usuario
$ID = "admpersonnes";
$PWD = "nobody";
// identidad de la base de datos
$DSN = "mysql:host=localhost;dbname=dbpersonnes";
// identidad del archivo de texto de comandos SQL a ejecutar
$TEXTE = "sql.txt";

// conexión
list($erreur, $connexion) = connecte($DSN, $ID, $PWD);
if ($erreur) {
  print "Erreur lors de la connexion à la base [$DSN] sous l'identité ($ID,$PWD) : $erreur\n";
  exit;
}

// ejecución de los comandos SQL
$erreurs = exécuterCommandes($connexion, $TEXTE, 1, 0);
//visualización del número de errores
print "il y a eu $erreurs[0] erreur(s)\n";
for ($i = 1; $i < count($erreurs); $i++)
  print "$erreurs[$i]\n";

// se desconecta y se sale
déconnecte($connexion);
exit;

// ---------------------------------------------------------------------------------
function connecte($dsn, $login, $pwd) {
  // se conecta ($login, $pwd) a la base $dsn
  // devuelve el ID de la conexión junto con un mensaje de error
  ...
}

// ---------------------------------------------------------------------------------
function déconnecte($connexion) {
  ...
}

// ---------------------------------------------------------------------------------
function exécuteRequête($connexion, $sql) {
  // ejecuta la consulta $sql en la conexión $connexion
  // devuelve una matriz de 2 elementos ($erreur, $résultat)
  
  // se determina si se trata de un select o no
  $commande = "";
  if (preg_match("/^\s*(\S+)/", $sql, $champs)) {
    $commande = $champs[0];
  }
  // ejecución del comando
  try {
    if (strtolower($commande) == "select") {
      $res = $connexion->query($sql);
    } else {
      $res = $connexion->exec($sql);
      if($res===FALSE){
        $info=$connexion->errorInfo();
        return array($info[2],null);
      }
    }
    // respuesta sin error
    return array("", $res);
  } catch (PDOException $e) {
    // respuesta con error
    return array($e->getMessage(), null);
  }
}

// ---------------------------------------------------------------------------------
function exécuterCommandes($connexion, $SQL, $suivi=0, $arrêt=1) {
  // utiliza la conexión $connexion
  // ejecuta los comandos SQL contenidos en el archivo de texto $SQL
  //: este archivo es un archivo de comandos SQL que se ejecutarán a razón de uno por línea
  // si $suivi=1, entonces cada vez que se ejecuta un comando SQL se muestra un mensaje indicando si se realizó con éxito o falló
  // si $arrêt=1, la función se detiene ante el primer error encontrado; de lo contrario, ejecuta todos los comandos SQL
  //: la función devuelve un array (número de errores, error1, error2, ...)
  // se verifica la presencia del archivo $SQL
  if (!file_exists($SQL))
    return array(1, "Le fichier $SQL n'existe pas");

  // ejecución de las consultas SQL contenidas en $TEXTE
  // se colocan en una tabla
  $requêtes = file($SQL);
  // se ejecutan; al principio no hay errores
  $erreurs = array(0);
  for ($i = 0; $i < count($requêtes); $i++) {
    //¿Tenemos una consulta vacía?
    if (preg_match("/^\s*$/", $requêtes[$i]))
      continue;
    // ejecución de la consulta $i
    list($erreur, $res) = exécuteRequête($connexion, $requêtes[$i]);
    //¿Hubo algún error?
    if ($erreur) {
      // otro error
      $erreurs[0]++;
      // mensaje de error
      $msg = "$requêtes[$i] : Erreur ($erreur)\n";
      $erreurs[] = $msg;
      // ¿Seguimiento en pantalla o no?
      if ($suivi)
        print "$msg\n";
      // ¿Nos detenemos?
      if ($arrêt)
        return $erreurs;
    } else
    if ($suivi) {
      print "$requêtes[$i] : Exécution réussie\n";
      // información sobre el resultado de la consulta ejecutada
      afficherInfos($res);
    }
  }//para
  // retorno
  return $erreurs;
}

// ---------------------------------------------------------------------------------
function afficherInfos($résultat) {
  // muestra el resultado $résultat de una consulta SQL
  // ¿Se trataba de un SELECT?
  if ($résultat instanceof PDOStatement) {
    // se muestran los nombres de los campos
    $titre = "";
    $nbColonnes = $résultat->columnCount();
    for ($i = 0; $i < $nbColonnes; $i++) {
      $infos = $résultat->getColumnMeta($i);
      $titre.=$infos['name'] . ",";
    }
    // se elimina el último carácter,
    $titre = substr($titre, 0, strlen($titre) - 1);
    // se muestra la lista de campos
    print "$titre\n";
    // línea separadora
    $séparateurs = "";
    for ($i = 0; $i < strlen($titre); $i++) {
      $séparateurs.="-";
    }
    print "$séparateurs\n";
    // datos
    foreach ($résultat as $ligne) {
      $data = "";
      for ($i = 0; $i < $nbColonnes; $i++) {
        $data.=$ligne[$i] . ",";
      }

      // se elimina el último carácter,
      $data = substr($data, 0, strlen($data) - 1);
      // se muestra
      print "$data\n";
    }
  } else {
    // no era una selección
    print " $résultat lignes(s) a (ont) été modifiée(s)\n";
  }
}

Resultados en pantalla:

select * from personnes
 : Exécution réussie
prenom,nom,age
--------------
Geraldine,Colou,26
Paulette,Girond,56
Paul,Langevin,48
Sylvie,Lefur,70
Pierre,Nicazou,35
select nom,prenom from personnes order by nom asc, prenom desc : Exécution réussie
nom,prenom
----------
Colou,Geraldine
Girond,Paulette
Langevin,Paul
Lefur,Sylvie
Nicazou,Pierre
select * from personnes where age between 20 and 40 order by age desc, nom asc, prenom asc : Exécution réussie
prenom,nom,age
--------------
Pierre,Nicazou,35
Geraldine,Colou,26
insert into personnes values('Josette','Bruneau',46) : Exécution réussie
 1 lignes(s) a (ont) été modifiée(s)
update personnes set age=47 where nom='Bruneau' : Exécution réussie
 1 lignes(s) a (ont) été modifiée(s)
select * from personnes where nom='Bruneau' : Exécution réussie
prenom,nom,age
--------------
Josette,Bruneau,47
delete from personnes where nom='Bruneau' : Exécution réussie
 1 lignes(s) a (ont) été modifiée(s)
select * from personnes where nom='Bruneau' : Exécution réussie
prenom,nom,age
--------------
xselect * from personnes where nom='Bruneau' : Erreur (You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'xselect * from personnes where nom='Bruneau'' at line 1)

il y a eu 1 erreur(s)
xselect * from personnes where nom='Bruneau' : Erreur (You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'xselect * from personnes where nom='Bruneau'' at line 1)

Comentarios

Cada uno de los comandos del archivo de texto [sql.txt] se ejecuta mediante la función exécuteRequête de la línea 43.

  • línea 43: los dos parámetros de la función son la conexión ($connexion) en la que se deben ejecutar los comandos SQL y el comando SQL ($sql) que se va a ejecutar. La función devuelve una matriz de dos valores ($erreur, $résultat) donde
    • $erreur es un mensaje de error que puede estar vacío si no se produjo ningún error
    • $résultat: el resultado devuelto por la ejecución de la orden SQL. Este resultado varía dependiendo de si la orden es una orden select o bien una orden insert, update, delete.
  • líneas 48-51: se recupera el primer elemento del pedido SQL para determinar si se trata de un pedido select o bien de un pedido insert, update, delete.
  • línea 55: en el caso de una orden select, esta se ejecuta con el método [PDO]->query("orden select"). El resultado devuelto es un objeto de tipo PDOStatement.
  • línea 57: en el caso de una orden insert, update o delete, esta se ejecuta con el método [PDO]->exec("orden SQL"). El resultado devuelto es el número de filas modificadas por la orden SQL. Así, si una orden SQL delete elimina dos líneas, el resultado devuelto es el número entero 2. Si se produce un error durante la ejecución, el resultado devuelto es el valor booleano false. En este caso, el método [PDO]->errorinfo() proporciona información sobre el error en forma de una matriz de valores. El elemento con índice 2 de esta matriz es el mensaje de error.
  • líneas 58-60: manejo de un posible error en la operación [PDO]->exec("orden SQL").
  • líneas 65-68: manejo de una posible excepción
  • línea 72: la función exécuterCommandes ejecuta en la conexión $connexion los comandos SQL almacenados en el archivo de texto $SQL. Este es un código que ya hemos visto antes, con un pequeño detalle: la línea 111.
  • línea 111: la función exécuteRequête devolvió una matriz ($erreur, $résultat) o $résultat es el resultado de la ejecución de un comando SQL. Este resultado varía dependiendo de si la orden SQL era una orden select o bien una orden insert, update, delete. La función afficherInfos muestra información sobre este resultado.
  • línea 122: si la orden SQL fuera una orden select, el resultado sería de tipo PDOStatement. Este tipo representa una tabla compuesta por filas y columnas.
  • línea 125: el método [PDOStatement]->getColumnCount() devuelve el número de columnas de la tabla resultante de select
  • línea 127: el método [PDOStatement]->getMeta(i) devuelve un diccionario con información sobre la columna n.º i de la tabla resultante de select.. En este diccionario, el valor asociado a la clave 'name' es el nombre de la columna.
  • líneas 127-129: los nombres de las columnas de la tabla de resultados de select se concatenan en una cadena de caracteres.
  • líneas 141-145: un objeto de tipo PDOStatement puede recorrerse mediante un bucle foreach. En cada iteración, el elemento obtenido es una fila de la tabla de resultados de select en forma de una matriz de valores que representa los valores de las diferentes columnas de la fila. Todos estos valores se muestran mediante un bucle for.
  • línea 154: el resultado de la ejecución de una orden insert, update, delete es el número de filas modificadas por la orden.
https://stahe.github.io
Este curso-tutorial, escrito por Serge Tahé, se pone a disposición del público según los términos de la Licencia Creative Commons Reconocimiento – Sin uso comercial – Compartir con los mismos términos 3.0 sin modificaciones.