12. Gebruik van SGBD MySQL

We gaan nu PHP-scripts schrijven met behulp van een MySQL-database:

In de bovenstaande architectuur communiceert het script PHP (1) niet rechtstreeks met SGBD (databasebeheersysteem) (3). Het communiceert via een tussenpersoon die de SGBD-driver of ook wel de SGBD-driver wordt genoemd. PHP biedt een standaardinterface voor deze stuurprogramma’s, namelijk de interface PDO (PHP Data Objects). Deze interface wordt geïmplementeerd door verschillende klassen die zijn aangepast aan elk SGBD: een klasse voor de SGBD MySQL, een andere voor de SGBD PostgreSQL… Om van SGBD te wisselen, wisselt men van stuurprogramma:

Het stuurprogramma PDO scheidt het script PHP (1) van SGBD (3, 6). Aangezien deze stuurprogramma's een standaardinterface implementeren, mag men verwachten dat het script PHP (1) niet verandert wanneer men overschakelt van SGBD MySQL (3) naar SGBD PostgreSQL (6). In de praktijk bestaat dit ideaal echter niet. Om namelijk te communiceren met de SGBD, verstuurt het script PHP opdrachten in de SQL (Standard Query Language). Dit is een taal die door alle SGBD wordt geïmplementeerd, maar die onvolledig is. Daarom hebben de SGBD er eigen commando’s aan toegevoegd. Dit is een eerste oorzaak van incompatibiliteit tussen SGBD. Bovendien kunnen de gegevenstypen die in de databases kunnen worden gebruikt, van het ene SGBD-systeem tot het andere verschillen. Zo ondersteunt PostgreSQL een veel groter aantal gegevenstypen dan SGBD en MySQL. Dit is een tweede oorzaak van incompatibiliteit. Een andere oorzaak is het beheer van automatische primaire sleutels (gegenereerd door de SGBD): vrijwel elke SGBD heeft zijn eigen beleid. Enzovoort… Er zijn talrijke oorzaken van incompatibiliteit.

Als men wil voorkomen dat het script PHP (1) opnieuw moet worden geschreven bij de overgang van MySQL (3) naar PostgreSQL (6), moeten we doorgaans een nieuwe laag invoegen tussen het script PHP (1) en de driver PDO (2, 5), die tot taak heeft de incompatibiliteiten tussen de twee SGBD weg te werken. In de eenvoudige gevallen die we zullen tegenkomen, is deze extra laag echter niet nodig.

We gaan nu de SGBD MySQL gebruiken. Deze is opgenomen in het Laragon-pakket (zie paragraaf ‘link’).

Als de lezer nog niet bekend is met het begrip database en de taal SQL, kan hij het document [http://sergetahe.com/cours-tutoriels-de-programmation/cours-tutoriel-sql-avec-le-sgbd-firebird/] raadplegen. Dit document maakt gebruik van Firebird SGBD en niet van MySQL, maar behandelt de basisprincipes van databases en de taal SQL. Net als MySQL beschikt Firebird over een vrij te gebruiken versie die weinig geheugen in beslag neemt.

12.1. Een database aanmaken

We laten nu zien hoe je een database en een gebruiker MySQL kunt aanmaken met de tool Laragon.

Zodra Laragon [1] is gestart, kan het worden beheerd via een menu [2];
in [3-5] installeert u de beheertool [phpMyAdmin] voor MySQL, indien deze nog niet is geïnstalleerd;

in [6] worden de Apache-webserver en SGBD MySQL gestart;
in [7] wordt de Apache-server gestart;
in [8] worden SBD en MySQL gestart;

in [8-10] wordt een database aangemaakt met de naam [dbpersonnes] [11]. We gaan een database met personen opbouwen;

in [11] gaan we de zojuist aangemaakte database beheren;

de bewerking [Bases de données] verstuurt een webverzoek naar de URL [http://localhost/phpmyadmin]. De Apache-webserver van Laragon geeft hierop een antwoord. URL [http://localhost/phpmyadmin] is de URL van het hulpprogramma [phpMyAdmin] dat we eerder hebben geïnstalleerd [5]. Met dit hulpprogramma kun je de MySQL-databases beheren;
standaard zijn de inloggegevens van de databasebeheerder: root [13] zonder wachtwoord [14];

in [16], de database die we eerder hebben aangemaakt;

we hebben momenteel een database [dbpersonnes] [17] die leeg is [18];

We maken een gebruiker aan, [admpersonnes], met het wachtwoord [nobody], die alle rechten krijgt op de database [dbpersonnes]:

in [19] bevinden we ons in de database [dbpersonnes];
in [20] selecteren we het tabblad [Privileges];
in [21-22] zien we dat de gebruiker [root] alle rechten heeft op de database [dbpersonnes];
in [23] maakt men een nieuwe gebruiker aan;

in [25-26] krijgt de gebruiker de gebruikersnaam [admdbpersonnes];
in [27-29] is zijn wachtwoord [nobody];
bij [30] geeft phpMyAdmin aan dat het wachtwoord erg zwak is (gemakkelijk te kraken). In de productieomgeving verdient het de voorkeur om een sterk wachtwoord te genereren met [31];
bij [32] wordt aangegeven dat de gebruiker [admdbpersonnes] alle rechten moet hebben op de database [dbpersonnes];
in [33] worden de opgegeven gegevens gevalideerd;

in [35] geeft phpMyAdmin aan dat de gebruiker is aangemaakt;
in [36] is de opdracht SQL die op basis daarvan is uitgegeven;
in [37] heeft de gebruiker [admpersonnes] alle rechten op de database [dbpersonnes];

Nu hebben we:

een database MySQL [dbpersonnes];
een gebruiker [admpersonnes/nobody] die alle rechten heeft op deze database;

We gaan PHP-scripts schrijven om de database te benutten. PHP beschikt over diverse bibliotheken voor het beheer van databases. We zullen de bibliotheek PDO (PHP Data Objects) gebruiken, die tussen de code PHP en SGBD wordt ingevoegd:

De bibliotheek PDO stelt het script PHP in staat om zich los te maken van de exacte aard van de gebruikte SGBD. Zo kan hierboven kan de SGBD MySQL worden vervangen door de SGBD PostgreSQL met minimale gevolgen voor de code van het script PHP. Deze bibliotheek is standaard niet beschikbaar. De beschikbaarheid kan als volgt worden gecontroleerd:

in [1-4] controleert men de actieve extensies PDO;
in [5] is te zien dat de uitbreiding PDO voor SGBD en MySQL actief is. De andere zijn dat niet. U hoeft er alleen maar op te klikken om ze te activeren;

Een andere manier om een extensie te activeren is door het bestand [php.ini] (zie de link hierboven) rechtstreeks te wijzigen, dat PHP configureert:

in [1] is de uitbreiding PDO van MySQL ingeschakeld;
in [2] is de Firebird-extensie PDO uitgeschakeld;

Nadat u het bestand [php.ini] hebt gewijzigd, moet u PHP van Laragon opnieuw starten om de wijzigingen door te voeren.

12.2. Verbinding maken met een database MySQL

De verbinding met een SGBD wordt tot stand gebracht door een PDO-object aan te maken. De constructor accepteert verschillende parameters:

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

De betekenis van de parameters is als volgt:

$dsn	(Data Source Name) is een tekenreeks die de aard van de SGBD en de locatie ervan op het internet aangeeft. De tekenreeks "mysql:host=localhost" geeft aan dat het gaat om een SGBD MySQL die op de lokale server draait. Deze tekenreeks kan nog andere parameters bevatten, met name de luisterpoort van de SGBD en de naam van de database waarmee verbinding moet worden gemaakt: "mysql:host=localhost:port=3306:dbname=dbpersonnes";
$user	gebruikersnaam van de gebruiker die verbinding maakt;
$passwd	zijn wachtwoord;
$driver_options	een array met opties voor de driver van de SGBD;

Alleen de eerste parameter is verplicht. Het zo gecreëerde object zal vervolgens dienen als basis voor alle bewerkingen die worden uitgevoerd op de database waarmee verbinding is gemaakt. Als het object PDO niet kon worden aangemaakt, wordt een uitzondering van het type PDOException gegenereerd.

Hier volgt een voorbeeld van een verbinding [mysql-01.php]:


<?php

// verbinding maken met een lokale database MySql
// de gebruikersidentiteit is (admpersonnes,nobody)
const ID = "admpersonnes";
const PWD = "nobody";
const HOTE = "localhost";

try {
  // verbinding
  $dbh = new PDO("mysql:host=".HOTE, ID, PWD);
  print "Connexion réussie\n";
  // verbinding verbreken
  $dbh = NULL;
} catch (PDOException $e) {
  print "Erreur : " . $e->getMessage() . "\n";
  exit();
}

Resultaten:

1	`Connexion réussie`

Opmerkingen

regel 11: de verbinding met een SGBD wordt tot stand gebracht door een PDO-object aan te maken. De constructor wordt hier gebruikt met de volgende parameters:
- een tekenreeks die de aard van de SGBD en de locatie ervan op het internet aangeeft. De tekenreeks "mysql:host=localhost" geeft aan dat het gaat om een SGBD MySQL die op de lokale server draait. De poort is niet gespecificeerd. In dat geval wordt standaard poort 3306 gebruikt. De naam van de database is evenmin opgegeven. Er wordt dan verbinding gemaakt met de SGBD MySQL, waarbij de selectie van een specifieke database later plaatsvindt;
- een gebruikersnaam;
- zijn wachtwoord;
regel 14: de verbinding wordt verbroken door het aanvankelijk aangemaakte object PDO te verwijderen;
regel 15: de verbinding met een SGBD kan mislukken. In dat geval wordt een uitzondering van het type PDOException gegenereerd. Deze is afgeleid van de uitzondering PHP [RuntimeException];
regel 16: de foutmelding van de uitzondering wordt weergegeven;

Laten we het script opnieuw uitvoeren door in regel 6 een onjuist wachtwoord in te voeren. Het resultaat is dan als volgt:


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

12.3. Een tabel aanmaken

Het script [mysql-02.php] laat zien hoe een tabel in een database wordt aangemaakt:


<?php

// database-identiteit
const DSN = "mysql:host=localhost;dbname=dbpersonnes";
// gebruikersgegevens
const ID = "admpersonnes";
const PWD = "nobody";

try {
  // verbinding met de database MySql
  $connexion = new PDO(DSN, ID, PWD);
  // verwijdering van de tabel ‘personen’ indien deze bestaat
  $sql = "drop table personnes";
  $connexion->exec($sql);
  // de tabel 'personen' aanmaken
  $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) {
  // foutmelding weergeven
  print "Erreur : " . $ex->getMessage() . "\n";
} finally {
  // indien nodig wordt de verbinding verbroken
  $connexion = NULL;
}
// einde
print "Terminé\n";
exit;

Opmerkingen

regel 11: verbinding met de database. Dit is altijd het eerste wat je moet doen. Het resultaat van de verbinding is een object [PDO] waarmee de bewerkingen op de database worden uitgevoerd;
regel 13: de opdracht SQL [drop table personnes] verwijdert de tabel [personnes] uit de database [dbpersonnes]. Als de tabel [personnes] niet bestaat, leidt dit niet tot een fout;
regel 14: uitvoering van de vorige opdracht SQL op de database [dbpersonnes]. Deze uitvoering kan een [PDOException] starten die op regel 18 wordt onderschept;
regel 16: deze opdracht SQL maakt een tabel [personnes] aan. Een tabel bevat rijen en kolommen. De kolommen vormen de zogenaamde structuur van de tabel. De rijen vormen de inhoud van de tabel. Een database kan één of meerdere tabellen bevatten. De tabel [personnes] zal hier drie kolommen hebben:
- voornaam: de voornaam van een persoon in de vorm van een tekenreeks van maximaal 30 tekens;
- achternaam: de achternaam van diezelfde persoon in de vorm van een tekenreeks van maximaal 30 tekens;
- leeftijd: de leeftijd van de persoon in de vorm van een geheel getal;
- het attribuut NOT NULL op een kolom vereist dat de kolom een waarde heeft. Als er geen waarde wordt opgegeven, leidt dit tot een [PDOException];
- [primary key(nom,prenom)] stelt een primaire sleutel in voor de tabel [personnes]. Een primaire sleutel heeft een unieke waarde voor elke rij in de tabel. Hier wordt de primaire sleutel verkregen door de kolommen [nom] en [prenom] van de rij samen te voegen. Deze beperking zorgt ervoor dat er in de tabel geen twee personen met dezelfde voor- en achternaam kunnen voorkomen, dus geen twee homoniemen. Het aanmaken van een homoniem van een persoon in de tabel leidt tot een foutmelding [PDOException];
regel 17: uitvoering van de opdracht SQL op de database [dbpersonnes];
regel 20: als er een [PDOException] optreedt, wordt de bijbehorende foutmelding weergegeven;
regels 21-24: er wordt in alle gevallen, ongeacht of er sprake is van een uitzondering, doorgegaan naar de clausule [finally] om de verbinding met de database te verbreken (regel 23);

Resultaten:

Als het script zonder fouten wordt uitgevoerd, is de aanwezigheid van de tabel te zien in phpMyAdmin:

in [3] de database;
in [4] wordt de tabel weergegeven;
in [5] wordt de structuur van de tabellen weergegeven in het tabblad [Structure];
in [6-8], de drie kolommen van de tabel;
in [9] mag geen van de drie kolommen leeg zijn;

in [10], de lijst met indexen van de tabel. Met een index kunnen de rijen met een bepaalde index sneller in de tabel worden teruggevonden dan wanneer de rijen van de tabel sequentieel zouden worden doorlopen. De primaire sleutel maakt altijd deel uit van de indexen, maar een index hoeft geen primaire sleutel te zijn;
in [11] is de index hier de primaire sleutel;
in [12] bestaat de index uit de kolommen [nom, prenom] van elke rij;

Laten we nu eens kijken wat er gebeurt als we fouten aanmaken, respectievelijk in de naam van de database, de gebruikersnaam en het wachtwoord:

Als we een niet-bestaande databasenaam invoeren:


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

Als we een niet-bestaande gebruikersnaam invoeren:


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

Als we een verkeerd wachtwoord invoeren:


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

12.4. Een tabel vullen

We gaan een script PHP schrijven dat de opdrachten SQL uitvoert die in het volgende tekstbestand [creation.txt] staan:

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)

Opmerkingen

de taal SQL (Structured Query Language) is niet hoofdlettergevoelig (hoofdletters, kleine letters) voor de opdrachten SQL;
regel 1: de tabel [personnes] wordt verwijderd als deze bestaat;
regel 2: de server MySQL wordt geïnformeerd dat er tekens worden verzonden die gecodeerd zijn in UTF-8. Dit SQL-commando, dat specifiek is voor MySQL, is hier bijvoorbeeld nodig om regel 7, de ‘é’ van Géraldine, in de database op te nemen. Als regel 2 niet wordt toegevoegd, wordt de ‘é’ omgezet in een reeks van twee vreemde tekens. De client is het script PHP, geschreven in NetBeans. Dit script codeert de bestanden in de onderstaande UTF-8 en [1-4]:

regel 3: aanmaken van de tabel [personnes] met de drie kolommen (voornaam, achternaam, leeftijd) en de primaire sleutel (achternaam, voornaam);
regels 4-10: invoegen van 7 regels in de tabel [personnes];
regel 6: deze invoegopdracht zou moeten mislukken, omdat deze dezelfde invoegactie probeert uit te voeren als regel 5. De primaire-sleutelbeperking zou deze invoegactie moeten verhinderen: er kunnen geen twee personen zijn met dezelfde voor- en achternaam;
regel 10: deze invoeropdracht zou moeten mislukken omdat deze dezelfde invoer probeert als regel 9;

Het script PHP, dat de opdrachten SQL uit dit tekstbestand uitvoert, is als volgt: [mysql-03.php]:


<?php

// database-ID
const DSN = "mysql:host=localhost;dbname=dbpersonnes";
// gebruikersgegevens
const ID = "admpersonnes";
const PWD = "nobody";
// identificatie van het tekstbestand met de uit te voeren SQL-opdrachten
const SQL_COMMANDS_FILENAME = "creation.txt";

// verbinding met de database openen MySql
try {
  $connexion = new PDO(DSN, ID, PWD);
} catch (PDOException $ex) {
  // foutmelding
  print "Erreur : " . $ex->getMessage() . "\n";
  exit;
}
// we willen dat bij elke fout in SGBD een uitzondering wordt gegenereerd
$connexion->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
// uitvoering van het opdrachtbestand SQL
$erreurs = exécuterCommandes($connexion, SQL_COMMANDS_FILENAME, TRUE, FALSE);
// verbinding afsluiten
$connexion = NULL;
// aantal fouten weergeven
printf("\n-----------------------\nIl y a eu %d erreur(s)\n", count($erreurs));
for ($i = 0; $i < count($erreurs); $i++) {
  print "$erreurs[$i]\n";
}

// het is klaar
print "Terminé\n";
exit;

// ---------------------------------------------------------------------------------
function exécuterCommandes(PDO $connexion, string $SQLFileName, bool $suivi = FALSE, bool $arrêt = TRUE): array {
// gebruik de verbinding $connexion
// voert de opdrachten SQL uit die in het tekstbestand SQLFileName staan
// dit bestand is een opdrachtenbestand SQL waarin de opdrachten per regel worden uitgevoerd
// als $suivi=1, dan wordt bij elke uitvoering van een SQL-opdracht een melding weergegeven die aangeeft of de opdracht is geslaagd of mislukt
// als $arrêt=1, stopt de functie bij de eerste fout die wordt aangetroffen; anders voert ze alle SQL-opdrachten uit
// de functie retourneert een array (aantal fouten, fout1, fout2…)
// er wordt gecontroleerd of het bestand SQLFileName aanwezig is

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

  // uitvoering van de query's SQL die zijn opgenomen in SQLFileName
  // deze worden in een array geplaatst
  $requêtes = file($SQLFileName);
  // fout?
  if ($requêtes === FALSE) {
    return ["Erreur lors de l'exploitation du fichier SQL [$SQLFileName]"];
  }
  // de query's worden één voor één uitgevoerd – in eerste instantie geen fouten
  $erreurs = [];
  $i = 0;
  $fini = FALSE;
  while ($i < count($requêtes) && !$fini) {
    // we halen de tekst van de query op
    // trim verwijdert het einde-van-regel-teken
    $requête = trim($requêtes[$i]);
    // lege query?
    if (strlen($requête) == 0) {
      // de query wordt genegeerd en we gaan door naar de volgende query
      $i++;
      continue;
    }
    try {
      // het verzoek wordt uitgevoerd – er kan een uitzondering worden gegenereerd
      $connexion->exec($requête);
      // schermvolging of niet?
      if ($suivi) {
        print "$requête : Exécution réussie\n";
      }
    } catch (PDOException $ex) {
      // er is een fout opgetreden
      addError($erreurs, $requête, $ex->getMessage(), $suivi);
      // Stoppen we?
      $fini = $arrêt;
    }
    // volgende query
    $i++;
  }
  // resultaat
  return $erreurs;
}

function addError(array &$erreurs, string $requête, string $msg, bool $suivi): void {
  // er wordt een foutmelding toegevoegd
  $msg = "$requête : Erreur (" . $msg . ")";
  $erreurs[] = $msg;
  // scherm volgen of niet?
  if ($suivi) {
    print "$msg\n";
  }
}

Opmerkingen

De functie [exécuterCommandes] (regels 36-89) is verantwoordelijk voor het uitvoeren van de opdrachten SQL die zij aantreft in het tekstbestand [$SQLFileName] (parameter 2). Om deze uit te voeren, maakt de functie gebruik van de geopende verbinding [$connexion] (parameter 1) met de server MySQL. De derde parameter [$suivi] is een booleaanse waarde die de schermweergave regelt: bij TRUE wordt de uitgevoerde opdracht SQL op het scherm weergegeven met de melding of deze is geslaagd of mislukt; anders verloopt de uitvoering van de opdracht SQL stil. De vierde parameter [$arrêt] bepaalt wat er moet gebeuren wanneer een opdracht SQL mislukt: bij TRUE geeft deze aan dat de uitvoering van de opdrachten SQL moet worden gestopt; anders gaat deze door. De functie [exécuterCommandes] retourneert een array met foutmeldingen, die leeg is als er geen fouten zijn opgetreden;
regels 11-18: de verbinding met de database wordt geopend via MySQL [dbpersonnes]. Als het openen mislukt, wordt er een foutmelding weergegeven en wordt het programma gestopt (regels 14-18);
regel 22: er wordt dus een geopende verbinding doorgegeven aan de functie [exécuterCommandes]. Deze wordt gesloten wanneer de functie terugkeert (regel 24);
regel 20: voordat de verbinding wordt doorgegeven aan de functie [exécuterCommandes], wordt deze geconfigureerd. In geval van een fout kunnen de bewerkingen SQL met een object [PDO] ofwel de booleaanse waarde FALSE (standaardwaarde) retourneren, ofwel een uitzondering genereren. Regel 20 kiest voor dit tweede geval. Het is namelijk gemakkelijk om te ‘vergeten’ het booleaanse resultaat van de uitvoering van een opdracht SQL te controleren. Dit zal later, maar elders in de code, een fout veroorzaken, waardoor het moeilijker wordt om de oorspronkelijke oorzaak ervan te achterhalen. In het geval van een niet-afgehandelde uitzondering (ontbreken van catch) zal de uitzondering door de code omhoog worden doorgegeven totdat een catch wordt aangetroffen of totdat deze de PHP-interpreter bereikt, die de uitzondering zal onderscheppen. In dit geval worden de aard van de uitzondering en de plaats van oorsprong in de code weergegeven;
regel 22: de functie [exécuterCommandes] wordt aangeroepen om het opdrachtbestand SQL [$SQLFileName] uit te voeren;
regels 45-47: er wordt gecontroleerd of het opdrachtbestand SQL daadwerkelijk bestaat. Als dat niet het geval is, wordt de fout geregistreerd en wordt dit resultaat teruggegeven;
regel 51: de opdrachten SQL worden in een array [$requêtes] geplaatst. Regels 53-55: als de bewerking mislukt, wordt een foutarray met één enkele foutmelding geretourneerd;
regel 57: de fouten worden verzameld in de array [$erreurs];
regel 58: nummer van de aanvraag;
regel 59: de booleaanse waarde [$fini] controleert de uitvoering van de opdrachten SQL uit de tabel [$requêtes]. Wanneer de waarde overgaat naar TRUE, wordt de uitvoering gestopt;
regel 60: er wordt een lus doorlopen over alle verzoeken;
regel 63: de tekst van opdracht SQL nr. i wordt opgehaald. De functie [trim] verwijdert de spaties voor en na de tekst van opdracht SQL. Met ‘spaties’ worden bedoeld: het witruimte-teken \b, de carriage return \r, het einde-van-regel-teken \n, de pagina-einde-teken \f, de tab-teken \t… Wat hier van belang is, is dat het einde-van-regel-teken van de tekst SQL verdwijnt;
regels 65-69: als de tekst SQL leeg is, wordt het verzoek genegeerd en gaan we door naar het volgende;
regel 72: de opdracht SQL wordt naar de server MySQL verzonden. De methode [PDO::exec] zal een uitzondering genereren als de uitvoering mislukt. We herinneren eraan dat dit gedrag het gevolg is van de configuratie op regel 20;
regel 79: het foutbericht wordt toegevoegd aan de foutentabel;
regel 81: de booleaanse variabele [$fini], die de lus regelt, wordt ingesteld. Als de parameter [$arrêt] (regel 36) de waarde TRUE heeft, moet de lus worden gestopt;
regels 74-76: als de uitvoering van de opdracht SQL is geslaagd, wordt deze op het scherm weergegeven als de parameter [$suivi] (regel 36) de waarde TRUE heeft;
regel 87: zodra alle opdrachten SQL zijn uitgevoerd, wordt de foutentabel [$erreurs] teruggegeven;

Met de functie [adError] in de regels 90-97 kan een fout worden toegevoegd aan de foutentabel [$erreurs]:

regel 90: de functie ontvangt 4 parameters:
- de parameter [$erreurs] wordt doorverwijzing doorgegeven. We willen namelijk de wijzigingen doorvoeren in de array die als parameter is doorgegeven, en niet in een kopie daarvan;
- de parameter [$requête] is de tekst SQL van de mislukte opdracht;
- de parameter [$msg] is de foutmelding die hoort bij de mislukte opdracht;
- de booleaanse waarde [$suivi] geeft aan of de foutmelding moet worden weergegeven ($suivi=TRUE) of niet ($suivi=FALSE) op de console moet worden weergegeven;

De functie [exécuterCommandes] wordt aangeroepen door het script in de regels 3-33:

regels 11-18: er wordt verbinding gemaakt met de database MySQL [dbpersonnes];
regel 20: de verbinding wordt geconfigureerd;
regel 22: het opdrachtenbestand SQL wordt vervolgens uitgevoerd;
regel 24: de verbinding wordt verbroken;
regels 26-29: de fouten die door de functie [exécuterCommandes] worden geretourneerd, worden weergegeven;

De schermresultaten:


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é

De aangebrachte invoegingen zijn zichtbaar met phpMyAdmin:

12.5. Uitvoering van willekeurige SQL-opdrachten

Het volgende script toont de uitvoering van de opdrachten SQL uit het volgende tekstbestand [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'

Onder deze SQL-opdrachten bevindt zich de select-opdracht die resultaten uit de database ophaalt, de insert-, update- en delete-opdrachten die de database wijzigen zonder resultaten op te halen, en ten slotte foutieve opdrachten zoals de laatste (xselect). Het script [mysql-04.php] is als volgt:


<?php

// database-identificatie
const DSN = "mysql:host=localhost;dbname=dbpersonnes";
// gebruikersgegevens
const ID = "admpersonnes";
const PWD = "nobody";
// identificatie van het tekstbestand met de uit te voeren SQL-opdrachten
const SQL_COMMANDS_FILENAME = "sql.txt";

try {
  // verbinding met de database MySql
  $connexion = new PDO(DSN, ID, PWD);
} catch (PDOException $ex) {
  // foutweergave
  print "Erreur : " . $ex->getMessage() . "\n";
  exit;
}
// bij elke fout in SGBD moet een uitzondering worden gegenereerd
$connexion->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
// uitvoering van het opdrachtbestand SQL
$erreurs = exécuterCommandes($connexion, SQL_COMMANDS_FILENAME, TRUE, FALSE);
// verbinding afsluiten
$connexion = NULL;
// aantal fouten weergeven
printf("\n-----------------------\nIl y a eu %d erreur(s)\n", count($erreurs));
for ($i = 0; $i < count($erreurs); $i++) {
  print "$erreurs[$i]\n";
}

// het is klaar
print "Terminé\n";
exit;

// ---------------------------------------------------------------------------------
function exécuterCommandes(PDO $connexion, string $SQLFileName, bool $suivi = FALSE, bool $arrêt = TRUE): array {
………………………………………………………….
  // de query's worden één voor één uitgevoerd – in eerste instantie geen fouten
  $erreurs = [];
  $i = 0;
  $fini = FALSE;
  while ($i < count($requêtes) && !$fini) {
    // de tekst van de aanvraag wordt opgehaald
    // trim verwijdert het einde-van-regel-teken
    $requête = trim($requêtes[$i]);
    // lege verzoek?
    if (strlen($requête) == 0) {
      // de query wordt genegeerd en we gaan door naar de volgende query
      $i++;
      continue;
    }
    // het verzoek wordt uitgevoerd
    // de naam wordt opgehaald
    $commande = "";
    if (preg_match("/^\s*(\S+)/", $requête, $champs)) {
      $commande = strtolower($champs[0]);
    }
    try {
      // is dit een opdracht SELECT?
      if ($commande === "select") {
        $résultat = $connexion->query($requête);
      } else {
        $résultat = $connexion->exec($requête);
      }
      // schermvolging of niet?
      if ($suivi) {
        print "[$requête] : Exécution réussie\n";
      }
      // het resultaat van de uitvoering weergeven
      afficherInfos($commande, $résultat);
    } catch (PDOException $ex) {
      // er is een fout opgetreden
      addError($erreurs, $requête, $ex->getMessage(), $suivi);
      // stoppen we?
      $fini = $arrêt;
    }
    // volgende aanvraag
    $i++;
  }
  // resultaat
  return $erreurs;
}

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

// ---------------------------------------------------------------------------------
function afficherInfos(string $commande, $résultat): void {
  // toont het resultaat $résultat van een SQL-query
  // Was dit een SELECT-query?
  switch ($commande) {
    case "select" :
      // de veldnamen worden weergegeven
      $titre = "";
      $nbColonnes = $résultat->columnCount();
      for ($i = 0; $i < $nbColonnes; $i++) {
        $infos = $résultat->getColumnMeta($i);
        $titre .= $infos['name'] . ",";
      }
      // het laatste teken wordt verwijderd,
      $titre = substr($titre, 0, strlen($titre) - 1);
      // de lijst met velden wordt weergegeven
      print "$titre\n";
      // scheidingslijn
      $séparateurs = "";
      for ($i = 0; $i < strlen($titre); $i++) {
        $séparateurs .= "-";
      }
      print "$séparateurs\n";
      // gegevens
      foreach ($résultat as $ligne) {
        $data = "";
        for ($i = 0; $i < $nbColonnes; $i++) {
          $data .= $ligne[$i] . ",";
        }
        // het laatste teken wordt verwijderd,
        $data = substr($data, 0, strlen($data) - 1);
        // wordt weergegeven
        print "$data\n";
      }
      break;
    case "update":
    case "insert":
    case "delete";
      print " $résultat lignes(s) a (ont) été modifiée(s)\n";
      break;
  }
}

Opmerkingen

regels 36-83: de functie [exécuterCommandes] is licht gewijzigd: de opdracht SQL [select] wordt niet op dezelfde manier uitgevoerd als de andere opdrachten SQL. Dit commando is het enige dat een tabel als resultaat oplevert, d.w.z. een verzameling rijen en kolommen uit de database;
regels 55-57: het eerste woord van de opdracht SQL wordt geïsoleerd met behulp van een reguliere expressie;
regels 60-64: als het commando SQL gelijk is aan [select], wordt de methode [PDO::query] gebruikt; anders wordt de methode [PDO::exec] gebruikt om het commando SQL uit te voeren. In beide gevallen wordt, als de uitvoering mislukt, een uitzondering gegenereerd en opgevangen in de regels 71-77. Als de uitvoering slaagt, wordt het resultaat weergegeven in regel 70;
regels 90-130: de functie afficherInfos geeft informatie weer over het resultaat van de uitvoering van een opdracht SQL;
regel 94: hier wordt het geval van [select] behandeld. Het resultaat hiervan is een object van het type [PDOStatement];
regel 96: de methode [PDOStatement::getColumnCount()] retourneert het aantal kolommen van de resultatentabel van select;
regels 98-99: de methode [PDOStatement::getMeta(i)] retourneert een woordenboek met informatie over kolom nr. i van de resultatentabel van select.. In dit woordenboek is de waarde die bij de sleutel 'name' hoort de naam van de kolom;
regels 97-102: de kolomnamen van de resultatentabel van select worden samengevoegd tot een tekenreeks;
regels 105-110: er wordt een scheidingsregel geconstrueerd met dezelfde lengte als de eerder geconstrueerde tekenreeks;
regels 112-121: een object van het type PDOStatement kan worden doorlopen met een foreach-lus. Bij elke iteratie is het verkregen element een rij uit de resultatentabel van de select in de vorm van een array met waarden die de waarden van de verschillende kolommen van de rij vertegenwoordigen. Al deze waarden worden weergegeven met een for-lus (regels 114-116);
regels 123-127: het resultaat van de uitvoering van een opdracht insert, update, delete is het aantal regels dat door de opdracht is gewijzigd;

De schermresultaten:


[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. Gebruik van voorbereide opdrachten SQL

12.6.1. Voorbeeld 1

Laten we het volgende script [mysql-05.php] eens bekijken:


<?php

// database-ID
const DSN = "mysql:host=localhost;dbname=dbpersonnes";
// gebruikersgegevens
const ID = "admpersonnes";
const PWD = "nobody";

try {
  // verbinding met de database MySql
  $connexion = new PDO(DSN, ID, PWD);
  // we willen dat bij elke fout in SGBD een uitzondering wordt gegenereerd
  $connexion->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
  // de tabel met personen wordt leeggemaakt
  $connexion->exec("delete from personnes");
  // een lijst met personen
  $personnes = [];
  $personnes[] = ["nom" => "Langevin", "prenom" => "Paul", "age" => 47];
  $personnes[] = ["nom" => "Lefur", "prenom" => "Sylvie", "age" => 28];
  // we gaan deze personen in de database opslaan
  $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) {
  // foutmelding weergeven
  print "Erreur : " . $ex->getMessage() . "\n";
} finally {
// verbinding verbreken
  $connexion = NULL;
}

// het is klaar
print "Terminé\n";
exit;

Opmerkingen

We richten ons hier op de regels 16-24, die twee personen toevoegen aan de personenlijst van de database [dbpersonnes].

regel 21: er wordt een geconfigureerde opdracht SQL ‘voorbereid’. De parameters worden voorafgegaan door het teken : :nom, :prenom, :age. Om een opdracht SQL ‘voor te bereiden’, wordt de methode [PDO::prepare] gebruikt. Het resultaat is een type [PDOStatement]. De ‘voorbereiding’ is geen uitvoering: er wordt niets uitgevoerd;
regel 23: uitvoering van de ‘voorbereide’ opdracht met de methode [PDOStatement::execute]. Hiervoor moeten waarden worden opgegeven voor de parameters :nom, :prenom en :age. Er zijn verschillende manieren om dit te doen. Hier gebruiken we een woordenboek met als sleutels de parameters van de voorbereide opdracht, die we doorgeven aan de methode [PDOStatement::execute]. Een andere manier is om de parameters een waarde te geven met de methode [PDOStatement::bindValue($paramètre,$valeur)]. Bijvoorbeeld hier:


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

Het nadeel is dat je deze instructie voor elke parameter moet herhalen. De woordenboekmethode kan dan handiger zijn. De methode [PDOStatement::execute] levert FALSE op als de uitvoering mislukt;

de methode die hier wordt gebruikt om de invoegingen te doen:
- een voorbereide methode;
- n uitvoeringen van de voorbereide opdracht;

is tijdbesparender dan het uitvoeren van n verschillende opdrachten SQL. Deze methode verdient daarom de voorkeur. Ze is bruikbaar voor de opdrachten SQL select, update, delete en insert. In het geval van de opdracht SQL select worden, na uitvoering met [PDOStatement::execute], de rijen van het resultaat opgehaald met de methode [PDOStatement::fetchAll];

12.6.2. Voorbeeld 2

Het volgende script [mysql-06.php] toont het gebruik van een voorbereide opdracht voor de bewerking SQL select, evenals verschillende manieren om de door deze bewerking geretourneerde rijen op te halen:


<?php

// database-identificatie
const DSN = "mysql:host=localhost;dbname=dbpersonnes";
// gebruikersgegevens
const ID = "admpersonnes";
const PWD = "nobody";

try {
  // verbinding met de database MySql
  $connexion = new PDO(DSN, ID, PWD);
  // we willen dat bij elke fout in SGBD een uitzondering wordt gegenereerd
  $connexion->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
  // de tabel met personen wordt leeggemaakt
  $connexion->exec("delete from personnes");
  // we gaan deze personen in de database opslaan
  $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]);
  }
  // we voeren een query uit op de database
  $statement = $connexion->prepare("select nom, prenom, age from personnes");
  $statement->execute();
  // 1e regel
  $ligne = $statement->fetch();
  var_dump($ligne);
  // 2e regel
  $ligne = $statement->fetch(PDO::FETCH_ASSOC);
  var_dump($ligne);
  // 3e regel
  $ligne = $statement->fetch(PDO::FETCH_OBJ);
  var_dump($ligne);
  // 4e regel
  $statement->setFetchMode(PDO::FETCH_CLASS, "Person");
  $ligne = $statement->fetch();
  var_dump($ligne);
  // alle regels achtereenvolgens uitlezen
  $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) {
  // foutmelding
  print "Erreur : " . $ex->getMessage() . "\n";
} finally {
// verbinding verbreken
  $connexion = NULL;
}

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

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

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

}

Opmerkingen

regels 17-20: er worden 10 regels ingevoegd in de tabel [personnes] van de database [admpersonnes]:

regel 22: er wordt een opdracht SQL [select] ‘voorbereid’, die op regel 23 wordt uitgevoerd;
regel 25: met de methode [PDOStatement::fetch] wordt een regel opgehaald uit het resultaat van de uitgevoerde bewerking SQL [select]. De methode [PDOStatement::fetch] kan op verschillende manieren de resultaatregels ophalen van een voorbereide bewerking SQL [select]. Het script toont er enkele. De methode [PDOStatement::fetch] zonder parameters retourneert de huidige regel van de [select] in de vorm van een woordenboek dat zowel op kolomnummers als op kolomnamen is geïndexeerd;
regel 26: geeft het volgende resultaat weer:


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"
}

regels 28-29: de parameter [PDO::FETCH_ASSOC] zorgt ervoor dat de weergegeven regel een woordenboek is dat is geïndexeerd op de kolomnamen van de tabel:

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

regels 31-32: de parameter [PDO::FETCH_OBJ] zorgt ervoor dat de geretourneerde regel een object is van het type [stdclass], waarvan de attributen de kolomnamen van de tabel zijn:

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

regel 34: de zoekmodus van de methode [fetch] wordt ingesteld met de methode [PDOStatement::setFetchMode]. Deze modus wordt dan de standaardmodus, zolang deze niet wordt gewijzigd, hetzij door een andere bewerking [PDOStatement::setFetchMode], hetzij door een modus als parameter door te geven aan de methode [PDOStatement::fetch], zoals eerder is gedaan. De bewerking [setFetchMode(PDO::FETCH_CLASS, "Person")] geeft aan dat de gelezen regel in een object van het type [Person] moet worden geplaatst. Deze klasse moet onder haar attributen attributen hebben die de namen van de kolommen van de gelezen regel dragen. Dit is het geval bij de klasse [Person] die in de regels 56-63 is gedefinieerd;
regel 36 geeft het volgende resultaat weer:

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

regels 38-43: laten zien hoe de resultaten van [select] achtereenvolgens kunnen worden verwerkt;
regel 42: bij de weergave van [$personne] wordt gebruikgemaakt van de methode [__toString] van de klasse [Person];

12.7. Gebruik van transacties

Met een transactie kan een reeks opdrachten SQL worden gebundeld tot één uitvoeringseenheid: ofwel slagen alle opdrachten, ofwel mislukt er één, en dan worden alle opdrachten SQL die daaraan voorafgingen, geannuleerd. Met andere woorden: wanneer een transactie wordt gebruikt om opdrachten SQL uit te voeren, bevindt de database zich na de uitvoering ervan in een stabiele toestand:

ofwel in een nieuwe toestand die is ontstaan door de succesvolle uitvoering van alle SQL-opdrachten van de transactie;
ofwel in de toestand waarin deze zich bevond voordat de transactie werd uitgevoerd;

We nemen het voorbeeld van de uitvoering van de opdrachten SQL uit een tekstbestand dat in de paragraaf ‘link’ is besproken. We zullen deze uitvoering in een transactie opnemen. De opdrachten SQL zullen in het volgende bestand [sql2.txt] staan:


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'

Door de foutieve opdracht op regel 12 zal de hele transactie mislukken. De database zou dus weer in de toestand moeten verkeren zoals deze was vóór de transactie. In het bovenstaande voorbeeld zou de rij die door regel 10 hierboven is ingevoegd, dus niet in de tabel te zien moeten zijn. Het script verandert nauwelijks. We geven echter de volledige code van [mysql-07.php] nogmaals weer:


<?php

// database-identificatie
const DSN = "mysql:host=localhost;dbname=dbpersonnes";
// gebruikersgegevens
const ID = "admpersonnes";
const PWD = "nobody";
// identificatie van het uit te voeren SQL-opdrachttekstbestand
const SQL_COMMANDS_FILENAME = "sql2.txt";

try {
  // verbinding met de database MySql
  $connexion = new PDO(DSN, ID, PWD);
} catch (PDOException $ex) {
  // foutweergave
  print "Erreur : " . $ex->getMessage() . "\n";
  exit;
}
// we willen dat bij elke fout in SGBD een uitzondering wordt gegenereerd
$connexion->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
// uitvoering van het opdrachtbestand SQL
$erreurs = exécuterCommandes($connexion, SQL_COMMANDS_FILENAME, TRUE);
// verbinding afsluiten
$connexion = NULL;
// aantal fouten weergeven
printf("\n-----------------------\nIl y a eu %d erreur(s)\n", count($erreurs));
for ($i = 0; $i < count($erreurs); $i++) {
  print "$erreurs[$i]\n";
}

// het is klaar
print "Terminé\n";
exit;

// ---------------------------------------------------------------------------------
function exécuterCommandes(PDO $connexion, string $SQLFileName, bool $suivi = FALSE): array {
// gebruik de verbinding $connexion
// voert de opdrachten SQL uit die in het tekstbestand SQLFileName staan
// dit bestand is een opdrachtenbestand SQL waarin de opdrachten per regel worden uitgevoerd
// de SQL-opdrachten worden uitgevoerd in een transactie
// als een van de opdrachten mislukt, wordt de transactie geannuleerd en keert de database terug naar de toestand van vóór de transactie
// als $suivi=1, dan wordt bij elke uitvoering van een opdracht SQL een melding weergegeven waarin wordt aangegeven of deze is geslaagd of mislukt
// de functie retourneert een array (aantal fouten, fout1, fout2…)
//
// er wordt gecontroleerd of het bestand SQLFileName aanwezig is
  if (!file_exists($SQLFileName)) {
    return ["Le fichier [$SQLFileName] n'existe pas"];
  }
  // de query's SQL die in SQLFileName zijn opgenomen, worden uitgevoerd
  // deze worden in een array geplaatst
  $requêtes = file($SQLFileName);
  // fout?
  if ($requêtes === FALSE) {
    return ["Erreur lors de l'exploitation du fichier SQL [$SQLFileName]"];
  }
  // de query's worden in een transactie geplaatst
  $connexion->beginTransaction();
  // de query's worden één voor één uitgevoerd – in eerste instantie geen fouten
  $erreurs = [];
  $i = 0;
  $fini = FALSE;
  while ($i < count($requêtes) && !$fini) {
    // we halen de tekst van de query op
    // trim verwijdert het einde-van-regel-teken
    $requête = trim($requêtes[$i]);
    // Lege query?
    if (strlen($requête) == 0) {
      // de query wordt genegeerd en we gaan door naar de volgende query
      $i++;
      continue;
    }
    // het verzoek wordt uitgevoerd
    // de naam wordt opgehaald
    $commande = "";
    if (preg_match("/^\s*(\S+)/", $requête, $champs)) {
      $commande = strtolower($champs[0]);
    }
    try {
      // is dit een opdracht SELECT?
      if ($commande === "select") {
        $résultat = $connexion->query($requête);
      } else {
        $résultat = $connexion->exec($requête);
      }
      // schermvolging of niet?
      if ($suivi) {
        print "[$requête] : Exécution réussie\n";
      }
      // het resultaat van de uitvoering weergeven
      afficherInfos($commande, $résultat);
    } catch (PDOException $ex) {
      // er is een fout opgetreden
      addError($erreurs, $requête, $ex->getMessage(), $suivi);
      // we stoppen bij de volgende beurt
      $fini = TRUE;
    }
    // volgende aanvraag
    $i++;
  }
  // einde van de transactie
  if (!$fini) {
    // er zijn geen fouten opgetreden: de transactie wordt gevalideerd
    $connexion->commit();
  } else {
    // er zijn fouten opgetreden: de transactie wordt geannuleerd
    $connexion->rollBack();
    // fout toegevoegd
    addError($erreurs, "", "Transaction annulée", $suivi);
  }
  // resultaat
  return $erreurs;
}

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

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

Opmerkingen

We hebben de wijzigingen ten opzichte van het oorspronkelijke script [mysql-04.php] gemarkeerd.

regels 22, 36: de functie [exécuterCommandes] heeft haar vierde parameter [$arrêt=TRUE] verloren. Aangezien de opdrachten SQL binnen een transactie worden uitgevoerd, zal elke fout ertoe leiden dat de transactie wordt afgebroken;
regels 40-41: de transactiefunctie wordt opnieuw aangeroepen;
regel 57: er wordt een transactie gestart. Vanaf nu wordt elke opdracht SQL die in de lus van de regels 62-99 wordt uitgevoerd, binnen deze transactie uitgevoerd;
regels 101-109: de booleaanse waarde [$fini] is gelijk aan TRUE als er een fout is opgetreden (regel 95). Als deze gelijk is aan FALSE, zijn er geen fouten opgetreden en wordt de transactie vervolgens gevalideerd (regel 103). Wanneer deze op TRUE staat, zijn er fouten opgetreden en wordt de transactie geannuleerd (regel 106) en wordt de transactiefout toegevoegd aan de foutenlijst (regel 108);

Resultaten

Vóór de uitvoering van het script verkeert de database [admpersonnes] in de volgende toestand:

Het script [mysql-07.php] wordt uitgevoerd. De schermweergaven zijn dan als volgt:


[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é

regel 53: er treedt een fout op bij de opdracht [xselect];
regel 54: de transactie wordt vervolgens geannuleerd;

Als we de status van de database controleren, zien we dat deze in dezelfde toestand verkeert als vóór de uitvoering van het script. Met name de regel [Josette, Bruneau, 46] uit regel 52 van de bovenstaande resultaten is er niet te zien.

Samenvatting

een transactie begint met de methode [PDO::beginTransaction];
de transactie wordt succesvol beëindigd met de methode [PDO::commit];
de transactie wordt bij een mislukking beëindigd met de methode [PDO::rollback];

Bij het werken met een database is het een goede gewoonte om elke bewerking met de methode SQL in een transactie te plaatsen om deze af te schermen van andere gebruikers van de database (de transactie heeft ook deze functie). Een transactie moet zo kort mogelijk zijn. Vergeet dus niet om deze af te sluiten met een [commit] of een [rollback], afhankelijk van de situatie.