Skip to content

7. Utilização do SGBD MySQL

Iremos escrever scripts PHP utilizando uma base de dados MySQL:

O SGBD MySQL está incluído no pacote WampServer (ver secção 2.1.1). Vamos mostrar como criar uma base de dados e um utilizador MySQL.

  • Depois de iniciado, o WampServer pode ser gerido através de um ícone [1] localizado no canto inferior direito da barra de tarefas.
  • Em [2], inicie a ferramenta de administração do MySQL

Crie uma base de dados [dbpersonnes]:

Image

Crie um utilizador [admpersonnes] com a palavra-passe [nobody]:

  • Em [1], o nome de utilizador
  • Em [2], o servidor DBMS no qual concede permissões
  • em [3], a sua palavra-passe
  • em [4], o mesmo que acima
  • em [5], não concedemos quaisquer direitos a este utilizador
  • em [6], crie o utilizador
  • em [7], volte à página inicial do phpMyAdmin
  • em [8], utilize o link [Privilégios] nesta página para modificar os privilégios do utilizador [admpersonnes] [9].

Image

  • Em [10], especifique que pretende conceder ao utilizador [admpersonnes] direitos sobre a base de dados [dbpersonnes]
  • Em [11], confirme a seleção
  • utilizando o link [12] [Selecionar tudo], conceda ao utilizador [admpersonnes] todos os direitos sobre a base de dados [dbpersonnes] [13]
  • Confirme em [14]

Agora temos:

  • uma base de dados MySQL [dbpersonnes]
  • um utilizador [admpersonnes / nobody] que tem acesso total a esta base de dados

Vamos escrever scripts PHP para trabalhar com a base de dados. O PHP dispõe de várias bibliotecas para a gestão de bases de dados. Vamos utilizar a biblioteca PDO (PHP Data Objects), que funciona como uma interface entre o código PHP e o SGBD:

A biblioteca PDO permite que o script PHP se abstraia da natureza exata do SGBD que está a ser utilizado. Assim, como mostrado acima, o SGBD MySQL pode ser substituído pelo SGBD Postgres com um impacto mínimo no código do script PHP. Esta biblioteca não está disponível por predefinição. Pode verificar a sua disponibilidade em da seguinte forma:

  • 1: No ícone de administração do WampServer, selecione a opção [PHP / Extensões PHP]
  • 2: Verá as várias extensões PDO disponíveis e aquelas que estão ativas: [PHP_pdo_mysql] para o SGBD MySQL, [PHP_pdo_sqlite] para o SGBD SQLite. Para ativar uma extensão, basta clicar nela. O interpretador PHP é então reiniciado com a nova extensão ativada.

7.1. Ligar-se a uma base de dados MySQL – 1 (mysql_01)

A ligação a um SGBD é feita através da criação de um objeto PDO. O construtor aceita vários parâmetros:

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

Os parâmetros têm os seguintes significados:

$dsn
(Data Source Name) é uma cadeia de caracteres que especifica o tipo de SGBD e a sua localização na Internet. A cadeia
"mysql:host=localhost" indica que estamos a lidar com um SGBD MySQL em execução no servidor local. Esta cadeia
pode incluir outros parâmetros, tais como a porta de escuta do SGBD e o nome da base de dados à
se deseja ligar: "mysql:host=localhost:port=3306:dbname=dbpersonnes".
$user
nome de utilizador do utilizador que está a iniciar sessão
$passwd
a sua palavra-passe
$driver_options
uma matriz de opções para o controlador do SGBD

Apenas o primeiro parâmetro é obrigatório. O objeto criado desta forma servirá então como veículo para todas as operações realizadas na base de dados à qual se ligou. Se o objeto PDO não puder ser criado, é lançada uma PDOException.

Eis um exemplo de uma ligação:


<?php
 
// connection to a MySql database
// user identity is (admpersonnes,nobody)
$ID = "admpersonnes";
$PWD = "nobody";
$HOTE = "localhost";
 
try {
  // connection
  $dbh = new PDO("mysql:host=$HOTE", $ID, $PWD);
  print "Connexion réussie\n";
  // closure
  $dbh = null;
} catch (PDOException $e) {
  print "Erreur : " . $e->getMessage() . "\n";
  exit();
}

Resultados:

Connexion réussie

Comentários

  • Linha 11: A ligação a um SGBD é estabelecida através da criação de um objeto PDO. O construtor é utilizado aqui com os seguintes parâmetros:
    • uma string que especifica o tipo de SGBD e a sua localização na Internet. A string «mysql:host=localhost» indica que estamos a lidar com um SGBD MySQL em execução no servidor local. A porta não foi especificada. Por conseguinte, é utilizada a porta 3306 por predefinição. O nome da base de dados também não foi especificado. Será então estabelecida uma ligação ao SGBD MySQL, devendo a seleção de uma base de dados específica ser feita posteriormente.
    • um ID de utilizador
    • a sua palavra-passe
  • Linha 14: A ligação é encerrada através da destruição do objeto PDO criado inicialmente.
  • Linha 15: A ligação a um SGBD pode falhar. Neste caso, é lançada uma PDOException.

7.2. Criação de uma tabela MySQL (mysql_02)


<?php
 
// connection to the MySql database
// user identity
$ID = "admpersonnes";
$PWD = "nobody";
// base identity
$DSN = "mysql:host=localhost;dbname=dbpersonnes";
 
// connection
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;
}
 
// delete the people table if it exists
$requête = "drop table personnes";
$erreur = exécuteRequête($connexion, $requête);
//was there a mistake?
if ($erreur)
  print "$requête : Erreur ($erreur)\n";
else
  print "$requête: Exécution réussie\n";
// create people table
$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);
//was there a mistake?
if ($erreur)
  print "$requête : Erreur ($erreur)\n";
else
  print "$requête: Exécution réussie\n";
// disconnect and exit
déconnecte($connexion);
exit;
 
// ---------------------------------------------------------------------------------
function connecte($dsn, $login, $pwd) {
  // connects ($login,$pwd) to base $dsn
  // returns the connection id and an error code
  try {
    // connection
    $dbh = new PDO($dsn, $login, $pwd);
    // error-free return
    return array("", $dbh);
  } catch (PDOException $e) {
    // return with error
    return array($e->getMessage(), null);
  }
}
 
// ---------------------------------------------------------------------------------
function déconnecte($connexion) {
  // closes the connection identified by $connexion
  $connexion = null;
}
 
// ---------------------------------------------------------------------------------
function exécuteRequête($connexion, $sql) {
  // executes the $sql request on the $connexion connection
  try {
    $connexion->exec($sql);
    // error-free return
    return "";
  } catch (PDOException $e) {
    // return with 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

No PHPMyAdmin, pode verificar que a tabela existe:

 

Comentários

  • Linhas 38–50: A função connect estabelece uma ligação a um sistema de gestão de bases de dados (DBMS). Devolve um array ($error, $connection*), em que $connection* é a ligação estabelecida ou null se a ligação não puder ser estabelecida. Neste último caso, $error contém uma mensagem de erro.
  • Linhas 53–56: A função *disconnect* encerra uma ligação
  • Linha 59: A função *executeQuery permite executar uma instrução SQL numa ligação. A ligação é um objeto PDO. O método utilizado para executar uma instrução SQL num objeto PDO é o método exec* (linha 63). A execução da consulta pode lançar uma PDOException. Por isso, isto é tratado. A função devolve uma mensagem de erro se ocorrer um erro; caso contrário, devolve uma cadeia de caracteres vazia.

7.3. Preenchimento da tabela people (mysql_03)

O script a seguir executa instruções SQL encontradas no seguinte ficheiro 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
 
// connection to the MySql database
// user identity
$ID = "admpersonnes";
$PWD = "nobody";
// base identity
$DSN = "mysql:host=localhost;dbname=dbpersonnes";
// identity of the SQL command text file to be executed
$TEXTE = "creation.txt";
 
// connection
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;
}
 
// table creation and filling
$erreurs = exécuterCommandes($connexion, $TEXTE, 1, 0);
//display number of errors
print "il y a eu $erreurs[0] erreurs\n";
for ($i = 1; $i < count($erreurs); $i++)
  print "$erreurs[$i]\n";
 
// disconnect and exit
déconnecte($connexion);
exit;
 
// ---------------------------------------------------------------------------------
function connecte($dsn, $login, $pwd) {
...
}
 
// ---------------------------------------------------------------------------------
function déconnecte($connexion) {
 ...
}
 
// ---------------------------------------------------------------------------------
function exécuteRequête($connexion, $sql) {
  // executes the $sql request on the $connexion connection
  // returns 1 error msg if error, empty string otherwise
  ...
}
 
// ---------------------------------------------------------------------------------
function exécuterCommandes($connexion, $SQL, $suivi=0, $arrêt=1) {
  // uses the $connexion connection
  // executes the SQL commands contained in the $SQL text file
  // this is a file of SQL commands to be executed one per line
  // if $suivi=1 then each execution of a SQL order is displayed as a success or failure
  // if $arrêt=1, the function stops on the 1st error encountered, otherwise it executes all sql commands
  // the function returns an array (nb of errors, error1, error2, ...)
 
  // check for the presence of the $SQL file
  if (! file_exists($SQL))
    return array(1, "Le fichier $SQL n'existe pas");
 
  // execution of SQL queries contained in $SQL
  // we put them in a table
  $requêtes = file($SQL);
  // we run them - initially no errors
  $erreurs = array(0);
  for ($i = 0; $i < count($requêtes); $i++) {
    //do we have an empty query
    if (preg_match("/^\s*$/", $requêtes[$i]))
      continue;
    // execute query $i
    $erreur = exécuteRequête($connexion, $requêtes[$i]);
    //was there a mistake?
    if ($erreur) {
      // one more mistake
      $erreurs[0]++;
      // error msg
      $msg = "$requêtes[$i] : Erreur ($erreur)\n";
      $erreurs[] = $msg;
      // screen tracking or not?
      if ($suivi)
        print "$msg\n";
      // shall we stop?
      if ($arrêt)
        return $erreurs;
    } else
    if ($suivi)
      print "$requêtes[$i] : Exécution réussie\n";
  }//for
  // return
  return $erreurs;
}

Resultados no ecrã:

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

As inserções efetuadas estão visíveis no phpMyAdmin:

 

Comentários

A nova funcionalidade é a função executeCommands nas linhas 48–90. Esta função executa os comandos SQL encontrados no ficheiro de texto denominado $SQL na $connection. Ela devolve uma matriz de erros ($nbErrors, $msg1, $msg2, …) em que $nbErrors é o número de erros e $msg1 é a mensagem de erro número i. Se não houver erros, a matriz devolvida é array(0).

7.4. Execução de consultas SQL arbitrárias (mysql_04)

O script a seguir demonstra a execução de comandos SQL a partir do seguinte ficheiro 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 instruções SQL, encontra-se a instrução SELECT, que devolve resultados da base de dados; as instruções INSERT, UPDATE e DELETE, que modificam a base de dados sem devolver resultados; e, por fim, instruções inválidas, como a última (xselect).


<?php
 
// connection to the MySql database
// user identity
$ID = "admpersonnes";
$PWD = "nobody";
// base identity
$DSN = "mysql:host=localhost;dbname=dbpersonnes";
// identity of the SQL command text file to be executed
$TEXTE = "sql.txt";
 
// connection
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;
}
 
// order execution SQL
$erreurs = exécuterCommandes($connexion, $TEXTE, 1, 0);
//display number of errors
print "il y a eu $erreurs[0] erreur(s)\n";
for ($i = 1; $i < count($erreurs); $i++)
  print "$erreurs[$i]\n";
 
// disconnect and exit
déconnecte($connexion);
exit;
 
// ---------------------------------------------------------------------------------
function connecte($dsn, $login, $pwd) {
  // connects ($login,$pwd) to base $dsn
  // returns the connection id and an error msg
  ...
}
 
// ---------------------------------------------------------------------------------
function déconnecte($connexion) {
  ...
}
 
// ---------------------------------------------------------------------------------
function exécuteRequête($connexion, $sql) {
  // executes the $sql request on the $connexion connection
  // returns an array of 2 elements ($erreur,$résultat)
 
  // determine whether it's a select or not
  $commande = "";
  if (preg_match("/^\s*(\S+)/", $sql, $champs)) {
    $commande = $champs[0];
  }
  // order processing
  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);
      }
    }
    // error-free return
    return array("", $res);
  } catch (PDOException $e) {
    // return with error
    return array($e->getMessage(), null);
  }
}
 
// ---------------------------------------------------------------------------------
function exécuterCommandes($connexion, $SQL, $suivi=0, $arrêt=1) {
  // uses the $connexion connection
  // executes the SQL commands contained in the $SQL text file
  // this is a file of SQL commands to be executed one per line
  // if $suivi=1 then each execution of a SQL order is displayed as a success or failure
  // if $arrêt=1, the function stops on the 1st error encountered, otherwise it executes all sql commands
  // the function returns an array (nb of errors, error1, error2, ...)
  // check for the presence of the $SQL file
  if (!file_exists($SQL))
    return array(1, "Le fichier $SQL n'existe pas");
 
  // execution of SQL queries contained in $TEXTE
  // we put them in a table
  $requêtes = file($SQL);
  // we run them - initially no errors
  $erreurs = array(0);
  for ($i = 0; $i < count($requêtes); $i++) {
    //do we have an empty query
    if (preg_match("/^\s*$/", $requêtes[$i]))
      continue;
    // execute query $i
    list($erreur, $res) = exécuteRequête($connexion, $requêtes[$i]);
    //was there a mistake?
    if ($erreur) {
      // one more mistake
      $erreurs[0]++;
      // error msg
      $msg = "$requêtes[$i] : Erreur ($erreur)\n";
      $erreurs[] = $msg;
      // screen tracking or not?
      if ($suivi)
        print "$msg\n";
      // shall we stop?
      if ($arrêt)
        return $erreurs;
    } else
    if ($suivi) {
      print "$requêtes[$i] : Exécution réussie\n";
      // information on the result of the query
      afficherInfos($res);
    }
  }//for
  // return
  return $erreurs;
}
 
// ---------------------------------------------------------------------------------
function afficherInfos($résultat) {
  // displays the $résultat result of an sql query
  // was it a select?
  if ($résultat instanceof PDOStatement) {
    // displays field names
    $titre = "";
    $nbColonnes = $résultat->columnCount();
    for ($i = 0; $i < $nbColonnes; $i++) {
      $infos = $résultat->getColumnMeta($i);
      $titre.=$infos['name'] . ",";
    }
    // remove the last character ,
    $titre = substr($titre, 0, strlen($titre) - 1);
    // displays the list of fields
    print "$titre\n";
    // dividing line
    $séparateurs = "";
    for ($i = 0; $i < strlen($titre); $i++) {
      $séparateurs.="-";
    }
    print "$séparateurs\n";
    // data
    foreach ($résultat as $ligne) {
      $data = "";
      for ($i = 0; $i < $nbColonnes; $i++) {
        $data.=$ligne[$i] . ",";
      }
 
      // remove the last character ,
      $data = substr($data, 0, strlen($data) - 1);
      // on affiche
      print "$data\n";
    }
  } else {
    // it wasn't a select
    print " $résultat lignes(s) a (ont) été modifiée(s)\n";
  }
}

Resultados da triagem:

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)

Comentários

Cada comando no ficheiro de texto [sql.txt] é executado pela função exécuteRequête na linha 43.

  • Linha 43: Os dois parâmetros da função são a ligação ($connexion) na qual os comandos SQL devem ser executados e o comando SQL ($sql) a ser executado. A função devolve uma matriz com dois valores ($erreur, $résultat), em que
    • $erreur é uma mensagem de erro, que pode estar vazia se não tiver ocorrido nenhum erro
    • $result: o resultado devolvido pela execução da instrução SQL. Este resultado varia consoante a instrução seja uma instrução SELECT ou uma instrução INSERT, UPDATE ou DELETE.
  • Linhas 48–51: Recuperamos o primeiro elemento da instrução SQL para determinar se se trata de uma instrução SELECT ou de uma instrução INSERT, UPDATE ou DELETE.
  • linha 55: no caso de uma instrução SELECT, esta é executada utilizando o método [PDO]->query("SELECT statement"). O resultado devolvido é um objeto PDOStatement.
  • Linha 57: No caso de uma instrução INSERT, UPDATE ou DELETE, esta é executada utilizando o método [PDO]->exec("instrução SQL"). O resultado devolvido é o número de linhas modificadas pela instrução SQL. Assim, se uma instrução SQL DELETE eliminar duas linhas, o resultado devolvido é o número inteiro 2. Se ocorrer um erro durante a execução, o resultado devolvido é o valor booleano false. Neste caso, o método [PDO]->errorinfo() fornece informações sobre o erro na forma de uma matriz de valores. O elemento no índice 2 desta matriz é a mensagem de erro.
  • linhas 58–60: tratamento de quaisquer erros da operação [PDO]->exec("instrução SQL").
  • linhas 65–68: Tratamento de quaisquer exceções
  • Linha 72: A função executeCommands executa os comandos SQL armazenados no ficheiro de texto *$SQL* na ligação *$connection*. Este é um código que já vimos, com uma pequena diferença: a linha 111.
  • Linha 111: A função executeQuery devolveu uma matriz (*$error*, *$result*), em que $result* é o resultado da execução de um comando SQL. Este resultado varia consoante o comando SQL tenha sido uma instrução SELECT* ou uma instrução INSERT*, UPDATE* ou DELETE*. A função displayInfo* apresenta informações sobre este resultado.
  • Linha 122: Se a instrução SQL for uma instrução SELECT, o resultado é do tipo PDOStatement. Este tipo representa uma tabela composta por linhas e colunas.
  • Linha 125: o método [PDOStatement]->getColumnCount() devolve o número de colunas na tabela resultante da instrução SELECT
  • Linha 127: O método [PDOStatement]->getMeta(i) devolve um dicionário com informações sobre a coluna i da tabela de resultados da instrução SELECT. Neste dicionário, o valor associado à chave «name» é o nome da coluna.
  • Linhas 127–129: Os nomes das colunas da tabela de resultados do SELECT são concatenados numa cadeia de caracteres.
  • Linhas 141–145: Um objeto PDOStatement pode ser iterado usando um loop foreach. Em cada iteração, o elemento devolvido é uma linha da tabela de resultados da instrução SELECT na forma de uma matriz de valores que representam os valores das várias colunas da linha. Exibimos todos estes valores usando um loop for.
  • Linha 154: O resultado da execução de uma instrução INSERT, UPDATE ou DELETE é o número de linhas modificadas pela instrução.
https://stahe.github.io
Este curso-tutorial escrito por Serge Tahé é disponibilizado ao público de acordo com os termos da Licença Creative Commons Atribuição – Sem uso comercial – Partilha nas mesmas condições 3.0 não adaptada.