Select and Bind

We have already looked at two ways to retrieve the results from a select in both mySQLi and PDO depending on whether you want to retrieve all the results at the start of to be able to only read ad much of the results as you need. With mySQLi as well as store_result() and get_result() there is also a third command that can be used for retrieving the results - bind_result().

The bind_result call is nowhere near as useful as the other two as it requires that variables into which each of the retrieved values will be loaded instead of returning them all in an associative array. This means that in order to keep tainted and untainted variable names separated in the naming conventions used in your code that you need to create your own associative array and repeat the list of all the fields being retrieved in the bind statement so as to load your associative array properly.

Here's how your mySQLi might look:

$query = "SELECT name, address FROM usertable WHERE age = ?";
if ($stmt = $mysqli->prepare($query);) {
$stmt->bind_param('i', $age);
$stmt->execute();$_R = [];
$stmt->bind_result($_R['name'], $_R['address']);
while ($stmt->fetch()) {
// sanitise and process results

With code like this we can then sanitize the values retrieved in the $_R array that are retrieved via fetch() just as we would with the values in $row that are retrieved via fetch_row() if we were using get_result() instead of bind_result().

The only difference between this and the get_result code is that this way involves writing a lot more code as you need to bind each field being returned to the appropriate entry in the associative array instead of it happening automatically.


This article written by Stephen Chapman, Felgall Pty Ltd.

go to top

FaceBook Follow
Twitter Follow