Retrieving Data

Using prepare statements is relatively straightforward for when you are updating the content in the database. You simply prepare the database statement in advance and then when you want to run it you bind the variables containing the data to the query and then execute it.

The same applies when you want to select data from the database right up to the point where you execute the database call. The part that makes selecting data using prepare statements slightly more complicated than update calls is that we need further commands to retrieve the selected data. This is because where a query call actually returns a pointer to the results, an execute call doesn't and instead gives you more flexibility where you only need to retrieve what you actually need rather than the entire result set.

For the purpose of getting started with using prepare with select statements though let's start by duplicating what a query call would do. We can easily drop any calls that we end up not needing depending on exactly what we are trying to do.

Here's a simple example where we want to retrieve two fields from a table.

$query = "SELECT name, address FROM usertable WHERE age = ?";
if ($stmt = $mysqli->prepare($query);) {
$stmt->bind_param('i', $age);
$stmt->execute();
$stmt->store_result();
$numrows = $stmt->num_rows;
$stmt->bind_result($name, $address);
while($stmt->fetch() {
echo $name.', '.$address;
}
$stmt->free_result();
$stmt->close();

The commands up to the execute are exactly the same as we use for any update calls except that this time we are using SELECT instead of INSERT, UPDATE, or DELETE.

The store_result() command is what retrieves all of the results the way that a query call would. If all we are going to do is to read through the results then we may decide to skip this statement as without it we can retrieve the results a row at a time and so not use anywhere near as much memory. When we are expecting more than a small number of rows to be returned then this would probably be a good idea (so if there are going to be thousands of results then you can probably use it but with millions or billions you should omit it).

Note that a call to retrieve the number of rows in the result set follows the command to retrieve the result set as if you retrieve the results set row by row there is no way to tell how many rows are in the results. With millions of rows of results you can get around this using a separate database call to count how many entries meet the criteria to be in the results.

The bind_result() statement allows you to specify the fields that the results are to be retrieved into. You can then simply call fetch() to retrieve each row of the result set and have the variables in that bind statement automatically loaded with the results ready for immediate processing avoiding the need to copy them inside of the loop.

The free_result() statement simply removes the result set once you have finished using it so as to free up the space that the result set used.

If we don't want the entire result set in memory at the one time we can simply drop the store_result and free_result calls from the above code and the results will be loaded into memory only when fetch is called with the result that it will only use as much memory within the PHP as is used by the variables you specify for bind_result.

Of course if you prefer to have the row returned as an associative array (as it is in what is returned from a query call then you can achieve that by replacing the store_result call with a get_result call that saves the value that is returned. this can then be used with a fetch_array call to retrieve the fields from the row as an array rather than into the individual fields specified in the bind_results.

$query = "SELECT name, address FROM usertable WHERE age = ?";
if ($stmt = $mysqli->prepare($query);) {
$stmt->bind_param('i', $age);
$stmt->execute();
$result = $stmt->get_result();
while ($row = $result->fetch_array(MYSQLI_ASSOC)) {
echo $row['name'].', '.$row['address'];
}
$stmt->close_result();
$stmt->close();

 

This article written by Stephen Chapman, Felgall Pty Ltd.

go to top

FaceBook Follow
Twitter Follow
Donate