Is stored procedure can return 2 results ?

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
User avatar
olog-hai
Forum Commoner
Posts: 34
Joined: Thu May 31, 2007 8:47 am
Location: Québec city, QC, Canada

Is stored procedure can return 2 results ?

Post by olog-hai »

Hi,

I have a stored procedure that return two select.

is there a way to access this two result in PHP ?

I use mysqli_multi_query to call my stored procedure, and I use mysqli_store_result to work with the result, but when I want access the second result of my stored procedure, I use mysqli_next_result() and this return me null, and I get this error mysqli_fetch_array expects parameter 1 to be mysqli_result.

thanks.
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

This is a little script I built to view results from MySQL stored procs. You might find it helpful:

Code: Select all

<html>
<head><title>Stored Procedure Tester - MySQL</title></head>

<body>
<?php
$sql = '';
$show_results = false;

if (isset($_POST['form_submitted']))
{
    // The form was submitted
    $sql = $_POST['query'];
    
    echo '<p>The query you entered entered was <strong>' . $sql . '</strong>.</p>';

    // Change the params to you own here...
    $mysql = new mysqli('localhost', 'USER', 'PASSWORD', 'DATABASENAME');
    
    if (mysqli_connect_errno())
    {
        die(printf('MySQL Server connection failed: %s', mysqli_connect_error()));
    }
    
    // Check our query results
    if ($mysql->multi_query($sql)) 
    {
        $show_results = true;
        $rs = array();
        
        do {
            // Lets work with the first result set
            if ($result = $mysql->use_result()) 
            {
                // Loop the first result set, reading it into an array
                while ($row = $result->fetch_array(MYSQLI_ASSOC)) 
                {
                    $rs[] = $row;
                }
                
                // Close the result set
                $result->close();
            }
        } while ($mysql->next_result());
    }
    else
    {
        echo '<p>There were problems with your query [' . $sql . ']:<br /><strong>Error Code ' . $mysql->errno . ' :: Error Message ' . $mysql->error . '</strong></p>';
    }
    
    $mysql->close();
}

echo '<form id="proc_tester" action="' . basename($_SERVER['SCRIPT_FILENAME']) . '" method="post">
    <p>Enter your procedure:</p>
    <p><input type="text" name="query" size="175" maxlength="255" value="' . $sql . '" /></p>
    <p><input type="hidden" name="form_submitted" value="true" /><input type="submit" name="submit" value="Submit query" /></p>
</form>';

if ($show_results) {
    echo '<pre>';
    print_r($rs);
    echo '</pre>';
}
?>
</body>
</html>
What this does, essentially, is put everything into a multi-dimensional array. It will be up to you to locate the array member you are looking for.
User avatar
olog-hai
Forum Commoner
Posts: 34
Joined: Thu May 31, 2007 8:47 am
Location: Québec city, QC, Canada

Post by olog-hai »

Thanks Everah,

Your code was very helpful.

So Yes we can call a stored procedure with two select statement.

Amen.

have a nice day.
Post Reply