12. Korzystanie z SGBD MySQL

Teraz napiszemy skrypty PHP, korzystając z bazy danych MySQL:

W powyższej architekturze skrypt PHP (1) nie komunikuje się bezpośrednio z SGBD (systemem zarządzania bazami danych) (3). Komunikuje się on za pośrednictwem modułu zwanego sterownikiem SGBD lub sterownikiem SGBD. PHP udostępnia standardowy interfejs dla tych sterowników – interfejs PDO (PHP Data Objects). Interfejs ten jest zaimplementowany przez różne klasy dostosowane do poszczególnych SGBD: klasa dla SGBD MySQL, inna dla SGBD PostgreSQL… Aby zmienić SGBD, należy zmienić sterownik:

Sterownik PDO oddziela skrypt PHP (1) od SGBD (3, 6). Ponieważ sterowniki te implementują standardowy interfejs, można oczekiwać, że skrypt PHP (1) nie ulegnie zmianie w przypadku przejścia z SGBD MySQL (3) na SGBD PostgreSQL (6). W rzeczywistości ten ideał nie istnieje. W rzeczywistości, aby komunikować się z SGBD, skrypt PHP wysyła polecenia SQL (Standard Query Language). Jest to język zaimplementowany przez wszystkie SGBD, ale jest on niekompletny. Dlatego też SGBD dodały do niego własne polecenia. Jest to główna przyczyna niekompatybilności między SGBD. Ponadto typy danych, które można wykorzystywać w bazach danych, mogą się różnić w zależności od systemu SGBD. Tak więc PostgreSQL akceptuje znacznie większą liczbę typów danych niż SGBD i MySQL. Jest to druga przyczyna niezgodności. Kolejną przyczyną jest obsługa automatycznych kluczy głównych (generowanych przez SGBD): praktycznie każdy SGBD ma własną politykę. Itd… Przyczyn niezgodności jest wiele.

Jeśli chcemy uniknąć przepisywania skryptu PHP (1) poprzez przejście z MySQL (3) na PostgreSQL (6), zazwyczaj konieczne jest wstawienie nowej warstwy pomiędzy skryptem PHP (1) a sterownikiem PDO (2, 5), której rolą będzie wyeliminowanie niezgodności między tymi dwoma SGBD. Jednak w prostych przypadkach, z którymi będziemy mieli do czynienia, ta dodatkowa warstwa nie będzie konieczna.

Teraz wykorzystamy SGBD MySQL. Jest on zawarty w pakiecie Laragon (patrz link w akapicie).

Jeśli czytelnik nie ma doświadczenia w zakresie baz danych i języka SQL, może zapoznać się z dokumentem [http://sergetahe.com/cours-tutoriels-de-programmation/cours-tutoriel-sql-avec-le-sgbd-firebird/]. Dokument ten wykorzystuje system Firebird SGBD, a nie MySQL, ale przedstawia podstawy baz danych i języka SQL. Podobnie jak MySQL, Firebird posiada wersję dostępną bezpłatnie, która charakteryzuje się niewielkim zużyciem pamięci.

12.1. Tworzenie bazy danych

Poniżej pokazujemy, jak utworzyć bazę danych oraz użytkownika MySQL za pomocą narzędzia Laragon.

Po uruchomieniu Laragon [1] można zarządzać za pomocą menu [2];
w [3-5] instaluje się narzędzie [phpMyAdmin] do zarządzania MySQL, jeśli nie zostało ono jeszcze zainstalowane;

w [6] uruchamia się serwer WWW Apache oraz SGBD i MySQL;
w [7] uruchamia się serwer Apache;
w pliku [8] uruchamiane są pliki SBD i MySQL;

w [8-10] tworzy się bazę danych o nazwie [dbpersonnes] [11]. Zbudujemy bazę danych osób;

w [11] będziemy zarządzać właśnie utworzoną bazą danych;

operacja [Bases de données] wysyła żądanie internetowe do URL [http://localhost/phpmyadmin]. Odpowiada serwer WWW Apache z Laragon. URL [http://localhost/phpmyadmin] to URL narzędzia [phpMyAdmin], które zainstalowaliśmy wcześniej [5]. Narzędzie to umożliwia zarządzanie bazami danych MySQL;
domyślnie dane logowania administratora bazy to: root [13] bez hasła [14];

w [16], bazie danych, którą utworzyliśmy wcześniej;

na razie mamy bazę o nazwie [dbpersonnes], [17], która jest pusta, oraz [18];

Tworzymy użytkownika o nazwie [admpersonnes] z hasłem [nobody], który będzie miał pełne uprawnienia do bazy danych [dbpersonnes]:

w [19] przechodzimy do bazy [dbpersonnes];
w [20] wybieramy zakładkę [Privileges];
w [21-22] widać, że użytkownik [root] ma wszystkie uprawnienia do bazy [dbpersonnes];
w [23] tworzy się nowego użytkownika;

w [25-26] użytkownik otrzyma identyfikator [admdbpersonnes];
w [27-29] jego hasło będzie brzmiało [nobody];
w przypadku [30], phpMyAdmin sygnalizuje, że hasło jest bardzo słabe (łatwe do złamania). W środowisku produkcyjnym zaleca się wygenerowanie silnego hasła przy użyciu [31];
w [32] określa się, że użytkownik [admdbpersonnes] powinien posiadać wszystkie uprawnienia do bazy [dbpersonnes];
w pliku [33] zatwierdza się podane informacje;

w [35], phpMyAdmin wskazuje, że użytkownik został utworzony;
w [36], zlecenie SQL, które zostało wydane w bazie;
w [37] użytkownik [admpersonnes] posiada wszystkie uprawnienia do bazy danych [dbpersonnes];

Obecnie mamy:

bazę danych MySQL oraz [dbpersonnes];
użytkownika [admpersonnes/nobody], który posiada pełne uprawnienia do tej bazy danych;

Napiszemy skrypty PHP w celu wykorzystania tej bazy danych. PHP udostępnia różne biblioteki do zarządzania bazami danych. Wykorzystamy bibliotekę PDO (PHP Data Objects), która znajduje się pomiędzy kodem PHP a SGBD:

Biblioteka PDO pozwala skryptowi PHP na abstrakcję od dokładnego charakteru używanego kodu SGBD. Tak więc w powyższym przykładzie SGBD MySQL można zastąpić przez SGBD PostgreSQL przy minimalnym wpływie na kod skryptu PHP. Biblioteka ta nie jest domyślnie dostępna. Jej dostępność można sprawdzić w następujący sposób:

w [1-4] sprawdza się aktywne rozszerzenia PDO;
w pliku [5] widać, że rozszerzenie PDO dla plików SGBD i MySQL jest aktywne. Pozostałe nie są aktywne. Wystarczy je kliknąć, aby je aktywować;

Innym sposobem aktywacji rozszerzenia jest bezpośrednia modyfikacja pliku [php.ini] (akapit „link”), który konfiguruje plik PHP:

w [1] rozszerzenie PDO dla MySQL jest włączone;
w pliku [2] rozszerzenie PDO dla Firebirda jest wyłączone;

Po zmodyfikowaniu pliku [php.ini] należy ponownie uruchomić program PHP firmy Laragon, aby zmiany zostały uwzględnione.

12.2. Łączenie się z bazą danych MySQL

Połączenie z SGBD odbywa się poprzez utworzenie obiektu PDO. Konstruktor akceptuje różne parametry:

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

Znaczenie parametrów jest następujące:

$dsn	(Data Source Name) to ciąg znaków określający charakter obiektu SGBD oraz jego lokalizację w Internecie. Ciąg znaków „mysql:host=localhost” wskazuje, że mamy do czynienia z SGBD MySQL działającym na serwerze lokalnym. Ciąg ten może zawierać inne parametry, w szczególności port nasłuchowy SGBD oraz nazwę bazy, z którą chcemy się połączyć: „mysql:host=localhost:port=3306:dbname=dbpersonnes”;
$user	identyfikator logujący się użytkownika;
$passwd	jego hasło;
$driver_options	tablica opcji dla sterownika SGBD;

Tylko pierwszy parametr jest obowiązkowy. Tak utworzony obiekt będzie następnie służył do obsługi wszystkich operacji wykonywanych na bazie danych, z którą nawiązano połączenie. Jeśli nie udało się utworzyć obiektu PDO, zgłaszany jest wyjątek typu PDOException.

Oto przykład połączenia [mysql-01.php]:


<?php

// połączenie z lokalną bazą danych MySql
// tożsamość użytkownika to (admpersonnes,nobody)
const ID = "admpersonnes";
const PWD = "nobody";
const HOTE = "localhost";

try {
  // połączenie
  $dbh = new PDO("mysql:host=".HOTE, ID, PWD);
  print "Connexion réussie\n";
  // zamknięcie połączenia
  $dbh = NULL;
} catch (PDOException $e) {
  print "Erreur : " . $e->getMessage() . "\n";
  exit();
}

Wyniki:

1	`Connexion réussie`

Uwagi

wiersz 11: połączenie z SGBD odbywa się poprzez utworzenie obiektu PDO. Konstruktor jest tutaj używany z następującymi parametrami:
- ciąg znaków określający rodzaj obiektu SGBD oraz jego lokalizację w Internecie. Ciąg znaków „mysql:host=localhost” wskazuje, że mamy do czynienia z obiektem SGBD MySQL działającym na serwerze lokalnym. Nie podano numeru portu. W takim przypadku domyślnie używany jest port 3306. Nie podano również nazwy bazy danych. Nastąpi wówczas połączenie z SGBD MySQL, a wybór konkretnej bazy nastąpi później;
- identyfikator użytkownika;
- jego hasło;
wiersz 14: zamknięcie połączenia następuje poprzez usunięcie obiektu PDO utworzonego na początku;
wiersz 15: połączenie z obiektem SGBD może się nie powieść. W takim przypadku generowany jest wyjątek typu PDOException. Wyjątek ten wywodzi się z wyjątku PHP [RuntimeException];
wiersz 16: wyświetlany jest komunikat o błędzie związany z wyjątkiem;

Uruchommy ponownie skrypt, wpisując w wierszu 6 nieprawidłowe hasło. Wynik będzie wówczas następujący:


Erreur : SQLSTATE[HY000] [1045] Access denied for user 'admpersonnes'@'localhost' (using password: YES)

12.3. Tworzenie tabeli

Skrypt [mysql-02.php] pokazuje tworzenie tabeli w bazie danych:


<?php

// identyfikator bazy danych
const DSN = "mysql:host=localhost;dbname=dbpersonnes";
// dane uwierzytelniające użytkownika
const ID = "admpersonnes";
const PWD = "nobody";

try {
  // połączenie z bazą MySql
  $connexion = new PDO(DSN, ID, PWD);
  // usunięcie tabeli „osoby”, jeśli istnieje
  $sql = "drop table personnes";
  $connexion->exec($sql);
  // utworzenie tabeli „osoby”
  $sql = "create table personnes (prenom varchar(30) NOT NULL, nom varchar(30) NOT NULL, age integer NOT NULL, primary key(nom,prenom))";
  $connexion->exec($sql);
} catch (PDOException $ex) {
  // wyświetlenie komunikatu o błędzie
  print "Erreur : " . $ex->getMessage() . "\n";
} finally {
  // w razie potrzeby następuje wylogowanie
  $connexion = NULL;
}
// koniec
print "Terminé\n";
exit;

Komentarze

wiersz 11: połączenie z bazą danych. Jest to zawsze pierwsza czynność, którą należy wykonać. Wynikiem połączenia jest obiekt [PDO], za pośrednictwem którego będą realizowane operacje na bazie danych;
wiersz 13: polecenie SQL [drop table personnes] spowoduje usunięcie tabeli [personnes] z bazy danych [dbpersonnes]. Jeśli tabela [personnes] nie istnieje, nie spowoduje to błędu;
wiersz 14: wykonanie poprzedniego polecenia SQL w bazie danych [dbpersonnes]. Wykonanie to może uruchomić polecenie [PDOException], które zostanie przechwycone w wierszu 18;
wiersz 16: to polecenie SQL tworzy tabelę [personnes]. Tabela zawiera wiersze i kolumny. Kolumny tworzą tzw. strukturę tabeli. Wiersze tworzą zawartość tabeli. Baza danych może zawierać jedną lub więcej tabel. Tabela [personnes] będzie miała w tym przypadku trzy kolumny:
- imię: imię osoby w postaci ciągu znaków o długości nieprzekraczającej 30 znaków;
- nazwisko: nazwisko tej samej osoby w postaci ciągu znaków o długości nieprzekraczającej 30 znaków;
- wiek: wiek osoby w postaci liczby całkowitej;
- atrybut NOT NULL w kolumnie wymaga, aby kolumna miała wartość. Brak wartości powoduje błąd [PDOException];
- [primary key(nom,prenom)] ustanawia klucz główny dla tabeli [personnes]. Klucz podstawowy ma unikalną wartość dla każdego wiersza w tabeli. W tym przypadku klucz podstawowy zostanie utworzony poprzez połączenie kolumn [nom] i [prenom] danego wiersza. Ograniczenie to sprawia, że w tabeli nie mogą występować dwie osoby o tym samym imieniu i nazwisku, a więc dwie osoby o tym samym imieniu i nazwisku. Utworzenie osoby o tym samym imieniu i nazwisku w tabeli powoduje błąd [PDOException];
wiersz 17: wykonanie polecenia SQL na bazie danych [dbpersonnes];
wiersz 20: jeśli wystąpi błąd [PDOException], wyświetlany jest powiązany komunikat o błędzie;
wiersze 21–24: w każdym przypadku, niezależnie od wystąpienia wyjątku, przechodzi się do klauzuli [finally] w celu zamknięcia połączenia z bazą danych (wiersz 23);

Wyniki:

Jeśli skrypt zostanie wykonany bez błędów, w sekcji phpMyAdmin widoczna jest obecność tabeli:

w pliku [3] – bazę danych;
w [4] – przedstawiona tabela;
w [5] struktura tabel jest przedstawiona w zakładce [Structure];
w pliku [6-8] – trzy kolumny tabeli;
w pliku [9] żadna z trzech kolumn nie może być pusta;

w [10] znajduje się lista indeksów tabeli. Indeks pozwala na znalezienie w tabeli wierszy o danym indeksie szybciej niż w przypadku sekwencyjnego przeglądania wierszy tabeli. Klucz podstawowy zawsze stanowi część indeksów, ale indeks nie musi być kluczem podstawowym;
w [11] indeks jest tutaj kluczem głównym;
w [12] indeks składa się z kolumn [nom, prenom] każdego wiersza;

Zobaczmy teraz, co się stanie, jeśli wprowadzimy błędy odpowiednio w nazwie bazy danych, nazwie użytkownika i jego haśle:

Jeśli podamy nieistniejącą nazwę bazy danych:


Erreur : SQLSTATE[HY000] [1044] Access denied for user 'admpersonnes'@'%' to database 'dbpersonnes2'

Jeśli podamy nieistniejącą nazwę użytkownika:


Erreur : SQLSTATE[HY000] [1045] Access denied for user 'admpersonnes2'@'localhost' (using password: YES)

Jeśli wprowadzimy nieprawidłowe hasło:


Erreur : SQLSTATE[HY000] [1045] Access denied for user 'admpersonnes'@'localhost' (using password: YES)

12.4. Wypełnianie tabeli

Napiszemy skrypt PHP, który wykonuje polecenia SQL znajdujące się w poniższym pliku tekstowym [creation.txt]:

drop table if exists personnes
SET NAMES 'utf8'
create table personnes (prenom varchar(30) not null, nom varchar(30) not null, age integer not null, primary key (nom,prenom))
insert into personnes (prenom, nom, age) values('Paul','Langevin',48)
insert into personnes (prenom, nom, age) values ('Sylvie','Lefur',70)
insert into personnes (prenom, nom, age) values ('Sylvie','Lefur',70)
insert into personnes (prenom, nom, age) values ('Pierre','Nicazou',35)
insert into personnes (prenom, nom, age) values ('Géraldine','Colou',26)
insert into personnes (prenom, nom, age) values ('Paulette','Girond',56)
insert into personnes (prenom, nom, age) values ('Paulette','Girond',56)

Uwagi

język SQL (Structured Query Language) nie rozróżnia wielkości liter (wielkich i małych) w poleceniach SQL;
wiersz 1: usuwa się tabelę [personnes], jeśli istnieje;
wiersz 2: informuje się serwer MySQL, że zostaną mu przesłane znaki zakodowane w formacie UTF-8. To polecenie SQL, specyficzne dla MySQL, jest tutaj konieczne na przykład po to, aby w bazie danych pojawił się znak „é” z imienia Géraldine w wierszu 7. Jeśli nie umieści się wiersza 2, znak „é” zostanie przekształcony w sekwencję dwóch dziwnych znaków. Klientem jest skrypt PHP napisany w środowisku NetBeans. Skrypt ten koduje pliki w formacie UTF-8 i [1-4] przedstawionych poniżej:

wiersz 3: utworzenie tabeli [personnes] z trzema kolumnami (imię, nazwisko, wiek) i kluczem głównym (nazwisko, imię);
wiersze 4–10: wstawienie 7 wierszy do tabeli [personnes];
wiersz 6: to polecenie wstawiania powinno zakończyć się niepowodzeniem, ponieważ próbuje wykonać to samo wstawienie co w wierszu 5. Ograniczenie klucza głównego powinno uniemożliwić to wstawienie: nie może istnieć dwie osoby o tym samym imieniu i nazwisku;
wiersz 10: to polecenie wstawiania powinno zakończyć się niepowodzeniem, ponieważ próbuje wykonać to samo wstawienie co w wierszu 9;

Skrypt PHP, odpowiedzialny za wykonanie poleceń SQL z tego pliku tekstowego, wygląda następująco: [mysql-03.php]:


<?php

// identyfikator bazy danych
const DSN = "mysql:host=localhost;dbname=dbpersonnes";
// identyfikatory użytkownika
const ID = "admpersonnes";
const PWD = "nobody";
// identyfikator pliku tekstowego z poleceniami SQL do wykonania
const SQL_COMMANDS_FILENAME = "creation.txt";

// nawiązanie połączenia z bazą danych MySql
try {
  $connexion = new PDO(DSN, ID, PWD);
} catch (PDOException $ex) {
  // wyświetlanie błędu
  print "Erreur : " . $ex->getMessage() . "\n";
  exit;
}
// chcemy, aby przy każdym błędzie pliku SGBD zgłaszano wyjątek
$connexion->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
// wykonanie pliku poleceń SQL
$erreurs = exécuterCommandes($connexion, SQL_COMMANDS_FILENAME, TRUE, FALSE);
// zamknięcie połączenia
$connexion = NULL;
//wyświetlenie liczby błędów
printf("\n-----------------------\nIl y a eu %d erreur(s)\n", count($erreurs));
for ($i = 0; $i < count($erreurs); $i++) {
  print "$erreurs[$i]\n";
}

// zakończono
print "Terminé\n";
exit;

// ---------------------------------------------------------------------------------
function exécuterCommandes(PDO $connexion, string $SQLFileName, bool $suivi = FALSE, bool $arrêt = TRUE): array {
// używa połączenia $connexion
// wykonuje polecenia SQL zawarte w pliku tekstowym SQLFileName
// ten plik jest plikiem poleceń SQL, które mają być wykonywane po jednym na wiersz
// jeśli $suivi=1, to po każdym wykonaniu polecenia SQL wyświetlany jest komunikat informujący o jego powodzeniu lub niepowodzeniu
// jeśli $arrêt=1, funkcja zatrzymuje się przy pierwszym napotkanym błędzie, w przeciwnym razie wykonuje wszystkie polecenia SQL
// funkcja zwraca tablicę (liczba błędów, błąd1, błąd2…)
// sprawdzana jest obecność pliku SQLFileName

  if (!file_exists($SQLFileName)) {
    return ["Le fichier [$SQLFileName] n'existe pas"];
  }

  // wykonanie zapytań SQL zawartych w pliku SQLFileName
  // umieszcza się je w tablicy
  $requêtes = file($SQLFileName);
  // błąd?
  if ($requêtes === FALSE) {
    return ["Erreur lors de l'exploitation du fichier SQL [$SQLFileName]"];
  }
  // wykonywuje się zapytania jedno po drugim – na początku nie ma błędów
  $erreurs = [];
  $i = 0;
  $fini = FALSE;
  while ($i < count($requêtes) && !$fini) {
    // pobieramy tekst zapytania
    // funkcja trim usunie znak końca linii
    $requête = trim($requêtes[$i]);
    // puste zapytanie?
    if (strlen($requête) == 0) {
      // pomijamy zapytanie i przechodzimy do następnego
      $i++;
      continue;
    }
    try {
      // wykonanie zapytania – może zostać zgłoszony wyjątek
      $connexion->exec($requête);
      // czy wyświetlać komunikaty na ekranie, czy nie?
      if ($suivi) {
        print "$requête : Exécution réussie\n";
      }
    } catch (PDOException $ex) {
      // wystąpił błąd
      addError($erreurs, $requête, $ex->getMessage(), $suivi);
      // czy należy zatrzymać proces?
      $fini = $arrêt;
    }
    // następne zapytanie
    $i++;
  }
  // wynik
  return $erreurs;
}

function addError(array &$erreurs, string $requête, string $msg, bool $suivi): void {
  // dodajemy komunikat o błędzie
  $msg = "$requête : Erreur (" . $msg . ")";
  $erreurs[] = $msg;
  // czy wyświetlać komunikat na ekranie, czy nie?
  if ($suivi) {
    print "$msg\n";
  }
}

Komentarze

Funkcja [exécuterCommandes] (wiersze 36–89) jest odpowiedzialna za wykonanie poleceń SQL, które znajduje w pliku tekstowym [$SQLFileName] (parametr 2). Aby je wykonać, wykorzystuje otwarte połączenie [$connexion] (parametr 1) z serwerem MySQL. Trzeci parametr [$suivi] jest wartością logiczną, która kontroluje wyświetlanie na ekranie: w przypadku TRUE wykonane polecenie SQL jest wyświetlane na ekranie wraz z informacją o powodzeniu lub niepowodzeniu, w przeciwnym razie wykonanie polecenia SQL przebiega w tle. Czwarty parametr [$arrêt] określa, co należy zrobić, gdy polecenie SQL zakończy się niepowodzeniem: w przypadku TRUE oznacza to, że wykonanie poleceń SQL musi zostać zatrzymane; w przeciwnym razie jest ono kontynuowane. Funkcja [exécuterCommandes] zwraca tablicę komunikatów o błędach, pustą, jeśli nie wystąpiły żadne błędy;
wiersze 11–18: otwierane jest połączenie z bazą danych MySQL [dbpersonnes]. Jeśli otwarcie nie powiedzie się, wyświetlany jest komunikat o błędzie i program zatrzymuje się (wiersze 14–18);
wiersz 22: przekazujemy zatem otwarte połączenie do funkcji [exécuterCommandes]. Zostanie ono zamknięte po powrocie z funkcji (wiersz 24);
wiersz 20: przed przekazaniem go do funkcji [exécuterCommandes] konfiguruje się połączenie. W przypadku błędu operacje SQL z obiektem [PDO] mogą albo zwrócić wartość logiczną FALSE (wartość domyślna), albo wygenerować wyjątek. Wiersz 20 wybiera ten drugi przypadek. Łatwo jest bowiem „zapomnieć” o sprawdzeniu wyniku logicznego wykonania polecenia SQL. Spowoduje to błąd w późniejszym etapie, ale w innym miejscu kodu, co utrudni zlokalizowanie pierwotnego źródła błędu. W przypadku nieobsługiwanego wyjątku (brak catch) wyjątek będzie się przesuwał w górę kodu, aż napotka instrukcję catch lub dotrze do interpretera PHP, który przechwyci ten wyjątek. W takim przypadku wyświetlany jest rodzaj wyjątku oraz miejsce jego wystąpienia w kodzie;
wiersz 22: wywoływana jest funkcja [exécuterCommandes] w celu wykonania pliku poleceń SQL [$SQLFileName];
wiersze 45–47: sprawdzane jest, czy plik poleceń SQL rzeczywiście istnieje. Jeśli tak nie jest, odnotowuje się błąd i zwraca ten wynik;
wiersz 51: umieszczamy zlecenia SQL w tablicy [$requêtes]. Wiersze 53–55: jeśli operacja się nie powiedzie, zwracamy tablicę błędów zawierającą pojedynczy komunikat;
wiersz 57: błędy są gromadzone w tablicy [$erreurs];
wiersz 58: numer zapytania;
wiersz 59: zmienna logiczna [$fini] kontroluje wykonywanie poleceń SQL z tablicy [$requêtes]. Gdy wartość ta zmienia się na TRUE, wykonanie zostaje zatrzymane;
wiersz 60: następuje pętla obejmująca wszystkie zapytania;
wiersz 63: wyodrębnia się tekst polecenia SQL o numerze i. Funkcja [trim] usunie spacje poprzedzające i następujące po tekście polecenia SQL. Przez „spacje” należy rozumieć znak spacji \b, znak powrotu karetki \r, znak końca linii \n, znak końca strony \f, znak tabulacji \t… W tym przypadku istotne jest to, że znak końca linii w tekście SQL zostanie usunięty;
wiersze 65–69: jeśli tekst SQL jest pusty, to pomijamy żądanie i przechodzimy do następnego;
wiersz 72: wysyłamy polecenie SQL do serwera MySQL. Metoda [PDO::exec] zgłosi wyjątek, jeśli wykonanie się nie powiedzie. Przypominamy, że takie zachowanie wynika z konfiguracji wprowadzonej w wierszu 20;
wiersz 79: komunikat o błędzie zostaje dodany do tablicy błędów;
wiersz 81: ustawia się zmienną logiczną [$fini], która kontroluje pętlę. Jeśli parametr [$arrêt] (wiersz 36) ma wartość TRUE, należy zatrzymać pętlę;
wiersze 74–76: jeśli wykonanie polecenia SQL zakończyło się powodzeniem, wyświetla się je na ekranie, o ile parametr [$suivi] (wiersz 36) ma wartość TRUE;
wiersz 87: po wykonaniu wszystkich poleceń SQL zwracana jest tablica błędów [$erreurs];

Funkcja [adError] z wierszy 90–97 pozwala dodać błąd do tablicy błędów [$erreurs]:

wiersz 90: funkcja otrzymuje 4 parametry:
- parametr [$erreurs] jest przekazywany przez odwołanie. Chodzi bowiem o to, aby operować na tablicy przekazanej jako parametr, a nie na jej kopii;
- parametr [$requête] to tekst SQL z zlecenia, które zakończyło się niepowodzeniem;
- parametr [$msg] to komunikat o błędzie związany z nieudanym zleceniem;
- wartość logiczna [$suivi] określa, czy komunikat o błędzie ma zostać wyświetlony ($suivi = TRUE) czy nie ($suivi = FALSE) na konsoli;

Funkcja [exécuterCommandes] jest wywoływana przez skrypt w wierszach 3–33:

wiersze 11–18: nawiązywane jest połączenie z bazą danych MySQL [dbpersonnes];
wiersz 20: konfiguracja połączenia;
wiersz 22: następnie wykonywany jest plik poleceń SQL;
wiersz 24: zamyka się połączenie;
wiersze 26–29: wyświetlane są błędy zwrócone przez funkcję [exécuterCommandes];

Wyniki wyświetlane na ekranie:


drop table if exists personnes : Exécution réussie
SET NAMES 'utf8' : 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 (prenom, nom, age) values('Paul','Langevin',48) : Exécution réussie
insert into personnes (prenom, nom, age) values ('Sylvie','Lefur',70) : Exécution réussie
insert into personnes (prenom, nom, age) values ('Sylvie','Lefur',70) : Erreur (SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry 'Lefur-Sylvie' for key 'PRIMARY')
insert into personnes (prenom, nom, age) values ('Pierre','Nicazou',35) : Exécution réussie
insert into personnes (prenom, nom, age) values ('Géraldine','Colou',26) : Exécution réussie
insert into personnes (prenom, nom, age) values ('Paulette','Girond',56) : Exécution réussie
insert into personnes (prenom, nom, age) values ('Paulette','Girond',56) : Erreur (SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry 'Girond-Paulette' for key 'PRIMARY')

-----------------------
Il y a eu 2 erreur(s)
insert into personnes (prenom, nom, age) values ('Sylvie','Lefur',70) : Erreur (SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry 'Lefur-Sylvie' for key 'PRIMARY')
insert into personnes (prenom, nom, age) values ('Paulette','Girond',56) : Erreur (SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry 'Girond-Paulette' for key 'PRIMARY')
Terminé

Wprowadzone dane są widoczne za pomocą funkcji phpMyAdmin:

12.5. Wykonanie dowolnych poleceń SQL

Poniższy skrypt przedstawia wykonanie poleceń SQL z poniższego pliku tekstowego [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'

Wśród tych poleceń SQL znajduje się polecenie select, które zwraca wyniki z bazy danych, polecenia insert, update, delete, które modyfikują bazę bez zwracania wyników, a także polecenia błędne, takie jak ostatnie (xselect). Skrypt [mysql-04.php] wygląda następująco:


<?php

// identyfikator bazy danych
const DSN = "mysql:host=localhost;dbname=dbpersonnes";
// dane uwierzytelniające użytkownika
const ID = "admpersonnes";
const PWD = "nobody";
// nazwa pliku tekstowego z poleceniami SQL do wykonania
const SQL_COMMANDS_FILENAME = "sql.txt";

try {
  // połączenie z bazą danych MySql
  $connexion = new PDO(DSN, ID, PWD);
} catch (PDOException $ex) {
  // wyświetlanie błędów
  print "Erreur : " . $ex->getMessage() . "\n";
  exit;
}
// chcemy, aby przy każdym błędzie SGBD zgłaszano wyjątek
$connexion->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
// wykonanie pliku poleceń SQL
$erreurs = exécuterCommandes($connexion, SQL_COMMANDS_FILENAME, TRUE, FALSE);
// zamknięcie połączenia
$connexion = NULL;
//wyświetlenie liczby błędów
printf("\n-----------------------\nIl y a eu %d erreur(s)\n", count($erreurs));
for ($i = 0; $i < count($erreurs); $i++) {
  print "$erreurs[$i]\n";
}

// zakończono
print "Terminé\n";
exit;

// ---------------------------------------------------------------------------------
function exécuterCommandes(PDO $connexion, string $SQLFileName, bool $suivi = FALSE, bool $arrêt = TRUE): array {
………………………………………………………….
  // wykonywanie zapytań jedno po drugim – na początku brak błędów
  $erreurs = [];
  $i = 0;
  $fini = FALSE;
  while ($i < count($requêtes) && !$fini) {
    // pobieramy tekst zapytania
    // funkcja trim usunie znak końca linii
    $requête = trim($requêtes[$i]);
    // puste zapytanie?
    if (strlen($requête) == 0) {
      // pomijamy zapytanie i przechodzimy do następnego
      $i++;
      continue;
    }
    // wykonanie zapytania
    // pobieramy jej nazwę
    $commande = "";
    if (preg_match("/^\s*(\S+)/", $requête, $champs)) {
      $commande = strtolower($champs[0]);
    }
    try {
      // czy jest to polecenie SELECT?
      if ($commande === "select") {
        $résultat = $connexion->query($requête);
      } else {
        $résultat = $connexion->exec($requête);
      }
      // czy ma być monitorowanie ekranu?
      if ($suivi) {
        print "[$requête] : Exécution réussie\n";
      }
      // wyświetlanie wyniku wykonania
      afficherInfos($commande, $résultat);
    } catch (PDOException $ex) {
      // wystąpił błąd
      addError($erreurs, $requête, $ex->getMessage(), $suivi);
      // czy należy przerwać działanie?
      $fini = $arrêt;
    }
    // kolejne zapytanie
    $i++;
  }
  // wynik
  return $erreurs;
}

function addError(array &$erreurs, string $requête, string $msg, bool $suivi): void {
  …
}

// ---------------------------------------------------------------------------------
function afficherInfos(string $commande, $résultat): void {
  // wyświetla wynik $résultat zapytania SQL
  // czy to było zapytanie SELECT?
  switch ($commande) {
    case "select" :
      // wyświetla nazwy pól
      $titre = "";
      $nbColonnes = $résultat->columnCount();
      for ($i = 0; $i < $nbColonnes; $i++) {
        $infos = $résultat->getColumnMeta($i);
        $titre .= $infos['name'] . ",";
      }
      // usuwa się ostatni znak ,
      $titre = substr($titre, 0, strlen($titre) - 1);
      // wyświetla się lista pól
      print "$titre\n";
      // linia oddzielająca
      $séparateurs = "";
      for ($i = 0; $i < strlen($titre); $i++) {
        $séparateurs .= "-";
      }
      print "$séparateurs\n";
      // dane
      foreach ($résultat as $ligne) {
        $data = "";
        for ($i = 0; $i < $nbColonnes; $i++) {
          $data .= $ligne[$i] . ",";
        }
        // usuwa się ostatni znak ,
        $data = substr($data, 0, strlen($data) - 1);
        // wyświetla
        print "$data\n";
      }
      break;
    case "update":
    case "insert":
    case "delete";
      print " $résultat lignes(s) a (ont) été modifiée(s)\n";
      break;
  }
}

Komentarze

wiersze 36–83: funkcja [exécuterCommandes] została nieznacznie zmodyfikowana: polecenie SQL [select] nie działa w taki sam sposób jak pozostałe polecenia SQL. To polecenie jako jedyne zwraca w wyniku tabelę, tj. zbiór wierszy i kolumn z bazy danych;
wiersze 55–57: wyodrębniamy pierwsze słowo z polecenia SQL za pomocą wyrażenia regularnego;
wiersze 60–64: jeśli polecenie SQL to [select], stosuje się metodę [PDO::query], w przeciwnym razie metodę [PDO::exec] w celu wykonania polecenia SQL. W obu przypadkach, jeśli wykonanie zakończy się niepowodzeniem, zostanie zgłoszony wyjątek, który zostanie przechwycony w wierszach 71–77. Jeśli wykonanie zakończy się powodzeniem, w wierszu 70 wyświetlany jest jego wynik;
wiersze 90–130: funkcja afficherInfos wyświetla informacje o wyniku wykonania polecenia SQL;
wiersz 94: obsługiwany jest przypadek [select]. Jego wynikiem jest obiekt typu [PDOStatement];
wiersz 96: metoda [PDOStatement::getColumnCount()] zwraca liczbę kolumn w tabeli wynikowej polecenia select;
wiersze 98–99: metoda [PDOStatement::getMeta(i)] zwraca słownik zawierający informacje o kolumnie nr i z tabeli wynikowej metody select.. W tym słowniku wartością powiązaną z kluczem „name” jest nazwa kolumny;
wiersze 97–102: nazwy kolumn tabeli wynikowej select są łączone w jeden ciąg znaków;
wiersze 105–110: tworzona jest linia oddzielająca o tej samej długości co utworzony wcześniej ciąg znaków;
wiersze 112–121: obiekt typu PDOStatement można przeglądać za pomocą pętli foreach. W każdej iteracji uzyskanym elementem jest wiersz tabeli wynikowej select w postaci tablicy wartości reprezentujących wartości poszczególnych kolumn tego wiersza. Wszystkie te wartości wyświetla się za pomocą pętli for (wiersze 114–116);
wiersze 123–127: wynikiem wykonania zlecenia insert, update, delete jest liczba wierszy zmodyfikowanych przez zlecenie;

Wyniki wyświetlane na ekranie:


[set names 'utf8'] : Exécution réussie
[select * from personnes] : Exécution réussie
prenom,nom,age
--------------
Géraldine,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,Géraldine
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
Géraldine,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
--------------
[insert into personnes values('Josette','Bruneau',46)] : Exécution réussie
 1 lignes(s) a (ont) été modifiée(s)
[xselect * from personnes where nom='Bruneau'] : Erreur (SQLSTATE[42000]: Syntax error or access violation: 1064 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 (SQLSTATE[42000]: Syntax error or access violation: 1064 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)
Terminé

12.6. Wykorzystanie przygotowanych zleceń SQL

12.6.1. Przykład 1

Przyjrzyjmy się poniższemu skryptowi [mysql-05.php]:


<?php

// identyfikator bazy danych
const DSN = "mysql:host=localhost;dbname=dbpersonnes";
// identyfikatory użytkownika
const ID = "admpersonnes";
const PWD = "nobody";

try {
  // połączenie z bazą danych MySql
  $connexion = new PDO(DSN, ID, PWD);
  // chcemy, aby przy każdym błędzie SGBD zgłaszano wyjątek
  $connexion->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
  // tabela osób jest opróżniana
  $connexion->exec("delete from personnes");
  // lista osób
  $personnes = [];
  $personnes[] = ["nom" => "Langevin", "prenom" => "Paul", "age" => 47];
  $personnes[] = ["nom" => "Lefur", "prenom" => "Sylvie", "age" => 28];
  // osoby te zostaną wprowadzone do bazy danych
  $statement = $connexion->prepare("insert into personnes (nom, prenom, age) values (:nom, :prenom, :age)");
  for ($i = 0; $i < count($personnes); $i++) {
    $statement->execute($personnes[$i]);
  }
} catch (PDOException $ex) {
  // wyświetlenie komunikatu o błędzie
  print "Erreur : " . $ex->getMessage() . "\n";
} finally {
// zamknięcie połączenia
  $connexion = NULL;
}

// to już koniec
print "Terminé\n";
exit;

Komentarze

Skupiamy się tutaj na wierszach 16–24, które dodają dwie osoby do tabeli osób w bazie danych [dbpersonnes].

wiersz 21: „przygotowuje się” zlecenie SQL z określonymi parametrami. Parametry są poprzedzone znakiem : :nazwisko, :imię, :wiek. Aby „przygotować” polecenie SQL, stosuje się metodę [PDO::prepare]. Wynikiem jest typ [PDOStatement]. „Przygotowanie” nie jest wykonaniem: nic nie jest wykonywane;
wiersz 23: wykonanie „przygotowanego” zlecenia za pomocą metody [PDOStatement::execute]. W tym celu należy nadać wartości parametrom :nazwisko, :imię i :wiek. Można to zrobić na kilka sposobów. W tym przypadku używamy słownika, którego klucze stanowią parametry przygotowanego zlecenia, które przekazujemy do metody [PDOStatement::execute]. Innym sposobem jest nadanie wartości parametrom za pomocą metody [PDOStatement::bindValue($paramètre,$valeur)]. Na przykład tutaj:


$statement→bindValue(“nom”,”Langevin”);
$statement→bindValue(“prenom”,”Paul”);
$statement→bindValue(“age”,47);
$statement→execute();

Wadą tego rozwiązania jest konieczność powtarzania tej instrukcji dla każdego parametru. W takim przypadku wygodniejsza może okazać się metoda słownikowa. Metoda [PDOStatement::execute] zwraca FALSE, jeśli wykonanie zakończy się niepowodzeniem;

metoda użyta tutaj do wstawiania:
- metoda przygotowana;
- n wykonań przygotowanego polecenia;

jest bardziej oszczędna pod względem czasu wykonania niż wykonanie n różnych poleceń SQL. Dlatego należy preferować tę metodę. Można ją stosować w przypadku poleceń SQL typu select, update, delete, insert. W przypadku polecenia SQL select, po jego wykonaniu za pomocą [PDOStatement::execute], pobiera się wiersze wyniku za pomocą metody [PDOStatement::fetchAll];

12.6.2. Przykład 2

Poniższy skrypt [mysql-06.php] pokazuje wykorzystanie polecenia przygotowanego dla operacji SQL select, a także różne sposoby pobierania wierszy zwróconych przez tę operację:


<?php

// identyfikator bazy danych
const DSN = "mysql:host=localhost;dbname=dbpersonnes";
// dane logowania użytkownika
const ID = "admpersonnes";
const PWD = "nobody";

try {
  // połączenie z bazą danych MySql
  $connexion = new PDO(DSN, ID, PWD);
  // chcemy, aby przy każdym błędzie SGBD zgłaszano wyjątek
  $connexion->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
  // opróżniamy tabelę osób
  $connexion->exec("delete from personnes");
  // osoby te zostaną wprowadzone do bazy danych
  $statement = $connexion->prepare("insert into personnes (nom, prenom, age) values (:nom, :prenom, :age)");
  for ($i = 0; $i < 10; $i++) {
    $statement->execute(["nom" => "nom" . $i, "prenom" => "prenom" . $i, "age" => $i * 10]);
  }
  // przeprowadzamy zapytanie do bazy danych
  $statement = $connexion->prepare("select nom, prenom, age from personnes");
  $statement->execute();
  // pierwszy wiersz
  $ligne = $statement->fetch();
  var_dump($ligne);
  // 2. wiersz
  $ligne = $statement->fetch(PDO::FETCH_ASSOC);
  var_dump($ligne);
  // trzeci wiersz
  $ligne = $statement->fetch(PDO::FETCH_OBJ);
  var_dump($ligne);
  // czwarty wiersz
  $statement->setFetchMode(PDO::FETCH_CLASS, "Person");
  $ligne = $statement->fetch();
  var_dump($ligne);
  // sekwencyjny odczyt wszystkich wierszy
  $statement = $connexion->prepare("select nom, prenom, age from personnes");
  $statement->execute();
  $statement->setFetchMode(PDO::FETCH_CLASS, "Person");
  while ($personne = $statement->fetch()) {
    print "$personne\n";
  }
} catch (PDOException $ex) {
  // wyświetlenie błędu
  print "Erreur : " . $ex->getMessage() . "\n";
} finally {
// zamknięcie połączenia
  $connexion = NULL;
}

// koniec
print "Terminé\n";
exit;

class Person {
  private $nom;
  private $prenom;
  private $age;

  public function __toString() {
    return "Personne[$this->nom,$this->prenom,$this->age]";
  }

}

Komentarze

wiersze 17–20: wstawia się 10 wierszy do tabeli [personnes] w bazie danych [admpersonnes]:

wiersz 22: „przygotowuje się” zlecenie SQL [select], które jest wykonywane w wierszu 23;
wiersz 25: za pomocą metody [PDOStatement::fetch] pobieramy jeden wiersz z wyniku wykonanej operacji SQL [select]. Metoda [PDOStatement::fetch] może na różne sposoby pobierać wiersze wynikowe z przygotowanej operacji SQL [select]. Skrypt przedstawia kilka z nich. Metoda [PDOStatement::fetch] bez parametrów zwraca bieżący wiersz z operacji [select] w postaci słownika indeksowanego zarówno według numerów kolumn, jak i ich nazw;
wiersz 26: wyświetla następujący wynik:


array(6) {
  ["nom"]=>
  string(4) "nom0"
  [0]=>
  string(4) "nom0"
  ["prenom"]=>
  string(7) "prenom0"
  [1]=>
  string(7) "prenom0"
  ["age"]=>
  string(1) "0"
  [2]=>
  string(1) "0"
}

wiersze 28–29: parametr [PDO::FETCH_ASSOC] powoduje, że zwrócony wiersz jest słownikiem indeksowanym według nazw kolumn tabeli:

array(3) {
  ["nom"]=>
  string(4) "nom1"
  ["prenom"]=>
  string(7) "prenom1"
  ["age"]=>
  string(2) "10"
}

wiersze 31–32: parametr [PDO::FETCH_OBJ] sprawia, że zwrócony wiersz jest obiektem typu [stdclass], którego atrybutami są nazwy kolumn tabeli:

object(stdClass)#2 (3) {
  ["nom"]=>
  string(4) "nom2"
  ["prenom"]=>
  string(7) "prenom2"
  ["age"]=>
  string(2) "20"
}

wiersz 34: ustawia się tryb wyszukiwania metody [fetch] za pomocą metody [PDOStatement::setFetchMode]. Tryb ten staje się wówczas trybem domyślnym, dopóki nie zostanie zmieniony albo przez inną operację [PDOStatement::setFetchMode], albo poprzez przekazanie trybu jako parametru do metody [PDOStatement::fetch], tak jak to miało miejsce wcześniej. Operacja [setFetchMode(PDO::FETCH_CLASS, "Person")] wskazuje, że odczytany wiersz ma zostać umieszczony w obiekcie typu [Person]. Klasa ta musi posiadać wśród swoich atrybutów atrybuty o nazwach odpowiadających nazwom kolumn odczytanego wiersza. Tak jest w przypadku klasy [Person] zdefiniowanej w wierszach 56–63;
w wierszu 36 wyświetlany jest następujący wynik:

object(Person)#4 (3) {
  ["nom":"Person":private]=>
  string(4) "nom3"
  ["prenom":"Person":private]=>
  string(7) "prenom3"
  ["age":"Person":private]=>
  string(2) "30"
}

wiersze 38–43: pokazują, jak sekwencyjnie wykorzystywać wyniki klasy [select];
wiersz 42: wyświetlenie [$personne] będzie wykorzystywać metodę [__toString] z klasy [Person];

12.7. Wykorzystanie transakcji

Transakcja pozwala zgrupować sekwencję poleceń SQL w jedną jednostkę wykonawczą: albo wszystkie polecenia zakończą się powodzeniem, albo jedno z nich zakończy się niepowodzeniem, a wtedy wszystkie polecenia SQL, które poprzedzały to polecenie, zostaną anulowane. Innymi słowy, gdy wykorzystuje się transakcję do wykonania poleceń SQL, po jej zakończeniu baza danych znajduje się w stanie stabilnym:

albo w nowym stanie, utworzonym przez pomyślne wykonanie wszystkich poleceń SQL w ramach transakcji;
albo w stanie, w jakim znajdowała się przed rozpoczęciem wykonywania transakcji;

Powrócimy do przykładu wykonania poleceń SQL zawartych w pliku tekstowym omówionym w akapicie „link”. Wykonanie to uwzględnimy w transakcji. Polecenia SQL będą zawarte w następującym pliku [sql2.txt]:


set names 'utf8'
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'
insert into personnes values('Josette','Bruneau',46)
select * from personnes where nom='Bruneau'
xselect * from personnes where nom='Bruneau'

Błędne polecenie w wierszu 12 spowoduje niepowodzenie całej transakcji. Powinniśmy zatem odzyskać stan bazy danych taki, jaki był przed transakcją. W powyższym przykładzie nie powinniśmy zatem widzieć w tabeli wiersza wstawionego przez wiersz 10 powyżej. Skrypt zmienia się w bardzo niewielkim stopniu. Przedstawiamy jednak ponownie cały kod [mysql-07.php]:


<?php

// identyfikator bazy danych
const DSN = "mysql:host=localhost;dbname=dbpersonnes";
// dane uwierzytelniające użytkownika
const ID = "admpersonnes";
const PWD = "nobody";
// identyfikator pliku tekstowego z poleceniami SQL do wykonania
const SQL_COMMANDS_FILENAME = "sql2.txt";

try {
  // połączenie z bazą danych MySql
  $connexion = new PDO(DSN, ID, PWD);
} catch (PDOException $ex) {
  // wyświetlanie błędów
  print "Erreur : " . $ex->getMessage() . "\n";
  exit;
}
// chcemy, aby przy każdym błędzie SGBD zgłaszano wyjątek
$connexion->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
// wykonanie pliku poleceń SQL
$erreurs = exécuterCommandes($connexion, SQL_COMMANDS_FILENAME, TRUE);
// zamknięcie połączenia
$connexion = NULL;
//wyświetlenie liczby błędów
printf("\n-----------------------\nIl y a eu %d erreur(s)\n", count($erreurs));
for ($i = 0; $i < count($erreurs); $i++) {
  print "$erreurs[$i]\n";
}

// zakończono
print "Terminé\n";
exit;

// ---------------------------------------------------------------------------------
function exécuterCommandes(PDO $connexion, string $SQLFileName, bool $suivi = FALSE): array {
// użyj połączenia $connexion
// wykonuje polecenia SQL zawarte w pliku tekstowym SQLFileName
// ten plik jest plikiem poleceń SQL, które mają być wykonywane po jednym na wiersz
// polecenia SQL są wykonywane w ramach jednej transakcji
// jeśli którekolwiek z poleceń zakończy się niepowodzeniem, transakcja zostanie anulowana, a baza danych powróci do stanu sprzed transakcji
// jeśli $suivi=1, to po każdym wykonaniu polecenia SQL wyświetlany jest komunikat informujący o jego powodzeniu lub niepowodzeniu
// funkcja zwraca tablicę (liczba błędów, błąd1, błąd2…)
//
// sprawdzana jest obecność pliku SQLFileName
  if (!file_exists($SQLFileName)) {
    return ["Le fichier [$SQLFileName] n'existe pas"];
  }
  // wykonanie zapytań SQL zawartych w pliku SQLFileName
  // umieszcza się je w tablicy
  $requêtes = file($SQLFileName);
  // błąd?
  if ($requêtes === FALSE) {
    return ["Erreur lors de l'exploitation du fichier SQL [$SQLFileName]"];
  }
  // zapytania zostaną umieszczone w transakcji
  $connexion->beginTransaction();
  // zapytania są wykonywane jedno po drugim – na początku nie ma błędów
  $erreurs = [];
  $i = 0;
  $fini = FALSE;
  while ($i < count($requêtes) && !$fini) {
    // pobieramy tekst zapytania
    // funkcja trim usunie znak końca linii
    $requête = trim($requêtes[$i]);
    // puste zapytanie?
    if (strlen($requête) == 0) {
      // pomijamy zapytanie i przechodzimy do następnego
      $i++;
      continue;
    }
    // wykonanie zapytania
    // pobieramy jej nazwę
    $commande = "";
    if (preg_match("/^\s*(\S+)/", $requête, $champs)) {
      $commande = strtolower($champs[0]);
    }
    try {
      // czy to polecenie SELECT?
      if ($commande === "select") {
        $résultat = $connexion->query($requête);
      } else {
        $résultat = $connexion->exec($requête);
      }
      // czy ma być monitorowanie ekranu?
      if ($suivi) {
        print "[$requête] : Exécution réussie\n";
      }
      // wyświetlanie wyniku wykonania
      afficherInfos($commande, $résultat);
    } catch (PDOException $ex) {
      // wystąpił błąd
      addError($erreurs, $requête, $ex->getMessage(), $suivi);
      // zatrzymujemy się w następnej rundzie
      $fini = TRUE;
    }
    // następne zapytanie
    $i++;
  }
  // koniec transakcji
  if (!$fini) {
    // nie wystąpiły żadne błędy: transakcja została zatwierdzona
    $connexion->commit();
  } else {
    // wystąpiły błędy: transakcja zostaje anulowana
    $connexion->rollBack();
    // dodano błąd
    addError($erreurs, "", "Transaction annulée", $suivi);
  }
  // wynik
  return $erreurs;
}

function addError(array &$erreurs, string $requête, string $msg, bool $suivi): void {
  …
}

// ---------------------------------------------------------------------------------
function afficherInfos(string $commande, $résultat): void {
  …
}

Komentarze

Podkreśliliśmy zmiany w oryginalnym skrypcie [mysql-04.php].

wiersze 22, 36: funkcja [exécuterCommandes] utraciła swój czwarty parametr [$arrêt=TRUE]. Ponieważ polecenia SQL są wykonywane w ramach transakcji, każdy błąd spowoduje jej przerwanie;
wiersze 40–41: wywołanie funkcji w ramach transakcji;
wiersz 57: rozpoczynamy transakcję. Od tego momentu każde polecenie SQL wykonywane w pętli w wierszach 62–99 jest realizowane w ramach tej transakcji;
wiersze 101–109: wartość logiczna [$fini] przyjmuje wartość TRUE, jeśli wystąpił błąd (wiersz 95). Gdy ma wartość FALSE, nie wystąpiły żadne błędy i transakcja zostaje zatwierdzona (wiersz 103). Gdy ma wartość TRUE, wystąpiły błędy, w związku z czym transakcja zostaje anulowana (wiersz 106), a błąd transakcji dodawany jest do listy błędów (wiersz 108);

Wyniki

Przed uruchomieniem skryptu baza [admpersonnes] ma następujący stan:

Uruchamiamy skrypt [mysql-07.php]. Wyświetlane są wówczas następujące ekrany:


[set names 'utf8'] : Exécution réussie
[select * from personnes] : Exécution réussie
prenom,nom,age
--------------
prenom0,nom0,0
prenom1,nom1,10
prenom2,nom2,20
prenom3,nom3,30
prenom4,nom4,40
prenom5,nom5,50
prenom6,nom6,60
prenom7,nom7,70
prenom8,nom8,80
prenom9,nom9,90
[select nom,prenom from personnes order by nom asc, prenom desc] : Exécution réussie
nom,prenom
----------
nom0,prenom0
nom1,prenom1
nom2,prenom2
nom3,prenom3
nom4,prenom4
nom5,prenom5
nom6,prenom6
nom7,prenom7
nom8,prenom8
nom9,prenom9
[select * from personnes where age between 20 and 40 order by age desc, nom asc, prenom asc] : Exécution réussie
prenom,nom,age
--------------
prenom4,nom4,40
prenom3,nom3,30
prenom2,nom2,20
[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
--------------
[insert into personnes values('Josette','Bruneau',46)] : 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,46
[xselect * from personnes where nom='Bruneau'] : Erreur (SQLSTATE[42000]: Syntax error or access violation: 1064 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)
[] : Erreur (Transaction annulée)

-----------------------
Il y a eu 2 erreur(s)
[xselect * from personnes where nom='Bruneau'] : Erreur (SQLSTATE[42000]: Syntax error or access violation: 1064 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)
[] : Erreur (Transaction annulée)
Terminé

wiersz 53: wystąpił błąd w poleceniu [xselect];
wiersz 54: transakcja zostaje anulowana;

Jeśli sprawdzimy stan bazy danych, okaże się, że jest ona w takim samym stanie jak przed uruchomieniem skryptu. W szczególności nie widać w niej wiersza [Josette, Bruneau, 46] z wiersza 52 powyższych wyników.

Podsumowanie

transakcja rozpoczyna się metodą [PDO::beginTransaction];
zakończono ją pomyślnie za pomocą metody [PDO::commit];
zakończona jest niepowodzeniem za pomocą metody [PDO::rollback];

Podczas korzystania z bazy danych dobrym nawykiem jest umieszczanie każdej operacji SQL w transakcji, aby odizolować się od innych użytkowników bazy (pełni ona również tę funkcję). Transakcja powinna być jak najkrótsza. Nie należy więc zapomnieć o zakończeniu jej poleceniem [commit] lub [rollback], w zależności od sytuacji.