Prepared statements
Een andere nieuwe feature die de mysqli extensie beschikbaar stelt, is het gebruik van prepared statements bij het uitvoeren sql queries. 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.
Er zijn twee soorten prepared statements:
Bound result prepared statements - Gebruikt voor SELECT
Bound parameter prepared statements - Gebruikt voor INSERT, UPDATE en DELETE
Bound Parameters
Bound parameter prepared statements bieden de mogelijkheid om templates van queries op te slaan op de MySQL server. Als de query uitgevoerd moet worden, worden de gegevens die in de template ingevuld moeten worden naar de database server gestuurd. Een complete query wordt gevormd en uitgevoerd.
Het proces van maken en gebruiken van bound parameter prepared statements is vrij eenvoudig en gaat als volgt.
Allereerst wordt er een query template geschreven en naar de MySQL server gestuurd. Deze ontvangt de template, controleert de syntax, voert hem uit om te kijken of hij logisch is en slaat hem vervolgens op in een speciale buffer. Vervolgens stuurt de server een speciale variabele terug waarmee later naar het statement gerefereerd kan worden.
Als een query uitgevoerd moet worden, wordt met behulp van deze variabele de in de query in te vullen gegevens naar de database gestuurd. Een query wordt gevormd en uitgevoerd.
Deze methode heeft een aantal belangrijke voordelen:
De body van de query hoeft maar een keer naar de server verzonden te worden. Bij het uitvoeren van de query worden alleen de in te vullen gegevens naar de server gestuurd. Het meeste werkt bestaande uit valideren en parsen van de query hoeft dus ook maar een keer uitgevoerd te worden.
De data die naar server verstuurd wordt, hoeft niet door een functie als mysqli_real_escape_string() gehaald te worden. In plaats daarvan werken de MySQL client en server samen om de verstuurde data veilig te verwerken zodra het gecombineerd wordt met het prepared statement.
Voorbeeld 10: Bound parameter prepared statement
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
$mysqli = new mysqli('host', 'user', 'password', 'database');
if(mysqli_connect_errno())
{
trigger_error('Fout bij verbinding: '.$mysqli->error);
}
$sql = "
INSERT INTO tabel (naam, email, leeftijd, datum)
VALUES (?, ?, ?, NOW())";
if($stmt = $mysqli->prepare($sql))
{
$stmt->bind_param('ssi', $naam, $email, $leeftijd);
$naam = 'Piet';
$email = '[email protected]';
$leeftijd = '34';
if(!$stmt->execute())
{
echo 'Het uitvoeren van de query is mislukt: '.$stmt->error.' in query: '.$sql;
}
$naam = 'Kees';
$email = '[email protected]';
$leeftijd = '40';
if(!$stmt->execute())
{
echo 'Het uitvoeren van de query is mislukt: '.$stmt->error.' in query: '.$sql;
}
$naam = 'Karel';
$email = '[email protected]';
$leeftijd = '25';
if(!$stmt->execute())
{
echo 'Het uitvoeren van de query is mislukt: '.$stmt->error.' in query: '.$sql;
}
$stmt->close();
}
else
{
echo 'Er zit een fout in de query: '.$mysqli->error;
}
?>
In dit script zien we weer een aantal nieuwe dingen, dus laten we er maar eens stap voor stap doorheen lopen.
Zoals elk script beginnen we natuurlijk weer met het maken van een database verbinding, dat moet nu toch wel al vrij vertrouwd zijn.
Daarna declareren we de SQL query die we gaan gebruiken, of beter gezegd: de template van de query. De template bestaat uit de complete query zonder de gegevens die telkens veranderen. We gebruiken zogenaamde placeholders (?) om aan te geven waar in de query variabele gegevens komen te staan.
De volgende stap is het gebruik van de bind_param() methode. Met deze methode geven we aan van wat voor type de in te voegen variabelen zijn. Als eerste parameter van deze variabele is een string vereist waarmee we het type van de variabelen vastleggen.
Elk teken uit die string komt overeen met een placeholder uit de query template en een extra parameter uit de bind_param() methode. Hierbij kan gebruik gemaakt worden van de volgende tekens die de volgende types aanduiden:
s - De variabele is een string
i - De variabele is een integer
d - De variabele is een double of een float
b - De variabele is een blob
In bovenstaand voorbeeld houdt 'ssi' in dat de eerste twee variabelen $naam en $email als strings behandeld worden en de variabele $leeftijd als integer. Let erop de de volgorde van genoemde variabelen overeenkomt met de positie van de placeholders in de template.
Let er ook op dat voor elke letter in de eerste parameter van bind_param() er een variabele als paramter meegegeven wordt:
2
3
4
5
$stmt->bind_param('s', $string);
$stmt->bind_param('si', $string, $int);
$stmt->bind_param('sid', $string, $int, $float);
?>
In het script worden vervolgens waarden toegekend aan de variabelen en wordt de query uitgevoert met de execute() methode. Vervolgens is het een kwestie van het opnieuw definieren van de variabelen en wederom het aanroepen van de execute() methode. Op deze manier wordt steeds dezelfde INSERT query met verschillende waarden uitgevoerd.
Voorbeeld 11: Een efficientere manier van gegevens verwerken
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
$mysqli = new mysqli('host', 'user', 'password', 'database');
if(mysqli_connect_errno())
{
trigger_error('Fout bij verbinding: '.$mysqli->error);
}
$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, email, leeftijd, datum)
VALUES (?, ?, ?, NOW())";
if($stmt = $mysqli->prepare($sql))
{
$stmt->bind_param('ssi', $naam, $email, $leeftijd);
foreach($personen as $persoon)
{
$naam = $persoon['naam'];
$email = $persoon['email'];
$leeftijd = $persoon['leeftijd'];
if(!$stmt->execute())
{
echo 'Het uitvoeren van de query is mislukt: '.$stmt->error.' in query: '.$sql;
}
}
$stmt->close();
}
else
{
echo 'Er zit een fout in de query: '.$mysqli->error;
}
?>
Dit voorbeeld laat zien dat je gegevens ook uit bijvoorbeeld een array kunt halen. Als je er maar te allen tijde voor zorgt dat de variabelen die voorkomen in de bind_param() methode bestaan en van het juiste type zijn.
Bound Results
De methode voor de bound result prepared statements is bijna hetzelfde als die voor de bound parameter prepared statements. Het enige verschil is dat we nu te maken hebben met gegevens die als resultaat uit een database query komen.
Het stappenplan is als volgt:
Maak een query
Vraag de MySQL server om de query voor te bereiden
Koppel PHP variabelen aan kolommen uit de query
Vraag de MySQL server om de query uit te voeren
Zorg dat een nieuwe rij met gegevens in de gekoppelde variabelen geladen wordt
Voorbeeld 12: Bound result prepared statement
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
$mysqli = new mysqli('host', 'user', 'password', 'database');
if(mysqli_connect_errno())
{
trigger_error('Fout bij verbinding: '.$mysqli->error);
}
$sql = "
SELECT naam, email, leeftijd
FROM tabel
ORDER BY leeftijd DESC ";
if($stmt = $mysqli->prepare($sql))
{
if(!$stmt->execute())
{
echo 'Het uitvoeren van de query is mislukt: '.$stmt->error.' in query: '.$sql;
}
else
{
$stmt->bind_result($naam, $email, $leeftijd);
while($stmt->fetch())
{
echo $naam.'-'.$leeftijd.'-'.$email.'<br>';
}
}
$stmt->close();
}
else
{
echo 'Er zit een fout in de query: '.$mysqli->error;
}
?>
Met de methode bind_result() is het dus mogelijk om variabelen te koppelen aan de kolomnamen (of indien gebruikt aliassen). Door de fetch() methode in een while loop te plaatsen wordt er door alle rijen uit de resultaat set gelopen en kunnen de gegevens weergegeven worden.
Dit script zal iets opleveren als:
2
3
Voorbeeld 13: Een combinatie van bound paramters en bound results
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
$mysqli = new mysqli('host', 'user', 'password', 'database');
if(mysqli_connect_errno())
{
trigger_error('Fout bij verbinding: '.$mysqli->error);
}
$sql = "
SELECT naam, leeftijd
FROM tabel
WHERE naam LIKE ?
ORDER BY naam";
if($stmt = $mysqli->prepare($sql))
{
$stmt->bind_param('s', $zoek);
$zoek = 'K%';
if(!$stmt->execute())
{
echo 'Het uitvoeren van de query is mislukt: '.$stmt->error.' in query: '.$sql;
}
else
{
$stmt->bind_result($naam, $leeftijd);
while($stmt->fetch())
{
echo $naam.'-'.$leeftijd.'<br>';
}
}
$stmt->close();
}
else
{
echo 'Er zit een fout in de query: '.$mysqli->error;
}
?>
Hier zien we een combinatie van de twee voorgaande voorbeelden. Allereerst gebruiken we een query template en vragen om die voor te bereiden. Vervolgens koppelen we de benodigde variabelen en laten we het statement uitvoeren.
Daarna gebruiken we de bind_result() methode om de resultaten te koppelen aan variabelen en geven we deze variabelen weer na fetchen van de resultaat set.
Dit scriptje laat duidelijk zien dat de placeholders in de query template ook andere dingen kunnen bevatten dan kolomnamen. Dit gaat allemaal goed, zolang je er maar voor zorgt dat de juiste variabelen aan de query gekoppeld worden en dat deze variabelen de juiste gegevens bevatten.
Inhoudsopgave
- Inleiding
- Transacties met mysqli: commit() en rollback()
- Proceduraal vs. Object georienteerd
- Uitbreiden van de mysqli klasse
- Object georienteerde interface van mysqli
- Slotwoord en referenties
- Meerdere queries tegelijk uitvoeren
- Prepared statements