Skip to content

7. Utilização do SGBD e do MySql

Vamos escrever scripts PHP utilizando um banco de dados MySQL:

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

  • uma vez iniciado, o WampServer pode ser administrado 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 um banco de dados [dbpersonnes]:

Image

Crie um usuário [admpersonnes] com a senha [nobody]:

  • em [1], o nome do usuário
  • em [2], a máquina de SGBD na qual lhe são concedidos direitos
  • em [3], sua senha
  • em [4], o mesmo
  • em [5], não se concede nenhum direito a esse usuário
  • em [6], ele é criado
  • em [7], retorna-se à página inicial de phpMyAdmin
  • em [8], utiliza-se o link [Privileges] desta página para ir editar os do usuário [admpersonnes] e [9].

1011

Image

  • em [10], indica-se que se deseja conceder ao usuário [admpersonnes] direitos sobre o banco de dados [dbpersonnes]
  • em [11], a escolha é validada
  • com o link [12] [Tout cocher], concede-se ao usuário [admpersonnes] todos os direitos sobre o banco de dados [dbpersonnes] [13]
  • confirmamos em [14]

Agora temos:

  • um banco de dados MySQL [dbpersonnes]
  • um usuário [admpersonnes / nobody] que possui todos os direitos sobre essa base de dados

Vamos escrever scripts PHP para explorar o banco de dados. O PHP dispõe de diversas bibliotecas para gerenciar bancos de dados. Utilizaremos a biblioteca PDO (PHP Data Objects), que faz a interface 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. Essa biblioteca não está disponível por padrão. É possível verificar se ela está disponível da seguinte maneira:

  • 1: a partir do ícone de administração do WampServer, selecione a opção [PHP / PHP extensions]
  • 2: são exibidas as diferentes extensões PDO disponíveis e aquelas 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. Conexão a uma base MySQL – 1 (mysql_01)

A conexão a um SGBD é feita por meio da criação de um objeto PDO. O construtor aceita diferentes parâmetros:

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

O significado dos parâmetros é o seguinte:

$dsn
(Data Source Name) é uma sequência de caracteres que especifica a natureza do SGBD e sua localização na internet. A sequência
“mysql:host=localhost” indica que se trata de um SGBD MySQL operando no servidor local. Essa string
pode incluir outros parâmetros, como a porta de escuta do SGBD e o nome do banco de dados ao qual
se deseja se conectar: “mysql:host=localhost:port=3306:dbname=dbpersonnes”.
$user
identificador do usuário que está se conectando
$passwd
sua senha
$driver_options
uma matriz de opções para o driver do SGBD

Apenas o primeiro parâmetro é obrigatório. O objeto assim criado servirá, em seguida, de suporte para todas as operações realizadas no banco de dados ao qual se conectou. Se o objeto PDO não puder ser criado, será lançada uma exceção do tipo PDOException.

Veja um exemplo de conexão:


<?php

// conexão a um banco de dados MySql
// a identidade do usuário é (admpersonnes, nobody)
$ID = "admpersonnes";
$PWD = "nobody";
$HOTE = "localhost";

try {
  // conexã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 conexão com um SGBD é feita por meio da criação de um objeto PDO. O construtor é utilizado aqui com os seguintes parâmetros:
    • uma sequência que especifica a natureza do SGBD e sua localização na internet. A sequência “mysql:host=localhost” indica que se trata de um SGBD MySQL operando no servidor local. A porta não foi especificada. Nesse caso, a porta 3306 é usada por padrão. O nome do banco de dados também não foi indicado. Haverá, então, conexão com o SGBD MySQL, sendo que a seleção de um banco de dados específico será feita posteriormente.
    • um nome de usuário
    • sua senha
  • linha 14: o encerramento da conexão é feito pela destruição do objeto PDO criado inicialmente.
  • linha 15: a conexão com 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

// conexão ao banco de dados MySql
// identidade do usuário
$ID = "admpersonnes";
$PWD = "nobody";
// identidade da base
$DSN = "mysql:host=localhost;dbname=dbpersonnes";

// conexã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;
}

// exclusã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);
//Houve algum erro?
if ($erreur)
  print "$requête : Erreur ($erreur)\n";
else
  print "$requête: Exécution réussie\n";
// desconectando e saindo
déconnecte($connexion);
exit;

// ---------------------------------------------------------------------------------
function connecte($dsn, $login, $pwd) {
  // conectando ($login,$pwd) ao banco de dados $dsn
  // retorna o ID da conexão e um código de erro
  try {
    // conexão
    $dbh = new PDO($dsn, $login, $pwd);
    // retorno sem erro
    return array("", $dbh);
  } catch (PDOException $e) {
    // retorno com erro
    return array($e->getMessage(), null);
  }
}

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

// ---------------------------------------------------------------------------------
function exécuteRequête($connexion, $sql) {
  // executa a consulta $sql na conexão $connexion
  try {
    $connexion->exec($sql);
    // retorna sem erros
    return "";
  } catch (PDOException $e) {
    // retorno 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 observar a presença da tabela:

 

Comentários

  • linhas 38-50: a função connecte cria uma conexão com um SGBD. Ela retorna um array ($erreur, $connexion), em que $connexion é a conexã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 conexão
  • linha 59: a função exécuteRequête permite executar um comando SQL em uma conexão. A conexão é um objeto PDO. O método utilizado para executar uma ordem SQL em um 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 retorna uma mensagem de erro em caso de erro; caso contrário, retorna uma string vazia.

7.3. Preenchimento da tabela de pessoas (mysql_03)

O script a seguir executa as ordens SQL encontradas no arquivo 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

// conexão com o banco de dados MySql
// identidade do usuário
$ID = "admpersonnes";
$PWD = "nobody";
// identidade do banco de dados
$DSN = "mysql:host=localhost;dbname=dbpersonnes";
// identidade do arquivo de texto com os comandos SQL a serem executados
$TEXTE = "creation.txt";

// conexã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";

// desconecta-se e sai
déconnecte($connexion);
exit;

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

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

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

// ---------------------------------------------------------------------------------
function exécuterCommandes($connexion, $SQL, $suivi=0, $arrêt=1) {
  // utiliza a conexão $connexion
  // executa os comandos SQL contidos no arquivo de texto $SQL
  // esse arquivo é um arquivo de comandos SQL a ser executado, um por linha
  // se $suivi=1, então cada execução de um comando SQL é acompanhada de uma mensagem indicando se foi bem-sucedida ou falhou
  // se $arrêt=1, a função interrompe a execução ao encontrar o primeiro erro; caso contrário, ela executa todos os comandos SQL
  // a função retorna um array (número de erros, erro1, erro2, ...)
  
  // verifica-se a existência do arquivo $SQL
  if (! file_exists($SQL))
    return array(1, "Le fichier $SQL n'existe pas");

  // execução das consultas SQL contidas em $SQL
  // colocam-se em uma matriz
  $requêtes = file($SQL);
  // elas são executadas — inicialmente, sem erros
  $erreurs = array(0);
  for ($i = 0; $i < count($requêtes); $i++) {
    //: há alguma consulta vazia?
    if (preg_match("/^\s*$/", $requêtes[$i]))
      continue;
    // execução da consulta $i
    $erreur = exécuteRequête($connexion, $requêtes[$i]);
    //: houve algum erro?
    if ($erreur) {
      // mais um erro
      $erreurs[0]++;
      // mensagem de erro
      $msg = "$requêtes[$i] : Erreur ($erreur)\n";
      $erreurs[] = $msg;
      // exibição na tela ou não?
      if ($suivi)
        print "$msg\n";
      // Vamos parar?
      if ($arrêt)
        return $erreurs;
    } else
    if ($suivi)
      print "$requêtes[$i] : Exécution réussie\n";
  }//para
  // retorno
  return $erreurs;
}

Resultados na tela:

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 feitas podem ser visualizadas com o PhpMyAdmin:

 

Comentários

A novidade está na função exécuterCommandes, nas linhas 48 a 90. Essa função executa, na conexão $connexion, os comandos SQL encontrados no arquivo de texto denominado $SQL. Ela retorna 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 retornada é a tabela array(0).

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

O script a seguir mostra a execução dos comandos SQL do arquivo 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 esses comandos SQL, há o comando SELECT, que retorna resultados do banco de dados; os comandos INSERT, UPDATE e DELETE, que modificam o banco de dados sem retornar resultados; e, por fim, comandos com erros, como o último (xselect).


<?php

// conexão com o banco de dados MySql
// identidade do usuário
$ID = "admpersonnes";
$PWD = "nobody";
// identidade do banco de dados
$DSN = "mysql:host=localhost;dbname=dbpersonnes";
// identidade do arquivo de texto com os comandos SQL a serem executados
$TEXTE = "sql.txt";

// conexã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";

// desconecta-se e sai
déconnecte($connexion);
exit;

// ---------------------------------------------------------------------------------
function connecte($dsn, $login, $pwd) {
  // conecta ($login,$pwd) ao banco de dados $dsn
  // retorna o ID da conexão e uma mensagem de erro
  ...
}

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

// ---------------------------------------------------------------------------------
function exécuteRequête($connexion, $sql) {
  // executa a consulta $sql na conexão $connexion
  // retorna um array com 2 elementos ($erreur, $résultat)
  
  // determina se é 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 erro
    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 conexão $connexion
  // executa os comandos SQL contidos no arquivo de texto $SQL
  // esse arquivo é um arquivo de comandos SQL a ser executado, um por linha
  // se $suivi=1, então cada execução de um comando SQL é acompanhada de uma mensagem indicando se foi bem-sucedida ou falhou
  // se $arrêt=1, a função interrompe a execução ao encontrar o primeiro erro; caso contrário, ela executa todos os comandos SQL
  // a função retorna um array (número de erros, erro1, erro2, ...)
  // verifica-se a existência do arquivo $SQL
  if (!file_exists($SQL))
    return array(1, "Le fichier $SQL n'existe pas");

  // execução das consultas SQL contidas em $TEXTE
  // elas são colocadas em uma matriz
  $requêtes = file($SQL);
  // são executados — inicialmente, sem erros
  $erreurs = array(0);
  for ($i = 0; $i < count($requêtes); $i++) {
    //: a consulta está vazia?
    if (preg_match("/^\s*$/", $requêtes[$i]))
      continue;
    // execução da consulta $i
    list($erreur, $res) = exécuteRequête($connexion, $requêtes[$i]);
    //Houve algum erro?
    if ($erreur) {
      // mais um erro
      $erreurs[0]++;
      // mensagem de erro
      $msg = "$requêtes[$i] : Erreur ($erreur)\n";
      $erreurs[] = $msg;
      // exibição na tela ou não?
      if ($suivi)
        print "$msg\n";
      // Vamos parar?
      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
  // seria uma consulta SELECT?
  if ($résultat instanceof PDOStatement) {
    // exibe 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 caractere,
    $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 caractere,
      $data = substr($data, 0, strlen($data) - 1);
      // exibe
      print "$data\n";
    }
  } else {
    // isso não era um select
    print " $résultat lignes(s) a (ont) été modifiée(s)\n";
  }
}

Resultados na tela:

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 arquivo 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 conexão ($connexion) na qual os comandos SQL devem ser executados e o comando SQL ($sql) a ser executado. A função retorna um array com dois valores ($erreur, $résultat), em que
    • $erreur é uma mensagem de erro, que pode estar vazia caso não tenha ocorrido nenhum erro
    • $résultat: o resultado retornado pela execução da ordem SQL. Esse resultado varia dependendo se a ordem é uma ordem select ou uma ordem insert, update, delete.
  • linhas 48-51: recupera-se o primeiro elemento do pedido SQL para verificar se se trata de um pedido select ou de um pedido insert, update, delete.
  • linha 55: no caso de uma ordem select, ela é executada com o método [PDO]->query("ordem select"). O resultado retornado é 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 retornado é o número de linhas modificadas pela ordem SQL. Assim, se uma ordem SQL delete excluir duas linhas, o resultado retornado é o número inteiro 2. Se ocorrer um erro durante a execução, o resultado retornado é o valor booleano false. Nesse caso, o método [PDO]->errorinfo() fornece informações sobre o erro na forma de uma matriz de valores. O elemento de índice 2 dessa matriz é a mensagem de erro.
  • linhas 58-60: tratamento de um possível 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 conexão $connexion, os comandos SQL armazenados no arquivo de texto $SQL. Esse é um código que já vimos antes, com uma pequena diferença: a linha 111.
  • linha 111: a função exécuteRequête retornou uma matriz ($erreur, $résultat) ou $résultat é o resultado da execução de um comando SQL. Esse resultado varia dependendo se a ordem SQL era uma ordem select ou uma ordem insert, update, delete. A função afficherInfos exibe informações sobre esse resultado.
  • linha 122: se a ordem SQL fosse uma ordem select, o resultado seria do tipo PDOStatement. Esse tipo representa uma tabela composta por linhas e colunas.
  • linha 125: o método [PDOStatement]->getColumnCount() retorna o número de colunas da tabela resultante do select
  • linha 127: o método [PDOStatement]->getMeta(i) retorna um dicionário com informações sobre a coluna nº i da tabela de resultados do select.. Nesse 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 em uma sequência de caracteres.
  • linhas 141-145: um objeto do tipo PDOStatement pode ser percorrido por um loop foreach. A cada iteração, o elemento obtido é uma linha da tabela de resultados do select na forma de uma matriz de valores que representa os valores das diferentes colunas da linha. Todos esses valores são exibidos por meio de um loop for.
  • linha 154: o resultado da execução de uma ordem insert, update, delete é o número de linhas modificadas pela ordem.
https://stahe.github.io
Este tutorial, escrito por Serge Tahé, é disponibilizado ao público de acordo com os termos da Creative Commons Atribuição – Sem uso comercial – Compartilhado sob os mesmos termos 3.0 inalterado.