Database

• Database
  • Queries maken als gebruiker met een database
    • PDO en RDBMS’en
    • Gegevens tonen met één PHP-bestand
      • Startpunt
      • Ophalen van de data
      • Renderen van de data
      • Weergeven van de data
      • Volledige code
    • Gegevens tonen in drie-lagenstructuur
      • Startpunt
      • Algemene structuur
      • Ophalen van de data
      • Renderen van de data
      • Weergeven van de code
      • Grote applicatie opdelen
    • Speciale tekens uit de database verwerken
  • Verbinding maken met Microsoft SQL management studio
  • Errors
    • Foutieve databasenaam
    • Foutieve username of foutief wachtwoord
    • Fout in de query

In dit hoofdstuk gaan we een database ontsluiten in PHP. Als het goed is weet je hoe je een database kan gebruiken en wat het is.

Queries maken als gebruiker met een database

❗️ Let er op je onderstaande niet kunt doen als je de WTIS-omgeving (Docker) niet hebt geïnstalleerd. De voorbeelden gegeven staan ook in die omgeving, zodat je ze werkend kunt zien.

Om een website zoals de onze bruikbaar te maken moeten gebruikers bijvoorbeeld kunnen zoeken of een query kunnen doen. Met een query doen bedoelen we niet dat gebruikers zelf een SQL-query op de database kunnen afvuren, maar ze moeten wel data kunnen opvragen waar ze naar zoeken.

Bijvoorbeeld als een gebruiker op een site als IMDb naar ‘Bourne’ moeten ze ook ‘Bourne Identity’ of zoiets binnenkrijgen.

Dat betekent dat je jouw website moet gaan koppelen met de database.

Er zijn verschillende Relational Database Management Systems (RDBMS’en). Bekende spelers zijn Microsoft SQL Server, Oracle Database, PostgreSQL en MySQL (zie ook de Stack Overflow survey in 2020). Omdat wij bij de course DBSQL gebruik maken van SQL Server gebruiken we deze ook hier.

In de video’s zijn de voorbeelden met de RDBMS MySQL, hoewel dit een productieklare database is (ook wel gebruikt door Spotify, Booking.com etc.) gebruiken wij Microsoft SQL Server omdat we dit toch al gebruiken in DBSQL en ook gebruikt wordt door veel grote bedrijven (Rolls Royce, Stack Overflow).

PDO en RDBMS’en

PDO (PHP Data Objects) is een PHP library (uitbreiding) dat ervoor zorgt dat de databasefuncties voor alle RDBMS’en hetzelfde zijn en werken. De systemen hebben wel eigen drivers nodig (software die de communicatie met apparaten en software regelt). Ook de connection string, een soort URL om verbinding te leggen met een database, verschilt. Als de verbinding er is, zijn de opdrachten voor alle RDBMS’en hetzelfde. Het is mogelijk een applicatie aan een ander databasesysteem te koppelen zonder al te veel in de PHP-broncode te veranderen.

Als de database met haar tabellen er is, kunnen we vanuit PHP een verbinding leggen.

$verbinding = new PDO('sqlsrv:Server=database_server;Database=muziekschool;ConnectionPooling=0',
  'mijnGebruiker',
  'ditIsOnveiligWachtwoord");

Hier komt een heleboel bij kijken. PDO is een class en dat heeft te maken met object-geörienteerd programmeren. Dat betekent dat we een speciale syntaxis gebruiken.

• Met new PDO() wordt een nieuw verbindingsobject aangemaakt. Op dit object kunnen dan verschillende acties worden uitgevoerd.
• Door de connectiestring te starten met sqlsrv geven we aan wat voor databaseprotocol we gaan gebruiken (SQL Server van Microsoft).
• Bij Server=..... noemen we de databaseserverlocatie. Als de database op dezelfde computer draait, kan dit ook met localhost of (local) worden aangeduid. In dit voorbeeld wordt een server op naam aangeroepen. Bij ons draaien zowel de PHP webserver als de database in hun eigen Docker container en heet de DB service database_server, dus gebruik database_server.
• Bij Database=..... hoort de naam van de database waar we mee willen werken. In de voorbeelden van deze reader is dat de muziekschool-database (dus Database=muziekschool)
• Daarna komen, gescheiden door komma’s, de user en het wachtwoord. Bij een simpele installatie van SQL Server heb je de user sa, met een wachtwoord dat je zelf hebt ingesteld. Dit is alleen erg onveilig. Op een productiesite bij een provider is dit vaak een ander verhaal, omdat er dan vaak credentials worden gegenereerd.

Iets netter is het definieren met variabelen (code staat in bestand db_connectie.php in de WTIS-omgeving):

<?php
// defined in file 'variables.env'
$db_user    = 'sa';          // db user
$db_password = 'abc123!@#';  // password db user

$db_host = 'database_server'; // de database server
$db_name = 'muziekschool';    // naam van database

// Het 'ssl certificate' wordt altijd geaccepteerd (niet overnemen op productie, daar altijd "TrustServerCertificate=0"!!!)
$verbinding = new PDO("sqlsrv:Server=$db_host;Database=$db_name;ConnectionPooling=0;TrustServerCertificate=1",
  $db_user,
  $db_password);

Nou zul je in een echte ontwikkelomgeving gebruik maken van omgevingsvariabelen of externe koppelingen voor de database.

In de [ontwikkelomgeving](https://github.com/hanaim-webtech/wt-is-beroepsproduct-env/) die wij gebruiken ziet de verbinding er uit zoals in de file [applicatie/db_connectie.php](https://github.com/hanaim-webtech/wt-is-beroepsproduct-env/blob/master/applicatie/db_connectie.php):

```php
<?php

// defined in 'variables.env'
$db_host = 'database_server'; // de database server
$db_name = 'muziekschool';    // naam van database

// defined in sql-script 'movies.sql'
$db_user    = 'sa';          // db user
$db_password = 'abc123!@#';  // wachtwoord db user

// Het 'ssl certificate' wordt altijd geaccepteerd (niet overnemen op productie, verder dan altijd "TrustServerCertificate=1"!!!)
$verbinding = new PDO('sqlsrv:Server=' . $db_host . ';Database=' . $db_name . ';ConnectionPooling=0;TrustServerCertificate=1', $db_user, $db_password);

// Bewaar het wachtwoord niet langer onnodig in het geheugen van PHP.
unset($db_password);

// Zorg ervoor dat eventuele fouttoestanden ook echt als fouten (exceptions) gesignaleerd worden door PHP.
$verbinding->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

// Functie om in andere files toegang te krijgen tot de verbinding.
function maakVerbinding() {
  global $verbinding;
  return $verbinding;
}
?>

Bovenstaande code is zoals wij het in onze WTIS-omgeving gaan gebruiken. Zoals je ziet wordt het SA account gebruikt, een onveilig wachtwoord én de connectie is onbeveiligd (TrustServerCertificate=1). Voor ons is dat niet erg, wij werken lokaal op onze machine waar de firewall het verkeer van buitenaf blokkeert. Ga je een dergelijke omgeving voor een echte webomgeving opzetten (productie), zul je deze connectie-string en server-omgeving veiliger moeten maken. Studenten die het ISM-profiel gaan volgen zullen hier veel over leren in de hoofdfase.

Gegevens tonen met één PHP-bestand

Over de code hierboven:

• $verbinding is een PDO-object.
• Dit object heeft functies (methoden) om databasehandelingen uit te voeren.
• Een voorbeeld is $verbinding->query($sqlquery) waarmee je een query op de database uitvoert.
• De cryptische regel $verbinding->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); zorgt ervoor dat niet alleen PHP-fouten maar ook query- of databasefouten worden getoond.

De code staat opgeslagen in het bestand db_connectie.php en dat bestand gaan we vervolgens op verschillende plekken gebruiken met behulp van require_once.

In een ander bestand, componist-aantalstukken.php, gaan we nu de query uitvoeren op de database.

Startpunt

Gebruik het volgende bestand als startpunt.

<?php
require_once 'db_connectie.php';

// 1. Ophalen van de data
// ..... TODO .....

// 2. Renderen van de data
// ..... TODO .....

?>
<!DOCTYPE html>
<html lang="nl">
<head>
  <meta charset="UTF-8">
  <meta http-equiv="X-UA-Compatible" content="IE=edge">
  <meta name="viewport" content="width=device-width, initial-scale=1.0">
  <title>Componisten stukken</title>
</head>
<body>
  <h1>Componisten met aantal geschreven stukken</h1>
  <!-- 3. Weergeven van de data -->
  <!--      .... TODO .....     -->
</body>
</html>

Zoals je ziet worden er drie dingen van elkaar gescheiden:

1. het ophalen van de data (uit de database),
2. het renderen van de data (het mooi weergeven in html)
3. het weergeven van de data (op de pagina zelf)

In eerste instantie gaan we deze scheiding van ophalen, renderen en weergeven in één bestand aanbrengen. Later volgt een korte uitleg hoe je dit verder kunt opdelen naar bestanden, zodat je slimme functies op meerdere pagina's kunt gebruiken.

In dit voorbeeld maken we een overzicht van alle componisten en aantal stukken dat die componist geschreven heeft. Dergelijke queries heb je leren maken in het vak Databases, de kennis uit dat vak gaan we hier nu toepassen.

Ophalen van de data

Als eerste stap wordt de verbinding met de database gelegd en de data voor de pagina opgevraagd. Die eerste stap ziet er zo uit:

// 1. Ophalen van de data
// maak verbinding met de database (zie db_connection.php)
$db = maakVerbinding();

// haal alle componisten op en tel het aantal stukken
$query = 'select c.componistId as id, c.naam as naam, count(S.stuknr) as aantal
          from Componist C left outer join Stuk S on C.componistId = S.componistId
          group by C.componistId, C.naam
          order by naam';
// voer de query uit op de database
$data = $db->query($query);

Wat gebeurt hier:

1. De verbinding met de database wordt gemaakt met maakVerbinding() en opgeslagen in de variabele $db.
2. Dan wordt query samengesteld en opgeslagen in een variabele $query.
3. Daarna wordt de query uitgevoerd op de database met $db->query($query) en het resultaat van de query opgeslagen in $data.

Als deze code is uitgevoerd, heeft de variable $data alle records die de query oplevert. In SQL Management Studio kun je de query ook uitvoeren en zo controleren of de query juist is.

Renderen van de data

In de tweede stap wordt de data netjes in HTML uitgewerkt, het zogenaamde renderen. Die HTML kan dan weer in de pagina zelf worden opgenomen. Eerst de code, daaronder een korte beschrijving van wat er gebeurt.

// 2. Renderen van de data
// Begin van de "table"
$componisten_table = '<table>';
// De "table heads"
$componisten_table = $componisten_table . '<tr><th>Id</th><th>Naam</th><th>Aantal stukken</th></tr>';

// Elke rij als een "table row"
foreach($data as $rij) {
  $id = $rij['id'];
  $naam = $rij['naam'];
  $aantal = $rij['aantal'];

  $componisten_table = $componisten_table . "<tr><td>$id</td><td>$naam</td><td>$aantal</td></tr>";
}

// Eind van de "table"
$componisten_table = $componisten_table . "</table>";

Wat gebeurt er:

1. Eerst wordt er een variabele $componisten_table gemaakt en gevuld <table> (zodat het een tabel in HTML wordt).
2. Daarna worden de koppen van de kolommen toegevoegd. De query selecteert drie waardes (id, naam en aantal), voor elke waarde een <th>...</th>.
3. Dan wordt elke rij één voor één opgehaald met foreach($data as $rij), de variabele $rij bevat steeds een associatieve array met de waardes uit het record.
4. In de foreach-loop worden alle velden uit een record opgehaald, bijv. $naam = $rij['naam']; waarbij de waarde uit kolom 'naam' in de variabele $naam wordt gezet.
5. Aan het eind van de loop worden alle waardes in een row gezet en toegevoegd aan de variabele $componisten_table.

Weergeven van de data

In de vorige stap, renderen van de data, is de data in HTML gezet. In ons voorbeeld is de componistendata in een HTML-table gezet. Deze HTML wordt op de juiste plek in de pagina gezet. Dat kan als volgt (om het wat overzichtelijk te houden is alleen de <body> opgenomen):

<body>
  <h1>Componisten met aantal geschreven stukken</h1>
  <!-- 3. Weergeven van de data -->
  <?= $componisten_table ?>
</body>

Wat gebeurt hier:

1. De eerdere aangemaakte en gevulde variabele $componisten_table wordt hier in de HTML geplakt.
2. De syntax <?= $componisten_table ?> is gelijk aan <?php echo($componisten_table) ?>, alleen iets korter.

In dit voorbeeld wordt er maar één data-element gebruikt in de pagina (de gegevens over componisten). In complexere websites, zoals je zelf gaat maken, zullen er meerdere data-elementen zijn. Probeer zoveel mogelijk de logica (stap 1 en 2) bovenaan de pagina te doen. En waar de HTML komt te staan, zo min mogelijk code, het liefst alleen <?= $var ?> of <?php echo($var) ?>. Hanteer de vuistregel in het PHP-deel (bovenaan in het bestand) variabelen vullen, in het HTML-deel (onderste stuk) alleen variabelen uitlezen.

Volledige code

componist-aantalstukken.php

<?php
require_once 'db_connectie.php';

// 1. Ophalen van de data
// maak verbinding met de database (zie db_connection.php)
$db = maakVerbinding();

// haal alle componisten op en tel het aantal stukken
$query = 'select c.componistId as id, c.naam as naam, count(S.stuknr) as aantal
          from Componist C left outer join Stuk S on C.componistId = S.componistId
          group by C.componistId, C.naam
          order by naam';
// voer de query uit op de database
$data = $db->query($query);

// 2. Renderen van de data
// Begin van de "table"
$componisten_table = '<table>';
// De "table heads"
$componisten_table = $componisten_table . '<tr><th>Id</th><th>Naam</th><th>Aantal stukken</th></tr>';

// Elke rij als een "table row"
foreach($data as $rij) {
  $id = $rij['id'];
  $naam = $rij['naam'];
  $aantal = $rij['aantal'];
  $componisten_table = $componisten_table . "<tr><td>$id</td><td>$naam</td><td>$aantal</td></tr>";
}

// Eind van de "table"
$componisten_table = $componisten_table . "</table>";

?>
<!DOCTYPE html>
<html lang="nl">
<head>
  <meta charset="UTF-8">
  <meta http-equiv="X-UA-Compatible" content="IE=edge">
  <meta name="viewport" content="width=device-width, initial-scale=1.0">
  <title>Componisten stukken</title>
</head>
<body>
  <h1>Componisten met aantal geschreven stukken</h1>
  <!-- 3. Weergeven van de data -->
  <?= $componisten_table ?>
</body>
</html>

Dit bestand vind je ook terug in je WTIS-omgeving. In de browser ziet het er nu niet zo mooi uit, met een beetje CSS-styling kun je mooier maken.

Gegevens tonen in drie-lagenstructuur

Hoewel het erg overzichtelijk is om alle code voor het ophalen, verwerken en tonen van data in één PHP-pagina te zetten, loop je al vrij snel tegen dubbele code aan. Dezelfde data-elementen gebruik je vaak op verschillende pagina's en dan is het juist handig die stukken code op één plek te zetten. Hieronder gaan we een eenvoudig voorbeeld uitwerken naar een drie-lagenstructuur m.b.v. functies verdeeld over verschillende bestanden. Dit lijkt hier erg omslachtig, maar een dergelijke opzet biedt meer mogelijkheden tot hergebruik van elementen én maakt het ook mogelijk om eenvoudiger testdata te gebruiken.

Startpunt

Voor de drie lagen maken we drie verschillende bestanden. Dit voorbeeld is erg klein, bij grotere websites is het gebruikelijk om meer opdeling te maken (functies verdelen over meerdere bestanden).

De bestanden:

1. data_functies.php bevat functies om gegevens op te halen uit bijv. een database.
2. view_functies.php bevat functies om HTML-code te maken met de data
3. niveaus.php de uiteindelijk pagina die de complete HTML samenstelt

In de WTIS-omgeving zijn deze bestanden (de volledige uitwerking) terug te vinden. Om de bestanden overzichtelijk te houden, zijn de twee bestanden data_functies.php en view_functies.php in de folder includes geplaatst. Als je deze bestanden wil gebruiken, moet je bij include of require het pad erbij geven dus zoiets als require_once './includes/data_functies.php';.

Algemene structuur

Globaal gezien ziet de code per bestand er als volgt uit:

• data_functies.php

  <?php
  require_once 'db_connectie.php';
  
  function haalAlleNiveausOp() {
    // query uitvoeren
    // return data
  }
  ?>
  

• view_functies.php

  <?php
  function niveausNaarHtmlTable($data) {
      // Verwerk de data naar HTML
      // geef de HTML terug
  }
  ?>
  

• niveaus.php

  <?php
  // 1. Ophalen van de data
  $niveaus = haalAlleNiveausOp();
  
  // 2. Renderen van de data
  $niveausHtml = niveausNaarHtmlTable($niveaus);
  ?>
  <html
  <body>
    <!-- 3. Weergeven van de data -->
    <?= $niveausHtml ?>
  </body>
  </html>
  

Ophalen van de data

De functie haalAlleNiveausOp() uit het bestand data_functies.php maakt verbinding met de database, selecteert alle rijen en geeft dat terug. Wat eerder in stap 1 gebeurde, is nu verplaatst naar een functie.

data_functies.php

<?php
require_once 'db_connectie.php';

function haalAlleNiveausOp() {
    // maak verbinding met de database (zie db_connection.php)
    $db = maakVerbinding();

    // haal niveaus op (code en omschijving)
    $query = 'select niveaucode, omschrijving
              from Niveau';
    // voer de query uit op de database
    $data = $db->query($query);
    // Geef de data terug
    return $data;
}
?>

De code spreekt grotendeels voor zich, de werking is gelijk aan de eerder besproken code waar alles in één pagina verwerkt wordt (zie Ophalen van de data).

Renderen van de data

Het omzetten van de data naar HTML wordt in de functie niveausNaarHtmlTable($data) gedaan. Deze functie krijgt de gegevens binnen middels een parameter en gaat niet zelf de data ophalen. Hierdoor is de functie een stuk flexibeler, want nu kun je ook een andere set van gegevens meegeven (bijv. minder rijen door filtering toe te passen).

view_functies.php

<?php
function niveausNaarHtmlTable($data) {
    // Begin van de "table"
    $html = '<table>';
    // De "table heads"
    $html = $html . '<tr><th>Code</th><th>Omschrijving</th></tr>';

    // Elke rij als een "table row"
    foreach($data as $rij) {
        $niveaucode = $rij['niveaucode'];
        $omschrijving = $rij['omschrijving'];
        $html = $html . "<tr><td>$niveaucode</td><td>$omschrijving</td></tr>";
    }
    // Eind van de "table"
    $html = $html . "</table>";

    // Geef de HTML terug
    return $html;
}
?>

Ook hier is de werking van de code gelijk aan de eerder besproken code (zie Renderen van de data).

Weergeven van de code

De basisstructuur van de pagina die de uiteindelijke HTML samenstelt is nagenoeg identiek aan die eerder besproken is (zie Volledige code). Het grootste verschil is dat het PHP-deel van de pagina een stuk kleiner is.

<?php
require_once './includes/data_functies.php';
require_once './includes/view_functies.php';

// 1. Ophalen van de data
// Haal alle niveaus op (zie data_functies.php)
$niveaus = haalAlleNiveausOp();

// 2. Renderen van de data
// Maak de HTML-code (zie bestand view_functies.php)
$niveausHtml = niveausNaarHtmlTable($niveaus);

?>
<!DOCTYPE html>
<html lang="nl">
<head>
  <meta charset="UTF-8">
  <meta http-equiv="X-UA-Compatible" content="IE=edge">
  <meta name="viewport" content="width=device-width, initial-scale=1.0">
  <title>Niveaus</title>
</head>
<body>
  <h1>Alle niveaus</h1>
  <!-- 3. Weergeven van de data -->
  <?= $niveausHtml ?>
</body>
</html>

Je kunt zelfs de methoden direct in de HTML uitvoeren (let wel op dat je code leesbaar blijft, zodat je het later nog begrijpt en kan aanpassen).

<?php
require_once './includes/data_functies.php';
require_once './includes/view_functies.php';
?>
<!DOCTYPE html>
<html lang="nl">
<head>
  <meta charset="UTF-8">
  <meta http-equiv="X-UA-Compatible" content="IE=edge">
  <meta name="viewport" content="width=device-width, initial-scale=1.0">
  <title>Niveaus</title>
</head>
<body>
  <h1>Alle niveaus</h1>
  <?= niveausNaarHtmlTable(haalAlleNiveausOp()); ?>
</body>
</html>

Grote applicatie opdelen

Het voorbeeld (de muziekdatabase) in deze reader is vrij klein. In website-projecten in de praktijk zijn applicaties al vrij snel veel groter. Een opdeling in maar twee bestanden (./includes/data_functies.php en ./includes/view_functies.php) wordt dan ook onoverzichtelijk. Men deelt dan de functies op naar meerdere bestanden en/of folders. Zo zou je kunnnen kiezen voor een opdeling als ./includes/componist_data_functies.php, ./includes/muziekschool_data_functies.ph etc. dus per component een apart bestand. Een opdeling in folders zie je ook vaak, dus dat er per component een folder is met de bijbehorende bestand, bijv. ./includes/componist/data_functies.php etc. Zo krijgt elk component z'n eigen folder.

Speciale tekens uit de database verwerken

In de voorgaande voorbeelden zijn we er vanuit gegaan dat de teksten die uit de database komen geen speciale karakters bevatten (denk aan <, >, & etc.). De tekstvelden uit de database zou je eigenlijk altijd moeten checken op speciale tekens. PHP heeft daar een standaard functie voor, namelijk htmlspecialchars. Deze functie vervangt speciale tekens met de HTML-code die daarvoor is. Bijv de < wordt vervangen met <.

In het bestand view_functies.php zou de cel 'omschrijving' een speciaal karakter kunnen bevatten (bijv. niveaucode D met omschrijving expert & te moeilijk).

De regel :

  $omschrijving = $rij['omschrijving'];

wordt dan:

  $omschrijving = htmlspecialchars( $rij['omschrijving'] );

Verbinding maken met Microsoft SQL management studio

Bij het vak Database heb je een eigen database geïnstalleerd waar je met MSSQL Management Studio verbinding maakt. De WTIS-omgeving met Docker bevat ook een MS SQL server. Hier kun je verbinding mee maken, maar dan moet je in Management Studio de juiste server aangeven. Bij het verbinden gebruik je de volgende instellingen:

• Server name: localhost, 1434
• Authentication: SQL Server Authentication
• Login: SA
• Password: abc123!@#

Nadat je ingelogd bent heb je de beschikking over de Muziekdatabase en de database die je nodig hebt voor het beroepsproduct. Op deze databases kun je nu met Management Studio de queries testen. Probeer maar eens uit:

1. Maak een 'New Query'

2. Stel een query op en voer die uit

   use muziekschool
   go
   
   select stuknr, titel, speelduur
   from stuk
   where speelduur < 5.0
   

3. Dat geeft de volgende mogelijke uitvoer:

   

Errors

Als je een error krijgt probeer de error dan goed te lezen. Hieronder staan drie scenario’s die vaak misgaan.

Helaas staan de errors altijd een beetje verstopt na de volgende statement:

Uncaught PDOException: SQLSTATE[42000]: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]

Dat geeft alleen aan waar de error vandaan komt. Dus uit de driver van SQL Server komt een error. En de foutmelding volgt dan daarna:

Cannot open database "Applicatisde" requested by the login. The login failed.

Foutieve databasenaam

In het volgende voorbeeld van een foutmelding is het enige wat misgegaan is dat de databasenaam verkeerd staat.

Foutieve username of foutief wachtwoord

De volgende foutmelding zie je als je de verkeerde username hebt gebruikt of het wachtwoord niet goed hebt. ⚠️ Let op, je moet goed zoeken naar de belangrijkste fout: Login failed for user 'sharona'.

Bij deze error is het handig om even in de log te gaan kijken van SSMS. Dan staat er vaak iets als:

Login failed for user 'applicatie'. Reason: Password did not match that for the login provided.

of

Login failed for user 'sharona'. Reason: Could not find a login matching the name provided.

Fout in de query

Als er iets misgaat in de query, dan geeft de query niet een object terug waar je ->fetch() op kan draaien. Noch kan je dan erdoorheen lopen met foreach.

Je ziet dan iets wat lijkt op het volgende:

Ga de query goed na of de tabellen en de data die je opvraagt kloppen. Je kan de query ook altijd eerst testen in SSMS.