PHP PHP MySQLi How to get data from a prepared statement
Example
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.
What if I cannot install mysqlnd?
If that is the case then @Sophivorus has you covered with this amazing answer.
This function can perform the task of get_result without it being installed on the server. It simply loops through the results and builds an associative array
function get_result(\mysqli_stmt $statement)
{
$result = array();
$statement->store_result();
for ($i = 0; $i < $statement->num_rows; $i++)
{
$metadata = $statement->result_metadata();
$params = array();
while ($field = $metadata->fetch_field())
{
$params[] = &$result[$i][$field->name];
}
call_user_func_array(array($statement, 'bind_result'), $params);
$statement->fetch();
}
return $result;
}
We can then use the function to get results like this, just as if we were using mysqli_fetch_assoc()
<?php
$query = $mysqli->prepare("SELECT * FROM users WHERE forename LIKE ?");
$condition = "J%";
$query->bind_param("s", $condition);
$query->execute();
$result = get_result($query);
while ($row = array_shift($result)) {
echo $row["id"] . ' - ' . $row["forename"] . ' ' . $row["surname"] . '<br>';
}
It will have the same output as if you were using the mysqlnd driver, except it does not have to be installed. This is very useful if you are unable to install said driver on your system. Just implement this solution.
