Select (without record count)

When we don't need to know how many results we have from a select, we don't need to retrieve all of the results at once. Instead of potentially loading a hige number or results into memory to process them all we can instead retrieve the rows individually as we need them.

Here's the mySQLi version:

$query = "SELECT name, address FROM usertable WHERE age = ?";
if ($stmt = $mysqli->prepare($query);) {
$stmt->bind_param('i', $age);
$stmt->bind_result($name, $address);
while($stmt->fetch_row() {
echo $name.', '.$address;

Replacing store_result with use_result and fetch with fetch_row are the only change needed to achieve this with mySQLi. Note that while doing this saves loading a huge amount of data all at once, it also locks the tables containing the data while the code is running so depending on what you are doing in the loop this may cause just as many but different problems than using store_result.

Here's the PDO version of the same thing:

$stmt = $db->prepare("SELECT name, address FROM usertable WHERE age = :age");
$stmt->bindValue(':age', $age, PDO::PARAM_INT);
while ($row = $stmt->fetch(PDO::FETCH_ASSOC, PDO::FETCH_ORI_NEXT))
echo $row['name'].', '.$row['address'];

Replacing fetchAll with fetch means that we can retrieve the rows one at a time. We also specify FETCH_ORI_NEXT to identify that we want to retrieve the next result. Values of FETCH_ORI_LAST to retrieve the last result and FETCH_ORI_PRIOR to retrieve the prior result mean that we can retrieve the rows in the reverse order if we wish (although changing the order in the ORDER BY clause is probably going to be easier in most cases.


This article written by Stephen Chapman, Felgall Pty Ltd.

go to top

FaceBook Follow
Twitter Follow