MySQLi for Beginners: Prepared Statements

[PaulSonny]

Introduction

This is the second tutorial in my ‘MySQLi for Beginners’ series. If you haven’t already done so I would recommend starting with Part 1.

1. MySQLi for Beginners Part 1

Prepared Statements

Prepared statements can be complex to get your head around at first, but they’re really useful and can help remove a lot of potential issues when escaping user input. Prepared statements essential work by placing a ? where you want to substitute in user input, whether it be string, integer, blob or double. Prepared statements substitute the value into the SQL query so the issues with SQL injections are mostly removed.

Define a Statement

Let’s assume we want to write a query that returns all of the usernames from the users table where they have a name of Paul. Firstly, we’d define the SQL that we’re going to use:

$sql = $db->prepare("SELECT `username` FROM `users` WHERE `name` = ?");

As you can see, that question mark is were we’re going to by assigning the name ‘Paul’ to.

Bind Parameters

We are going to use the bind_param method to bind a parameter to the $sql object. You must specify the type as the first parameter and then the variables as the second parameter. For instance we’d use ‘s’ as the first parameter for string, and the $name variable as the second parameter.

$name = 'Paul';
$sql->bind_param('s', $name);

If you have multiple parameters to bind which are of varying types we do the following:

$name = 'Paul';
$age = '29';
$sql->bind_param('si', $name, $age);

Please note the types are not separated like the variables are.

Execute the Statement

To execute the statement we would do the following. Not a lot to it really.

$sql->execute();

Iterating over results

To iterate over results we would first bind the result to variables, this can be done using the bind_result() method which allows us to specify the variables to assign the results to. If we assign the returned ‘username’ to to the variable $returned_username we’d do the following:

$sql->bind_result($returned_username);

Doing exactly as before, if you have multiple variables to assign to, you are able to comma separate them.

Once you have bind the result variables, you want to go ahead and fetch the results. This is as simple as I have done in my MySQLi for Beginners tutorial – we’d use the fetch() method, which returns the values into the binded variables.

while($sql->fetch()){
echo $returned_username . '<br />';
}

Close $sql

Once you are finished with your sql statement, it’s good practise to free the result to keep your code neat, clean and lean!

$sql->free_result();

In my next MySQLi Tutorial I will cover MySQLi Transactions. 

This tutorial and more like it can be found on my company website: P S Web Solutions Ltd.

View full guide