Thoughts, rants and commentary of a simple man

Handling multiple MySQL results in PHP

Posted on October 16th, 2009 in Geek Stuff,MySQL,PHP,Programming

Back 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 called a stored procedure that happened to have multiple results in it. Skip to how this really works…

Since then I have improved my little handler, added some library code to actually handle queries and results (and timers and requests and… dude, so much). The new code, which is much fatter (not necessarily better, just fatter in that it has a lot more features in it), now encapsulates much of what the original, simple script was doing. Unfortunately, the script is no longer as simple as I wanted it to be. However, it does now come in several files which might be of use to you in some of your own code.

Because of the change that I made to the way this code works, I have decided to package it into a little zip file for your downloading pleasure. The zip file includes:

  • index.php (the actual procedure/result tester)
  • A lib directory, which contains:
    • memorystats.php (A memory use and reporting object)
    • mysql.php (The database connection and query object)
    • mysqlresult.php (The database result object)
    • request.php (The HTTP request object)
    • timer.php (The process timer object)

It should be ready to use right out of the box, with the exception of making sure to add your own username, password and database name to the $db->connect() call (at about line 85 in index.php). I’m sure you could expand it to be more of what you want in a tester (like adding a database chooser to it – right now I have a database server chooser, but not a database chooser within a server) but overall it is a very nice little script that does pretty much what it was built to do: run a query/procedure and return all of the results in it.

Please note: This is seriously not something that you should think to use in a production environment. The farthest this has ever made in any of my architectures is the dev machine. Not that the code is not good. It is just that putting something that has free form access to your database onto a production machine is, in my opinion, a remarkable stupid thing to do and I would hate to have my name associated with code that was used to exploit your business because you chose to put a huge gaping hole into your network through a simple little test script. Just sayin’.

Under the hood
Before diving into that mash of code I included in the zip file, it might be a good idea to see just the relevant code so you can, if you want to, just tap into the multiple result set handling of MySQL results in PHP. Without further ado…

<?php
// Set a query
$sql = "SHOW TABLES FROM `mysql`; SELECT VERSION() AS `version`; SELECT NOW() AS `date`;";
 
// Change the params to you own here...
$mysql = new mysqli('HOSTNAME', 'USERNAME', 'PASSWORD', 'DATABASE');
 
if (mysqli_connect_errno()) {
	die(printf('MySQL Server connection failed: %s', mysqli_connect_error()));
}
 
// Initialize our result set array
$results = array();
 
/** 
 * Check our query results
 * 
 * This is where the magic happens, and it must be this way anytime you use a
 * stored procedure. The reason for that is the MySQL server always sends a 
 * status return with any query, even a select query. That means that even a
 * single result set select query will return two results. Those results will
 * not necessarily make it to your application (I think the client handles 
 * that) but in my experience I have always had to use multi_query to get this
 * to work.
 */
if ($mysql->multi_query($sql)) {
	do {
		// Create the records array
		$records = array();
 
		// Lets work with the first result set
		if ($result = $mysql->use_result()) {
			// Loop the first result set, reading the records into an array
			while ($row = $result->fetch_array(MYSQLI_ASSOC)) {
				$records[] = $row;
			}
 
			// Close the record set
			$result->close();
		}
 
		// Add this record set into the results array
		$results[] = $records;
	} while ($mysql->next_result());
}
 
// Close the connection
$mysql->close();
 
// Set our output type
header('Content-Type: text/plain');
 
// What do we have
print_r($results);

And there you have it. Nice, simple, clean and easy to use all by itself.

4 Responses to “Handling multiple MySQL results in PHP”

  1. […] About Me   MySQL Multiple Result Procs in PHP Posted on June 1st, 2007 by Robert Gonzalez 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. […]

  2. It was great. Thank you for sharing this code. You saved me much time…:)

  3. How to use parameters in the query ?

  4. @Mayank As in using prepared statements?

Leave a Reply


Fatal error: Call to undefined function show_subscription_checkbox() in /home1/robertg2/public_html/wp-content/themes/dark-sky/comments.php on line 79