mysqli & nested queries - maybe I'm rusty...

[email protected] (Jay Blanchard)
Newsgroups php.general
Message-ID <[email protected]>
I am putting together an application where we are almost exclusively 
using stored procedures in MySQL because most of the heavy lifting (as 
it should be) is done in the database.

I have exactly one situation where I need to execute a stored procedure 
and while returning the results if I run a across a particular value in 
those results I need to execute another SP to handle. Because I have 
forgotten which combination or store_result, use_result, fetch_row (not 
to mention that fetch row doesn't have the column names in the array, 
just the index) to use I am banging my head against the wall while 
getting the dastardly "2014 Commands out of sync;" error. Can someone 
provide some insight please?

Assume that $mysqli is the connection -

$sql = "CALL sp_ONE(" . $product . "," . $level . ")";
if(!$result = $mysqli->multi_query($sql)) {
     echo "Error: (" . $mysqli->errno . ") " . $mysqli->error . " on 
Query " . $sql;
} else {
     do {
         if($side = $mysqli->store_result()) {

             /* output the list */
             echo '<ul>';
             while($row = $side->fetch_row()) {
                 /* careful, column names aren't carried over */
                 echo '<li data-page="' . $row[2] . '">' . $row[1] . 
'</li>';
                 /* do we need to do a another stored procedure? */
                 if(isset($row[3])) {
                     /* get the info */
                     $sql = "CALL sp_TWO(" . $product . "," . $row[0] . ")";
                     if(!$subResult = $mysqli->multi_query($sql)) {
                         echo "Error: (" . $mysqli->errno . ") " . 
$mysqli->error . " on Query " . $sql;
                     }
                     /* output the extra info */
                     echo '<ul>';
                     while($sub = mysqli_fetch_array($subResult)) {
                         echo '<li data-page="' . $sub[1] . '">' . 
$sub[0] . '</li>';
                     }
                     echo '</ul>';
                 }
             }
             echo '</ul>';
             $side->close();
         }
     } while($mysqli->next_result());
}
lmpx.com only provides a reader for public news (NNTP) servers. It is not affiliated with the servers or forums shown here and is not responsible for the content of articles, which is written by their respective authors.