Skip to content

7. Utilización del MySQL DBMS

Escribiremos scripts PHP utilizando una base de datos MySQL:

El MySQL DBMS está incluido en el paquete WampServer (véase la sección 2.1.1). Mostraremos cómo crear una base de datos y un usuario MySQL.

  • Una vez iniciado, WampServer puede gestionarse a través de un icono [1] situado en la parte inferior derecha de la barra de tareas.
  • En [2], inicie la herramienta de administración MySQL

Crear una base de datos [dbpersonnes]:

Image

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

  • En [1], el nombre de usuario
  • En [2], el servidor DBMS sobre el que concede permisos
  • en [3], su contraseña
  • en [4], igual que arriba
  • en [5], no concedemos ningún derecho a este usuario
  • en [6], crear el usuario
  • en [7], volver a la phpMyAdmin página de inicio
  • en [8], utilice el enlace [Privilegios] de esta página para modificar los privilegios del usuario [admpersonnes] [9].

Image

  • En [10], especifique que desea conceder al usuario [admpersonnes] derechos sobre la base de datos [dbpersonnes]
  • En [11], confirme la selección
  • mediante el enlace [12] [Seleccionar todo], conceda al usuario [admpersonnes] todos los derechos sobre la base de datos [dbpersonnes] [13]
  • Confirmar en [14]

Ahora sí:

  • una base de datos MySQL [dbpersonnes]
  • un usuario [admpersonnes / nobody] que tiene acceso completo a esta base de datos

Escribiremos scripts PHP para trabajar con la base de datos. PHP dispone de varias librerías para gestionar bases de datos. Utilizaremos la librería PDO (PHP Data Objects), que actúa como interfaz entre el código PHP y el DBMS:

La biblioteca PDO permite al script PHP abstraerse de la naturaleza exacta del DBMS que se está utilizando. Así, como se muestra arriba, el MySQL DBMS puede ser reemplazado por el DBMS de Postgres con un impacto mínimo en el código del script PHP. Esta librería no está disponible por defecto. Puede comprobar su disponibilidad como sigue:

  • 1: En el icono de administración WampServer, seleccione la opción [Extensiones PHP / PHP]
  • 2: Verá las distintas extensiones PDO disponibles y las que están activas: [PHP_pdo_mysql] para el MySQL DBMS, [PHP_pdo_sqlite] para el SQLite DBMS. Para activar una extensión, basta con hacer clic sobre ella. El intérprete PHP se reiniciará con la nueva extensión activada.

7.1. Conexión a una base de datos MySQL - 1 (mysql_01)

La conexión a un DBMS se realiza mediante la creación de un objeto PDO. El constructor acepta varios parámetros:

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

Los parámetros tienen el siguiente significado:

$dsn
(Nombre de la fuente de datos) es una cadena que especifica el tipo de DBMS y su ubicación en Internet. La cadena
"mysql:host=localhost" indica que se trata de un MySQL DBMS ejecutándose en el servidor local. Esta cadena
puede incluir otros parámetros, como el puerto de escucha DBMS y el nombre de la base de datos a
al que quieres conectarte: "mysql:host=localhost:port=3306:dbname=dbpeople".
$usuario
nombre de usuario del usuario que se conecta
$passwd
su contraseña
$driver_options
una matriz de opciones para el controlador DBMS

Sólo es necesario el primer parámetro. El objeto así creado servirá de vehículo para todas las operaciones que se realicen en la base de datos a la que se haya conectado. Si no se ha podido crear el objeto PDO, se lanza un PDOException.

He aquí un ejemplo de conexión:


1. <?php
2. 
3. // Connect to a MySQL database
4. // The user ID is (admpersonnes,nobody)
5. $ID = "admpersonnes";
6. $PWD = "nobody";
7. $HOST = "localhost";
8. 
9. try {
10.   // connection
11.   $dbh = new PDO("mysql:host=$HOST", $ID, $PWD);
12.   print "Connection successful\n";
13.   // close
14.   $dbh = null;
15. } catch (PDOException $e) {
16.   print "Error: " . $e->getMessage() . "\n";
17.   exit();
18. }

Resultados:

Connection successful

Comentarios

  • Línea 11: La conexión a un DBMS se establece creando un objeto PDO. Aquí se utiliza el constructor con los siguientes parámetros:
    • una cadena que especifica el tipo de DBMS y su ubicación en Internet. La cadena "mysql:host=localhost" indica que se trata de un MySQL DBMS ejecutándose en el servidor local. No se ha especificado el puerto. Por defecto se utiliza el puerto 3306. Tampoco se ha especificado el nombre de la base de datos. Se establecerá entonces una conexión con el MySQL DBMS, con la selección de una base de datos específica que se hará más tarde.
    • un usuario ID
    • su contraseña
  • Línea 14: La conexión se cierra destruyendo el objeto PDO creado inicialmente.
  • Línea 15: La conexión a un DBMS puede fallar. En este caso, se lanza un PDOException.

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


1. <?php
2. 
3. // Connect to the MySQL database
4. // user ID
5. $ID = "admpersonnes";
6. $PWD = "nobody";
7. // database name
8. $DSN = "mysql:host=localhost;dbname=dbpersonnes";
9. 
10. // connection
11. list($error, $connection) = connect($DSN, $ID, $PWD);
12. if ($error) {
13.   print "Error connecting to database [$DSN] using credentials ($ID, $PWD): $error\n";
14.   exit;
15. }
16. 
17. // Delete the 'people' table if it exists
18. $query = "drop table people";
19. $error = executeQuery($connection, $query);
20. // Was there an error?
21. if ($error)
22.   print "$query: Error ($error)\n";
23. else
24.   print "$query: Execution successful\n";
25. // Create the people table
26. $query = "create table people (first_name varchar(30) NOT NULL, last_name varchar(30) NOT NULL, age integer NOT NULL, primary key(last_name, first_name))";
27. $error = executeQuery($connection, $query);
28. // Was there an error?
29. if ($error)
30.   print "$query: Error ($error)\n";
31. else
32.   print "$query: Execution successful\n";
33. // Disconnect and exit
34. disconnect($connection);
35. exit;
36. 
37. // ---------------------------------------------------------------------------------
38. function connect($dsn, $login, $pwd) {
39.   // Connects ($login, $pwd) to the database $dsn
40.   // returns the connection ID and an error code
41.   try {
42.     // connection
43.     $dbh = new PDO($dsn, $login, $pwd);
44.     // return without error
45.     return array("", $dbh);
46.   } catch (PDOException $e) {
47.     // return with error
48.     return array($e->getMessage(), null);
49.   }
50. }
51. 
52. // ---------------------------------------------------------------------------------
53. function disconnect($connection) {
54.   // closes the connection identified by $connection
55.   $connection = null;
56. }
57. 
58. // ---------------------------------------------------------------------------------
59. function executeQuery($connection, $sql) {
60.   // executes the $sql query on the $connection
61.   try {
62.     $connection->exec($sql);
63.     // return without error
64.     return "";
65.   } catch (PDOException $e) {
66.     // return with error
67.     return $e->getMessage();
68.   }
69. }

Resultados:

drop table people: Execution successful
create table people (first_name varchar(30) NOT NULL, last_name varchar(30) NOT NULL, age integer NOT NULL, primary key(last_name, first_name)): Execution successful

En PHPMyAdmin, puede ver que la tabla existe:

 

Comentarios

  • Líneas 38-50: El "conectar establece una conexión con un sistema de gestión de bases de datos (DBMS). Devuelve una matriz ($error, $connection*) donde $conexión* es la conexión establecida o "null si no se ha podido establecer la conexión. En este último caso, $error contiene un mensaje de error.
  • Líneas 53-56: La `desconectar cierra una conexión
  • Línea 59: El *executeQuery permite ejecutar una sentencia SQL en una conexión. La conexión es un objeto PDO. El método utilizado para ejecutar una sentencia SQL sobre un objeto PDO es la función exec* (línea 63). La ejecución de la consulta puede lanzar un PDOException. Por lo tanto, esto se maneja. La función devuelve un mensaje de error si se produce un error, una cadena vacía en caso contrario.

7.3. Rellenar la tabla de personas (mysql_03)

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

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


1. <?php
2. 
3. // Connect to the MySQL database
4. // user ID
5. $ID = "admpersonnes";
6. $PWD = "nobody";
7. // database name
8. $DSN = "mysql:host=localhost;dbname=dbpersonnes";
9. // path to the text file containing the SQL commands to execute
10. $TEXT = "creation.txt";
11. 
12. // connection
13. list($error, $connection) = connect($DSN, $ID, $PWD);
14. if ($error) {
15.   print "Error connecting to database [$DSN] using credentials ($ID, $PWD): $error\n";
16.   exit;
17. }
18. 
19. // Create and populate the table
20. $errors = executeCommands($connection, $TEXT, 1, 0);
21. //display number of errors
22. print "There were $errors[0] errors\n";
23. for ($i = 1; $i < count($errors); $i++)
24.   print "$errors[$i]\n";
25. 
26. // Disconnect and exit
27. disconnect($connection);
28. exit;
29. 
30. // ---------------------------------------------------------------------------------
31. function connect($dsn, $login, $pwd) {
32. ...
33. }
34. 
35. // ---------------------------------------------------------------------------------
36. function disconnect($connection) {
37. ...
38. }
39. 
40. // ---------------------------------------------------------------------------------
41. function executeQuery($connection, $sql) {
42.   // executes the $sql query on the $connection
43.   // returns an error message if an error occurs, otherwise returns an empty string
44.   ...
45. }
46. 
47. // ---------------------------------------------------------------------------------
48. function executeCommands($connection, $SQL, $trace=0, $stop=1) {
49.   // uses the $connection connection
50.   // executes the SQL commands contained in the text file $SQL
51.   // this file is a file of SQL commands to be executed, one per line
52.   // if $tracking=1, then each execution of an SQL command is followed by a message indicating whether it succeeded or failed
53.   // If $stop=1, the function stops at the first error encountered; otherwise, it executes all SQL commands
54.   // The function returns an array (number of errors, error1, error2, ...)
55.   
56.   // Check if the $SQL file exists
57.   if (!file_exists($SQL))
58.     return array(1, "The $SQL file does not exist");
59. 
60.   // execute the SQL queries contained in $SQL
61.   // we put them into an array
62.   $queries = file($SQL);
63.   // execute them - initially, no errors
64.   $errors = array(0);
65.   for ($i = 0; $i < count($queries); $i++) {
66.     // Is the query empty?
67.     if (preg_match("/^\s*$/", $queries[$i]))
68.       continue;
69.     // execute query $i
70.     $error = executeRequest($connection, $requests[$i]);
71.     //was there an error?
72.     if ($error) {
73.       // one more error
74.       $errors[0]++;
75.       // error message
76.       $msg = "$requests[$i]: Error ($error)\n";
77.       $errors[] = $msg;
78.       // Show on screen or not?
79.       if ($tracking)
80.         print "$msg\n";
81.       // Stop?
82.       if ($stop)
83.         return $errors;
84.     } else
85.     if ($tracking)
86.       print "$requests[$i]: Execution successful\n";
87.   }//for
88.   // return
89.   return $errors;
90. }

Pantalla resultados:

drop table people: Execution successful
create table people (first_name varchar(30) not null, last_name varchar(30) not null, age integer not null, primary key (last_name, first_name)): Execution successful
insert into people values('Paul','Langevin',48): Execution successful
insert into people values ('Sylvie','Lefur',70): Execution successful
insert into people values ('Pierre','Nicazou',35): Execution successful
insert into people values ('Geraldine','Colou',26) : Execution successful
insert into people values ('Paulette','Girond',56) : Execution successful
There were 0 errors

Las inserciones realizadas son visibles en phpMyAdmin:

 

Comentarios

La nueva función es la executeCommands en las líneas 48-90. Esta función ejecuta los comandos SQL que se encuentran en el archivo de texto llamado $SQL en el $conexión. Devuelve una matriz de errores ($nbErrors, $msg1, $msg2, ..) donde $nbErrors es el número de errores, y $msg1 es el número de mensaje de error i. Si no hay errores, el array devuelto es array(0).

7.4. Ejecución de consultas SQL arbitrarias (mysql_04)

El siguiente script demuestra la ejecución de comandos SQL desde el siguiente archivo de texto [sql.txt]:

select * from people
select last_name, first_name from people order by last_name asc, first_name desc
select * from people where age between 20 and 40 order by age desc, last_name asc, first_name asc
insert into people values('Josette','Bruneau',46)
update people set age=47 where last_name='Bruneau'
SELECT * FROM people WHERE last_name='Bruneau'
delete from people where last_name='Bruneau'
SELECT * FROM people WHERE last_name='Bruneau'
xselect * from people where name='Bruneau'

Entre estas declaraciones SQL, se encuentra la SELECT que devuelve resultados de la base de datos; la sentencia INSERT, UPDATE, y DELETE que modifican la base de datos sin devolver resultados; y, por último, las sentencias no válidas, como la última (xselect).


1. <?php
2. 
3. // Connect to the MySQL database
4. // user ID
5. $ID = "admpersonnes";
6. $PWD = "nobody";
7. // database credentials
8. $DSN = "mysql:host=localhost;dbname=dbpersonnes";
9. // path to the text file containing the SQL commands to execute
10. $TEXT = "sql.txt";
11. 
12. // connection
13. list($error, $connection) = connect($DSN, $ID, $PWD);
14. if ($error) {
15.   print "Error connecting to database [$DSN] using credentials ($ID, $PWD): $error\n";
16.   exit;
17. }
18. 
19. // execute SQL commands
20. $errors = executeCommands($connection, $TEXT, 1, 0);
21. // display number of errors
22. print "There were $errors[0] error(s)\n";
23. for ($i = 1; $i < count($errors); $i++)
24.   print "$errors[$i]\n";
25. 
26. // Disconnect and exit
27. disconnect($connection);
28. exit;
29. 
30. // ---------------------------------------------------------------------------------
31. function connect($dsn, $login, $pwd) {
32.   // Connects ($login, $pwd) to the $dsn database
33.   // returns the connection ID and an error message
34.   ...
35. }
36. 
37. // ---------------------------------------------------------------------------------
38. function disconnect($connection) {
39.   ...
40. }
41. 
42. // ---------------------------------------------------------------------------------
43. function executeQuery($connection, $sql) {
44.   // executes the $sql query on the $connection
45.   // returns an array of 2 elements ($error, $result)
46.   
47.   // Determine whether it is a SELECT query or not
48.   $query = "";
49.   if (preg_match("/^\s*(\S+)/", $sql, $fields)) {
50.     $command = $fields[0];
51.   }
52.   // execute the command
53.   try {
54.     if (strtolower($command) == "select") {
55.       $res = $connection->query($sql);
56.     } else {
57.       $res = $connection->exec($sql);
58.       if($res===FALSE){
59.         $info = $connection->errorInfo();
60.         return array($info[2], null);
61.       }
62.     }
63.     // Return without error
64.     return array("", $res);
65.   } catch (PDOException $e) {
66.     // return with error
67.     return array($e->getMessage(), null);
68.   }
69. }
70. 
71. // ---------------------------------------------------------------------------------
72. function executeCommands($connection, $SQL, $tracking=0, $stop=1) {
73.   // uses the $connection connection
74.   // executes the SQL commands contained in the text file $SQL
75.   // this file is a file of SQL commands to be executed, one per line
76.   // if $log=1, then each execution of an SQL command is followed by a message indicating whether it succeeded or failed
77.   // if $stop=1, the function stops at the first error encountered; otherwise, it executes all SQL commands
78.   // the function returns an array (number of errors, error1, error2, ...)
79.   // Check if the $SQL file exists
80.   if (!file_exists($SQL))
81.     return array(1, "The $SQL file does not exist");
82. 
83.   // execute the SQL queries contained in $TEXT
84.   // we put them into an array
85.   $queries = file($SQL);
86.   // execute them - initially, no errors
87.   $errors = array(0);
88.   for ($i = 0; $i < count($queries); $i++) {
89.     // Is the query empty?
90.     if (preg_match("/^\s*$/", $requests[$i]))
91.       continue;
92.     // execute query $i
93.     list($error, $res) = executeQuery($connection, $queries[$i]);
94.     // Was there an error?
95.     if ($error) {
96.       // one more error
97.       $errors[0]++;
98.       // error message
99.       $msg = "$requests[$i]: Error ($error)\n";
100.       $errors[] = $msg;
101.       // Log to screen or not?
102.       if ($log)
103.         print "$msg\n";
104.       // should we stop?
105.       if ($stop)
106.         return $errors;
107.     } else
108.     if ($tracking) {
109.       print "$requests[$i]: Execution successful\n";
110.       // information about the result of the executed request
111.       displayInfo($res);
112.     }
113.   }//for
114.   // return
115.   return $errors;
116. }
117. 
118. // ---------------------------------------------------------------------------------
119. function displayInfo($result) {
120.   // displays the $result of an SQL query
121.   // Was it a SELECT query?
122.   if ($result instanceof PDOStatement) {
123.     // display the field names
124.     $title = "";
125.     $numberOfColumns = $result->columnCount();
126.     for ($i = 0; $i < $numberOfColumns; $i++) {
127.       $info = $result->getColumnMeta($i);
128.       $title.=$info['name'] . ",";
129.     }
130.     // remove the last character,
131.     $title = substr($title, 0, strlen($title) - 1);
132.     // display the list of fields
133.     print "$title\n";
134.     // separator line
135.     $separators = "";
136.     for ($i = 0; $i < strlen($title); $i++) {
137.       $separators.="-";
138.     }
139.     print "$separators\n";
140.     // data
141.     foreach ($result as $row) {
142.       $data = "";
143.       for ($i = 0; $i < $nbColumns; $i++) {
144.         $data.=$row[$i] . ",";
145.       }
146. 
147.       // remove the last character,
148.       $data = substr($data, 0, strlen($data) - 1);
149.       // display
150.       print "$data\n";
151.     }
152.   } else {
153.     // it wasn't a SELECT
154.     print " $result lines have been modified\n";
155.   }
156. }

Pantalla resultados:

select * from people
: Execution successful
first_name,last_name,age
--------------
Geraldine, Colou, 26
Paulette, Girond, 56
Paul, Langevin, 48
Sylvie, Lefur, 70
Pierre, Nicazou, 35
SELECT last_name, first_name FROM people ORDER BY last_name ASC, first_name DESC: Execution successful
last_name,first_name
----------
Colou, Geraldine
Girond, Paulette
Langevin, Paul
Lefur, Sylvie
Nicazou, Pierre
select * from people where age between 20 and 40 order by age desc, last_name asc, first_name asc : Query executed successfully
first_name,last_name,age
--------------
Pierre, Nicazou, 35
Geraldine,Colou,26
insert into people values('Josette','Bruneau',46) : Execution successful
1 row(s) was (were) modified
update people set age=47 where name='Bruneau': Execution successful
1 row(s) has (have) been modified
SELECT * FROM people WHERE last_name='Bruneau': Execution successful
first_name,last_name,age
--------------
Josette,Bruneau,47
delete from people where last_name='Bruneau' : Execution successful
1 row(s) modified
select * from people where last_name='Bruneau': Execution successful
first_name,last_name,age
--------------
xselect * from people where last_name='Bruneau': Error (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 people where last_name='Bruneau'' at line 1)

There was 1 error
xselect * from people where last_name='Bruneau': Error (You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the correct syntax to use near 'xselect * from people where last_name='Bruneau'' at line 1)

Comentarios

Cada comando del archivo de texto [sql.txt] es ejecutado por el comando ejecutaQuery en la línea 43.

  • Línea 43: Los dos parámetros de la función son la conexión ($conexión) en el que se ejecutarán los comandos SQL y el comando SQL ($sql) a ejecutar. La función devuelve una matriz de dos valores (error, resultado) donde
    • $error es un mensaje de error, que puede estar vacío si no se ha producido ningún error
    • $resultado: el resultado obtenido al ejecutar la sentencia SQL. Este resultado varía en función de si la sentencia es un SELECT o una INSERT, UPDATE, o DELETE declaración.
  • Líneas 48-51: Recuperamos el primer elemento de la sentencia SQL para determinar si es un SELECT o una INSERT, UPDATE, o DELETE declaración.
  • línea 55: en caso de SELECT se ejecuta mediante la sentencia [PDO]->query("SELECT statement") método. El resultado devuelto es un PDOStatement objeto.
  • Línea 57: En caso de INSERT, UPDATE, o DELETE se ejecuta mediante la sentencia [PDO]->exec("SQL statement") método. El resultado devuelto es el número de filas modificadas por la sentencia SQL. Por lo tanto, si una sentencia DELETE si la sentencia SQL borra dos filas, el resultado devuelto es el entero 2. Si se produce un error durante la ejecución, el resultado devuelto es el booleano falso. En este caso, el [PDO]->errorinfo() proporciona información sobre el error en forma de una matriz de valores. El elemento en el índice 2 de esta matriz es el mensaje de error.
  • líneas 58-60: gestión de los errores de la aplicación [PDO]->exec("SQL statement") operación.
  • líneas 65-68: Manejo de excepciones
  • Línea 72: El executeCommands ejecuta los comandos SQL almacenados en el archivo de texto *$SQL* en la conexión *$conexión*. Este es el código que ya hemos visto, con una pequeña diferencia: la línea 111.
  • Línea 111: El executeQuery devuelve una matriz (*$error*, *$resultado*), donde $resultado*es el resultado de ejecutar un comando SQL. Este resultado varía en función de si el comando SQL fue un *SELECTdeclaración o un *INSERT, *UPDATE, o *DELETEdeclaración. La dirección *displayInfo` muestra información sobre este resultado.
  • Línea 122: Si la sentencia SQL era un SELECT el resultado es de tipo PDOStatement. Este tipo representa una tabla formada por filas y columnas.
  • línea 125: el método [PDOStatement]->getColumnCount() devuelve el número de columnas de la tabla resultante de el SELECT
  • Línea 127: El [PDOStatement]->getMeta(i) devuelve un diccionario con información sobre la columna i de la SELECT tabla de resultados. En este diccionario, el valor asociado a la clave "nombre" es el nombre de la columna.
  • Líneas 127-129: Los nombres de columna de la base de datos SELECT de la tabla de resultados se concatenan en una cadena.
  • Líneas 141-145: A PDOStatement se puede iterar sobre él utilizando un foreach bucle. En cada iteración, el elemento devuelto es una fila del bucle SELECT en forma de matriz de valores que representan los valores de las distintas columnas de la fila. Todos estos valores se muestran mediante un para bucle.
  • Línea 154: El resultado de ejecutar un INSERT, UPDATE, o DELETE es el número de filas modificadas por la sentencia.
https://tahe.developpez.com
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.