PHP MySQLi and Multiple Prepared Statements

While sprucing up the PHP code I use to provide my own Stack Overflow API for GeeQe I ran into an error caused by trying to use multiple prepared statements with MySQLi. It turned up when I tried to execute one prepared statement while looping over the result set from another prepared statement that were both created on the same connection. What came out was the following error:

"Commands out of sync; you can't run this command now"

Details about this error can be found in the mysql docs. Reading those details makes it clear that the result sets of a prepared statement execution need to be fetched completely before executing another prepared statement on the same connection.

Fixing the issue can be accomplished by using the store result call. Here is an example of what I initially was trying to do:

<?php

  $db_connection = new mysqli('127.0.0.1', 'user', '', 'test');

  $post_stmt = $db_connection->prepare("select id, title from post where id = 1000");
  $comment_stmt = $db_connection->prepare("select user_id from comment where post_id = ?");

  if ($post_stmt->execute())
  {
    $post_stmt->bind_result($post_id, $post_title);

    if ($post_stmt->fetch())
    {
      $comments = array();

      $comment_stmt->bind_param('i', $post_id);
      if ($comment_stmt->execute())
      {
        $comment_stmt->bind_result($user_id);
        while ($comment_stmt->fetch())
        {
          array_push($comments, array('user_id' => $user_id));
        }
      }
      else
      {
        printf("Comment statement error: %s\n", $comment_stmt->error);
      }
    }
  }
  else
  {
    printf("Post statement error: %s\n", $post_stmt->error);
  }

  $post_stmt->close();
  $comment_stmt->close();

  $db_connection->close();

  printf("ID: %d -> %s\n", $post_id, $post_title);
  print_r($comments);
?>

The above will result in the following error:

Comment statement error: Commands out of sync; you can't run this command now
PHP Notice:  Undefined variable: post_title in error.php on line 41
ID: 9033 -&gt;
Array
(
)

Here is what needs to be done to make it work correctly:

<?php

  $db_connection = new mysqli('127.0.0.1', 'user', '', 'test');

  $post_stmt = $db_connection->prepare("select id, title from post where id = 1000");
  $comment_stmt = $db_connection->prepare("select user_id from comment where post_id = ?");

  if ($post_stmt->execute())
  {
    $post_stmt->store_result();
    $post_stmt->bind_result($post_id, $post_title);

    if ($post_stmt->fetch())
    {
      $comments = array();

      $comment_stmt->bind_param('i', $post_id);
      if ($comment_stmt->execute())
      {
        $comment_stmt->bind_result($user_id);
        while ($comment_stmt->fetch())
        {
          array_push($comments, array('user_id' => $user_id));
        }
      }
      else
      {
        printf("Comment statement error: %s\n", $comment_stmt->error);
      }
    }

    $post_stmt->free_result();
  }
  else
  {
    printf("Post statement error: %s\n", $post_stmt->error);
  }

  $post_stmt->close();
  $comment_stmt->close();

  $db_connection->close();

  printf("ID: %d -> %s\n", $post_id, $post_title);
  print_r($comments);
?>

A couple things to note about the above example:

  • The bind and fetch on the statement still works correctly.
  • Make sure the results are freed when the processing is done.
This entry was posted in programming and tagged , ,

3 Comments

  1. Andrey

    You can also use cursors, with 5.0+ then you don't need to store the result. Cursors fetch data row by row and every fetch means a round-trip to the server. The data is materialized on the server side. Thus, if the results are big cursors are not recommended.

  2. Is this a simplified example? Why don't you do this in one query using a join:

    select p.id, p.title, c.user_id
    from post p left outer join comment c on p.id = c.post_id
    where p.id = 1000;

    Granted, that will give return n rows with repeating values in the post columns, but you won't have to worry about commands out of sync.

  3. @bill I didn't want to return a ton of duplicated data from the join is all. It feels cleaner to make the second round trip.

One Trackback

  1. [...] my post on using PHP MySQLi and multiple prepared statements at the same time someone commented that using cursors could do the same thing. With that comment I dug some more and [...]

Post a Comment

Your email is never published nor shared. Required fields are marked *

*
*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>