Prepared statements

See Prepared statements in MySQLi for how to prepare and execute a query.

Binding of results

Object-oriented style

$stmt->bind_result($forename);

Procedural style

mysqli_stmt_bind_result($stmt, $forename);

The problem with using bind_result is that it requires the statement to specify the columns that will be used. This means that for the above to work the query must have looked like this SELECT forename FROM users. To include more columns simply add them as parameters to the bind_result function (and ensure that you add them to the SQL query).

In both cases, we’re assigning the forename column to the $forename variable. These functions take as many arguments as columns you want to assign. The assignment is only done once, since the function binds by reference.

We can then loop as follows:

Object-oriented style

while ($stmt->fetch())
    echo "$forename<br />";

Procedural style

while (mysqli_stmt_fetch($stmt))
    echo "$forename<br />";

The drawback to this is that you have to assign a lot of variables at once. This makes keeping track of large queries difficult. If you have MySQL Native Driver (mysqlnd) installed, all you need to do is use get_result.

Object-oriented style

$result = $stmt->get_result();

Procedural style

$result = mysqli_stmt_get_result($stmt);

This is much easier to work with because now we’re getting a mysqli_result object. This is the same object that mysqli_query returns. This means you can use a regular result loop to get your data.