We help IT Professionals succeed at work.

PHP's mysqli and prepared statements

stardar
stardar asked
on
3,219 Views
Last Modified: 2013-12-12
I am using PHP's mysqli with prepared statements.

I am trying to loop through one query, using the results in a second query.

This creates an error.

I have pasted the code and the error below.

I also get the same error if I put the contents of the loop in a function.

How can I achieve this kindof functionality with prepared statements?

CODE:

    $sql = "select id, name 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);
      
    while (mysqli_stmt_fetch($stmt)) {
       
        print "<pre>$id, $name</pre>";
        
        $sql = "select dob from person where id = ?";
      
        $stmt2 = mysqli_prepare($link, $sql);
            
        mysqli_stmt_bind_param($stmt2, "i", $id);      
      
        mysqli_stmt_execute($stmt2);
            
        mysqli_stmt_bind_result($stmt2, $dob);      
            
        mysqli_stmt_fetch($stmt2);
            
        print "<pre>$dob</pre>";
            
        mysqli_stmt_close($stmt2);
 
    }

    mysqli_stmt_close($stmt);

ERROR:

Warning: mysqli_prepare() [function.mysqli-prepare]: All data must be fetched before a new statement prepare takes place in XX

Where XX is the line:

$stmt2 = mysqli_prepare($link, $sql);
Comment
Watch Question

Richard QuadlingSenior Software Developer

Commented:
Why not just get the DOB as part of the first query? Then no need for a second query?
Top Expert 2006

Commented:
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)
Richard QuadlingSenior Software Developer

Commented:
   $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);

Author

Commented:
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.
Richard QuadlingSenior Software Developer

Commented:
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.
?>

Author

Commented:
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?
Richard QuadlingSenior Software Developer

Commented:
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);
?>
Richard QuadlingSenior Software Developer

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

Author

Commented:
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?
Richard QuadlingSenior Software Developer

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

Richard QuadlingSenior Software Developer

Commented:
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 ...
Senior Software Developer
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
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
Richard QuadlingSenior Software Developer

Commented:
As the user note mentions, make sure the stored data is as small as is possible. Whilst mysqli is protecting itself from using too much dbserver memory, you don't want to just shift the burden to the webserver memory.

It may well be better to build a temp table on the server with all the data in it and trawl that.
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.