In les 4 leer je met behulp van PHPMyAdmin de mogelijkheden van MySQL databases en SQL verkennen. Het eindproduct van les 4 is een database met daarin één tabel waar we informatie in kunnen opslaan en van kunnen opvragen.

Inleiding

MySQL is misschien wel de snelste, goedkoopste, eenvoudigste en meest betrouwbare database, die ook nog eens de meeste faciliteiten heeft en verkrijgbaar is voor zowel unix, windows, linux, enzovoorts.

Wat is een database eigenlijk? Een database is een afzonderlijke toepassing die een verzameling gegevens opslaat. Elke database heeft één of meer verschillende mogelijkheden voor het maken, benaderen, beheren en doorzoeken van de gegevens die er in zitten. Andere vormen van gegevensopslag kunnen ook worden gebruikt, maar als het op opslag van (rich-)tekst aankomt, wordt het gebruik van een database sterk aangeraden.

Databases en PHP vormen een onafscheidelijk duo. Een van de grote voordelen van PHP is dan ook de gemakkelijke databaseconnectiviteit die het biedt.

In les 5 wordt aandacht besteed aan het werken met een database vanuit eigen ontwikkelde PHP code. In de les van vandaag wordt op een andere manier gewerkt met een database, namelijk met het pakket PHPMyAdmin.

Werken met PHPMyAdmin

PHPMyAdmin openen

Tijdens les 4 wordt er vanuit gegaan dat je werkt met de XAMPP webserver. Om PHPMyAdmin te benaderen, moet je er voor zorgen dat zowel Apache als MySQL is gestart:

Wanneer alles “groen” is, ga je met FireFox naar http://localhost/phpmyadmin. Mogelijk kom je het volgende scherm tegen. Vul daarin je gebruikersnaam en wachtwoord is (bij standaard XAMPP installaties is de gebruikersnaam ‘root’ en het wachtwoord leeg:

Wanneer je succesvol weet in te loggen, of als je bovenstaand scherm niet tegenkomt, kom je in het “overzichtsscherm” van PHPMyAdmin terecht:

Aan de linkerkant van het scherm zie je het overzicht van databases dat op dit moment beschikbaar is.

Een database aanmaken en openen

Voor deze cursus is het de bedoeling dat je een nieuwe database aanmaakt waarin je gaat experimenteren met MySQL. De naam die je aan deze database geeft is “phpcursus”. Je maakt een database aan door op “databases” boven in het scherm te klikken en het volgende in te vullen en op “create” te klikken:

Wanneer de database aangemaakt is, zie je hem links in het overzichtsscherm van PHPMyAdmin verschijnen. Je kunt de inhoud van de database bekijken door er op te klikken.

Een tabel aanmaken in een geopende database

Wanneer je een lege database opent krijg je direct de mogelijkheid om een tabel aan te maken in de database. Hierbij moet je een naam voor de tabel verzinnen en moet je aangeven wat het aantal kolommen van de tabel is. Voor deze cursus maak je een tabel aan met als naam “guestbook” en het aantal kolommen zal 6 bedragen. Hier over zo dadelijk meer. Als je er klaar voor bent klik je op “Go”:

Van mensen die een bericht in ons gastenboek gaan plaatsen, willen we een aantal zaken weten. Deze worden hieronder besproken, maar eerst een screenshot van wat je moet invullen bij het aanmaken van de tabel “guestbook”:

1. De id van het bericht: om berichten later te kunnen verwijderen of te kunnen wijzigen, is het belangrijk dat je het bericht kunt benaderen aan de hand van een uniek nummer. We nemen een kolom “id” op in de tabel waar we met behulp van “auto increment” (A_I) er voor zorgen dat elk nieuw aangemaakt bericht automatisch een identificatienummer toebedeeld krijgt. De kolom “id” zal dus een nummer bevatten. Dat geven we aan door bij “type” te kiezen voor “int”. “int” staat voor integer. Door een lengte van 5 aan te geven, zorgen we er voor dat er maximaal 99.999 berichten in het gastenboek kunnen worden geplaatst.
2. De name van de afzender: we willen graag weten wie de afzender is van het bericht. Vandaar dat we een kolom “name” aanmaken. Aangezien een naam meestal uit tekst bestaat, kiezen we als type voor “varchar”.  Hiermee geef je aan dat de naam van de afzender zowel letters als cijfers mag bevatten. De maximum lengte van de naam beperken we tot 40 tekens.
3. De email van de afzender: om de afzender te kunnen bereiken, is het handig dat de afzender een e-mailadres kan achterlaten.
4. De eventuele website van de afzender.
5. De postdate van het bericht is de datum+tijd waarop het bericht is ingevuld. Deze kolom zal een unix timestamp bevatten van wanneer het bericht is ingevuld door de afzender van het bericht. Omdat de unix timestamp een cijfer vertegenwoordigd, kiezen wel als type wederom voor INT.
6. De message kolom bevat het daadwerkelijke bericht dat de afzender in het gastenboek wil plaatsen. Het bericht kan zowel cijfers als letters bevatten, maar we kiezen deze keer niet voor varchar als type. Dat heeft er mee maken dat een kolom die van het type varchar is, niet meer dan 256 karakters mag bevatten. Als je een kolom als type “text” meegeeft, kan er véél meer tekst in de kolom (tot 16.700.000 tekens).

Voor deze cursus wordt er geen aandacht besteed aan de “storage engine” die wordt gebruikt voor het opslaan van gegevens in de tabel. Als deze op “InnoDB” of “MyISAM” staat is dat prima.

Als je klaar bent met het definieren van de kolommen, klik je op “save”. Als alles goed gaat, krijg je kort de melding “table ‘guestbook’ has been created”. De pagina wordt ververst, en je ziet de tabel verschijnen in het overzicht van tabellen voor de database “phpcursus”.

Een rij (of record) toevoegen in een tabel

Aangezien we nog niet zelf een gastenboek hebben gebouwd waarmee we berichten kunnen toevoegen en verwijderen, moeten we ons op dit moment redden met PHPMyAdmin. In het overzicht van tabellen vind je achter elke tabel een aantal knopjes:

Met behulp van de knop “insert” is het mogelijk om data in de tabel op te slaan. Wanneer je hier op klikt krijg je het volgende scherm met mogelijkheden te zien:

In de kolom “value” kun je voor elke tabelkolom een betreffende waarde invullen. In de kolom “function” kun je voor de betreffende tabelkolom een functie laten uitvoeren. Hier doen we tijdens de basiscursus niets mee.

Aangezien we bij het aanmaken van de tabel hebben aangegeven dat het id automatisch moet worden ingevuld, laten we de “value” hiervoor leeg. De andere “values” vul je met je naam, e-mailadres, website, postdate (doe voor de lol 1234567890) en een bericht. Als je klaar bent klik je op “Go”. Als alles goed gaat zie je kort de melding “1 row inserted” verschijnen.

De inhoud (= rijen of records) van een tabel bekijken

Aangezien je nu een of meer berichten hebt toegevoegd met de hiervoor beschreven methode, kun je nu op de knop “browse” achter je gastenboek klikken om de inhoud van de tabel te bekijken:

Het mooie van PHPMyAdmin is dat je, nadat je een betreffende knop hebt aangeklikt, vaak ziet welke vraag of instructie (officieel heet dit query) hiermee naar de database wordt gestuurd. Blijkbaar wordt met de query “SELECT * FROM guestbook LIMIT 0,30” de instructie gestuurd om de eerste 30 berichten uit het gastenboek te tonen. Over queries volgt dadelijk meer uitleg.

Zoals je ziet krijg je een overzicht van berichten te zien en kan je berichten wijzigen, kopiëren of verwijderen met behulp van PHPMyAdmin.

Zelf queries (SQL) uitvoeren in de database

Als je goed kijkt, zie je bovenin het scherm een knop, eigenlijk tab genaamd “SQL” staan. Wanneer je deze tab aanklikt, kan je zelf queries loslaten op de database. Neem de SQL uit onderstaande screenshot over, klik op GO en bewonder het resultaat.

Hieronder wordt dieper ingegaan op SQL en wordt er vanuit gegaan dat je deze via de knop “SQL” steeds uitvoert op de database.

Structured Query Language in MySQL

Om met een MySQL database te kunnen communiceren, gebruik je SQL. SQL is een min of meer universele taal waarmee je “vragen aan databases” kunt stellen. Wanneer je via SQL met MySQL databases kunt werken, kun je meestal ook al snel uit de voeten met SQL in Microsoft of Oracle databases.

Er zijn een aantal taken (statements) die je na deze cursus met behulp van SQL in een database moet kunnen uitvoeren: SELECT, INSERT, UPDATE en DELETE. Deze worden hieronder een voor een uitgelegd. Vooral bij UPDATE en DELETE moet je oppassen. Als je bij deze statements niet goed specificeert (met behulp van WHERE) voor welke records in de tabel de query moet worden uitgevoerd, heb je kans dat àlle data in je tabel wordt beïnvloed of zelfs verwijderd. Proceed with caution.

SELECT statement: vraag gegevens op

Basis SELECT

De meest eenvoudige query voor het opvragen van gegevens uit onze guestbook tabel is de volgende:

SELECT * FROM guestbook

Bovenstaande query bestaat uit de volgende onderdelen:

1. SELECT: hiermee geef je aan dat je iets wilt opvragen
2. *: selecteer alle kolommen. Als je * vervangt door een of meerdere kolomnamen gescheiden door komma’s, krijg je alleen de betreffende kolommen terug.
3. FROM guestbook: in dit gedeelte geef je aan van welke tabel of welke tabellen je gegevens wilt opvragen.

Er worden in de bovenstaande query geen eisen gesteld aan wèlke rijen/records (met behulp van WHERE) en hoeveel rijen/records (met behulp van LIMIT x,y, waarbij x een startpunt vertegenwoordigd en y het aantal records dat vanaf dat startpunt moet worden meegenomen) moeten worden opgehaald.

Specifieke kolommen SELECTeren

Met behulp van de query SELECT name, email, website FROM guestbook vraag je alleen de naam, e-mailadres en website op van het gastenboek.

Specifieke rijen SELECTeren met behulp van WHERE

Soms wil je alleen berichten opvragen die aan een specifieke eis moeten voldoen. Met behulp van de volgende query worden id’s van berichten van een specifiek persoon opgevraagd:

SELECT id FROM guestbook WHERE name = 'Jaap'

Als je berichten wilt opvragen van mensen die ‘Jaap’ heten of ‘Jaap van der Veen’, pas je de query aan zodat name moet beginnen met Jaap:

SELECT id FROM guestbook WHERE name LIKE 'Jaap%'

In plaats van het = teken (dat aangeeft dat een kolom exact moet overeenkomen met een bepaalde waarde) geef je met LIKE aan dat de kolom moet voldoen aan iets. Een % teken binnen de aanhalingstekens geeft aan dat op de plek van de % iets willekeurigs mag staan.

Naast = en LIKE mag je ook andere vergelijkingsoperatoren gebruiken (>, <, >=, enzovoorts).

INSERT statement: voeg gegevens toe

De basis syntax voor het toevoegen van gegevens in een tabel is als volgt:

INSERT INTO tabelnaam(kolomnaam1,kolomnaam2,kolomnaam3) VALUES('waarde-voor-kolom1','waarde-voor-kolom-2','waarde-voor-kolom-3')

Met INSERT INTO geef je aan dat je iets in een tabel wilt stoppen. Welke tabel dat is, vul je in bij tabelnaam. Je moet direct na de tabelnaam tussen haakjes aangeven in welke kolommen van die tabel je iets wilt plaatsen. Het aantal waarden in VALUES() moet overeenkomen met het aantal gegeven kolomnamen in tabelnaam().

Wanneer we een bericht zouden willen toevoegen aan ons gastenboek, schrijven we daarvoor de volgende query:

INSERT INTO guestbook(name,email,website,postdate,message) VALUES('Karel','karel@kiers.nl','www.kiers.nl',2016549870,'Leuk om dit eens te testen')

Omdat we bij het aanmaken van de tabel hebben aangegeven dat het id automatisch moet worden ingevuld, nemen we het id niet mee bij het toevoegen van een nieuw bericht aan het gastenboek, omdat dit id automatisch wordt toegekend. Zodra je deze query zou uitvoeren in PHPMyAdmin, krijg je het volgende te zien als alles goed gaat:

MySQL heeft zelf een id van 18 toegekend aan het toegevoegde bericht.

Belangrijk voor je verder gaat: de WHERE clausule

Zoals je bij de uitleg over het SELECT statement al hebt ervaren, kun je met het WHERE gedeelte in een query bepalen welke rijen je precies wilt opvragen uit de database, of waar de opgevraagde rijen aan moeten voldoen:

SELECT * FROM guestbook WHERE name = 'Jaap'

Met behulp van bovenstaande query worden alle berichten (=rijen) opgevraagd waarvan de kolom gelijk is aan de tekst ‘Jaap’. Als je een specifieke rij wilt aanspreken (dat is bij UPDATE en DELETE erg belangrijk), dan gebruik je WHERE id = 14 . Bij het UPDATE en DELETE statement wil je meestal iets doen met een specifiek bericht. Zorg er in je WHERE clausule dan àltijd voor dat je maar een bericht aanspreekt, bijvoorbeeld op basis van een id. Berichten selecteren op basis van een andere kolom kan tot gevolgen hebben dat er meerdere berichten worden gevonden die voldoen aan je WHERE clausule.

ORDER BY clausule

GROUP BY clausule

UPDATE statement: wijzig gegevens

Bij een systeem waarmee je een website kunt beheren, is het gebruikelijk dat je geplaatste artikelen kunt wijzigen. Voor les 4 gaan we kijken hoe je een gastenboekbericht kunt wijzigen met behulp van MySQL. De basis syntax voor UPDATE ziet er als volgt uit:

UPDATE tabelnaam SET kolomnaam1='waarde1',kolomnaam2='xyz' WHERE id = identificatienummer

Bovenstaande spreekt in principe voor zich. Alles wat in hoofdletters staat is MySQL en is vereist voor een correcte wijziging van een record. identificatienummer vervang je door een daadwerkelijk id van een bepaald bericht. In het INSERT voorbeeld hebben we een bericht toegevoegd dat automatisch het id 18 toebedeeld heeft gekregen. Stel dat we het e-mailadres van het geplaatste bericht willen aanpassen, dan ziet de query er als volgt uit:

UPDATE guestbook SET email='karel@karelkiers.nl' WHERE id=18

Als alles goed gaat geeft PHPMyAdmin als melding “1 row affected” terug. Dat betekent dat er maar één bericht is gewijzigd. Als je de WHERE clausule achterwege laat, worden de e-mailadressen van àlle berichten aangepast.

DELETE statement: verwijder gegevens

Met het DELETE statement is het mogelijk om gegevens uit een tabel te verwijderen. De basissyntax ziet er als volgt uit:

DELETE FROM tabelnaam WHERE kolomnaam = waarde

Wil je alle gastenboekberichten van afzender Bart verwijderen, dan lukt dat met de volgende query:

DELETE FROM guestbook WHERE name = 'Bart'

Wil specifiek een gastenboekbericht verwijderen met id 18, dan kan dat met de volgende query:

DELETE FROM guestbook WHERE id = 18

LET OP: als je geen WHERE clausule gebruikt, worden alle gastenboekberichten verwijderd:

DELETE FROM guestbook

 Voorbereidingen om succesvol te kunnen starten met les 5

In les 5 ga je MySQL met PHP combineren. Het is daarom van groot belang dat je de (weinige) syntax en statements van MySQL die je voor deze cursus moet weten, vloeiend leert schrijven. Pak voor les 5 nog een paar keer PHPMyAdmin er bij en experimenteer met SQL!