De grote MySQL tutorial voor dummies

MySQL leren: een inleiding

De digitale wereld is een constant groeiende hoeveelheid aan data en het zijn databasemanagementsystemen zoals MySQL die hieraan ten grondslag liggen. Dergelijke systemen maken het mogelijk om grote hoeveelheden gegevens elektronisch te verwerken, consistent op te slaan en permanent te bewaren. Daarbij worden ingewikkelde gegevensbestanden opgedeeld in handzame subcategorieën en waar nodig met elkaar verbonden. In onze cursus MySQL voor dummies krijg je een inleiding in de basics van het databasemanagement en leer je aan de hand van voorbeelden hoe je het databasemanagement voor jouw webproject met MySQL kunt optimaliseren.

Wat is MySQL?

MySQL behoort naast Oracle en Microsoft SQL Server tot de populairste relationele databasemanagementsystemen ter wereld (een actueel overzicht vind je op ranking db-engines.com). De software is in 1994 ontwikkeld door het Zweedse bedrijf MySQL AB en behoort tegenwoordig tot de Oracle Corporation. Het wordt verkocht op basis van een duaal licentiesysteem: naast de betaalde Enterprise-uitvoering biedt Oracle een open-source-versie met GPL-licentie aan.

Deze dubbele licentieverlening geeft bedrijven de mogelijkheid eigen toepassingen op basis van MySQL te ontwikkelen, zonder dat deze onderhevig zijn aan de open-source-licentie. In open-source-kringen wordt echter veel kritiek geleverd op de overname van MySQL door Oracle.

MySQL is geschreven in C en C++ en beschikt over een Yacc-gebaseerde SQL-parser met zelfontwikkelde lexer (lexicale scanner). Het databasemanagementsysteem blinkt bovendien uit doordat het veel besturingssystemen ondersteunt.

MariaDB – MySQL-fork met potentieel

De opname van het MySQL-project in het productportfolio van Oracle stuit in de ontwikkelaarswereld overwegend op wantrouwen en kritiek. Dit komt in de eerste plaats door het feit dat de verschillen tussen de MySQL-versie met GPL-licentie en de betaalde Enterprise-versie steeds groter worden. Nieuwe functies van het databasemanagementsysteem zijn steeds vaker alleen beschikbaar in de betaalde versie. Niet-openbare foutendatabases en gebrek aan tests doen de community vermoeden dat het open-source-project onder de hoede van de softwaregigant Oracle wordt verwaarloosd. De steun uit de open-source-gemeenschap neemt daarom langzaam maar zeker af.

In 2009 heeft het kernteam van ontwikkelaars rondom MySQL-uitvinder Michael “Monty” Widenius het populaire databasesysteem de rug toegekeerd en met MariaDB een open-source-fork van de software ingevoerd. Eind 2012 leidden de eerste Linux-distributies de omschakeling van MySQL naar MariaDB (als standaardinstallatie) in. Dit deden ze aan de hand van de systemen Fedora, OpenSUSE, Slackware en Arch Linux. Talrijke open-source-projecten alsook bekende softwarebedrijven en webplatforms, waaronder Mozilla, Ubuntu, Google, Red Hat Enterprise Linux, Web of Trust, TeamSpeak, de Wikimedia Foundation en het softwareproject XAMPP, volgden hun voorbeeld.

Het valt nu al op dat MariaDB zich in vergelijking met de open-source-versie van MySQL voortdurend verder ontwikkelt. Daarom kan worden verondersteld dat de fork zijn moederproject al snel voorbij zal streven.

Het relationele databasemodel

MySQL is de definitie van een relationeel DBMS. Dat betekent dat de gegevens worden beheerd volgens een databasemodel op basis van tabellen. Alle gegevens die MySQL verwerkt, worden opgeslagen in tabellen die door sleutels (keys) met elkaar in verband kunnen worden gebracht.

Dit wordt duidelijk gemaakt aan de hand van een simpel voorbeeld. Hieronder zie je de twee tabellen Auteurs en Werken:

Alle tabellen van een relationele database bestaan uit kolommen en rijen. Elke kolom van een tabel staat voor een bepaald kenmerk. In de tabel Auteurs vind je bijvoorbeeld de kenmerken id, voornaam en achternaam. De rijen van een tabel worden aangeduid als rows en bevatten elk een gegevensset. Deze wordt normaal gesproken eenduidig geïdentificeerd (genummerd) door een primaire sleutel. Welk kenmerk als primaire sleutel fungeert, wordt bepaald bij het aanmaken van de tabel. Voorwaarde is dat de primaire sleutel een eenduidige toewijzing mogelijk maakt. Daarom mag elke primaire sleutel binnen een kolom slechts één keer worden gebruikt. Het is raadzaam de rijen doorlopend te nummeren via een ID.

De tabel Werken bevat naast de primaire sleutel werk_id ook de auteur_id als refererende sleutel (foreign key). Deze brengt de twee tabellen met elkaar in verband en zorgt ervoor dat de gegevenssets van de ene tabel aan die van de andere tabel kunnen worden gekoppeld. Als men twee tabellen van een relationele database met elkaar verbindt, wordt dat een join genoemd. Dit kan bijvoorbeeld worden bereikt door alle werken van de auteur John Ronald Reuel Tolkien inclusief datum van de eerste publicatie op te roepen.

Tolkien is in de tabel Auteurs opgenomen met de primaire sleutel auteur_id 1. Om alle werken van de auteur op te roepen wordt deze in de tabel Werken als refererende sleutel gebruikt. Zo worden alle rows opgeroepen die aan de auteur_id 1 gekoppeld zijn.

In de praktijk worden bewerkingen in MySQL-databases uitgevoerd met gestandaardiseerde SQL-opdrachten zoals SELECT, INSERT, UPDATE en DELETE. Deze opdrachten worden in de volgende hoofdstukken van onze MySQL-cursus nader toegelicht.

Natuurlijk was het ook mogelijk geweest alle gegevens van beide auteurs en hun werken in slechts één tabel op te slaan. Dit zou er echter toe leiden dat de database een enorm aantal overbodige vermeldingen bevat, omdat bijvoorbeeld de gegevens uit de kolommen voornaam en achternaam in dit geval voor elk werk apart zouden moeten worden ingevuld. Dat neemt niet alleen veel geheugen in beslag, maar zorgt er ook voor dat als je de tabel wilt bijwerken, je dit voor elke rij afzonderlijk moet doen in plaats van slechts één keer de wijziging aan te brengen. Personen die werken met relationele databasesystemen beperken zich daarom tot één onderwerp per tabel. Dit wordt normalisatie van de gegevens genoemd.

De voornaamste functie van MySQL is het opslaan van gegevens in het kader van dynamische webtoepassingen. Doorgaans wordt MySQL voor webontwikkeling gebruikt in combinatie met de webserversoftware Apache en de scripttalen PHP of Perl. Deze set van softwarepakketten staat, afhankelijk van het gebruikte serverbesturingssysteem, bekend als LAMP (Linux), MAMP (macOS) of WAMP (Windows).

Voor het MySQL leren raden wij de lokale testomgeving XAMPP aan om ervaring op te doen in het gebruik van het databasemanagementsysteem. Deze maakt in de huidige versie gebruik van MariaDB.

Installatie van het database­managementsysteem

Om de MySQL-basics begrijpelijk te maken, werken we met voorbeelden uit de praktijk. Onze MySQL-tutorial is daarbij gebaseerd op de testomgeving XAMPP. Codesnippets en screenshots concentreren zich op databasebewerkingen die mogelijk zijn gemaakt via PHP met behulp van een Apache HTTP-server op een lokale Windows-pc. In plaats van de klassieke MySQL-database wordt daarbij gebruikgemaakt van de fork MariaDB. Op dit moment zijn beide databasemanagementsystemen echter zo compatibel dat alle bewerkingen 1:1 kunnen worden overgedragen. Voor jou als eindgebruiker maakt het voor deze tutorial voor beginners dus niet uit of je met MySQL of met MariaDB werkt.

Hoe je zo’n testomgeving lokaal op je Windows-pc installeert, wordt beschreven in onze XAMPP-tutorial. Als je vanaf het begin wilt leren om met relationele databases te werken, raden wij aan direct met MariaDB te beginnen. Een alternatieve kosteloze testomgeving op basis van MySQL is overigens beschikbaar met AMPPS.

Bovendien bestaat de mogelijkheid een aangepaste web stack (set van softwarepakketten) samen te stellen. MySQL en MariaDB kunnen naar wens met diverse besturingssystemen, webservers en scripttalen worden gecombineerd. Kosteloos te downloaden pakketten met GPL-licentie worden aangeboden op mysql.com en mariadb.com. Gedetailleerde installatiehandleidingen voor verschillende platforms vind je in de Engelstalige documentatie van MySQL en MariaDB.

Databasemanagement met phpMyAdmin

Bij het beheer van MySQL maken wij gebruik van de gratis webtoepassing phpMyAdmin. Deze maakt deel uit van de installatie van de XAMPP-softwarebundel, maar wordt op de officiële projectwebsite ook apart aangeboden als downloadpakket.

phpMyAdmin wordt beschouwd als standaardsoftware voor het beheer van MySQL-databases op het internet. De webtoepassing, die in PHP en JavaScript is geschreven, maakt het mogelijk databasebewerkingen uit te voeren via een grafische gebruikersinterface. Zo kun je de tabellen van je relationele database heel gemakkelijk met een paar muisklikken in je webbrowser aanmaken en beheren. Kennis van bijbehorende SQL-opdrachten is daarvoor vooralsnog niet nodig.

phpMyAdmin oproepen

Als de softwarebundel XAMPP is geïnstalleerd, start je het databasemanagementsysteem (MySQL of MariaDB) net als de andere componenten van de testomgeving via het configuratiescherm (Control Panel). Gebruik daarvoor de knop ‘Start’ onder ‘Actions’. Om phpMyAdmin via de webbrowser te kunnen oproepen, moet je bovendien de webserver Apache starten. Geactiveerde modules krijgen in het XAMPP Control Panel een groene achtergrond. Daarnaast wordt de actuele status van de XAMPP-modules als melding in het tekstkader weergegeven.

Start MySQL en de Apache HTTP-server via het XAMPP Control Panel

Feit: XAMPP is in het kader van het softwareproject Apache Friends ontwikkeld als compact testsysteem voor gebruik op een lokale computer. De softwarebundel is niet bedoeld om webdiensten op internet te zetten. Wegens de vele beperkingen op het gebied van veiligheid is XAMPP niet geschikt als productiesysteem.

---

Voor lokale testdoeleinden is de webinterface van de beheersoftware beschikbaar op http://localhost/phpmyadmin/.

Als je bij de installatie van MySQL een wachtwoord voor het root-account hebt ingesteld, vraagt phpMyAdmin hiernaar via een aanmeldingsscherm. Als je phpMyAdmin voor een webhostingproduct gebruikt, worden de aanmeldingsgegevens via de desbetreffende aanbieder verstrekt. In dat geval heb je meestal geen root-rechten.

Als je succesvol bent aangemeld, verschijnt de startpagina van phpMyAdmin. Hier kun je basisinstellingen voor de tekenset (sortering)van de MySQL-verbinding wijzigen en de gewenste weergavemodus (taal, design, tekengrootte) kiezen. Daarnaast vind je aan de rechterzijde een overzicht van de basisgegevens van je databaseserver, de gebruikte webserversoftware en informatie over de actuele versie van phpMyAdmin. De menubalk van de startpagina wordt, net als alle andere menubalken in de toepassing, weergegeven in de vorm van tabbladen. De startpagina bevat de tabbladen Databases, SQL, Status, User accounts, Export, Import, Settings, Replication, Variables en More.

Nadat je de URL http://localhost/phpmyadmin/ hebt opgeroepen, verschijnt de startpagina van phpMyAdmin

Aan de linkerkant van de gebruikersinterface vind je een navigatievenster. Hierin staan alle tabellen van je database waar je met phpMyAdmin toegang tot hebt. Onder het programmalogo in de linkerbovenhoek staan links naar de startpagina en naar de officiële documentatie. Daarnaast heb je hier de mogelijkheid het navigatievenster te configureren en de weergave te vernieuwen.

In deze MySQL cursus begin je met het aanmaken van een eerste database.

Een database aanmaken

Om met phpMyAdmin een database aan te maken, klik je eerst op het tabblad ‘Databases’ in de menubalk van de startpagina.

Databases maak je met phpMyAdmin aan in het tabblad ‘Databases’

Voer de gewenste naam voor je database in het invoerveld onder ‘Create database’ (database maken) in, en selecteer een tekenset. Wij raden de sortering utf8mb4_unicode_ci aan. Met de keuze van een tekenset deel je de databaseserver mee welke codering moet worden gebruikt voor de te verzenden en te ontvangen gegevens. De mb4-varianten laten ook exotische tekens toe, zoals symbolen of emoji’s die buiten het Unicode-basisniveau (Basic Multilingual Plane) liggen, en zijn daarom aan te raden.

Om een database aan te maken, vul je een naam in en selecteer je de tekenset die als basis moet dienen

Bevestig de invoer door op ‘Create’ (maken) te klikken. De aangemaakte database wordt in het navigatievenster aan de linkerzijde van het beeldscherm weergegeven. Nieuwe databases hebben in eerste instantie geen inhoud. Om gegevens op te slaan, maak je in de volgende stap een tabel aan.

Tabellen aanmaken

Om een nieuwe tabel aan te maken, selecteer je de gewenste database en navigeer je via de menubalk naar het tabblad ‘Structure’ (structuur).

Via Create table maak je een nieuwe tabel aan

Maak een tabel aan door een naam (bijv. users) en het gewenste aantal kolommen in het veld ‘Create table’ (tabel maken) in te voeren. Vergeet niet dat elke kolom een kenmerk van een gegevensset vertegenwoordigt. Als je meer kolommen nodig hebt, kun je deze later ook nog toevoegen.

Als je bijvoorbeeld een database voor gebruikers van je website wilt maken, kun je de volgende titels voor de kolommen van de tabel gebruiken:

Kolom	Beschrijving
id	Een uniek identificatienummer voor elke gebruiker
forename	Voornaam van de gebruiker
surname	Achternaam van de gebruiker
email	Emailadres van de gebruiker
password	Wachtwoord van de gebruiker
created_at	Tijdstip waarop de vermelding is aangemaakt
updated_at	Tijdstip waarop de vermelding is gewijzigd

Voor je gebruikersdatabase maak je dus een tabel users met zeven kolommen. Je bevestigt de invoer met ‘Go’.

Voer een tabelnaam en het gewenste aantal kolommen in

Nadat de tabel is aangemaakt, geeft phpMyAdmin je de mogelijkheid titels voor de kolommen van de tabel in te voeren en opmaakinstellingen voor de te verwachten gegevens uit te voeren.

De voorbeeldtabel users – aanvankelijk zonder kenmerken

Een beschrijving van de tabelstructuur en mogelijke opmaakinstellingen vind je in onderstaande tabel.

Optie	Omschrijving
Name	Elke kolom van een databasetabel krijgt een titel. Deze kan, met enkele beperkingen, vrij worden gekozen. Latijnse letters (hoofdletters en kleine letters, maar zonder trema’s), cijfers, dollartekens en underscores zijn geen probleem. De underscore kan in plaats van de spatie (niet toegestaan) worden gebruikt (onjuist: user id; juist: user_id). Kolomtitels mogen niet alleen uit cijfers bestaan. Bovendien bevat de databasetaal SQL enkele sleutelwoorden die voor bepaalde taken zijn gereserveerd. Een lijst daarvan vind je in de MySQL-documentatie. De meeste van deze beperkingen kun je omzeilen, maar dan moet je de desbetreffende kolom altijd tussen backticks (``) zetten. Dezelfde regels gelden overigens ook voor tabel- en andere namen in MySQL. Het is raadzaam relevante kolomtitels in het Nederlands te gebruiken die bij het kenmerk passen.
Type	Het gegevenstype geeft aan welke soort gegevens in een kolom wordt opgeslagen. In MySQL en MariaDB kun je gegevens als gehele getallen, drijvendekommagetallen, tijdstippen en data alsook als teksttekenreeksen en binaire gegevens definiëren. Een toelichting hierop vind je in de tabel ‘Gegevenstypen’.
Length/Values	Bij sommige gegevenstypen (bijvoorbeeld teksttekenreeksen) kun je voor de waarden van een kolom een maximale lengte instellen. Deze opmaak is echter optioneel.
Default	Met de optie ‘Default’ kun je een standaardwaarde voor een kolom bepalen. Deze wordt dan automatisch ingevoegd als een gegevensset geen waarde voor de desbetreffende kolom bevat.
Collation	Met de optie ‘Collation’ wijs je een bepaald tekentype aan een kolom toe, die kan afwijken van de globale database-instellingen. Je kunt de codering ook op tabelniveau wijzigen voor alle kolommen.
Attributes	Sommige gegevenstypen kunnen door optionele kenmerken nauwkeuriger worden bepaald. Zo kun je met de kenmerken signed en unsigned bijvoorbeeld vastleggen of gehele getallen en drijvendekommagetallen ook negatieve (signed) of alleen positieve (unsigned) waarden kunnen aannemen.
Index	Via de optie ‘Index’ stel je regels in voor de indexering. Als je voor een kolom de indexinstelling PRIMARY selecteert, geldt deze kolom als primaire sleutel van de tabel. De instelling UNIQUE legt vast dat waarden binnen deze kolom slechts één keer kunnen worden opgeslagen. Zo kunnen dubbele vermeldingen worden voorkomen.
A_I	De afkorting ‘A_I’ staat voor AUTO_INCREMENT en zorgt ervoor dat het databasemanagementsysteem een waarde automatisch doornummert als er bij het aanmaken van een gegevensset geen waarde is ingevoerd. Deze optie wordt bijvoorbeeld gebruikt bij de indexering van gegevenssets.
Comments	In het veld ‘Comments’ kun je commentaren bij de kolommen van de tabel zetten.

Dit zijn de belangrijkste instellingen voor de opmaak van tabelkolommen. Als je met behulp van de schuifbalk verder naar rechts scrolt, kom je nog meer instellingsmogelijkheden tegen, maar deze bespreken wij verder niet in de MySQL-tutorial voor dummies.

In onderstaande tabel zijn verschillende gegevenstypes opgesomd die met MySQL en MariaDB kunnen worden verwerkt, evenals hun waardenbereik en het benodigde geheugen.

Type	Omschrijving	Waardenbereik	Geheugenvereiste
TINYINT	Een zeer klein geheel getal	Zonder teken: 0 tot 255 Met teken: -128 tot +127	1 Byte
SMALLINT	Een klein geheel getal	Zonder teken: 0 tot 65.535 Met teken: -32.768 tot +32.767	2 Byte
MEDIUMINT	Een middelgroot geheel getal	Zonder teken: 0 tot 16.777.215 Met teken: -8.388.608 tot +8.388.607	3 Byte
INT/INTEGER	Een geheel getal van normale grootte	Zonder teken: 0 tot 4.294.967.295 Met teken: -2.147.483.648 tot +2.147.483.647	4 Byte
BIGINT	Een groot geheel getal	Zonder teken: 0 tot 18.446.744.073 Met teken: -9.223.372.036.854.775.808 tot +9.223.372.036.854.775.807	8 Byte
FLOAT	Een drijvend kommagetal met enkele precisie	Zonder teken: 0 tot 3,4e+38 Met teken: -3,4e+38 tot 3,4e+38	4 Byte
DOUBLE	Een drijvend kommagetal met dubbele precisie	Zonder teken: 0 tot 3,4e+38 Met teken: -3,4e+38 tot 3,4e+38	8 Byte
DATE	Datumweergave in ‘YYYY-MM-DD’	‘1000-01-01’ tot ‘9999-12-31’	3 Byte
TIME	Tijdweergave in ‘HH:MM:SS:ssssss	‘-838:59:59.999999’ tot ‘838:59:59.999999’	3 Byte
DATETIME	Datumweergave in ‘YYY-MM-DD HH:MM:SS.ssssss’	Komt overeen met DATE en TIME (tot 23:59:59.999999 uur)	8 Byte
TIMESTAMP	Tijdstempel in ‘YYY-MM-DD HH:MM:DD’	‘1970-01-01 00:00:01’ (UTC) tot ‘2038-01-19 05:14:07’ (UTC)	4 Byte
YEAR	Tussen het jaar 1901 en 2155	1901 tot 2155 en 0000	1 Byte
CHAR	Tekenreeks met vaste lengte; M komt overeen met aantal tekens	Voor M: 0 tot 255 tekens	M Byte
VARCHAR	Tekenreeks met variabele lengte; M komt overeen met aantal tekens	Voor M: 0 tot 65.535 tekens	Max. M + 2 Byte
TINYTEXT	Zeer kleine tekenreeks met variabele lengte; M komt overeen met aantal tekens	Voor M: 0 tot 255 tekens	M + 1 Byte
TEXT	Tekenreeks met variabele lengte; M komt overeen met aantal tekens	Voor M: 0 tot 255 tekens	M + 2 Byte
MEDIUMTEXT	Middelgrote tekenreeks met variabele lengte; M komt overeen met aantal tekens	Voor M: 0 tot 16.777.215 tekens	M + 3 Byte
LONGTEXT	Lange tekenreeks met variabele lengte; M komt overeen met aantal tekens	Voor M: 0 tot 4.294.967.295 tekens (4 GB)	M + 4 Byte
BLOB	Een BLOB (Binary Large Object) is een binair object met gegevens van variabele lengte (bijv. afbeeldingen, audiobestanden)	Max. lengte M: 65.535 Byte	M + 2 Byte
TINYBLOB	Klein binair object met gegevens van variabele lengte	Max. lengte M: 255 Byte	M + 1 Byte
MEDIUMBLOB	Middelgroot binair object met gegevens van variabele lengte	Max. lengte M: 16.777.215 Byte	M + 3 Byte
LONGBLOB	Groot binair object met gegevens van variabele lengte	Max. lengte M: 4.294.967.295 Byte (4 GB)	M + 4 Byte
ENUM (opsomming)	Een tekenreeksobject waarvan de toelaatbare waarden bij het aanmaken van de kolom worden gedefinieerd	Max. 65,535 verschillende elementen	1 of 2 Bytes, afhankelijk van het aantal mogelijke waarden
SET	Een tekenreeksobject waarvan de toelaatbare waarden bij het aanmaken van de tabel worden gedefinieerd. Meerkeuze is mogelijk	Max. 64 verschillende waarden	1, 2, 3, 4 of 8 Byte, afhankelijk van het aantal mogelijke waarden

Voor de voorbeeldtabel users hebben we de volgende instellingen gekozen:

Mogelijke waarden voor de kolom id zijn gedefinieerd als geheel getal (integer, INT) en dragen het kenmerk UNSIGNED. De id kan zodoende alleen positieve getalswaarden aannemen. Onder ‘Index’ hebben we voor de id de instelling PRIMARY geselecteerd. Het identificatienummer fungeert zodoende als primaire sleutel voor de tabel users. Het vinkje bij ‘A_I’ (Auto_Increment) geeft aan het databasemanagementsysteem door dat ID’s voor elke vermelding automatisch doorlopend genummerd moeten worden.

De waarden voor de kolommen forename, surname, email en password zijn als gegevenstype VARCHAR ingesteld. Dit zijn dus variabele tekenreeksen waarvan wij de lengte (M) met de optie ‘Length/Value’ tot 50 tekens hebben beperkt. Voor de kolom email is bovendien de indexoptie UNIQUE geactiveerd. Zo zorgen we ervoor dat elk e-mailadres slechts één keer in onze tabel wordt opgeslagen.

Voor de kolommen created_at en updated_at hebben we het gegevenstype TIMESTAMP gekozen. Het databasemanagementsysteem slaat tijdsgegevens voor het aanmaken en bijwerken van gegevenssets op in het formaat YYYY-MM-DD HH:MM:DD. Omdat het systeem voor elke nieuwe vermelding automatisch een tijdstempel moet aanmaken, selecteren we voor de kolom created_at de standaardwaarde CURRENT_TIMESTAMP. De kolom updated_at wordt pas relevant als we een vermelding bijwerken. Daarom laten we null-waarden voor deze kolom toe en stellen NULL in als standaardwaarde.

Feit: De waarde NULL staat in PHP voor een lege vermelding. Een veld heeft de waarde NULL als er aan dit veld nog geen waarde is toegewezen.

De voorbeeldtabel users na de opmaak

Als opslag-engine gebruiken we het standaard tabelformat van MySQL, InnoDB.

Alle tabelinstellingen die je via de grafische gebruikersinterface uitvoert, worden door phpMyAdmin omgezet in SQL-code. Indien gewenst kunnen de instellingen alvast worden bekeken door op de knop ‘SQL Preview’ te klikken.

CREATE TABLE test.users 
( id INT UNSIGNED NOT NULL AUTO_INCREMENT , 
forename VARCHAR(50) NOT NULL , surname VARCHAR(50) NOT NULL , 
email VARCHAR(50) NOT NULL , password VARCHAR(50) NOT NULL , 
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP , 
updated_at TIMESTAMP NULL DEFAULT NULL , 
PRIMARY KEY (id), UNIQUE (email)) ENGINE = InnoDB;

Een gedetailleerde beschrijving van de SQL-syntaxis volgt in het hoofdstuk over databasequery’s.

Je slaat de instellingen op door op ‘Save’ te klikken. De tabel users verschijnt in het navigatievenster onder de database test.

Tabellen beheren

Om een aangemaakte tabel te beheren, klik je op de naam van de tabel in het navigatievenster. phpMyAdmin toont vervolgens in het tabblad ‘Browse’ (bladeren) een overzicht van de gegevens die in de tabel zijn opgeslagen. Dit levert in het geval van onze voorbeeldtabel een leeg resultaat op. Er zijn tenslotte nog geen gegevenssets in de tabel users opgeslagen.

In het tabblad ‘Browse’ toont phpMyAdmin een overzicht van de gegevens die in de tabel zijn opgeslagen

Voor het beheer van gegevenstabellen staan in de menubalk verschillende tabbladen ter beschikking. Als je de structuur van een gegevenstabel wilt veranderen, klik je op het tabblad ‘Structure’. In het tabblad ‘Insert’ (invoegen) kun je nieuwe gegevensarchieven aan je tabel toevoegen. Daarnaast heb je in phpMyAdmin de mogelijkheid tabellen te doorzoeken, autorisaties te beheren en gegevenssets te exporteren of uit andere tabellen te importeren.

Tabelstructuur bewerken

Als je later een extra kolom aan je tabel wilt toevoegen of een bestaande kolom wilt wissen of bewerken, ga je naar het tabblad ‘Structure’.

Voeg kolommen toe via ‘Add column(s)’ door het gewenste aantal nieuwe kolommen en de gewenste positie aan te geven.

Onderstaande afbeelding toont dat de nieuwe kolom na de kolom updated_at wordt ingevoegd:

Via ‘Add column(s)’ geef je aan hoeveel kolommen je waar wilt invoegen

Wil je bestaande kolommen wissen, selecteer dan de gewenste kolommen door een vinkje in het selectievakje te plaatsen en klik vervolgens op ‘Drop’.

Geselecteerde kolommen kunnen naar wens worden bewerkt of gewist

Je kunt een kolom bewerken via de knop ‘Change’. Hiermee kom je in een bewerkingsweergave terecht, die je zult herkennen van het aanmaken van de tabel:

Geselecteerde kolommen worden in een nieuwe weergave bewerkt

Wijzigingen in de tabelstructuur kunnen eventueel verlies van gegevens tot gevolg hebben. Daarom is het verstandig een back-up van je database te maken voordat je aangemaakte tabelkolommen bewerkt of wist. Ga daarvoor naar het tabblad ‘Export’ (exporteren), selecteer de gewenste bestandsindeling voor de back-up en bevestig de bewerking met ‘Go’. Er verschijnt een dialoogvenster waarin je webbrowser vraagt waar je de download wilt opslaan. Een alternatief om back-ups van databases via phpMyAdmin te maken, is het gratis back-upprogramma MySQLDumper.

Tabelvermeldingen toevoegen

Om gegevens in de tabel in te vullen via phpMyAdmin, heb je twee mogelijkheden: je kunt gegevenssets uit een extern bestand importeren (bijvoorbeeld uit een back-up) of je maakt tabelvermeldingen handmatig aan. Als je voor de laatste optie kiest, selecteer je de voorbeeldtabel users en navigeer je naar het tabblad ‘Insert’ (invoegen).

phpMyAdmin toont dan het volgende invoervenster:

Tabelvermeldingen maak je aan in het tabblad ‘Insert’ van phpMyAdmin

Onder ‘Column’ zijn de kolommen opgesomd die we voor de tabel users hebben aangemaakt. Onder ‘Type’ staat aangegeven welk gegevenstype voor de desbetreffende kolom wordt verwacht evenals een eventuele beperking van het aantal tekens tussen haakjes. De kolom ‘Function’ laten we buiten beschouwing en we gaan direct naar de kolom ‘Value’. Hier voeren we de waarden in voor de afzonderlijke kolommen van onze voorbeeldtabel.

In het vorige hoofdstuk hebben we de tabel users zodanig geconfigureerd dat de gegevensvelden voor de kolommen id, created_at en updated_at automatisch worden gevuld door het databasemanagementsysteem. In de kolom id wordt dankzij AUTO_INCREMENT voor elke nieuwe vermelding een doorlopend nummer ingevoegd. In het veld created_at wordt automatisch de actuele tijdstempel ingevuld. En voor updated_at vult het systeem voorlopig de standaardwaarde NULL in. We moeten dus alleen nog de gegevens voor de kolommen forename, surname en password handmatig invoeren. We laten dit zien aan de hand van fictieve gebruikersgegevens:

• forename: John
• surname: Doe
• email: john@doe.com
• password: qwertz
  
  

Voer de gewenste gegevens in het invoervenster in en bevestig de invoer met ‘Go’

Door op ‘Go’ te klikken, sla je de gegevens op in de tabel. phpMyAdmin springt automatisch naar het tabblad ‘SQL’ en geeft de uitgevoerde databasebewerking weer als statement in de SQL-syntaxis:

INSERT INTO users (id, forename, surname, email, password, 
		
created_at, updated_at) VALUES (NULL, 'John', 'Doe', 'john@doe.com',
		
'qwerty', CURRENT_TIMESTAMP, NULL);

phpMyAdmin geeft elke databasebewerking weer als SQL-statement

In principe kunnen alle databasebewerkingen die je in phpMyAdmin gemakkelijk via een grafische gebruikersinterface uitvoert, ook in de databasetaal SQL worden geschreven. Bij webontwikkeling is dat de standaardprocedure.

Zogenaamde SQL-query’s (informatieverzoeken) komen voor in de broncode van alle dynamische webtoepassingen en maken de interactie tussen webserver en database mogelijk. De databasetaal SQL is gebaseerd op commando’s, bijvoorbeeld om gegevens op te roepen en deze te gebruiken voor het uitvoeren van een programma. De belangrijkste SQL-commando’s SELECT, INSERT, DELETE en UPDATE alsook de syntaxis van deze databasebewerkingen worden toegelicht in de volgende hoofdstukken van onze MySQL-tutorial.

Eerst voeren we in de tabel users echter meer gebruikersgegevens in en bekijken we het tabeloverzicht in het tabblad ‘Browse’:

In het tabeloverzicht in het tabblad ‘Browse’ worden alle tabelvermeldingen opgesomd, gesorteerd op primaire sleutel

Door op een andere kolomtitel te klikken, kun je de tabel in de gewenste volgorde sorteren.

Databaseverbinding tot stand brengen

Nadat we de voorbeeldtabel users hebben ingevuld met vermeldingen, leren we in de volgende hoofdstukken hoe de opgeslagen gegevens kunnen worden opgevraagd via PHP op de Apache-webserver.

Daarvoor brengen we eerst een databaseverbinding tot stand. Hiervoor biedt PHP drie interfaces: MySQL Extension, MySQL Improved Extension (MySQLi) en PHP Data Objects (PDO).

• MySQL Extension: MySQL Extension is een MySQL-interface die vroeger erg populair was, maar nu nogal verouderd is. Ten opzichte van MySQLi en PDO heeft de oude MySQL Extension het nadeel dat deze interface noch prepared statements, noch benoemde parameters ondersteunt.
• MySQLi: MySQLi is een verbeterde versie van de klassieke PHP-extensie voor de toegang tot MySQL-databases. Deze interface werkt zowel procedureel als objectgeoriënteerd. De extensie kan echter alleen voor MySQL- en MariaDB-databases worden gebruikt.
• PDO: PHP Data Objects (PDO) is een objectgeoriënteerde interface die een ‘abstraction layer’ voor de gegevenstoegang ter beschikking stelt. Zodoende kunnen via PDO niet alleen MySQL-databases, maar ook andere databasesystemen zoals PostgreSQL, Oracle, MSSQL of SQLite in PHP worden geïntegreerd.
  
  

In het volgende deel van deze tutorial voor MySQL beperken we ons tot databaseverbindingen via PDO.

Om via een PHP-script gegevens uit een database te kunnen opvragen, moet de database eerst worden geverifieerd. Een databaseverbinding via PDO wordt met behulp van de volgende coderegel tot stand gebracht:

?php
		$pdo = new PDO('DSN', 'username', 'password');
		?;

Het is raadzaam deze regel in te voegen aan het begin van elk script dat databasebewerkingen bevat.

We gebruiken het PHP-sleutelwoord new om een entiteit van de PDO-basisklasse aan te maken. Hiervoor zijn in principe drie parameters vereist: de Data Source Name (DSN), een gebruikersnaam en het wachtwoord voor de database, mits van toepassing. De DSN bestaat in ons geval uit de volgende parameters:

• PDO-databasestuurprogramma: mysql
• Databaseserver (host=): localhost
• Databasenaam (dbname=): test
• Tekenset (charset=): utf8
  
  

Als je voor de database geen toegangsgegevens hebt ingesteld, gebruik je de gebruikersnaam root en een leeg wachtwoord:

?php
		$pdo = new PDO('mysql:host=localhost;dbname=test;charset=utf8', 'root', '');
		?;

De databaseverbinding wordt in de variabele $pdo opgeslagen. Hiermee kun je later in de programmacode terugverwijzen naar de databaseverbinding.

Als er een verbinding met de database tot stand is gebracht, kun je in de volgende scriptcode een willekeurig aantal query’s naar de database verzenden. Als je het script beëindigt, wordt ook de databaseverbinding verbroken.

Gegevensquery’s met SELECT, INSERT, UPDATE en DELETE

Der STRATO Homepage-Baukasten führt Sie auch ohne Vorkenntnisse und mit wenig Aufwand zur eigenen modernen Website. Anspruchsvolle Privat- und Firmenkunden verfügen dank Expertenmodus gleichzeitig über viel Flexibilität für individuelle Anpassungen und spezielle Bedürfnisse. Das Hosting-Paket mit eigener Domain und persönlicher E-Mail ist natürlich dauerhaft inklusive!

Om gegevens uit onze database op te roepen, maken we gebruik van de databasetaal SQL. Deze is semantisch op de Engelse taal gebaseerd en bewust eenvoudig gehouden. De SQL-syntaxis spreekt grotendeels voor zich.

In SQL wordt met statements gewerkt, ook wel query’s of aanvragen genoemd.

Een simpele SELECT-query bestaat bijvoorbeeld uit de volgende componenten:

SELECT kolom FROM tabel;

Eerst specificeer je het SQL-commando SELECT en vervolgens geef je aan op welke kolommen en tabellen het commando van toepassing is. Een puntkomma sluit het statement af.

Daarnaast heb je de mogelijkheid het statement uit te breiden met een optionele voorwaarde en/of een sorteer- of groepeerfunctie:

SELECT kolom FROM tabel WHERE voorwaarde ORDER BY sorteervolgorde;

Als je tabelnamen en kolomtitels gebruikt die overeenkomen met SQL-sleutelwoorden (wat niet aan te bevelen is), moeten deze tussen backticks (``) worden gezet.

We verduidelijken de syntaxis van simpele SQL-statements door voorbeelden te geven van de commando’s SELECT, INSERT, UPDATE en DELETE.

SELECT

Het commando SELECT gebruik je om geselecteerde gegevensrijen (rows) uit een willekeurig aantal tabellen op te roepen. Als je bijvoorbeeld de voor- en achternamen en e-mailadressen van alle gebruikers uit de zojuist aangemaakte voorbeeldtabel in de webbrowser wilt weergeven, maak je in de map htdocs van je XAMPP-omgeving een nieuw PHP-bestand text.php aan en voeg je het volgende script in:

?php
$pdo = new PDO('mysql:host=localhost;dbname=test;charset=utf8', 'root', '');
$sql = "SELECT forename, surname, email FROM users";
foreach ($pdo->query($sql) as $row) {
   echo $row['forename'] . " " . $row['surname'] ;
   echo "E-Mail: " . $row['email'] ;
}
?

Je leest de voorbeeldcode als volgt: we beginnen het script met de PHP-begincode <?php. In regel 2 brengen we de verbinding met onze database test op localhost tot stand en slaan we deze op in de variabele $pdo. Regel 3 bevat het SQL-statement met het commando SELECT. Hier wordt het databasemanagementsysteem opgedragen de kolommen forename, surname en email uit de tabel users op te roepen. Dit statement slaan we op in de variabele $sql.

De regels 4 t/m 7 tonen een foreach-lus. Deze biedt ons de mogelijkheid een willekeurige matrix te herhalen, dat wil zeggen een gegevensstructuur stap voor stap te doorlopen. Welke matrix moet worden herhaald en hoe de opgevraagde gegevens moeten worden opgeslagen, geven we tussen haakjes aan achter de foreach-constructie:

$pdo->query($sql) as $row

De variabele $pdo spreekt de gewenste database aan via de in regel 2 gespecificeerde verbinding. Met de functie query() sturen we het in de variabele $sql opgeslagen SQL-statement hiernaartoe.

De webserver roept zodoende alle kolommen forename, surname en email van de tabel users op uit de database test en doorloopt dankzij de foreach-lus elke afzonderlijke tabelrij. Waar de opgevraagde gegevens moeten worden opgeslagen, geeft het PHP-sleutelwoord as aan in de matrixvariabele $row.

In de eerste ronde van de foreach-lus ziet deze matrix er als volgt uit:

$row = array (
    forename => John,
    surname => Doe,
    email => john@doe.com
)

In dit voorbeeld is de tekstuitvoer via echo in elke ronde onderdeel van de foreach-lus. Zo doorlopen we elke rij van de tabel users afzonderlijk, lezen de opgeslagen gegevens uit de kolommen die in de SQL-statement zijn gespecificeerd uit, en geven deze weer in de webbrowser.

Het browservenster toont het resultaat van de uitvoering van het script

Als alle kolommen van een gegevenstabel moeten worden uitgelezen, gebruik je in de SQL-statement een asterisk (*) als jokerteken (ook wel wildcard genoemd).

<?php
$pdo = new PDO('mysql:host=localhost;dbname=test;charset=utf8', 'root', '');
$sql = "SELECT * FROM users";
foreach ($pdo->query($sql) as $row) {
   echo $row['forename'] . " " . $row['surname'] ;
   echo "E-Mail: " . $row['email'] ;
   echo "Created at: " . $row['created_at'] ;
}
?>

Daarmee kunnen we alle gegevens die in de tabel ‘users’ zijn opgeslagen in het script gebruiken. In het volgende screenshot is de tekstuitvoer aangevuld met een tijdstempel die aangeeft wanneer de vermelding is aangemaakt.

Tekstuitvoer in de browser, inclusief tijdstempel

In beide voorgaande voorbeelden geeft de webserver de gebruikersgegevens weer in de volgorde waarin wij ze in de tabel users hebben ingevoerd (gesorteerd op ID-nummer). Als je de gegevens gesorteerd wilt weergeven, bepaal je de volgorde met behulp van het SQL-sleutelwoord ORDER BY. In het volgende voorbeeld worden de gegevens gesorteerd en op de alfabetische volgorde van de voornaam weergegeven:

<?php
$pdo = new PDO('mysql:host=localhost;dbname=test;charset=utf8', 'root', '');
$sql = "SELECT forename, surname, email FROM users ORDER BY forename";
foreach ($pdo->query($sql) as $row) {
   echo $row['forename'] . " " . $row['surname'] ;
   echo "E-Mail: " . $row['email'] ;
}
?>

Met behulp van ORDER BY kunnen gegevens in de gewenste volgorde worden weergegeven

INSERT

Het aanmaken van databasevermeldingen wordt in phpMyAdmin zelden handmatig uitgevoerd. Meestal worden de gegevens via een script door de webserver in de database geschreven, bijvoorbeeld als een internetgebruiker een online formulier op een website invult of een commentaar in een webshop achterlaat. In beide gevallen wordt op de achtergrond het SQL-commando INSERT gebruikt. Een SQL-statement met het commando INSERT wordt volgens het volgende schema opgesteld:

INSERT INTO tabel (kolom1, kolom2, kolom3) VALUES (waarde1, waarde2, waarde3);

Dit lees je als volgt: roep de genoemde tabel op en voeg de waarden 1, 2 en 3 in de kolommen 1, 2 en 3 in.

Een eenvoudig PHP-script om een nieuwe gegevensset aan onze voorbeeldtabel users toe te voegen, kan er zo uitzien:

<?php
$pdo = new PDO('mysql:host=localhost;dbname=test;charset=utf8', 'root', '');
$sql = "INSERT INTO users (forename, surname, email, password) 
VALUES ('Paddy', 'Irish', 'paddy@irish.com', 'qaywsx')";
if ($pdo->exec($sql) === 1)
  echo "New record created successfully";
?>

Eerst maken we verbinding met de database en slaan we deze op in de variabele $pdo. Daarna specificeren we het SQL-statement en slaan we dit op in de variabele $sql. In regel 5 gebruiken we de pijloperator (->) om ons toegang te verschaffen tot de variabele $pdo en met behulp van de functie exec() het in $sql opgeslagen SQL-statement uit te voeren.

Om er zeker van te zijn dat ons script slechts één gegevensset aan de tabel users toevoegt, controleren we het aantal rijen met behulp van een if-voorwaarde. Deze zorgt ervoor dat de tekst New record created successfully alleen door de webbrowser kan worden weergegeven als het aantal ingevoegde gegevenssets 1 bedraagt. Als het script opnieuw wordt uitgevoerd, wordt de melding niet weergegeven. Dubbele vermeldingen worden voorkomen doordat de waarde email als UNIQUE is ingesteld.

Met behulp van het SQL-commando INSERT voeg je nieuwe gegevenssets toe aan je tabel

Als we nu het overzicht van onze voorbeeldtabel users in de database test oproepen, zien we dat de tabel is aangevuld met gegevensset 5. Het doorlopende identificatienummer en het tijdstempel zijn, zoals de bedoeling is, automatisch toegevoegd.

Indien gewenst kun je via phpMyAdmin een bijgewerkte weergave van je gegevenstabel oproepen (nieuwe gegevensset met rode achtergrond)

UPDATE

Als je een bestaande gegevensset wilt bijwerken, gebruik je het SQL-commando UPDATE volgens het volgende schema:

UPDATE tabel SET kolom1 = waarde1, kolom2 = waarde2 WHERE kolom3 = waarde3

Dit SQL-statement betekent: selecteer de aangegeven tabel en vervang de waarde in kolom 1 door waarde 1 en de waarde in kolom 2 door waarde 2 indien kolom 3 de waarde 3 bevat. Let op: als je de voorwaarde vergeet, overschrijft MySQL de betreffende velden in alle gegevenssets.

We hebben hier dus met een SQL-statement te maken dat een databasebewerking aan een voorwaarde koppelt. Als we dit commando op onze voorbeeldtabel willen toepassen, kan bijvoorbeeld het e-mailadres van de gebruiker John Doe worden geactualiseerd door het volgende PHP-script uit te voeren:

<?php
$pdo = new PDO('mysql:host=localhost;dbname=test;charset=utf8', 'root', '');
$sql = "UPDATE users 
SET email = 'john.doe@example.com', updated_at = NOW() WHERE id = 1";
if ($pdo->exec($sql) === 1)
  echo "Update successful";
?>

In het SQL-statement bepalen we dat de huidige waarde in de kolom email moet worden vervangen door de nieuwe waarde john.doe@example.com indien de waarde in de kolom id 1 bedraagt. Zo wijzigen we alleen de gegevensset met de primaire sleutel 1. Daarnaast werken we in hetzelfde SQL-statement de waarde voor de kolom updated_at bij met behulp van de MySQL-functie NOW(), die de actuele tijdstempel invoegt. Het SQL-statement wordt, net als het vorige statement, uitgevoerd met de coderegel $pdo->exec($sql) en een if-voorwaarde.

SQL-statement met UPDATE-commando en de TIMESTAMP-functie NOW()

Als de update succesvol is, moet phpMyAdmin in het tabblad ‘Browse’ de bijgewerkte tabel weergeven:

De functie NOW() overschrijft het veld updated_at met het actuele tijdstempel (bijgewerkte gegevens met rode achtergrond)

In het voorbeeld hebben we een e-mailadres gewijzigd en de standaardwaarde NULL in de kolom updated_at vervangen door een tijdstempel. Het commando UPDATE maakt het bovendien mogelijk waarden uit een kolom naar een andere kolom te kopiëren. Deze bewerking kan bijvoorbeeld worden gebruikt als we de voorbeeldtabel users willen uitbreiden met de kolom email_registration. Op die manier kunnen we onderscheid maken tussen twee e-mailadressen: een die bij de registratie is gebruikt en een actueel contactadres dat in de loop van de tijd kan veranderen. In eerste instantie zijn beide adressen echter hetzelfde, zodat we de waarden van het ene veld in het andere kunnen kopiëren. Daarvoor maken we met phpMyAdmin eerst een nieuwe kolom email_registration aan via ‘Add column(s)’ in het tabblad ‘Structure’:

In het tabblad ‘Structure’ voeg je via ‘Add column(s)’ op de gewenste positie een nieuwe tabelkolom toe

Om de waarden te kopiëren, gebruiken we het volgende UPDATE-statement:

UPDATE users SET email_registration = email

Omdat we alle gegevenssets willen bijwerken, formuleren we geen voorwaarde voor de update.

<?php
$pdo = new PDO('mysql:host=localhost;dbname=test;charset=utf8', 'root', '');
$sql = "UPDATE users SET email_registration = email";
$affected = $pdo->exec($sql);
echo "$affected rows updated";
?>

Als het script via de webserver wordt uitgevoerd, kopieert het databasemanagementsysteem de waarden van de kolom email voor alle gegevenssets naar de kolom email_registration.

Het script kopieert alle waarden van de kolom email naar de kolom email_registration en geeft het aantal betroffen kolommen weer in de browser

De e-mailadressen van de geselecteerde gegevenssets zijn van de kolom email naar de kolom email_registration gekopieerd

DELETE

Via de SQL-commando DELETE kunnen databasevermeldingen worden gewist. Dit kan worden uitgevoerd door het volgende schema te gebruiken:

DELETE FROM tabel WHERE kolom = waarde

Als je in een database met ID’s werkt, is het voor de hand liggend deze te gebruiken om de vermeldingen te identificeren die je wilt wissen. Als je bijvoorbeeld de 5e vermelding uit onze voorbeeldtabel wilt wissen, ga je als volgt te werk:

<?php
$pdo = new PDO('mysql:host=localhost;dbname=test;charset=utf8', 'root', '');
$sql = "DELETE FROM users WHERE id = 5";
$affected = $pdo->exec($sql);
echo "$affected rows updated";
?>

Het SQL-commando DELETE wist altijd een hele database-rij. Als je alleen waarden in bepaalde kolommen van een gegevensset wilt wissen, kun je dit doen met een UPDATE-statement. Met UPDATE tabel SET kolom = NULL WHERE … kun je aan een kolom de waarde NULL toewijzen – uiteraard op voorwaarde dat voor de desbetreffende kolom de waarde NULL is toegestaan.

Prepared Statements

Met PDO kunnen databasebewerkingen als zogenaamde Prepared Statements worden uitgevoerd. Deze ‘kant-en-klare query’s’ zijn tegenwoordig gebruikelijk in de webontwikkeling en worden ondersteund door alle moderne databasemanagementsystemen.

In de voorgaande voorbeelden hebben we parameterwaarden direct in het SQL-statement overgedragen. Prepared Statements werken daarentegen met tijdelijke aanduidingen die pas later door waarden worden vervangen. Zo kan het databasemanagementsysteem eerst de geldigheid van parameters controleren alvorens deze te verwerken. Prepared Statements bieden, mits ze in de broncode consistent worden toegepast, een effectieve bescherming tegen SQL-injectie. Bij dit aanvalspatroon creëren of wijzigen hackers SQL-opdrachten om aan gevoelige gegevens te komen, gegevens te overschrijven of eigen opdrachten in het systeem uit te voeren.

SQL-injectie is gebaseerd op een bekend beveiligingslek met betrekking tot SQL-databases. Wanneer gebruikersgegevens bijvoorbeeld via $_GET met statische parameters worden overgedragen, geeft dit hackers de mogelijkheid de input aan te vullen met metatekens die ongewilde effecten tot gevolg kunnen hebben als deze zonder maskering in de SQL-interpreter terechtkomen. Dit kan effectief worden voorkomen met geparametriseerde query’s. Prepared Statements fungeren daarbij als sjablonen voor SQL-opdrachten die gescheiden van de eigenlijke parameters aan de database worden overgedragen. Deze valideert de overgedragen gegevens, maskeert metatekens automatisch en voegt de parameters in op de plaats van de tijdelijke aanduidingen in het SQL-statement.

Prepared Statements bieden naast het beveiligingsaspect bovendien betere prestaties. Dit wordt duidelijk wanneer dezelfde SQL-opdracht in een lus met verschillende parameters moet worden uitgevoerd. Als een Prepared Statement eenmaal is geparset, is het beschikbaar in het databasesysteem en moet het daar alleen nog maar met nieuwe parameters worden uitgevoerd. Ingewikkelde query’s kunnen zo aanzienlijk sneller worden uitgevoerd.

In PDO worden Prepared Statements uitgevoerd met behulp van de functie prepare(). Deze functie bereidt een statement voor op de uitvoering en geeft een statementobject terug. Als tijdelijke aanduiding voor de desbetreffende waarden wordt ofwel het vraagteken (?) of een benoemde parameter (named parameter) gebruikt.

Prepared Statements met onbenoemde parameters

Het volgende codevoorbeeld toont de databasebewerking ‘INSERT’ als Prepared Statement met onbenoemde parameters:

<?php
$pdo = new PDO('mysql:host=localhost;dbname=test;charset=utf8', 'root', '');

// SQL-statement voorbereiden
$statement = $pdo->prepare("INSERT INTO users (forename, surname, email, password) 
VALUES (?, ?, ?, ?)");

// Parameters verbinden
$statement ->bindParam(1, $forename);
$statement ->bindParam(2, $surname);
$statement ->bindParam(3, $email);
$statement ->bindParam(4, $password);

// Gegevensrecords invoegen
$forename = "Anders";
$surname = "Andersen";
$email = "anders@andersen.com";
$password = "mnbvc";
if ($statement->execute())
  echo "New record $forename created successfully";

$forename = "Matti";
$surname = "Meikäläinen";
$email = "matti@meikäläinen.com";
$password = "jhgfd";

// Status weergeven
if ($statement->execute())
  echo "New record $forename created successfully";
?>

Eerst creëren we met behulp van de functie prepare() een statementobject van de gewenste query en slaan dit op in de matrix $statement. In plaats van concrete parameterwaarden wordt het vraagteken als tijdelijke aanduiding gebruikt.

Als een SQL-statement uitsluitend tijdelijke aanduidingen bevat, moeten afzonderlijk overgedragen waarden in de volgende code aan het statement worden verbonden. In PHP wordt daarvoor de functie bindParam() gebruikt. We gebruiken de pijloperator (->) om toegang te krijgen tot de bindParam()-methode van het object $statement en hieraan variabelen toe te wijzen (1 komt overeen met het eerste vraagteken, 2 met het tweede enz.).

De gecreëerde SQL-sjabloon kan nu eindeloos vaak met de gewenste parameters worden uitgevoerd. In het huidige voorbeeld specificeren we variabelenwaarden voor twee gegevensrecords. Het voorbereide SQL-statement wordt via execute() uitgevoerd voor elke gegevensrecord.

Prepared Statements met benoemde parameters

Benoemde parameters zijn overzichtelijker dan tijdelijke aanduidingen in de vorm van vraagtekens. Dit zijn aangepaste tijdelijke aanduidingen die naar behoefte volgens het onderstaande schema kunnen worden benoemd:

:voorbeeld

Benoemde parameters mogen geen spaties of koppeltekens (-) bevatten. Gebruik in plaats daarvan de underscore (_).

Het volgende voorbeeld toont de databasebewerking INSERT als Prepared Statement met benoemde parameters:

<?php
$pdo = new PDO('mysql:host=localhost;dbname=test;charset=utf8', 'root', '');

// SQL-statement voorbereiden
$statement = $pdo->prepare("INSERT INTO users (forename, surname, email, password) 
VALUES (:forename, :surname, :email, :password)");

// Parameters verbinden
$statement ->bindParam(':forename', $forename);
$statement ->bindParam(':surname', $surname);
$statement ->bindParam(':email', $email);
$statement ->bindParam(':password', $password);

// Gegevensrecords invoegen
$forename = "Anders";
$surname = "Andersen";
$email = "anders@andersen.com";
$password = "mnbvc";

if ($statement->execute())
  echo "New record $forename created successfully";

$forename = "Matti";
$surname = "Meikäläinen";
$email = "matti@meikäläinen.com";
$password = "jhgfd";
$statement->execute();

// Status weergeven
if ($statement->execute())
  echo "New record $forename created successfully";
?>

In het Prepared Statement zie je de benoemde parameters :forename, :surname, :email en :password. Deze verbinden we via bindParam() aan de variabelen $forename, $surname, $email en $password. In het huidige voorbeeld hebben we zowel de parameters als de variabelen benoemd volgens de kolomtitels van onze voorbeeldtabel. Dit wordt niet gespecificeerd door de syntaxis. Daarom is een consistente benoeming in de zin van een goed leesbare broncode aan te bevelen. De toewijzing van de variabelenwaarden en de uitvoering van het SQL-statement vinden op dezelfde manier plaats als in het voorgaande voorbeeld.

Datum- en tijdfuncties in MySQL

MySQL en MariaDB ondersteunen diverse functies om met datum- en tijdnotaties te werken. Een volledige lijst hiervan vind je hier. In onze MySQL-tutorial voor dummies beperken we ons tot een paar van deze functies.

Een mogelijke toepassing voor tijd- en datumfuncties in MySQL zijn bijvoorbeeld databasequery’s waarbij alle gegevensrecords moeten worden uitgelezen die op een bepaalde dag zijn aangemaakt.

Het volgende script geeft alle gegevensrecords uit onze voorbeeldtabel users weer die vandaag zijn aangemaakt:

<?php
$pdo = new PDO('mysql:host=localhost;dbname=test;charset=utf8', 'root', '');
$sql = "SELECT forename, surname, email FROM users WHERE DATE(created_at) = CURDATE()";
foreach ($pdo->query($sql) as $row) {
   echo $row['forename'] . " " . $row['surname'];
   echo "E-Mail: " . $row['email'];
}
?>

Om uitsluitend vermeldingen van vandaag weer te geven, gebruiken we in het SQL-statement de volgende voorwaarde:

DATE(created_at) = CURDATE()

Eerst extraheren we met behulp van de functie DATE() de datum uit de tijdstempel die in de kolom created_at is opgeslagen. In de volgende stap vergelijken we deze datum met de datum van vandaag. De opdracht SELECT kiest zodoende alleen de vermeldingen waarvan de tijdstempel overeenkomt met de huidige datum.

We kunnen ook de vermelding selecteren die we op 16-12-2016 hebben bijgewerkt. Daarvoor moeten we alleen de voorwaarde van ons SQL-statement aanpassen:

SELECT forename, surname, email FROM users WHERE DATE(updated_at) = '2016-12-16'

In dit geval wordt de uit het tijdstempel geëxtraheerde datumnotitie vergeleken met een concrete datum. Daarnaast kun je query’s beperken tot een bepaald jaar, een maand of een dag. Het volgende statement heeft betrekking op alle vermeldingen van de tabel users die in december zijn aangemaakt:

SELECT forename, surname, email FROM users WHERE month(created_at) = 12";

Naast het gelijkteken ondersteunt SQL in voorwaarden ook de volgende vergelijkingsoperators:

Bovendien kunnen meerdere voorwaarden door middel van logische operators worden gekoppeld:

Het volgende statement selecteert bijvoorbeeld alle vermeldingen die na februari en voor april zijn aangemaakt:

SELECT forename, surname, email FROM users WHERE MONTH(created_at) > 2 AND MONTH(created_at) < 4";

Tot nu toe hebben we datum- en tijdnotities in onze database volgens de vastgelegde opmaak opgeslagen. In MySQL en MariaDB ben je echter niet aan deze opmaak gebonden. De functie DATE_FORMAT() biedt je de mogelijkheid datum- en tijdnotities met optionele parameters naar wens op te maken.

<?php
$pdo = new PDO('mysql:host=localhost;dbname=test;charset=utf8', 'root', '');
$sql = "SELECT forename, surname, DATE_FORMAT(created_at,'%D %M %Y') 
AS created_at FROM users WHERE id=1";
foreach ($pdo->query($sql) as $row) {
echo $row['forename'] . " " . $row['surname'] ." your profile was created at: ". 
$row['created_at'] .".
";
}
?>

Onderstaande tabel toont mogelijke parameters voor de functie DATE_FORMAT() volgens de documentatie van MySQL.

MySQL-foutmeldingen

Als een script niet zoals gewenst kan worden uitgevoerd, komt dit meestal door syntactische fouten in de broncode of onjuist benoemde tabellen, kolommen en variabelen. De databaseserver geeft in dit geval echter niet altijd een foutmelding. Vaak blijft het gewenste resultaat uit zonder verwijzing naar de mislukte bewerking.

Met errorInfo() stelt PDO daarom een functie ter beschikking waarmee uitgebreide foutgegevens met betrekking tot de laatste bewerking doelgericht kunnen worden opgevraagd – bijvoorbeeld door deze in de webbrowser te laten weergeven.

In het volgende script voor het bijwerken van een e-mailadres wordt de functie errorInfo() gebruikt in combinatie met een if-lus. De voorwaarde is de correcte uitvoering van het SQL-statement. Als het statement zonder fouten wordt uitgevoerd, geeft de webserver de melding Update succesful weer. Anders wordt de onder else genoemde code uitgevoerd.

In het huidige voorbeeld informeren wij de gebruiker dat er een SQL-fout is opgetreden en geven het betreffende SQL-statement alsook uitgebreide foutgegevens weer via errorInfo():

<?php
$pdo = new PDO('mysql:host=localhost;dbname=test;charset=utf8', 'root', '');
$statement = $pdo -> prepare("UPDATE user SET password = :password, updated_at = NOW() WHERE id = 1");
$statement ->bindParam(':password', $password);
$password = "YXDli_89%s";
if ($statement ->execute()){
   echo "Update successful";
} else {
   echo "SQL Error 
";
   echo $statement->queryString."
";
   echo $statement->errorInfo()[2];
}
?>

Als we het script via de webserver uitvoeren, wordt de volgende informatie weergegeven:

SQL Error 
UPDATE user SET password = :password, updated_at = NOW() WHERE id = 1
Table 'test.user' doesn’t exist

De SQL-opdracht UPDATE verwijst naar een tabel met de naam user. Onze voorbeeldtabel heet echter users. Daarom kan de SQL-server de opgevraagde tabel niet vinden en geeft de melding Table 'test.user' doesn’t exist weer. De oorzaak van de fout is dus een tikfout in het SQL-statement die snel kan worden verholpen.

Bij retourwaarden van de functie errorInfo() hebben we te maken met een matrix dat uit drie elementen bestaat:

[0] = SQL-foutcode

[1] = Stuurprogrammaspecifieke foutcode

[2] = Stuurprogrammaspecifieke foutmelding

Welke gegevens we via errorInfo() opvragen, leggen we vast door het gewenste element tussen vierkante haakjes te plaatsen.

Weergave van de foutgegevens in de webbrowser

In de praktijk worden gedetailleerde foutgegevens zelden in de webbrowser weergegeven. Met deze gegevens kunnen eindgebruikers doorgaans niet zoveel. Potentiële hackers kunnen foutmeldingen daarentegen gebruiken om SQL-query’s te begrijpen en zo de zwakke plekken van een toepassing op te sporen. Daarom is het raadzaam gebruikers slechts te informeren dat er een fout is opgetreden, maar concrete foutgegevens intern op te slaan. Dit kan bijvoorbeeld met behulp van de volgende code:

<?php
$pdo = new PDO('mysql:host=localhost;dbname=test;charset=utf8', 'root', '');
$statement = $pdo->prepare("UPDATE user SET password = :password, updated_at = NOW() WHERE id = 1");
$statement->bindParam(':password', $password);
$password = "YXDli_89%s";
if ($statement->execute()) {
   echo "Update successful";
} else {
  echo "Helaas is er een fout opgetreden bij de wijziging van uw wachtwoord. Neem contact op met onze systeembeheerder via: 
admin@website.nl.";
  $error = DATE("Y-m-d H:i:s") . " - " . __FILE__ . " - " . $statement->queryString . " - " . $statement->errorInfo()[2] . " \r\n";
  file_put_contents("sqlerrors.log", $error, FILE_APPEND); 
}
?>

In plaats van de retourwaarde van de functie errorInfo() via echo in de webbrowser weer te geven, slaan wij deze samen met de tijdstempel, het bestandspad en het betreffende SQL-statement op in de variabele $error.

De PHP-functie DATE() geeft de UNIX-tijdstempel weer in de aangegeven opmaak. De zogenaamde magische constante __FILE__ levert ons het volledige pad naar het bestand text.php. Het huidige SQL-statement roepen we net als in het vorige voorbeeld op via $statement->queryString. Daarna worden de in $error opgeslagen bestanden met behulp van de functie file_put_contents() als tekst opgeslagen in het bestand sqlerrors.log in de map htdocs.

De opgeslagen foutgegevens vind je in het bestand sqlerrors.log

JOIN

Zoals in het hoofdstuk over relationele databases al aangegeven, kunnen we gegevens uit meerdere tabellen tegelijk opvragen. Nu je bekend bent met de basisbewerkingen van databases, laten we zien hoe je verschillende tabellen van je database kunt verbinden tot een join (‘verbinding’ of ‘samenvoeging’).

Je kunt tabellen samenvoegen in het kader van een query met behulp van het SQL-commando JOIN. Daarbij worden twee of meer genormaliseerde tabellen via gemeenschappelijke kolommen verbonden. Dit is mogelijk via een refererende sleutel.

We laten zien hoe je databasetabellen verbindt aan de hand van het volgende voorbeeld:

Deze tabel bevat een aantal populaire liedjes uit de jaren ‘60 en dient als voorbeeld voor een slecht databaseontwerp.

Het valt meteen op dat deze tabel veel redundante gegevensvelden bevat. Deze kunnen we verhelpen door de gegevens te verdelen in gescheiden tabellen en deze tabellen met behulp van refererende sleutels te koppelen.

Normaalvormen

Een goed databaseontwerp kenmerkt zich door zo min mogelijk redundantie. Dubbele vermeldingen kunnen worden vermeden door gegevenstabellen te normaliseren. Op het gebied van het relationele databasemodel hebben drie op elkaar gebaseerde normaalvormen vaste voet gekregen, die regels vastleggen voor de optimale structurering van gegevens.

1e normaalvorm

Een tabel voldoet aan de 1e normaalvorm als alle kenmerkwaarden atomisch zijn. Kenmerkwaarden zijn atomisch als ze slechts één gegeven bevatten. Dit kunnen we duidelijk maken aan de hand van een foutief voorbeeld.

Kijk maar eens naar de kolommen album_title en interpret in de tabel album. In plaats van alle gegevens in de tabel in aparte kolommen te vermelden, hebben we ervoor gekozen informatie over het verschijningsjaar van een album en over het bestaan van een band gewoon tussen haakjes achter de albumtitel of de band te schrijven. Vroeg of laat krijgen we hier spijt van, bijvoorbeeld wanneer we alle titels willen opvragen die in een bepaald jaar zijn verschenen.

Daarom is het raadzaam gegevenstabellen altijd volgens de regels van de 1e normaalvorm op te stellen. Volgens deze regels ziet onze voorbeeldtabel er dan als volgt uit:

Alle gegevens zijn nu gescheiden en dus zonder problemen op te vragen. Onze tabel bevat echter nog steeds redundanties. In de volgende stappen laten we zien hoe we deze verhelpen.

2e normaalvorm

Een tabel voldoet aan de 2e normaalvorm als alle voorwaarden van de 1e normaalvorm zijn vervuld én elk niet-sleutelkenmerk functioneel afhankelijk is van de primaire sleutel van de tabel.

Vaak hebben gegevenstabellen slechts één kolom die als primaire sleutel dient. Zulke tabellen voldoen automatisch aan de 2e normaalvorm, mits de voorwaarden van de 1e normaalvorm zijn vervuld. Het komt echter ook voor dat de primaire sleutel van een tabel uit twee kolommen bestaat. Dit is bij onze voorbeeldtabel het geval.

Om een bepaalde titel uit de kolom title van de tabel album op te roepen, hebben we ten eerste de album_id nodig en ten tweede het titelnummer uit de kolom track. De titel Sympathy for the Devil kan bijvoorbeeld worden opgeroepen met de primaire sleutel album_ID = 3 AND track = 1. We hebben hier dus te maken met een samengestelde primaire sleutel. Deze is echter alleen nodig bij query’s met betrekking tot de kolom title. De kolommen album_title, released, interpret en years_active zijn alleen afhankelijk van de album_id. Deze kolommen zijn dus niet volledig functioneel afhankelijk van de complete primaire sleutel. De voorwaarden van de 2e normaalvorm zijn hierbij niet vervuld.

Dit kunnen we echter aanpassen door de kolom title in een nieuwe tabel op te slaan en via een refererende sleutel (album_id) aan de eerste tabel te koppelen.

Dit kunnen we echter aanpassen door de kolom title in een nieuwe tabel op te slaan en via een refererende sleutel (album_id) aan de eerste tabel te koppelen.

De aangepaste tabel album bevat nu een eendelige primaire sleutel en voldoet dus aan de voorwaarden van de 2e normaalvorm. De nieuwe tabel title bevat uitsluitend de niet-sleutelkolom title. Deze is volledig functioneel afhankelijk van de primaire sleutel (album_id en track) en voldoet dus eveneens aan de 2e normaalvorm.

Maar ook in de 2e normaalvorm heeft onze gegevenstabel album nog redundante vermeldingen.

3e normaalvorm

Om aan de 3e normaalvorm te voldoen, moeten alle voorwaarden van de 2e normaalvorm (en dus ook van de 1e normaalvorm) zijn vervuld. Daarnaast geldt dat geen enkel niet-sleutelkenmerk transitief afhankelijk mag zijn van een sleutelkenmerk. Deze voorwaarde klinkt ingewikkeld, maar is eenvoudig uit te leggen: een transitieve afhankelijkheid bestaat wanneer een niet-sleutelkenmerk afhankelijk is van een ander niet-sleutelkenmerk.

Dit is het geval bij onze voorbeeldtabel album. Deze bevat de kolommen interpret en years_active. Terwijl de band kan worden bepaald aan de hand van de album_id, is de bestaansperiode van de band wederom afhankelijk van de band en dus transitief afhankelijk van de album_id. Dit heeft als nadeel dat het databasemanagementsysteem, telkens als er een nieuw album van een reeds ingevoerde band wordt ingevoerd, automatisch een redundante waarde in de kolom years_active opslaat.

Om aan de voorwaarden van de 3e normaalvorm te voldoen en dus alle redundanties uit onze tabel te verwijderen, moeten we ook de kolom interpret, inclusief years_active, in een aparte tabel opslaan en via een refererende sleutel interpret_id aan de tabel album koppelen.

We hebben dan drie genormaliseerde tabellen: interpret, album en title.

Als we nu een bepaalde titel, inclusief informatie over het album en de band, willen opvragen in de database, moeten we de drie aparte tabellen verbinden met behulp van het SQL-commando JOIN en de desbetreffende refererende sleutels.

Refererende sleutels via phpMyAdmin vastleggen

Als je als database-engine InnoDB hebt gekozen, kun je de relaties van refererende sleutels gemakkelijk vastleggen via de grafische gebruikersinterface van de beheersoftware phpMyAdmin. Daarbij kan de primaire sleutel van een tabel in een willekeurig aantal andere tabellen als refererende sleutel worden gebruikt.

In ons voorbeeld hebben we twee verbindingen nodig om de drie genormaliseerde tabellen album, interpret, en title te koppelen:

• Voor de eerste verbinding gebruiken we de primaire sleutel album_id van de tabel album als refererende sleutel in de tabel title.
• Voor de tweede verbinding gebruiken we de primaire sleutel interpret_id van de tabel interpret als refererende sleutel in de tabel album.

Deze relaties van refererende sleutels worden door onderstaande afbeelding verduidelijkt:

De genormaliseerde tabellen album, title en interpret gekoppeld d.m.v. refererende sleutels

Bij de koppeling van de tabellen geldt dat een kolom die als refererende sleutel moet fungeren, de kenmerken UNIQUE of INDEX moet hebben.

De relatie tussen primaire sleutels en refererende sleutels komt normaal gesproken overeen met het relatietype 1:n. Elk gegevensveld in de kolom van de primaire sleutel van tabel A staat in relatie met een willekeurig aantal (n) gegevensvelden in de kolom van de refererende sleutel van tabel B. Maar elk gegevensveld in de kolom van de refererende sleutel van tabel B heeft altijd betrekking tot slechts één gegevensveld in de kolom van de primaire sleutel van tabel A. Zo hebben we in de kolom van de primaire sleutel album_id van de tabel album vier vermeldingen die via de refererende sleutel title.album_id gekoppeld zijn aan acht vermeldingen van de tabel title.

Primaire sleutels en refererende sleutels hebben een 1:n-relatie

Om de gewenste koppelingen te genereren, maken we de tabellen album, interpret en title aan in phpMyAdmin en leggen we de primaire sleutels bij het aanmaken van de tabellen vast met de optie ‘Index’. Let erop dat kolommen die later als refererende sleutel moeten dienen, eveneens via de optie ‘Index’ gemarkeerd zijn als INDEX of UNIQUE. Voor 1:n-relaties is eigenlijk alleen INDEX geschikt, omdat de waarden in een UNIQUE-veld niet vaker mogen voorkomen.

In de volgende stap bepalen we de refererende sleutels. Dit laten we zien aan de hand van de tabel album. We selecteren de tabel in het navigatievenster en gaan naar het tabblad Structure in de menubalk. Hier vind je de knop ‘Relation view’ (relatieweergave):

De knop ‘Relation view’ (met rode achtergrond) in het tabblad ‘Structure’

Relaties van refererende sleutels bepaal je in de relatieweergave van een gegevenstabel via het invoerveld ‘Foreign key constraints’:

Relaties van refererende sleutels bepaal je via het invoerveld ‘Foreign key constraints’

In de tabel album moet de kolom interpret_id fungeren als refererende sleutel die is gebaseerd op de primaire sleutel interpret_id van de tabel interpret.

Onder ‘Foreign key constraints’ (INNODB) bepaal je op welke primaire sleutel de refererende sleutel betrekking heeft

We selecteren in de vervolgkeuzelijst onder ‘Column’ de kolom interpret_id als refererende sleutel. Let op dat hier alleen kolommen worden weergegeven die als INDEX, UNIQUE of PRIMARY zijn gemarkeerd. In het driedelige invoerveld ‘Foreign key constraint (InnoDB)’ bepalen we op welke primaire sleutel, van welke tabel, in welke database onze refererende sleutel gebaseerd moet zijn. We selecteren de volgende opties:

Database: test

Tabel: interpret

Primaire sleutel: interpret_id

Het veld ‘Constraint name’ kan leeg blijven. Het databasemanagementsysteem vult hier automatisch een naam in. We moeten echter wel bepalen hoe een tabel met refererende sleutel zich gedraagt zodra de primaire sleutel die aan de refererende sleutel ten grondslag ligt, wordt gewijzigd of gewist.

Als bijvoorbeeld een band uit de bovenliggende tabel interpret wordt gewist, verdwijnt ook de primaire sleutel die met deze vermelding verbonden is. Daarom moet worden bepaald wat er gebeurt met vermeldingen die via refererende sleutels betrekking hebben op deze vermelding – in ons voorbeeld dus albums van een band.

Om het gedrag van een tabel met refererende sleutel in het geval van een UPDATE of DELETE te bepalen, heb je in MySQL en MariaDB vier opties.

• RESTRICT - De optie RESTRICT sluit een wijziging van de bovenliggende tabel uit indien er andere tabellen bestaan die naar deze tabel verwijzen. In ons geval zou dat betekenen dat een gegevensset in de tabel interpret niet kan worden gewist, als hieraan gegevenssets in de tabel album zijn gekoppeld.
• CASCADE - De optie CASCADE zorgt ervoor dat een wijziging in de bovenliggende tabel wordt doorgegeven aan alle tabellen die betrekking hebben op deze bovenliggende tabel. Als we bijvoorbeeld de interpret_id van de band The Rolling Stones van 2 in 8 veranderen, wordt deze wijziging bij de optie CASCADE ook toegepast op alle tabellen die de interpret_id als refererende sleutel gebruiken. Als een vermelding in de bovenliggende tabel wordt gewist, heeft dit tot gevolg dat ook alle gegevenssets die met deze vermelding verbonden zijn in andere tabellen worden gewist. Let op: op deze manier kan het wissen van een enkele vermelding het verdwijnen van talrijke gegevens tot gevolg hebben.
• SET NULL - Als je de optie SET NULL kiest, wordt de waarde van de kolom van de refererende sleutel op NULL gezet, zodra de primaire sleutel in de bovenliggende tabel wordt gewijzigd of gewist.
• NO ACTION - In MySQL is de optie NO ACTION een equivalent van de optie RESTRICT.

Als je de gewenste optie voor de relatie van de refererende sleutel hebt geselecteerd, bevestig je de invoer door op ‘Save’ te klikken. Het databasemanagementsysteem geeft de nieuwe relatie automatisch een naam.

Alle vastgelegde relaties van refererende sleutels worden weergegeven onder ‘Foreign key constraint’ in het tabblad ‘Structure’

JOIN-typen in MySQL en MariaDB

Dankzij relaties van refererende sleutels kun je gegevens uit verschillende tabellen tegelijkertijd oproepen met slechts één SQL-statement. Daarvoor zijn er in MySQL en MariaDB vier JOIN-typen beschikbaar:

• INNER JOIN - Bij een INNER JOIN zoekt het databasemanagementsysteem naar gemeenschappelijke vermeldingen in de via JOIN verbonden tabellen. Er worden alleen gegevenssets gescand die overeenstemmingen bevatten, oftewel waarin de waarden in de gekoppelde kolommen (primaire sleutel en refererende sleutel) van beide tabellen overeenkomen.
• OUTER JOIN - Bij een OUTER JOIN maakt men onderscheid tussen de linker- en rechtergegevenstabel. Anders dan bij de INNER JOIN worden niet alleen de gegevenssets gescand die overeenstemmingen in beide tabellen hebben, maar ook alle overige gegevenssets van de rechter- of de linkertabel.
• LEFT JOIN - Alle gegevenssets van de linkertabel worden gescand, en alle gegevenssets van de rechtertabel die overeenstemmingen bevatten.
• RIGHT JOIN - Alle gegevenssets van de rechtertabel worden gescand, en alle gegevenssets van de linkertabel die overeenstemmingen bevatten.

In onze MySQL basics tutorial beperken wij ons tot de INNER JOIN.

De syntaxis van een INNER JOIN volgt het volgende basisschema:

SELECT * FROM tabel1
INNER JOIN tabel2 ON tabel1.refererende sleutel = tabel2.primaire sleutel
WHERE kolom = waarde

De SQL-opdracht SELECT in combinatie met het jokerteken * zorgt ervoor dat het databasemanagementsysteem de waarden selecteert van alle kolommen waarvoor de voorwaarden van de ON- en de WHERE- clausule gelden.

Omdat het een INNER JOIN betreft, worden alleen de gegevenssets uit de database gehaald die een overeenstemming tussen de refererende sleutel van tabel1 en de primaire sleutel van tabel2 hebben. Bovendien kan met behulp van de WHERE-clausule een optionele filterfunctie worden toegepast.

Dit kunnen we duidelijk maken aan de hand van een voorbeeld dat betrekking heeft op onze genormaliseerde tabellen album, interpret en title:

?php
$pdo = new PDO('mysql:host=localhost;dbname=test;charset=utf8', 'root', '');
$sql = "SELECT * FROM album
INNER JOIN interpret ON album.interpret_id = interpret.interpret_id";
foreach ($pdo->query($sql) as $row) {
   echo $row['album_title'] . " was released by " . $row['interpret'] . " in " . $row['released'];
}
?

Het voorbeeldscript toont een INNER JOIN, waarbij de tabel album wordt verbonden met de tabel interpret. We vragen alleen de gegevenssets op waarbij een overeenstemming bestaat tussen de primaire sleutel en de refererende sleutel.

album.interpret_id = interpret.interpret_id

In ons bestand zijn dat alle gegevenssets (een LEFT of RIGHT JOIN had daarom hetzelfde resultaat gehad). Daarna geven we de opgevraagde waarden weer in de browser met behulp van een foreach-lus en de taalconstructie echo.

Het voorbeeldscript vraagt alle albums in de database op, inclusief band en het verschijningsjaar

Met een SQL-statement hebben we dus de gegevens over de band uit de tabel interpret en de gegevens over de albumtitel en het verschijningsjaar van de plaat uit de tabel album opgevraagd.

Welke gegevenssets van de joins worden opgevraagd, kan met een voorwaarde in de WHERE-clausule worden ingeperkt. Als we bijvoorbeeld alleen de albums willen opvragen die in het jaar 1968 zijn verschenen, gaan we als volgt te werk:

?php
$pdo = new PDO('mysql:host=localhost;dbname=test;charset=utf8', 'root', '');
$sql = "SELECT * FROM album
INNER JOIN interpret ON album.interpret_id = interpret.interpret_id WHERE released = 1968";
foreach ($pdo->query($sql) as $row) {
   echo $row['album_title'] . " was released by " . $row['interpret'] . " in " . $row['released'];
}
?

Door de voorwaarde WHERE released = 1968 beperken we de weergave in de browser tot één album. Beggars Banquet van de Rolling Stones is het enige album in onze nog overzichtelijke database dat in het jaar 1968 is verschenen.

Met de voorwaarde in de WHERE-clausule beperken we de weergave tot één gegevensset

Met behulp van het JOIN-commando kunnen een willekeurig aantal tabellen worden gekoppeld. In het volgende voorbeeld verbinden we de tabel album in een INNER JOIN met de tabellen interpret en title, om alle gegevens over de liedjes, die in de database zijn opgeslagen, te laten weergeven.

?php
$pdo = new PDO('mysql:host=localhost;dbname=test;charset=utf8', 'root', '');
$sql = "SELECT * FROM album
INNER JOIN interpret ON album.interpret_id = interpret.interpret_id 
INNER JOIN title ON album.album_id = title.album_id";
foreach ($pdo->query($sql) as $row) {
   echo $row['title'] . " was released by " . $row['interpret'] . " as Track " . $row['track'] . 
" on " . $row['album_title'] . " in " . $row['released'] . ";
}
?

Met de SQL-opdracht JOIN kun je zoveel gegevenstabellen koppelen als je maar wilt

Ook hier kunnen we eventueel een WHERE-clausule met een filterfunctie instellen; bijvoorbeeld als we alleen de gegevens over track 7 op het album ‘Abbey Road’ willen opvragen.

?php
$pdo = new PDO('mysql:host=localhost;dbname=test;charset=utf8', 'root', '');
$sql = "SELECT * FROM album
INNER JOIN interpret ON album.interpret_id = interpret.interpret_id 
INNER JOIN title ON album.album_id = title.album_id WHERE track = 7 AND album_title = 'Abbey Road'";
foreach ($pdo->query($sql) as $row) {
echo $row['title'] . " was released by " . $row['interpret'] . " as Track " . $row['track'] .
 " on " . $row['album_title'] . " in " . $row['released'];
}
?

INNER JOIN met twee door een logische AND gekoppelde voorwaarden

Vergeet niet dat de tabel title een meerdelige primaire sleutel heeft. Als we naar een bepaald nummer willen verwijzen, hebben we naast het tracknummer de album_id nodig, die samen met de titel van het album is opgeslagen in de tabel album.

Van beginner tot professional

Onze MySQL-tutorial voor dummies kan worden gezien als stoomcursus die je de basics van op SQL gebaseerde databasesystemen bijbrengt en aan de hand van praktische voorbeelden eenvoudige databasebewerkingen begrijpelijk maakt. Mocht je geïnteresseerd zijn in toepassingsmogelijkheden die de hierboven beschreven mogelijkheden te boven gaan, dan raden wij aan de documentatie van de DBMS MySQL, MariaDB en Node.js te lezen. Daarnaast vind je op internet talrijke websites die tutorials en toepassingsvoorbeelden van het populaire databasemanagementsysteem aanbieden.

Daarnaast is het internetplatform Stack Overflow zeker aan te bevelen. Het is een platform waarop een actieve community van meer dan 6,5 miljoen ontwikkelaars discussieert over vragen en problemen rondom softwareontwikkeling.