[SOLVED] - Interesting MySQLi issue - Stored Procedures with Multiple Results.

[eluser]Unknown[/eluser]
Too bad I didn't find this before I started hacking on a solution myself.

I used your old code as a base and came up with this:

Code:

function next_result($store_result = TRUE)
{
    if (is_object($this->conn_id))
    {
        // Free old result
        $this->free_result();
        
        // Check if there are more results
        $has_result = @mysqli_next_result($this->conn_id);
        if ($has_result)
        {
            // Clear the existing result data so we can get the new data
            $this->result_object = array();
            $this->result_array = array();
            
            if ($store_result)
            {
                // Set the new result id
                $this->result_id = @mysqli_store_result($this->conn_id);
                $this->num_rows = $this->num_rows();
            }
        }
        
        return $has_result;
    }
}

Which can simply be used like this:

Code:

$storedProc = 'CALL fetchMultipleResultSets()';
$parameters = array();
$query = $this->db->query($storedProc, $parameters);

if ($query->num_rows() > 0)
{
    foreach ($query->result() as $row)
    {
        // Do stuff
    }
}

// False if you simply want to discard the result
if ($query->next_result())
{
    // Same again, but with the new result set
    if ($query->num_rows() > 0)
    {
        foreach ($query->result() as $row)
        {
            // Do stuff
        }
    }
}

// Clear the status result set generated by the stored procedure
$query->next_result(FALSE);

By clearing the result_object and result_array, the existing functions are used to populate these during the next call to result(), row(), etc.
This code also sets the num_rows field, and returns TRUE or FALSE depending on if additional result sets where found.
By using next_result(FALSE); you get the behaviour from your old post, where one simply discards the result set to get stored procedures working.