Skip to content

7. Uso de SGBD MySql

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

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

  • Una vez iniciado, WampServer se puede administrar desde un icono [1] situado en la parte inferior derecha de la barra de tareas.
  • En [2], se ejecuta 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], el equipo de SGBD en el que se le conceden 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 vuelve a la página de inicio de phpMyAdmin
  • en [8], se utiliza el enlace [Privileges] de esta página para ir a modificar los del usuario [admpersonnes] [9].

1011

Image

  • en [10], se indica que se desea otorgar al usuario [admpersonnes] derechos sobre la base de datos [dbpersonnes]
  • en [11], se valida la selección
  • con el enlace [12]. En [Tout cocher], se conceden al usuario [admpersonnes] todos los derechos sobre la base de datos [dbpersonnes]. En [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 explotar la base de datos. PHP dispone de diversas bibliotecas para gestionar las 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 Postgres con un impacto mínimo en el código del script PHP. Esta biblioteca no está disponible de forma predeterminada. Se puede comprobar su disponibilidad en de la siguiente manera:

  • 1: desde el icono de administración de WampServer, se selecciona option [PHP / PHP extensions]
  • 2: se ven las diferentes extensiones PDO disponibles y las que están activas: [PHP_pdo_mysql] para SGBD MySQL, [PHP_pdo_sqlite] para SGBD SQL Lite. Para activar una extensión, basta con 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 Fuente 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 de datos 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á de soporte para todas las operaciones realizadas en la base de datos a la que se ha conectado. Si no se ha podido 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 {
  // conexió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 construcció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. En ese caso, se utiliza el puerto 3306 por defecto. Tampoco se indica el nombre de la base de datos. Se establecerá entonces una conexión con el SGBD MySQL, y la selección de una base concreta se realizará más tarde.
    • un nombre de usuario
    • su contraseña
  • línea 14: el cierre de la conexión se realiza mediante la destrucció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);
//¿Ha habido 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);
//¿Ha habido algún error?
if ($erreur)
  print "$requête : Erreur ($erreur)\n";
else
  print "$requête: Exécution réussie\n";
// se desconecta y se sale
déconnecte($connexion);
exit;

// ---------------------------------------------------------------------------------
function connecte($dsn, $login, $pwd) {
  // conectando ($login,$pwd) a la base de datos $dsn
  // devuelve el id de la conexión junto con un código de error
  try {
    // conexión
    $dbh = new PDO($dsn, $login, $pwd);
    // respuesta sin errores
    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 errores
    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 ver la presencia de la tabla:

 

Comentarios

  • líneas 38-50: la función «connect» crea una conexión a un SGBD. Devuelve una matriz ($erreur, $connexion) donde $connexion es la conexión creada o null si no se ha podido crear. En este último caso, $erreur es un mensaje de error.
  • líneas 53-56: la función de desconexión 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 lanzar 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. Relleno 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
$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 rellenado 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 error; de lo contrario, 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, cada ejecución de un comando SQL se muestra con un mensaje que indica si se ha realizado correctamente o no
  // 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 una matriz (número de errores, error1, error2, ...)
  
  // se comprueba 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]);
    //¿Ha habido 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
  // volver
  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 reside en la función exécuterCommandes de las líneas 48-90. Esta función ejecuta en la conexión $connexion los comandos SQL que se encuentran en el archivo de texto con el nombre $SQL. Devuelve una matriz de errores ($nbErreurs, $msg1, $msg2, …) donde $nbErreurs es el número de errores, $msgi el mensaje de error n.º i. Si no hay errores, la matriz devuelta es la matriz array(0).

7.4. Ejecución de consultas SQL cualquiera (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 sin devolver resultados; y, por último, órdenes erróneas como la última (xselect).


<?php

// conexión a la base MySql
// identidad del usuario
$ID = "admpersonnes";
$PWD = "nobody";
// identidad de la base
$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) {
  // 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 una selección 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, cada ejecución de un comando SQL se muestra con un mensaje que indica si se ha realizado correctamente o no
  // 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 una matriz (número de errores, error1, error2, ...)
  // se comprueba 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 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
    list($erreur, $res) = exécuteRequête($connexion, $requêtes[$i]);
    //¿Ha habido 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 una consulta 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 deben ejecutarse 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 ha producido ningún error
    • $résultat: el resultado devuelto por la ejecución de la orden SQL. Este resultado varía según si la orden es una orden select o bien una orden insert, update, delete.
  • líneas 48-51: se recupera el primer elemento de la orden SQL para saber si se trata de una orden select o bien de una orden 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 líneas modificadas por la orden SQL. Así, si una orden SQL delete elimina dos líneas, el resultado devuelto es el 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 de índice 2 de esta matriz es el mensaje de error.
  • líneas 58-60: tratamiento del posible error de la operación [PDO]->exec("orden SQL").
  • líneas 65-68: tratamiento de la 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. Es un código que ya hemos visto antes, salvo por un pequeño detalle: la línea 111.
  • Línea 111: la función exécuteRequête ha devuelto 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 era una orden select, el resultado es 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 la consulta 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 la consulta 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 resultante de la consulta 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 resultante de la consulta select en forma de una matriz de valores que representan los valores de las diferentes columnas de la fila. Se muestran todos estos valores con un bucle for.
  • línea 154: el resultado de la ejecución de una orden insert, update o delete es el número de líneas 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 en las mismas condiciones 3.0 no adaptada.