MySQL Multiple Result Procs in PHP

Announcement (October 16, 2009): A newer, cleaner, more robust query/procedure tester is now available to test queries and stored procedures in PHP using MySQLi. You can read about it and get it right here.

I thought this might be useful for those of you running MySQL 5+ with stored procedures that return multiple result sets.

<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>.';
 
    // 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>';
}
?>


FTR: 0/20

7 Replies to “MySQL Multiple Result Procs in PHP”

  1. Glad I could help. I actually wrote a query interface tool that provides a little more information when calling procs. Perhaps I will post that soon. It helps me tremendously everday.

  2. What a fantastic piece of code.
    I have been looking to solve the mulktiple result set problem of mysql with php on and off for 6 months, and in the last 48 hours it became urgent, and frustrating.
    I eventually came to the conclusion I needed mysqli, but had no code example to try and understand.

    Now I have a stored procedure tester as well!!!

    God bless you richly my friend!

    You were #1 in Google with “handle multiple result sets php mysql”

    Regards Jem

  3. Dude, thanks for the comment and the google ranking note. That is awesome. I think, when I can actually get around to updating my blog, that I will post my newest query tester. It has a bit of profiling built in so you can look at memory consumption, time to process and a few more pieces of useful information.

Leave a Reply

Your email address will not be published. Required fields are marked *