MYSQLI Prepared Statements in PHP

In dit artikel laat ik je zien hoe je hoe je query’s zoals SELECT, UPDATE, DELETE en dergelijke met verschillende condities kunt uitvoeren met MYSQLI prepared statements in PHP. Voordat we onze handen gaan vuil maken eerst een korte introductie over prepared statements.

Waarom zijn Prepared Statements belangrijk?

Simpel gezegd beschermen prepared statements websites tegen SQL Injection die gebruikt kan worden om een website aan te vallen. Daarnaast zijn prepared statements volgens sommigen sneller dan normale query’s. Maar het mooiste van prepared statements is hun leesbaarheid. Ze zijn eenvoudig te lezen, te begrijpen en te managen.

Benodigdheden

Een computer waarop PHP en MYSQL zijn geïnstalleerd. Ikzelf gebruik hiervoor Xampp die kun je hier downloaden.
Een PHP script die verbinding heeft met de database. De verbinding met de database slaan we op in de $mysqli variabele.

In onze database creëren we een gebruikerstabel met de naam “users” waar we een paar gebruikers invoegen dit ziet er als volgt uit:

Hoe we Prepared Statements in PHP gebruiken

Laten we eens kijken hoe we een prepared statement in php uitvoeren met gebruikmaking van MYSQLI. Dit is het basis concept. In verschillende query’s (SELECT, UPDATE enzovoort) zullen we verschillende manieren en trucs gebruiken.

Eerst gaan we een query voorbereiden (prepare)

<?php
$stmt = $mysqli->prepare('SELECT * FROM users WHERE id = ?');

Zoals je kunt zien slaan het statement op in de variabele $stmt. Dit is een mysqli_stmt object. In de volgende stappen roepen we de methodes (methods) van deze klasse (class) aan om acties uit te voeren.

Merk op dat ? in de query wordt gebruikt als tijdelijke aanduiding. We zeggen als het ware dat dit een lege doos is die we later zullen vullen.

Nu gaan we de gegevens binden. Met andere woorden we gaan de doos vullen.
$userId = 2;
$stmt->bind_param('i', $userId);

Hier binden 2 als de id van de gebruiker in de bind_param() methode. De eerste parameter geeft de datatypen voor elke variabele aan.

Vervolgens voeren we de query uit met

$stmt->execute();

Na deze stap verschilt de procedure naar gelang de query je wilt uitvoeren. Laten we eens wat voorbeelden bekijken.

1 SELECT 1 Rij Selecteren

$stmt = $mysqli-&gt;prepare('SELECT name, email FROM users WHERE id = ?');
<code>$userId = 1; // Ofwel $_GET['userId'];</code>

$stmt-&gt;bind_param('i', $userId);
$stmt-&gt;execute();
$stmt-&gt;store_result();
$stmt-&gt;bind_result($name, $email);
<code>$stmt-&gt;fetch();</code>

echo $name; //Pieter

echo $email; //pieter@mail.comstore_result(); slaat het resultaat op.
//bind_result(); verbind de waardes aan de variabelen.
//fetch(); haalt de resultaten op naar de variabelen.

 

Voor een beginner is dit lastig maar als je de andere stappen doet ga je het vanzelf begrijpen. Belangrijk om niet te vergeten, fetch() slaat het resultaat van de huidige rij op in bind_result(). Standaard is de huidige rij de eerste in de resultaten set. Als we eenmaal fetch() aanroepen is de huidige rij de tweede in de resultaten. Echter, we hebben slechts één rij in deze query.

2 SELECT Meerdere Rijen Selecteren

$stmt = $mysqli->prepare('SELECT name, email FROM users');

$stmt->execute();
$stmt->store_result();
$stmt->bind_result($name, $email);

while($stmt->fetch()) {
echo $name;
echo $email;
}

Hiermee worden alle gebruikers geselecteerd en worden de naam en email van alle gebruikers weergegeven. De bind_param() functie is hier niet nodig aangezien er geen variabele wordt doorgegeven.

Belangrijk:
fetch() levert bij succes een true op en bij falen false. Deze false wordt weergegeven als er geen rij wordt gevonden. Het mooie is dat we het direct als conditie in de while-loop kunnen gebruiken.
Telkens als fetch() wordt aangeroepen wordt het resultaat van de huidige rij opgeslagen in de $name en $email variabelen. Hierna gaan we naar de volgende rij. (Dus elke keer als fetch() wordt aangeroepen, wordt de volgende rij opgehaald)

3. SELECT Het Aantal van de Geselecteerde Rijen Verkrijgen

$stmt = $mysqli->prepare('SELECT name, email FROM users');

$stmt->execute();
$stmt->store_result();
// 3
echo $stmt->num_rows;

Belangrijk om te onthouden, store_result() moet worden aangeroepen voordat num_rows wordt gebruikt.

4 SELECT Verkrijg resultaten

$stmt = $mysqli->prepare('SELECT name, email FROM users WHERE id > ?');

$greaterThan = 1;
$stmt->bind_param('i', $greaterThan);
$stmt->execute();
$result = $stmt->get_result();

// Nu doet $result hetzelfde als $mysqli->query(...). Je kan het op de volgende manier gebruiken:
    
while($row = $result->fetch_assoc()) {
echo $row['name'];
echo $row['email'];
}

5 SELECT Met Widcards

Wildcards worden gebruikt om patronen in MYSQL te vergelijken

$stmt = $mysqli->prepare('SELECT name, email FROM users WHERE name LIKE ?');

$like = 'k%';
$stmt->bind_param('s', $like);
$stmt->execute();
$stmt->store_result();
$stmt->bind_result($name, $email);

while($stmt->fetch()) {
echo $name;
echo $email;
}

In dit voorbeeld selecteren we alle gebruikers waarvan de naam begint met met de letter k. In ons geval is dat Karin.

6 SELECT Met een Array van ID’s

Dit is vij moeilijk om te doen met prepared statements. We zullen dynamisch het vraagteken in de query moeten voegen.

// array van gebvruiker id's
$userIdArray = [1,2,3];
// aantal vraagtekens
$questionMarksCount = count($userIdArray);
// creëer een array met vraagtekens
$questionMarks = array_fill(0, $questionMarksCount, '?');
// voeg hen bij ,
$questionMarks = implode(',', $questionMarks);
// datatypes voor bind param
$dataTypes = str_repeat('i', $questionMarksCount);

$stmt = $mysqli->prepare("SELECT name, email From users WHERE id IN ($questionMarks)");


$stmt->bind_param($dataTypes, ...$userIdArray);
$stmt->execute();
$stmt->store_result();
$stmt->bind_result($name, $email);


while($stmt->fetch()) {
echo $name;
echo $email;

7 SELECT LIMIT en OFFSET

$stmt = $mysqli -> prepare("SELECT name, email FROM users LIMIT ? OFFSET ?");

// limiet van rows
$limit = 2;
// overslaan van rows
$offset = 1;

$stmt -> bind_param('ii', $limit, $offset);
$stmt -> execute();
$stmt -> store_result();
$stmt -> bind_result($name, $email);

while ($stmt -> fetch()) {
echo $name;
echo $email;
}

8 SELECT BETWEEN

$stmt = $mysqli -> prepare("SELECT name, email FROM users WHERE id BETWEEN ? AND ?");

$betweenStart = 2;
$betweenEnd = 4;

$stmt -> bind_param(‘ii’, $betweenStart, $betweenEnd);
$stmt -> execute();
$stmt -> store_result();
$stmt -> bind_result($name, $email);

while ($stmt -> fetch()) {
echo $name;
echo $email;
}

9 INSERT 1 Row

$stmt = $mysqli -> prepare('INSERT INTO users (name, email) VALUES (?,?)');

$name = ‘Frank’;
$email = ‘frank@mail.com’;

$stmt -> bind_param(‘ss’, $name, $email);
$stmt -> execute();

10 INSERT Verkrijg de ingevoerde ID

Willen we bij het gebruik van een automatische incrementele kolom voor het opslaan van de ID weten wat de ID van de gebruiker is die we als laatste in database hebben ingevoegd, dan gebruiken we in dit geval $stmt->insert_id.

$stmt = $mysqli -> prepare('INSERT INTO users (name, email) VALUES (?,?)');

$name = ‘Willemijn’;
$email = ‘willemijn@mail.com’;

$stmt -> bind_param(‘ss’, $name, $email);
$stmt -> execute();

echo ‘Je account id is ‘ . $stmt -> insert_id;

11 INSERT Meerdere Rijen invoegen

Recursieve invoegingen zijn zeer krachtig als dit gedaan wordt met één prepared statement . Je bereidt één prepared statement voor en gebruikt deze om meerdere rijen in te voeren.

$newUsers = [
[ 'jan', 'jan@mail.com' ],
[ 'carlo', 'carlo@mail.com' ],
[ 'vincent', 'vincent@mail.com' ]
];

$stmt = $mysqli -> prepare(‘INSERT INTO users (name, email) VALUES (?,?)’);

foreach ($newUsers as $user) {

$name = $user[0];
$email = $user[1];

$stmt -> bind_param(‘ss’, $name, $email);
$stmt -> execute();

echo “{$name}’s account id is {$stmt -> insert_id}”;

}

Je ziet dat $stmt->insert_id iedere keer update als je een nieuwe rij hebt ingevoegd.

12 UPDATE

$stmt = $mysqli -> prepare('UPDATE users SET email = ? WHERE id = ? LIMIT 1');

$email = ‘nieuwemaill@mail.com’;
$id = 2;

$stmt -> bind_param(‘si’, $email, $id);
$stmt -> execute();

13 UPDATE  Verkrijg aangepaste rijen

Soms is het nodig te weten hoeveel rijen er zijn aangepast door je update query

$stmt = $mysqli -> prepare('UPDATE users SET email = ? WHERE name = ? LIMIT 1');

$email = ‘nieuwemail@mail.com’;
$name = ‘willem’;

$stmt -> bind_param(‘ss’, $email, $name);
$stmt -> execute();

// 1
echo $stmt -> affected_rows;

14 DELETE

$stmt = $mysqli -> prepare('DELETE FROM users WHERE id = ?');

$userId = 4;

$stmt -> bind_param(‘i’, $userId);
$stmt -> execute();

// number of deleted rows
echo $stmt -> affected_rows;

Fouten Afhandeling

Het is altijd goed om te weten hoe je MYSQLI prepared statements kunt debuggen. Bekijk hieronder een aantal tips.

1 Als prepare faalt

Soms faalt $mysqli->prepare() functie omdat er een fout in de query zit.

Hoe dedecteer je dit?

$stmt = $mysqli -> prepare('SELECT * FROM no_table WHERE id = ?');

$id = 1;
$stmt -> bind_param(‘i’, $id);

//Als je bij het aanroepen van $stmt methoden een fout in PHP ziet zoals “Call to a member function bind_param () on boolean” dan is de voorbereiding mislukt. 
//Daarom $stmt een boolean en geen object. In dit geval kunnen we $mysqli-> gebruiken om de fout in de query te vinden.

$stmt = $mysqli -> prepare('SELECT * FROM no_table WHERE id = ?');
echo $mysqli -> error;

2 Als de uitvoering faalt

Fouten in de uitvoering laten over het algemeen geen fouten zien. Om te controleren of de uitvoering gelukt is moeten we een voorwaarde toevoegen die laat zien waar de fout zit. Met $stmt->fout wordt aangegeven wat de fout is.

$stmt = $mysqli -> prepare('INSERT INTO stmt_users (name) VALUES (?)');

$name = ‘User’;
$stmt -> bind_param(‘i’, $name);

if (! $stmt -> execute()) {
echo $stmt -> error;
}

In dit artikel hebben we besproken hoe we prepared statements voor SELECT, INSERT, UPDATE en DELETE gebruiken. Ik vond het leuk om dit artikel samen te stellen en ik heb zelf ook weer wat over MYSQLI prepared statements bijgeleerd. Wil je wat stoeien met hetgeen je zojuist gelezen hebt dan kun je hieronder het bestand downloaden. Vond je het artikel leerzaam of zie je een foutje? Feedback is altijd welkom in de reacties hieronder.

Geef een reactie