Recursion in prepared statements

Question

I've been using PDO and preparing all my statements primarily for security reasons. However, I have a part of my code that does execute the same statement many times with different parameters, and I thought this would be where the prepared statements really shine. But they actually break the code...

The basic logic of the code is this.

function someFunction($something) {
  global $pdo;

  $array = array();

  static $handle = null;
  if (!$handle) {
    $handle = $pdo->prepare("A STATEMENT WITH :a_param");
  }

  $handle->bindValue(":a_param", $something);
  if ($handle->execute()) {
    while ($row = $handle->fetch()) {
      $array[] = someFunction($row['blah']);
    }
  }

  return $array;
}

It looked fine to me, but it was missing out a lot of rows. Eventually I realised that the statement handle was being changed (executed with different param), which means the call to fetch in the while loop will only ever work once, then the function calls itself again, and the result set is changed.

So I am wondering what's the best way of using PDO prepared statements in a recursive way.

One way could be to use fetchAll(), but it says in the manual that has a substantial overhead. The whole point of this is to make it more efficient.

The other thing I could do is not reuse a static handle, and instead make a new one every time. I believe that since the query string is the same, internally the MySQL driver will be using a prepared statement anyway, so there is just the small overhead of creating a new handle on each recursive call. Personally I think that defeats the point.

Or is there some way of rewriting this?

Answer 1

You cannot nest the statement handles: you need to close the previously open handle before opening another one within a single session.

In fact, PDO does if automatically when you issue a new prepare.

When you call the function recursively:

• The initial handle is allocated (1)
• The first record is fetched of (1)
• The function is called recursively. Value of (1) resides in the recursion stack.
• The new handle is allocated (2), invalidating (1)
• The first record is fetched of (2)
• The function returns
• You try to fetch the next record of (1) and fail since it's invalid

Thus said, MySQL does not support recursion on its side and that means you'll have to do it on PHP side, using fetchAll.

Answer 2

The real problem is that $handle is static. Static variables are problematic for recursion when state needs to be preserved across a recursive call, not just for prepared statements. In this case, the recursive call executes a new query, discarding the previous state. PDO::fetchAll is indeed the only option if you want a single prepared query.

Depending on what the statement is, you could potentially rewrite it to return all results at once, building the tree after that.