Prepared statements

Op dit punt zul je misschien denken dat de PDO extensie niet veel meer biedt dan de mogelijkheid van het verbinden met meerdere database systemen en het uitvoeren van database gerelateerde taken. Dat is zeker niet het geval aangezien PDO met een aantal mooie features komt, waaronder het werken met prepared statements.

Deze prepared statements bieden de programmeur de mogelijkheid tot het creeren van queries die veiliger zijn, waarvan de performance beter is en die eenvoudiger te noteren zijn. Het grote voordeel van prepared statements is dat een bepaalde query op een efficiente manier vele malen achter elkaar uitgevoerd kan worden.

Bij het gebruik van prepared statements wordt er allereerst een template van de uit te voeren query naar de server gestuurd. Deze wordt door de server gecontroleerd op syntax fouten en uitgevoerd om te controleren of de query logisch is. Vervolgens wordt deze opgeslagen in een speciale buffer.

Op het moment dat de query daadwerkelijk uitgevoerd moet worden, stuur je de in de query in te vullen gegevens naar de database. Vervolgens wordt een complete query gevormd en uitgevoerd.

Voorbeeld 16: Uitvoeren van een prepared INSERT statement

Code (php)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

<?php
try
{
 $db = new PDO('mysql:host=localhost;dbname=test','user','password');
 $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

 $sql = "
 INSERT INTO tabel (naam)
 VALUES (:naam)
 ";

 $stmt = $db->prepare($sql);

 $stmt->bindParam(':naam', $naam, PDO::PARAM_STR);

 $naam = 'Jan';
 $stmt->execute();
}
catch(PDOException $e)
{
 echo '<pre>';
 echo 'Regel: '.$e->getLine().' ';
 echo 'Bestand: '.$e->getFile().' ';
 echo 'Foutmelding: '.$e->getMessage();
 echo '</pre>';
}
?>

In dit script hebben we eerst een template gemaakt van de INSERT query die we willen gebruiken. Het stukje ':naam' in de template is een zogenaamde parameter marker of placeholder en geeft de plaats aan waar straks een variabele ingevuld gaat worden.

Vervolgens roepen we de prepare() methode aan om de query naar de server te sturen en te laten controleren. Als dat gelukt is, gebruiken we de bindParam() methode. Met deze methode koppelen we een bepaalde variabele aan een bepaalde placeholder. Op die manier weet PHP straks welke variabele hij onder welke naam naar de server moet sturen zodat deze het begrijpt. De optionele derde paramter van deze methode geeft aan als wat voor type de variabele behandeld moet worden, in dit geval dus een string.

Nadat we de variabele vervolgens een waarde hebben gegeven, sturen we alles wederom naar de server met de execute() methode. Als alles goed is gegaan krijgen we geen foutmeldingen en zal er een nieuw record ingevoegd zijn.

Natuurlijk is het ook mogelijk om meerdere placeholders te gebruiken om meerdere gegevens in te voeren. En daarnaast kunnen we ook nog eens meerdere queries achter elkaar uitvoeren.

Voorbeeld 17: Meerdere gegevens en meerdere queries

Code (php)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50

<?php
try
{
 $db = new PDO('mysql:host=localhost;dbname=test','user','password');
 $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);


 $personen = array(
 array(
 'naam' => 'Piet',
 'email' => '[email protected]',
 'leeftijd' => 34 ),
 array(
 'naam' => 'Kees',
 'email' => '[email protected]',
 'leeftijd' => 40 ),
 array(
 'naam' => 'Karel',
 'email' => '[email protected]',
 'leeftijd' => 25 )
 );

 $sql = "
 INSERT INTO tabel (naam, leeftijd, email)
 VALUES (:naam, :leeftijd, :email)
 ";

 $stmt = $db->prepare($sql);

 $stmt->bindParam(':naam', $naam, PDO::PARAM_STR);
 $stmt->bindParam(':email', $email, PDO::PARAM_STR);
 $stmt->bindParam(':leeftijd', $leeftijd, PDO::PARAM_INT);

 foreach($personen as $persoon)
 {
 $naam = $persoon['naam'];
 $email = $persoon['email'];
 $leeftijd = $persoon['leeftijd'];
 $stmt->execute();
 }
}
catch(PDOException $e)
{
 echo '<pre>';
 echo 'Regel: '.$e->getLine().' ';
 echo 'Bestand: '.$e->getFile().' ';
 echo 'Foutmelding: '.$e->getMessage();
 echo '</pre>';
}
?>

Voor elke placeholder die we in de query template aangegeven hebben, roepen we de bindParam() methode aan om een variabele te koppelen.

We lezen de array met gegevens uit met een foreach loop, geven de gekoppelde variabelen een waarde en voeren de statement uit met de execute() methode. Je ziet dat ik geen gebruik maak van de quote() methode om in te voegen strings te beveilgen. Dit is niet nodig aangezien de execute() methode al zorgt voor een veilige afhandeling van de gegevens.

Daarnaast is er nog een andere manier waarop we de prepared statement uit kunnen voeren. We kunnen alle gegevens namelijk ook in een array als parameter meegeven aan de execute() methode van het statement.

Voorbeeld 18: Statement uitvoeren met gegevensarray als parameter

Code (php)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42

<?php
try
{
 $db = new PDO('mysql:host=localhost;dbname=test','user','password');
 $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);


 $personen = array(
 array(
 ':naam' => 'Piet',
 ':email' => '[email protected]',
 ':leeftijd' => 34 ),
 array(
 ':naam' => 'Kees',
 ':email' => '[email protected]',
 ':leeftijd' => 40 ),
 array(
 ':naam' => 'Karel',
 ':email' => '[email protected]',
 ':leeftijd' => 25 )
 );

 $sql = "
 INSERT INTO tabel (naam, leeftijd, email)
 VALUES (:naam, :leeftijd, :email)
 ";

 $stmt = $db->prepare($sql);
 foreach($personen as $persoon)
 {
 $stmt->execute($persoon);
 }
}
catch(PDOException $e)
{
 echo '<pre>';
 echo 'Regel: '.$e->getLine().' ';
 echo 'Bestand: '.$e->getFile().' ';
 echo 'Foutmelding: '.$e->getMessage();
 echo '</pre>';
}
?>

Zoals je ziet hoe je geen gebruik meer te maken van de bindParam() methode, de array met gegevens in de execute() methode doet zijn werk.

Voorbeeld 19: Updaten van gegevens met een prepared statement

Code (php)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41

<?php
try
{
 $db = new PDO('mysql:host=localhost;dbname=test','user','password');
 $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

 $personen = array('Piet', 'Karel');

 $sql = "
 UPDATE
 tabel
 SET
 email = 'geen mail'
 WHERE
 naam = :naam
 ";

 $stmt = $db->prepare($sql);

 $stmt->bindParam(':naam', $naam, PDO::PARAM_STR);

 foreach($personen as $persoon)
 {
 $naam = $persoon;
 $stmt->execute();

 if($stmt->rowCount() == 0)
 {
 throw new PDOException('Er zijn geen rijen gewijzigd in de UPDATE query met naam: '.$naam);
 }
 }
}
catch(PDOException $e)
{
 echo '<pre>';
 echo 'Regel: '.$e->getLine().' ';
 echo 'Bestand: '.$e->getFile().' ';
 echo 'Foutmelding: '.$e->getMessage();
 echo '</pre>';
}
?>

Dit voorbeeld lijk veel op het INSERT voorbeeld maar kent toch een belangrijk verschil. Bij een UPDATE of DELETE query is het namelijk altijd van belang om te controleren of er wel degelijk rijen aangepast zijn.

Met de methode rowCount() kunnen we het aantal aangepaste rijen van het laatste uitgevoerde statement ophalen. Mocht dit gelijk zijn aan 0 dan creëren we een nieuw PDOException object met de betreffende melding.

Selecteren van gegevens met een prepared statement
Naast het invoegen, updaten en verwijderen van records met een prepared statement, is het ook mogelijk om een SELECT query uit te voeren.

Hoewel een het uitvoeren van een prepared SELECT statement veel lijkt op de voorbeelden hierboven, werkt het toch net iets anders. We hebben hier namelijk te maken met gegevens die we uit de database ophalen, dus zullen we die gegevens op een bepaalde manier zichtbaar moeten maken.

Voorbeeld 20: Gegevens uitlezen met fetch()

Code (php)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28

<?php
try
{
 $db = new PDO('mysql:host=localhost;dbname=test','user','password');
 $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

 $sql = "SELECT naam FROM tabel WHERE leeftijd > :leeftijd";

 $stmt = $db->prepare($sql);
 $stmt->bindParam(':leeftijd', $leeftijd, PDO::PARAM_INT);

 $leeftijd = 30;
 $stmt->execute();

 while($row = $stmt->fetch(PDO::FETCH_ASSOC))
 {
 echo $row['naam'].' ';
 }
}
catch(PDOException $e)
{
 echo '<pre>';
 echo 'Regel: '.$e->getLine().' ';
 echo 'Bestand: '.$e->getFile().' ';
 echo 'Foutmelding: '.$e->getMessage();
 echo '</pre>';
}
?>

Dit geeft als resultaat iets als:

Code (php)

1
2

Piet - 34
Kees - 40

Tot aan de execute() methode is dit script precies hetzelfde als voorgaande voorbeelden. Maar zoals ik al zei moeten we de resultaten van de query natuurlijk zichtbaar kunnen maken. Hier gebruiken we de fetch() methode voor.

Aan de fetch() methode kunnen we optioneel een parameter meegeven om aan te geven hoe we willen dat de rijen gefetched worden. Dit is vergelijkbaar met de optionele parameter van de mysql_fetch_array() functie.

De belangrijkste beschikbare parameters zijn:

PDO::FETCH_ASSOC - Een rij als associtieve array
PDO::FETCH_NUM - Een rij als numerieke array
PDO::FETCH_BOTH - Een rij zowel associtieve als numeriek te gebruiken (standaard)

In tegenstelling tot bijvoorbeeld de MySQLi extensie kent PDO geen methode om het aantal rijen in een resultaatset te bepalen. Wil je dit aantal weten, dan zul je een extra query uit moeten voeren om dat aantal te bepalen. Het makkelijkste is het om hier een query met COUNT() voor te gebruiken:

Voorbeeld 20a: Bepalen van het aantal rijen in een resultaat set

Code (php)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

<?php
try
{
 $sql = "SELECT COUNT(naam) FROM tabel WHERE leeftijd > 30";
 $row = $db->query($sql)->fetch(PDO::FETCH_ASSOC);
 echo $row['aantal']; // Output: 2
}
catch(PDOException $e)
{
 echo '<pre>';
 echo 'Regel: '.$e->getLine().' ';
 echo 'Bestand: '.$e->getFile().' ';
 echo 'Foutmelding: '.$e->getMessage();
 echo '</pre>';
}
?>

In dit voorbeeld gebruiken we query() om een eenvoudige query uit te voeren en fetch() om het resultaat van de query in een array te zetten. De variabele $row['aantal'] zal in dit geval het aantal rijen in de resultaat set van de query bevatten.

Verder met het fetchen van gegevens. Een tweede methode die we daarvoor kunnen gebruiken is fetchAll().

Voorbeeld 21: Gegevens uitlezen met fetchAll()

Code (php)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30

<?php
try
{
 $db = new PDO('mysql:host=localhost;dbname=test','user','password');
 $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

 $sql = "SELECT naam FROM tabel WHERE leeftijd > :leeftijd";

 $stmt = $db->prepare($sql);
 $stmt->bindParam(':leeftijd', $leeftijd);

 $leeftijd = 30;
 $stmt->execute();

 $result = $stmt->fetchAll();

 foreach($result as $row)
 {
 echo $row['naam'].' ';
 }
}
catch(PDOException $e)
{
 echo '<pre>';
 echo 'Regel: '.$e->getLine().' ';
 echo 'Bestand: '.$e->getFile().' ';
 echo 'Foutmelding: '.$e->getMessage();
 echo '</pre>';
}
?>

Wederom zien we hier hetzelfde resultaat:

Code (php)

1
2

Piet - 34
Kees - 40

De fetchAll() methode doet dus niets meer dan alle rijen uit de resultaat set onder een nieuwe key in een array zetten. Vervolgens kunnen we deze array met een foreach loop uitlezen.

Aan de fetchAll() methode kunnen we natuurlijk ook weer een parameter meegeven om aan te geven hoe de resultaten gefetched moeten worden.

« vorige pagina | volgende pagina »

« Lees de omschrijving en reacties

Forum berichten

Reacties

PHP scripts

PHP tutorials

Actief op PHPhulp

Prepared statements

Inhoudsopgave

PHP tutorial opties

Over PHPhulp

Support

Gesponsord

Extra's

Privacy