[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

PHP mysqli calling another stored procedures within a result set from another stored procedures

Posted on 2009-12-29
3
Medium Priority
?
560 Views
Last Modified: 2013-12-12
what's the proper way to apply this code:

      while ($mysqli->next_result()) {
            $result = $mysqli->use_result();
            if ($result instanceof mysqli_result) {
                  $result->free();
            }
      }


into my code:
      $mysqli=new mysqli(host,user,pass,db);
      $sql='call test1()';
      if($rs_a=$mysqli->query($sql)){
            while($fields_a=$rs_a->fetch_assoc()){
                  $sql='call test2('.$fields_a['id'].')';
                  if($rs_b=$mysqli->query($sql)){
                        while($fields_b=$rs_b->fetch_assoc()){
                              //do something
                        }
                        $rs_b->close();
                  }
            }
            $rs_a->close();
      }
      $mysqli->close();
0
Comment
Question by:L_inPeace
  • 2
3 Comments
 
LVL 24

Expert Comment

by:mankowitz
ID: 26146081
I don't think you need to modify anything. When you use the mysqli::query with no constant, it defaults to store mode. In order to free the result set, you just use close(), as you did.

from php docs:
Either the constant MYSQLI_USE_RESULT or MYSQLI_STORE_RESULT depending on the desired behavior. By default, MYSQLI_STORE_RESULT is used.

If you use MYSQLI_USE_RESULT all subsequent calls will return error Commands out of sync unless you call mysqli_free_result()
0
 

Author Comment

by:L_inPeace
ID: 26151189
According to http://www.rvdavid.net/using-stored-procedures-mysqli-in-php-5/
mysqli will return two result sets after every stored procedure call, the first is the query result and the second is the returned mysqli call information.

I have to close the second result set before calling another stored procedure.

Both of my stored procedures are SELECT statement.
0
 
LVL 24

Accepted Solution

by:
mankowitz earned 2000 total points
ID: 26154065
Ahhh.

now i see the problem. You can't have two queries open at the same time. I think you're kinda stuck with a couple of options

1. Create another database connection
2. Store the result of the outer loop in an array, close that result set and cycle through the array.
3. Rewrite the database query to give you all the data at once


$mysqli=new mysqli(host,user,pass,db);
$sql='call test1()';
if($rs_a=$mysqli->query($sql)){
    while($fields_a=$rs_a->fetch_assoc()){
        $rs_a_copy[] = $fields;
    }

    while ($mysqli->next_result()) {
        $result = $mysqli->use_result();
        if ($result instanceof mysqli_result) {
            $result->free();
        }
    }
}

// now go through the second loop
foreach ($rs_a_copy as $fields_a){
 $sql='call test2('.$fields_a['id'].')';
                  if($rs_b=$mysqli->query($sql)){
                        while($fields_b=$rs_b->fetch_assoc()){
                              //do something
                        }
                        $rs_b->close();
                  }

Open in new window

0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this blog, we’ll look at how improvements to Percona XtraDB Cluster improved IST performance.
It’s a season to be thankful, and we’re thankful for users like you who engage on site, solve technology problems, and network with others in the industry. What tech are we most thankful for? Keep reading.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses
Course of the Month19 days, 16 hours left to enroll

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question