Thoughts, rants and commentary of a simple man

MySQL Multiple Result Procs in PHP

Posted on June 1st, 2007 in MySQL,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 Responses to “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. GREAT ! Nice code ! Very thanks….

    From Joinville – SC, BRAZIL !

  6. [...] in the day (June of 2007 to be a little more exact) I posted a little snippet about using PHP to hand multiple MySQL query results with the MySQLi extension. At the time it was something I was using just to see what was coming back from the database when I [...]

  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

Back to top