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

Anyone? I have not been able to find a solution online. On 4/1/2013 11:33 AM, Jay Blanchard wrote:

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());

}