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

About Robert Gonzalez

I am a geek. Actually, a professional geek as of September 2006, when I became a full time professional web developer. I am a husband, father, all around great guy and I am cute and cuddly. And I like pizza.

7 Thoughts on “MySQL Multiple Result Procs in PHP

  1. Excellent, this is exactly what I was looking for. Thank you!

  2. 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.

  3. 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

  4. 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.

  5. Fabiano on August 25, 2009 at 5:38 am said:

    GREAT ! Nice code ! Very thanks….

    From Joinville – SC, BRAZIL !

  6. Pingback: One man’s voice » Handling multiple MySQL results in PHP

  7. I have updated the code base with some more features and encapsulated many of the procedural calls into their own objects.

    Read about it and download the code for it here.

Leave a Reply

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

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Post Navigation