PHP's mysqli and prepared statements

Hi,

As the error message suggests - you need to finish returning one set of values before you can get the new ones.

There are three ways around this
- you could loop through and put the initial results into an array or an object then perform the second query
- you could try to have two distinct connections to the same database and then query from the dissimilar ones
- you could just achieve this through sql and not have two queries at all.

I'd suggest here you could do, select id, name, dob from person where age = ?

I would always go for the sql solution if possible because it is easier to maintain, and should also be faster (and easier to spot logic mistakes)

   $sql = "select id, name, dob from person where age = ?";
    $stmt = mysqli_prepare($link, $sql);
    $age = 55;
    mysqli_stmt_bind_param($stmt, "i", $age);
    mysqli_stmt_execute($stmt) or die(mysqli_error($link));
    mysqli_stmt_bind_result($stmt, $id, $name, $dob);
    while (mysqli_stmt_fetch($stmt)) {
         print "<pre>$id, $name, $dob</pre>";
    }

    mysqli_stmt_close($stmt);

Sorry, this was a trivial example to demonstrate the problem.

In reality, it would not be possible to combine the query within the loop and the query which created the loop.

As the statements can be prepared ahead of time, does this work ...

<?php
// do all your connection logic now.
...
$stmt1 = mysqli_prepare($link, "select id, name from person where age = ?");
$stmt2 = mysqli_prepare($link, "select dob from person where id = ?");

// Now do the query and the loop.
?>

Raynard7, are you saying that it is IMPOSSIBLE to loop through a result using mysqli_stmt_fetch, and perform any subsequent queries using data within the result (whilst looping)?

This seems extraordinary!

What about if you had user-defined functions which relied on database queries which you needed to use within your loop?

And what about if other scripts were trying to run unrelated queries on the same link at the same time?

This is untested (I don't use mySQL, sorry), but from what I understand of the binding mechanism, you only need to do it once.

<?php
// do all your connection logic now.
...

// Prepare all the statements.      
$stmt1 = mysqli_prepare($link, "select id, name from person where age = ?");
$stmt2 = mysqli_prepare($link, "select dob from person where id = ?");

$age = 55;
$id = null;

// Bind
mysqli_stmt_bind_param($stmt1, "i", $age);
mysqli_stmt_bind_result($stmt1, $id, $name);

mysqli_stmt_bind_param($stmt2, "i", $id);
mysqli_stmt_bind_result($stmt2, $dob);

// Query
mysqli_stmt_execute($stmt1) or die(mysqli_error($link));
while (mysqli_stmt_fetch($stmt1))
      {
      mysqli_stmt_execute($stmt2);
      mysqli_stmt_fetch($stmt2);
      print "<pre>$id, $name $dob</pre>";
      }

// Close statements.
mysqli_stmt_close($stmt2);
mysqli_stmt_close($stmt1);
?>

The idea is we have a dummy ID which is overwritten every time you fetch on stmt1.

Hi RQuadling,

Thanks for the suggestion.

I tried both examples, and the script died without an error on the line:

mysqli_stmt_execute($stmt2)

As mentioned in my reply to Raynard7, I find it hard to believe that this is correct behaviour.

What if I wanted to apply a user-defined function, which used a database query, whilst looping through results from mysql_stmt_fetch?

Can you add error_reporting(E_ALL); to the top of the code.

http://www.php.net/manual/en/function.mysqli-stmt-execute.php

Note:
When using mysqli_stmt_execute(), the mysqli_stmt_fetch() function must be used to fetch the data prior to performing any additional queries.


So, sorry. This is how it works. Which seems royally stupid to me, but ...

Thanks RQuadling,

Inserting mysqli_stmt_store_result($stmt) just after the execute and before the loop allows me to run additional queries within the loop.

Problem solved!

Cheers
Dan