Skip to content

7. Utilização do SGBD e do MySql

Vamos escrever scripts PHP utilizando uma base de dados MySQL:

O SGBD MySQL está incluído no pacote WampServer (ver parágrafo 2.1.1). Mostramos como criar uma base de dados e um utilizador MySQL.

  • uma vez iniciado, o WampServer pode ser gerido a partir de um ícone [1] localizado no canto inferior direito da barra de tarefas.
  • No [2], inicia-se a ferramenta de administração do MySQL

Cria-se uma base de dados [dbpersonnes]:

Image

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

  • em [1], o nome do utilizador
  • em [2], o computador SGBD no qual lhe são atribuídos direitos
  • em [3], a sua palavra-passe
  • em [4], o mesmo
  • em [5], não se concede qualquer direito a este utilizador
  • em [6], é criado
  • em [7], regressa-se à página inicial de phpMyAdmin
  • em [8], utiliza-se o link [Privileges] desta página para ir alterar os do utilizador [admpersonnes] [9].

1011

Image

  • em [10], indica-se que se pretende conceder ao utilizador [admpersonnes] direitos sobre a base de dados [dbpersonnes]
  • em [11], valida-se a escolha
  • através do link [12] [Tout cocher], concedem-se ao utilizador [admpersonnes] todos os direitos sobre a base de dados [dbpersonnes] [13]
  • valida-se em [14]

Agora temos:

  • uma base de dados MySQL [dbpersonnes]
  • um utilizador [admpersonnes / nobody] que possui todos os direitos sobre esta base de dados

Vamos escrever scripts PHP para explorar a base de dados. O PHP dispõe de várias bibliotecas para gerir bases de dados. Iremos utilizar a biblioteca PDO (PHP Data Objects), que faz a ligação entre o código PHP e o SGBD:

A biblioteca PDO permite que o script PHP ignore a natureza exata do SGBD utilizado. Assim, no exemplo 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. É possível verificar a sua disponibilidade da seguinte forma:

  • 1: a partir do ícone de administração do WampServer, selecione a opção [PHP / PHP extensions]
  • 2: são apresentadas as diferentes extensões PDO disponíveis e as que estão ativas: [PHP_pdo_mysql] para o SGBD MySQL, [PHP_pdo_sqlite] para o SGBD SQL Lite. Para ativar uma extensão, basta clicar nela. O interpretador PHP é então reiniciado com a nova extensão ativada.

7.1. Ligação a uma base 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)

O significado dos parâmetros é o seguinte:

$dsn
(Data Source Name) é uma cadeia de caracteres que especifica a natureza do SGBD e a sua localização na Internet. A cadeia
«mysql:host=localhost» indica que se trata de um SGBD MySQL a funcionar no servidor local. Esta cadeia
pode incluir outros parâmetros, nomeadamente a porta de escuta do SGBD e o nome da base à qual
se pretende ligar: «mysql:host=localhost:port=3306:dbname=dbpersonnes».
$user
identificador do utilizador que se liga
$passwd
a sua palavra-passe
$driver_options
um conjunto de opções para o controlador do SGBD

Apenas o primeiro parâmetro é obrigatório. O objeto assim criado servirá de suporte para todas as operações realizadas na base de dados à qual se estabeleceu a ligação. Se não for possível criar o objeto PDO, é lançada uma exceção do tipo PDOException.

Eis um exemplo de ligação:


<?php

// ligação a uma base de dados MySql
// a identidade do utilizador é (admpersonnes,nobody)
$ID = "admpersonnes";
$PWD = "nobody";
$HOTE = "localhost";

try {
  // ligação
  $dbh = new PDO("mysql:host=$HOTE", $ID, $PWD);
  print "Connexion réussie\n";
  // encerramento
  $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 é feita através da criação de um objeto PDO. O construtor é aqui utilizado com os seguintes parâmetros:
    • uma sequência que especifica a natureza do SGBD e a sua localização na Internet. A sequência «mysql:host=localhost» indica que se trata de um SGBD MySQL a funcionar no servidor local. A porta não foi especificada. Nesse caso, é utilizada a porta 3306 por predefinição. O nome da base de dados também não foi indicado. Será então estabelecida uma ligação ao SGBD MySQL, sendo a seleção de uma base de dados específica efetuada posteriormente.
    • um nome de utilizador
    • a sua palavra-passe
  • linha 14: o encerramento da ligação é efetuado através da eliminação do objeto PDO criado inicialmente.
  • linha 15: a ligação a um SGBD pode falhar. Nesse caso, é lançada uma exceção do tipo PDOException.

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


<?php

// ligação à base de dados MySql
// identidade do utilizador
$ID = "admpersonnes";
$PWD = "nobody";
// identidade da base de dados
$DSN = "mysql:host=localhost;dbname=dbpersonnes";

// ligação
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;
}

// eliminação da tabela «pessoas», caso exista
$requête = "drop table personnes";
$erreur = exécuteRequête($connexion, $requête);
//ocorreu algum erro?
if ($erreur)
  print "$requête : Erreur ($erreur)\n";
else
  print "$requête: Exécution réussie\n";
// criação da tabela «pessoas»
$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);
//ocorreu algum erro?
if ($erreur)
  print "$requête : Erreur ($erreur)\n";
else
  print "$requête: Exécution réussie\n";
// desligar-se e sair
déconnecte($connexion);
exit;

// ---------------------------------------------------------------------------------
function connecte($dsn, $login, $pwd) {
  // ligar ($login,$pwd) à base de dados $dsn
  // retorna o ID da ligação e um código de erro
  try {
    // ligação
    $dbh = new PDO($dsn, $login, $pwd);
    // resposta sem erros
    return array("", $dbh);
  } catch (PDOException $e) {
    // resposta com erro
    return array($e->getMessage(), null);
  }
}

// ---------------------------------------------------------------------------------
function déconnecte($connexion) {
  // encerra a ligação identificada por $connexion
  $connexion = null;
}

// ---------------------------------------------------------------------------------
function exécuteRequête($connexion, $sql) {
  // executa a consulta $sql na ligação $connexion
  try {
    $connexion->exec($sql);
    // retorna sem erros
    return "";
  } catch (PDOException $e) {
    // resposta com erro
    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

Na tabela PHPMyAdmin, é possível verificar a existência da tabela:

 

Comentários

  • linhas 38-50: a função connecte estabelece uma ligação a um SGBD. Esta função devolve um array ($erreur, $connexion), em que $connexion é a ligação criada ou null, caso não tenha sido possível criá-la. Neste último caso, $erreur é uma mensagem de erro.
  • linhas 53-56: a função déconnecte encerra uma ligação
  • linha 59: a função exécuteRequête permite executar um comando SQL numa ligação. A ligação é um objeto PDO. O método utilizado para executar uma ordem SQL num objeto PDO é o método exec (linha 63). A execução da consulta pode acionar um PDOException. Por isso, este também é tratado. A função devolve uma mensagem de erro em caso de erro; caso contrário, devolve uma cadeia vazia.

7.3. Preenchimento da tabela de pessoas (mysql_03)

O script seguinte executa as ordens SQL encontradas no ficheiro de texto [creation.txt] a seguir:

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

// ligação à base de dados MySql
// identidade do utilizador
$ID = "admpersonnes";
$PWD = "nobody";
// identidade da base de dados
$DSN = "mysql:host=localhost;dbname=dbpersonnes";
// identidade do ficheiro de texto com os comandos SQL a executar
$TEXTE = "creation.txt";

// ligação
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;
}

// criação e preenchimento da tabela
$erreurs = exécuterCommandes($connexion, $TEXTE, 1, 0);
//exibição do número de erros
print "il y a eu $erreurs[0] erreurs\n";
for ($i = 1; $i < count($erreurs); $i++)
  print "$erreurs[$i]\n";

// desligar-se e sair
déconnecte($connexion);
exit;

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

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

// ---------------------------------------------------------------------------------
function exécuteRequête($connexion, $sql) {
  // executa a consulta $sql na ligação $connexion
  // retorna 1 mensagem de erro em caso de erro; caso contrário, retorna a cadeia vazia
  ...
}

// ---------------------------------------------------------------------------------
function exécuterCommandes($connexion, $SQL, $suivi=0, $arrêt=1) {
  // utiliza a ligação $connexion
  // executa os comandos SQL contidos no ficheiro de texto $SQL
  // este ficheiro é um ficheiro de comandos SQL a executar à razão de um por linha
  // se $suivi=1, então cada execução de um comando SQL é acompanhada de uma mensagem indicando o seu sucesso ou falha
  // se $arrêt=1, a função pára no primeiro erro encontrado; caso contrário, executa todos os comandos SQL
  // a função devolve um array (número de erros, erro1, erro2, ...)
  
  // verifica-se a existência do ficheiro $SQL
  if (! file_exists($SQL))
    return array(1, "Le fichier $SQL n'existe pas");

  // execução das consultas SQL contidas no ficheiro $SQL
  // colocam-se num tabuleiro
  $requêtes = file($SQL);
  // executam-se as consultas — inicialmente, sem erros
  $erreurs = array(0);
  for ($i = 0; $i < count($requêtes); $i++) {
    //existe alguma consulta vazia?
    if (preg_match("/^\s*$/", $requêtes[$i]))
      continue;
    // execução da consulta $i
    $erreur = exécuteRequête($connexion, $requêtes[$i]);
    //: ocorreu algum erro?
    if ($erreur) {
      // mais um erro
      $erreurs[0]++;
      // mensagem de erro
      $msg = "$requêtes[$i] : Erreur ($erreur)\n";
      $erreurs[] = $msg;
      // acompanhamento no ecrã ou não?
      if ($suivi)
        print "$msg\n";
      // paramos?
      if ($arrêt)
        return $erreurs;
    } else
    if ($suivi)
      print "$requêtes[$i] : Exécution réussie\n";
  }//para
  // voltar
  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 podem ser visualizadas com o PhpMyAdmin:

 

Comentários

A novidade reside na função exécuterCommandes, nas linhas 48 a 90. Esta função executa, na ligação $connexion, os comandos SQL encontrados no ficheiro de texto denominado $SQL. Ela devolve uma tabela de erros ($nbErreurs, $msg1, $msg2, …) em que $nbErreurs é o número de erros, $msgi a mensagem de erro n.º i.. Se não houver erros, a tabela devolvida é a tabela array(0).

7.4. Execução de quaisquer consultas SQL (mysql_04)

O script seguinte mostra a execução das ordens SQL do ficheiro de texto [sql.txt] a seguir:

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 estes comandos SQL, encontra-se o comando «select», que devolve resultados da base de dados; os comandos «insert», «update» e «delete», que alteram a base de dados sem devolver resultados; e, por fim, comandos com erros, como o último (xselect).


<?php

// ligação à base de dados MySql
// identidade do utilizador
$ID = "admpersonnes";
$PWD = "nobody";
// identidade da base de dados
$DSN = "mysql:host=localhost;dbname=dbpersonnes";
// identidade do ficheiro de texto com os comandos SQL a executar
$TEXTE = "sql.txt";

// ligação
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;
}

// execução dos comandos SQL
$erreurs = exécuterCommandes($connexion, $TEXTE, 1, 0);
//exibição do número de erros
print "il y a eu $erreurs[0] erreur(s)\n";
for ($i = 1; $i < count($erreurs); $i++)
  print "$erreurs[$i]\n";

// desligar-se e sair
déconnecte($connexion);
exit;

// ---------------------------------------------------------------------------------
function connecte($dsn, $login, $pwd) {
  // ligar ($login,$pwd) à base de dados $dsn
  // retorna o ID da ligação e uma mensagem de erro
  ...
}

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

// ---------------------------------------------------------------------------------
function exécuteRequête($connexion, $sql) {
  // executa a consulta $sql na ligação $connexion
  // retorna um array com 2 elementos ($erreur, $résultat)
  
  // determina-se se se trata de um «select» ou não
  $commande = "";
  if (preg_match("/^\s*(\S+)/", $sql, $champs)) {
    $commande = $champs[0];
  }
  // execução do 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);
      }
    }
    // retorno sem erros
    return array("", $res);
  } catch (PDOException $e) {
    // retorno com erro
    return array($e->getMessage(), null);
  }
}

// ---------------------------------------------------------------------------------
function exécuterCommandes($connexion, $SQL, $suivi=0, $arrêt=1) {
  // utiliza a ligação $connexion
  // executa os comandos SQL contidos no ficheiro de texto $SQL
  // este ficheiro é um ficheiro de comandos SQL a executar à razão de um por linha
  // se $suivi=1, então cada execução de um comando SQL é acompanhada de uma mensagem indicando o seu sucesso ou falha
  // se $arrêt=1, a função pára no primeiro erro encontrado; caso contrário, executa todos os comandos SQL
  // a função devolve um array (número de erros, erro1, erro2, ...)
  // verifica-se a existência do ficheiro $SQL
  if (!file_exists($SQL))
    return array(1, "Le fichier $SQL n'existe pas");

  // execução das consultas SQL contidas em $TEXTE
  // colocam-se num tabuleiro
  $requêtes = file($SQL);
  // executa-se - inicialmente sem erros
  $erreurs = array(0);
  for ($i = 0; $i < count($requêtes); $i++) {
    //– temos uma consulta vazia?
    if (preg_match("/^\s*$/", $requêtes[$i]))
      continue;
    // execução da consulta $i
    list($erreur, $res) = exécuteRequête($connexion, $requêtes[$i]);
    //ocorreu algum erro?
    if ($erreur) {
      // mais um erro
      $erreurs[0]++;
      // mensagem de erro
      $msg = "$requêtes[$i] : Erreur ($erreur)\n";
      $erreurs[] = $msg;
      // acompanhamento no ecrã ou não?
      if ($suivi)
        print "$msg\n";
      // paramos?
      if ($arrêt)
        return $erreurs;
    } else
    if ($suivi) {
      print "$requêtes[$i] : Exécution réussie\n";
      // informações sobre o resultado da consulta executada
      afficherInfos($res);
    }
  }//para
  // retorno
  return $erreurs;
}

// ---------------------------------------------------------------------------------
function afficherInfos($résultat) {
  // exibe o resultado $résultat de uma consulta SQL
  // tratava-se de um SELECT?
  if ($résultat instanceof PDOStatement) {
    // são apresentados os nomes dos campos
    $titre = "";
    $nbColonnes = $résultat->columnCount();
    for ($i = 0; $i < $nbColonnes; $i++) {
      $infos = $résultat->getColumnMeta($i);
      $titre.=$infos['name'] . ",";
    }
    // remove-se o último carácter,
    $titre = substr($titre, 0, strlen($titre) - 1);
    // exibe-se a lista de campos
    print "$titre\n";
    // linha separadora
    $séparateurs = "";
    for ($i = 0; $i < strlen($titre); $i++) {
      $séparateurs.="-";
    }
    print "$séparateurs\n";
    // dados
    foreach ($résultat as $ligne) {
      $data = "";
      for ($i = 0; $i < $nbColonnes; $i++) {
        $data.=$ligne[$i] . ",";
      }

      // remove-se o último carácter,
      $data = substr($data, 0, strlen($data) - 1);
      // exibe-se
      print "$data\n";
    }
  } else {
    // não era uma seleção
    print " $résultat lignes(s) a (ont) été modifiée(s)\n";
  }
}

Resultados no ecrã:

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 um dos comandos do ficheiro de texto [sql.txt] é executado pela função exécuteRequête da linha 43.

  • linha 43: os dois parâmetros da função são a ligação ($connexion) na qual devem ser executados os comandos SQL e o comando SQL ($sql) a executar. A função devolve um tabulácio com dois valores ($erreur, $résultat), em que
    • $erreur é uma mensagem de erro, que pode estar vazia caso não tenha ocorrido qualquer erro
    • $résultat: o resultado devolvido pela execução da ordem SQL. Este resultado varia consoante a ordem seja uma ordem select ou uma ordem insert, update, delete.
  • linhas 48-51: recupera-se o primeiro elemento da ordem SQL para determinar se se trata de uma ordem select ou de uma ordem insert, update, delete.
  • linha 55: no caso de uma ordem select, esta é executada com o método [PDO]->query("ordem select"). O resultado devolvido é um objeto do tipo PDOStatement.
  • linha 57: no caso de um comando insert, update ou delete, este é executado com o método [PDO]->exec("comando SQL"). O resultado devolvido é o número de linhas alteradas pela ordem SQL. Assim, se uma ordem 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 sob a forma de um tabuleiro de valores. O elemento com índice 2 deste tabuleiro é a mensagem de erro.
  • linhas 58-60: tratamento de um eventual erro na operação [PDO]->exec("ordem SQL").
  • linhas 65-68: tratamento de uma eventual exceção
  • linha 72: a função exécuterCommandes executa, na ligação $connexion, os comandos SQL armazenados no ficheiro de texto $SQL. Trata-se de um código que já vimos, com uma pequena diferença: a linha 111.
  • linha 111: a função exécuteRequête devolveu um tabuleiro ($erreur, $résultat) ou $résultat é o resultado da execução de um comando SQL. Este resultado varia consoante a ordem SQL tenha sido uma ordem select ou uma ordem insert, update, delete. A função afficherInfos apresenta informações sobre este resultado.
  • linha 122: se a ordem SQL fosse uma ordem select, o resultado seria 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 da tabela resultante do select
  • linha 127: o método [PDOStatement]->getMeta(i) devolve um dicionário com informações sobre a coluna n.º i da tabela de resultados do 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 do tipo PDOStatement pode ser percorrido por um ciclo foreach. Em cada iteração, o elemento obtido é uma linha da tabela de resultados do select, sob a forma de um array de valores que representam os valores das diferentes colunas da linha. Todos estes valores são apresentados através de um ciclo for.
  • linha 154: o resultado da execução de uma ordem insert, update, delete é o número de linhas alteradas pela ordem.
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.