MySQL en PHP

Inleiding

Veel webapplicaties maken gebruik van gegevens uit een database. Om hier gebruik van te kunnen maken moet allereerst verbinding met de te gebruiken database tot stand worden gebracht om er vervolgens een of meer queries op los te kunnen laten.

De handelingen die moeten worden verricht om op een correcte manier verbinding te maken en ook weer af te verbreken en gegevens op te halen worden in deze naslag stapsgewijs beschreven.

De voorbeelden bij de stappen breiden voor iedere stap verder uit, waarbij de handelingen uit voorgaande stappen steeds weer opnieuw erin staan.

Verbinding maken

Om toegang tot de gegevens uit een database te krijgen moet er eerst verbinding worden gemaakt met de database. Hiervoor gebruiken we de functie mysqli_connect().

syntax

mysqli_connect( host , username , password , dbname );

mysqli_connect( host , username , password , dbname, port , socket );

parameters

De naam of het IP-adres van de MySQL-server.
Vanuit Codeanywhere is dit de de tekenreeks "localhost"
De MySQL-gebruikersnaam.
In de Codeanywhere MySQL-server is er staandaard een gebruiker met de naam "root"
Het bijbehorende wachtwoord.
In de Codeanywhere MySQL-server is het wachtwoord voor root standaard leeg ("").
De naam van de database waarmee verbinding moet worden gemaakt.
Het poortnummer van de MySQL-server. (optioneel)
Voor de Codeanywhere MySQL-server is dit niet van toepassing.
De socket of named pipe, die gebruikt moet worden. (optioneel)
Voor de Codeanywhere MySQL-server is dit niet van toepassing.

resultaat

Deze functie geeft een object terug, waarin de verbinding is opgeslagen, of een leeg object (null), wanneer de verbinding niet tot stand is gebracht.

Dit object moet je in een variabele opslaan om via deze verbinding onder andere queries uit te kunnen voeren. Deze variabele kun je ook testen om te zien of de verbinding geslaagd is.

voorbeeld

<?php

    function page_load() {
        
        $verbinding = mysqli_connect("localhost", "root", "••••••••••", "bibliotheek");
        if (!$verbinding) {
            return foutmelding("Fout bij het verbinden met de database...");
        }
        
    }
    
    page_load();

?>

Verbinding afsluiten

Om de performance van je server niet in de weg te zitten moet aan het eind van de verwerking iedere databaseverbinding ook weer worden afgesloten. Hiervoor gebruiken we de functie mysqli_close().

syntax

mysqli_close( link );

parameters

Het object waarin de verbinding is opgeslagen.

resultaat

Deze functie geeft altijd waar (true) terug.

voorbeeld

<?php

    function page_load() {
        
        $verbinding = mysqli_connect("localhost", "root", "••••••••••", "bibliotheek");
        if (!$verbinding) {
            return foutmelding("Fout bij het verbinden met de database...");
        }
        
        mysqli_close($verbinding);
        
    }
    
    page_load();

?>

Karakterset instellen

Om tekens met hogere ASCII-waardes (letters met accenten e.d.) correct weer te laten geven moet de karakterset worden opgegeven, die gebruikt moet worden bij de communicatie van en naar de database. Hiervoor gebruiken we de functie mysqli_set_charset().

syntax

mysqli_set_charset( link , charset );

parameters

Het object waarin de verbinding is opgeslagen.
De karakterset die gebruikt moet worden voor de communicatie van en naar de database als tekenreeks.

resultaat

Deze functie levert bij succes de waarde true op of false wanneer de karakterset niet succesvol is ingesteld.

voorbeeld

<?php

    function page_load() {
        
        $verbinding = mysqli_connect("localhost", "root", "••••••••••", "bibliotheek");
        if (!$verbinding) {
            return foutmelding("Fout bij het verbinden met de database...");
        }
        
        mysqli_set_charset($verbinding, "utf8");
        
        mysqli_close($verbinding);
        
    }
    
    page_load();

?>

Query uitvoeren

Om gegevens uit een database op te halen kunnen we SQL-queries schrijven. Deze kunnen we uit laten voeren op de database met de functie mysqli_query().

syntax

mysqli_query( link , query );

parameters

Het object waarin de verbinding is opgeslagen.
Een tekenreeks met de uit te voeren query.

resultaat

Voor queries met een SELECT-statement levert deze functie een object met de resultaatset op, wanneer de query succesvol is uitgevoerd. Voor de meeste andere soorten queries (INSERT, UPDATE etc.) levert dit bij succes de waarde true op. Wanneer de query niet succesvol kon worden uitgevoerd levert deze functie de waarde false op.

Dit resultaat moet je in een variabele opslaan om er later de individuele records uit op te kunnen halen. Deze variabele kun je eerst testen om te zien of de query geslaagd is.

voorbeeld

<?php

    function page_load() {
        
        $verbinding = mysqli_connect("localhost", "root", "••••••••••", "bibliotheek");
        if (!$verbinding) {
            return foutmelding("Fout bij het verbinden met de database...");
        }
        
        mysqli_set_charset($verbinding, "utf8");
        
        $query = "  SELECT    documenten.* 
                    FROM      documenten
                            , auteurs
                    WHERE     documenten.auteurnr = auteurs.auteurnr
                      AND     auteurs.voornaam = 'Lucinda'
                      AND     auteurs.achternaam = 'Riley'
                 ";

        $resultaat = mysqli_query($verbinding, $query);
        if (!$resultaat) {
            mysqli_close($verbinding);
            return foutmelding("Fout bij het uitvoeren van een query op de database...");
        }
        
        mysqli_close($verbinding);
        
    }
    
    page_load();

?>

Merk op dat de databaseverbinding ook wordt afgesloten wanneer de query onsuccesvol was.

Resultaat vrijgeven

Om ook hier de performance van je server niet in de weg te zitten moet nadat het resultaat gebruikt is het resultaat worden vrijgegeven. Hiervoor gebruiken we de functie mysqli_free_result().

syntax

mysqli_free_result( result );

parameters

Een object, waarin het vrij te geven resultaat is opgeslagen.

resultaat

Deze functie heeft geen resultaat.

voorbeeld

<?php

    function page_load() {
        
        $verbinding = mysqli_connect("localhost", "root", "••••••••••", "bibliotheek");
        if (!$verbinding) {
            return foutmelding("Fout bij het verbinden met de database...");
        }
        
        mysqli_set_charset($verbinding, "utf8");
        
        $query = "  SELECT    documenten.* 
                    FROM      documenten
                            , auteurs
                    WHERE     documenten.auteurnr = auteurs.auteurnr
                      AND     auteurs.voornaam = 'Lucinda'
                      AND     auteurs.achternaam = 'Riley'
                 ";

        $resultaat = mysqli_query($verbinding, $query);
        if (!$resultaat) {
            mysqli_close($verbinding);
            return foutmelding("Fout bij het uitvoeren van een query op de database...");
        }
        
        mysqli_free_result($resultaat);
        mysqli_close($verbinding);
        
    }
    
    page_load();

?>

Aantal records in het resultaat

Om te zien of er wel een resultaat is (en zo ja, hoeveel) gebruiken we de functie mysqli_num_rows().

syntax

mysqli_num_rows( result );

parameters

Een object, waarin het resultaat is opgeslagen, waarvan het aantal records moet worden teruggegeven.

resultaat

Deze functie geeft het aantal records in het resultaat terug (als geheel getal).

voorbeeld

<?php

    function page_load() {
        
        $verbinding = mysqli_connect("localhost", "root", "••••••••••", "bibliotheek");
        if (!$verbinding) {
            return foutmelding("Fout bij het verbinden met de database...");
        }
        
        mysqli_set_charset($verbinding, "utf8");
        
        $query = "  SELECT    documenten.* 
                    FROM      documenten
                            , auteurs
                    WHERE     documenten.auteurnr = auteurs.auteurnr
                      AND     auteurs.voornaam = 'Lucinda'
                      AND     auteurs.achternaam = 'Riley'
                 ";

        $resultaat = mysqli_query($verbinding, $query);
        if (!$resultaat) {
            mysqli_close($verbinding);
            return foutmelding("Fout bij het uitvoeren van een query op de database...");
        }
        
        if (mysqli_num_rows($resultaat) == 0) {
            mysqli_free_result($resultaat);
            mysqli_close($verbinding);
            return foutmelding("Geen documenten gevonden...");
        }
        
        mysqli_free_result($resultaat);
        mysqli_close($verbinding);
        
    }
    
    page_load();

?>

Merk op dat ook wanneer er geen records gevonden zijn het resultaat wordt vrijgegeven en de databaseverbinding wordt afgesloten.

Een record ophalen uit het resultaat

De individuele records moeten nog wel uit het resultaat gehaald worden om iets met de gegevens te kunnen doen. We kunnen een record op verschillende manieren ophalen. We kunnen het bijvoorbeeld opslaan in een object, een array of een associatieve array.

Wanneer we een record als gewone array ophalen heeft het eerste veld de index 0, het tweede index 1 enzovoorts. Bij een associatieve array is de veldnaam (of de alias, wanneer je die met AS in je query hebt opgegeven) de index. Dit is overzichtelijker in het gebruik. Enkel deze optie wordt hier besproken.

Een record kunnen we als associatieve array ophalen met de functie mysqli_fetch_assoc().

syntax

mysqli_fetch_assoc( result )

parameters

Een object waarin het resultaat is opgeslagen, waarvan het eerstvolgende record moet worden teruggegeven.

resultaat

Deze functie geeft een associatieve array terug met de velden van het eerste of eerst volgende record. Je moet deze functie dus vaker uitvoeren om meerdere records op te halen.

Wanneer er geen records (meer) zijn geeft deze functie null terug.

LET OP! Waar je rekening mee moet houden is dat – in tegenstelling tot SQL – de veldnamen (en aliassen) hoofdlettergevoelig zijn.

voorbeeld

<?php

    function page_load() {
        
        // controles
        
        $llnr = $_GET["llnr"];
        
        $verbinding = mysqli_connect("localhost", "root", "••••••••••", "bibliotheek");
        if (!$verbinding) {
            return foutmelding("Fout bij het verbinden met de database...");
        }
        
        mysqli_set_charset($verbinding, "utf8");
        
        $query = "  SELECT  * 
                    FROM    leerlingen
                    WHERE   llnr = $llnr
                 ";

        $resultaat = mysqli_query($verbinding, $query);
        if (!$resultaat) {
            mysqli_close($verbinding);
            return foutmelding("Fout bij het uitvoeren van een query op de database...");
        }
        
        if (mysqli_num_rows($resultaat) == 0) {
            mysqli_free_result($resultaat);
            mysqli_close($verbinding);
            return foutmelding("Geen leerling met llnr $llnr gevonden...");
        }
        
        $leerling = mysqli_fetch_assoc($resultaat);
        
?>
        <table>
            <tr>
                <td class="label">llnr</td>
                <td class="waarde"><?= $llnr ?></td>
            </tr>
            <tr>
                <td class="label">voornaam</td>
                <td class="waarde"><?= $leerling["voornaam"] ?></td>
            </tr>
            <tr>
                <td class="label">tussenv</td>
                <td class="waarde"><?= $leerling["tussenv"] ?></td>
            </tr>
            <tr>
                <td class="label">achternaam</td>
                <td class="waarde"><?= $leerling["achternaam"] ?></td>
            </tr>
        </table>
<?php
        
        mysqli_free_result($resultaat);
        mysqli_close($verbinding);
        
    }
    
    page_load();

?>

Meerdere records ophalen (i)

We willen natuurlijk niet alleen het eerste record, maar alle records ophalen. We moeten dan voor ieder record de functie mysqli_fetch_assoc() aanroepen. Dit kunnen we uiteraard het beste met een loop doen.

We zouden hiervoor een for-loop kunnen gebruiken en het resultaat van mysqli_num_rows() gebruiken om te bepalen hoe vaak deze moet worden doorlopen. Dit is nu alleen een perfecte situatie om een while-loop te gebruiken. Deze wordt herhaald zolang een bepaalde voorwaarde waar is.

mysqli_fetch_assoc() geeft null terug wanneer er geen records (meer) op te halen zijn. In expressies in PHP is null equivalent aan false. We kunnen hierdoor de toewijzing van de associatieve array aan een variabele als voorwaarde gebruiken.

syntax

while ( record = mysqli_fetch_assoc( result ) ) {
    ...
}

Hierin is het record een variabele waarin binnen de loop steeds het record als associatieve array is opgeslagen.

Het result is het object waarin het resultaat is opgeslagen, waarvan steeds het volgende record moet worden opgehaald.

LET OP! Hierbij gebruik je dus de toewijzingsoperator (=) en niet de vergelijkingsoperator (== of ===).

voorbeeld

<?php

    function page_load() {
        
        // controles
        
        $klas = $_GET["klas"];
        
        $verbinding = mysqli_connect("localhost", "root", "••••••••••", "bibliotheek");
        if (!$verbinding) {
            return foutmelding("Fout bij het verbinden met de database...");
        }
        
        mysqli_set_charset($verbinding, "utf8");
        
        $query = "  SELECT    * 
                    FROM      leerlingen
                    WHERE     klas = '$klas'
                    ORDER BY  achternaam ASC
                            , tussenv ASC
                            , voornaam ASC
                 ";
        
        $resultaat = mysqli_query($verbinding, $query);
        if (!$resultaat) {
            mysqli_close($verbinding);
            return foutmelding("Fout bij het uitvoeren van een query op de database...");
        }
        
        if (mysqli_num_rows($resultaat) == 0) {
            mysqli_free_result($resultaat);
            mysqli_close($verbinding);
            return foutmelding("Geen leerlingen gevonden voor klas $klas...");
        }
        
?>
        <h3>Leerlingen uit klas <?= $klas ?></h3>
        <table>
            <tr>
                <th scope="col">llnr</th>
                <th scope="col">voornaam</th>
                <th scope="col">tussenv</th>
                <th scope="col">achternaam</th>
            </tr>
<?php            
        
        while ($leerling = mysqli_fetch_assoc($resultaat)) {
        
?>
            <tr>
                <td><?= $leerling["llnr"] ?></td>
                <td><?= $leerling["voornaam"] ?></td>
                <td><?= $leerling["tussenv"] ?></td>
                <td><?= $leerling["achternaam"] ?></td>
            </tr>
<?php

        }
        
?>
        </table>
<?php
        
        mysqli_free_result($resultaat);
        mysqli_close($verbinding);
        
    }
    
    page_load();

?>

Meerdere records ophalen (ii)

Als alternatief voor het steeds opnieuw ophalen van het eerst volgende record, kunnen ook alle records in een keer als multidimensionale array worden opgehaald. Hier kan dan met een for-loop (of foreach-loop) doorheen gelopen kunnen worden.

Om alle records uit een resultaat in een keer op te halen gebruiken we de functie mysqli_fetch_all().

syntax

mysqli_fetch_all( result )

mysqli_fetch_all( result , mode )

parameters

Een object waarin het resultaat is opgeslagen, waarvan alle records moeten worden teruggegeven.
Het soort array dat, per record, moet worden teruggegeven. (optioneel)
De modelijke waarden voor deze parameter zijn MYSQLI_NUM, MYSQLI_ASSOC of MYSQLI_BOTH. Wanneer deze wordt weggelaten wordt standaard MYSQLI_NUM gebruikt.

resultaat

Deze functie geeft een multidimensionale array terug. Deze heeft de vorm van een sequentiële ('normale') array met records. Ieder record is steeds een sequentiële of associatieve array met de betreffende velden.

Of ieder record in een sequentiële of associatieve array wordt teruggegeven, hangt af van wat er als mode is opgegeven.

voorbeeld

<?php

    function page_load() {
        
        // controles
        
        $klas = $_GET["klas"];
        
        $verbinding = mysqli_connect("localhost", "root", "••••••••••", "bibliotheek");
        if (!$verbinding) {
            return foutmelding("Fout bij het verbinden met de database...");
        }
        
        mysqli_set_charset($verbinding, "utf8");
        
        $query = "  SELECT    * 
                    FROM      leerlingen
                    WHERE     klas = '$klas'
                    ORDER BY  achternaam ASC
                            , tussenv ASC
                            , voornaam ASC
                 ";
        
        $resultaat = mysqli_query($verbinding, $query);
        if (!$resultaat) {
            mysqli_close($verbinding);
            return foutmelding("Fout bij het uitvoeren van een query op de database...");
        }
        
        if (mysqli_num_rows($resultaat) == 0) {
            mysqli_free_result($resultaat);
            mysqli_close($verbinding);
            return foutmelding("Geen leerlingen gevonden voor klas $klas...");
        }
        
?>
        <h3>Leerlingen uit klas <?= $klas ?></h3>
        <table>
            <tr>
                <th scope="col">llnr</th>
                <th scope="col">voornaam</th>
                <th scope="col">tussenv</th>
                <th scope="col">achternaam</th>
            </tr>
<?php            
        
        $leerlingen = mysqli_fetch_all($resultaat, MYSQLI_ASSOC);
        
        for ($i = 0; $i < count($leerlingen); $i++) {
            
            $leerling = $leerlingen[$i];
        
?>
            <tr>
                <td><?= $leerling["llnr"] ?></td>
                <td><?= $leerling["voornaam"] ?></td>
                <td><?= $leerling["tussenv"] ?></td>
                <td><?= $leerling["achternaam"] ?></td>
            </tr>
<?php
            
        }
        
?>
        </table>
<?php
        
        mysqli_free_result($resultaat);
        mysqli_close($verbinding);
        
    }
    
    page_load();

?>

Beveiliging

Om een informatiesysteem, waarbij gebruikgemaakt wordt van een database, zo goed mogelijk te beveiligen tegen aanvallen van buitenaf, kunnen we een aantal lagen van beveiliging toepassen. Het is namelijk mogelijk om, zonder goede beveiliging, de op de database uit te voeren queries aan te passen of zelfs helemaal nieuwe SQL-statements (SQL injecties) uit te laten voeren. Deze kunnen variëren van nieuwe records toevoegen aan de database tot complete tabellen verwijderen of zelfs het beheerderswachtwoord wijzigen.

Een correcte en zo volledig mogelijke beveiliging is dus van essentieel belang.

client-side formulier validatie

Door in het formulier de juiste type formulierelementen te gebruiken en op de juiste manier eigenschappen als required toe te passen, kunnen we een eerste laag van beveiliging opwerpen. Hiermee kunnen we echter alleen voorkomen dat ‘gewone’ welwillende gebruikers per ongeluk verkeerde dingen kunnen invullen. Deze vorm van beveiliging is - zoals we al hebben gezien - eenvoudig te omzeilen. We kunnen dus niet enkel op deze beveiliging vertrouwen.

server-side formulier controles

Omdat de client-side formulier validatie eenvoudig te omzeilen is, moeten alle formuliergegevens ook binnen de server-side verwerking nogmaals worden gecontroleerd. Hiervoor hebben we alle controles, zoals we die hebben gezien bij de verwerking van html-formulieren, tot onze beschikking.

Voor alle formuliergegevens moet gekeken worden of deze überhaupt bestaan, d.w.z. voorkomen in de associatieve array $_GET of $_POST (m.b.v. de functie isset()). Voor alle verplichte velden moet gecontroleerd worden of er wel iets is ingevuld (door het te vergelijken met een lege tekenreeks). Voor alle numeriek velden moet gecontroleerd worden of er wel daadwerkelijk een numerieke waarde is ingevuld (m.b.v. de functie is_numeric()).

Al deze controles moeten uiteraard binnen de functie page_load() worden geplaatst en bij voorkeur voordat de databaseverbinding tot stand wordt gebracht. Wanneer een of meerdere van deze controles namelijk tot een foutmelding zou leiden heeft het sowieso geen zin om verbinding te maken met de database.

Expliciete numerieke datatype conversie

Alle formuliergegevens van een html-formulier worden doorgestuurd als tekenreeks. Om te voorkomen dat er voor numerieke gegevens onverwachte waarden in zouden kunnen staan, is het verstandig deze waarden expliciet om te zetten van een tekenreeks naar het juiste numerieke datatype.

syntax

intval( waarde );

floatval( waarde );

parameters

De waarde die omgezet moet worden naar een numeriek datatype.

resultaat

De functie intval() levert een integer (geheel getal) op, waarmee de opgegeven waarde overeenkomt. Wanneer de waarde decimalen achter de komma bevat wordt deze afgerond. Wanneer de waarde niet naar een getal om te zetten is geeft deze functie de waarde 0 (nul) op.

De functie floatval() levert een zogenaamde float (kommagetal) op, waarmee de opgegeven waarde overeenkomt. Wanneer de waarde niet naar een getal om te zetten is geeft deze functie de waarde 0 (nul) op.

voorbeeld

<?php

    $llnr = intval($_POST["llnr"]);
    $boete = floatval($_POST["boete"]);

?>

Escapen van tekenreeksen

Om te voorkomen dat er binnen een tekenreeks ongeldige tekens of bijvoorbeeld SQL injecties voor kunnen komen, moeten deze formuliergegevens omgezet worden naar een veilige tekenreeks voor gebruik in SQL. Hiervoor kunnen we de functie mysqli_real_escape_string() gebruiken. Deze functie escapet verschillende karakters, waaronder enkele en dubbele quotes en newline karakters.

syntax

mysqli_real_escape_string( link , waarde );

parameters

Het object waarin de verbinding is opgeslagen.
Een tekenreeks waarin alle onveilige karakters ge-escaped moeten worden.

resultaat

Deze functie geeft een tekenreeks terug waarin alle onveilige tekens binnen de opgegeven waarde ge-escaped zijn.

voorbeeld

<?php

    $antwoord = mysqli_real_escape_string($verbinding, $_POST["antwoord"]);

?>