Handling multiple MySQL results in PHP

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.

Little Bobby Tables? Or do I prefer Robert?

Way back when I ran across a comic at xkcd.com that outlined the funny things that can happen when developers forget to escape input:

Little Bobby Tables strikes hard

This brought a tremendous amount of laughter to everyone that works in my department (since many are database people and a few are developers) and from that point on I had the lovely nickname “Little Bobby Tables”.

In an effort to try to rectify that I found a LOLCAT picture that sums up a little better what my position is on the name “Little Bobby Tables”:

Bobcat prefers to be called Robert

Little Bobby Details has spoken.

Meeting up with some local MySQL users

This past Monday I had the fortune of attending my very first meetup. I had set a goal for myself a few weeks ago that I would attend one before the end of the year. It happened way sooner that I thought it would and resulted in me being able to mix it up with a few fine folks at the Silicon Valley MySQL Meetup.

There was a business need for me hitting this thing up. Firstly, I am a web developer. I am not a DBA and I am certainly not a MySQL guru. I can write queries but the extent of my MySQL knowledge ends right about there. Secondly, I am responsible for management and maintenance of all of our MySQL servers at work. There is one chief DBA who is a Sybase queen, another two or three folks that know their way around a Sybase server (and to a lesser extent a general database server) and then there is me and my colleague who write web apps and MySQL queries. Thirdly, I am an administrator for a very popular PHP developers forum and knowing how to get myself out of the stupid ass scraped I have gotten us into when it comes to MySQL would be darn handy.

So I set out to hit this meetup. It was held at the Sun Microsystems complex in Palo Alto (or Redwood City or wherever you are when you come off the Dumbarton Bridge on the Peninsula side). I was looking forward to finally meeting a man that I have been communicating with for some time now (yes, you Don) and to networking with other MySQL/PHP/Web developers in my local geographic region. And I was not disappointed.

The talk itself was a little boring to be honest. But that is because I am not at all interested in database shards and the whatnot. However I was very interested in meeting Don Ravey, a fellow moderator on our forums, Mr. Tish Wood, a very prominent member of the PHP Meetup community and one of the coordinators of the meetup Eric Bergen from Proven Scaling. Not only that, but there were a host of other people there that provided excellent commentary, questions and challenges for the speaker of the evening.

I love being in the mixed company of brilliant people like that. It is humbling, exciting and gives me something to look forward to. I so enjoyed being there, connecting, talking with people, meeting a few folks and hopefully being able to glean something from someone somewhere. I just hope that I can, at some point, make it to another meetup. Perhaps a LAMP meetup or a PHP meetup. After this last meetup Tish asked me if I would at all be interested in speaking to a group at the Greater SF Bay Area LAMP meetup. I think I would love to do that.

If I can find an evening to get away for a while longer than this last evening. And if we can find a way to not have a meetup in San Francisco. Because as much as I like socializing and hanging with other technology professionals, I cannot fathom the thought of heading to The City for a 7:00 PM meeting on a weekday evening. Until then though, I can start thinking about what I would talk about if I were to ever talk.

And hopefully I will be a little more relevant to a n00B than that fella at the MySQL meetup was to me. He was good and he knew his stuff for the most part. But it was not my cup of tea so I sort of lost interest a little in the subject matter. That was ok though, because I met people, got numbers and had a free coke. In the end, what could be better than that?

Falling in love again

It has been a while since I have had a chance to translate the Simple Factory method and the Factory Pattern from the Head First Design Patterns book. I have wanted to, but I have been so buried in development that I have really not had time to spend with my now second favorite book.

Of course, I am not complaining. I actually have developed an entirely new fondness for web development in recent weeks. I have been nose deep in framework development at work. As I have mentioned before, my company is very technology oriented for not being a tech company. As a result, I get to develop applications for all the newest technologies every day. I use Linux (RHES or Fedora Core 6), Apache 2, MySQL 5 and PHP 5. And I am loving it.

In fact, I have reviewed some PHP 4 code recently and thought that it so needed to be spruced up into some cool PHP5 objects. And then it hit me. I am a spoiled technobrat. I get to work in an environment most devheads would dream of being able to play in. I get to do things locally, in dev and in test that many never get a chance to do at all. And I have a team of experienced IT professionals working with me to guide me and help me learn how to get things up and running and keep them that way. I work a team of network specialists, several data masters (and one super database guru) and a guy who knows computers inside and out. I also have an associate web developer that I get to collaborate with and that gets to work with me in unison as we develop the framework that will drive all of the company’s web applications in the future. I am spoiled rotten.

But the thing I like most about the environment that I code in is that it is as close to the cutting edge of stable technology as it gets. Whenever we need a new site, we roll out an entire machine for it. We set it up from the go with our own choice of OS (Linux at the moment) and we get to set up our server software, database software, our code management software (if needed on the machine) and everything we want, the way we want it. And if we don’t like it, we get to start over the way we want. It is just plain awesome.

And it has made me fall in love developing again. I didn’t realize it until a few days ago, but I am truly enjoying developing now more than I have in the past. And not just server side development. I am talking about the whole enchilada. Client side, UI, Ajax, markup, CSS… oh baby I am getting sweaty palms just writing about it.

Anyway, try to not hate me because I am spoiled. You can me because I am beautiful, but not because I am spoiled.

Essential tools for any PHP developer

The following list was written by Chris Neale, of ooer.com fame, and reproduced with permission from the PHP Developers Network Forums

I’m sort of writing a new article listing all the things I use, or should use, to write awesome websites. If you fancy adding to the list then please do, it’d help.

The Obvious
PHP – I think we all know what this is.
MySQL – And this.
Apache – My personal choice of web server mainly because I understand htaccess.

Libraries
Swiftmailer – The best PHP mail sending library available bar none.
ADODB Lite – The best database abstraction layer around.
Template Lite – The best template engine.
FPDF – Awesome PDF library.
SHA256 – Hashing replacement for MD5/SHA1.
HTMLPurifier – Stop XSS attacks before they happen.
SimpleTest – Unit Testing library that’ll improve your code no end.
JQuery – Javascript AJAX/effects/make stuff easier type of thing.
JPGraph – Delightful library for charts and graphs in PHP.
ExCanvas – Get < canvas > working seamlessly in IE.

Browsers
Firefox – Best browser around.
Internet Explorer – Needed for testing because there’s still people who suffer it.
Opera – Again, needed for testing.
Safari – And again.

Extensions and Plugins
Web Developer – Absolutely critical for finding out information about the clientside things.
Firebug – Brilliant javascript console that makes AJAX so much less of a headache to debug.
YSlow – Site speed profiling plugin (needs Firebug).
NoScript – The easy way to test a site with JS and more switched off.
SearchStatus – Site search profiler, find out keyword information, page rank, Alexa rank etc.
XDebug – An awesome PHP debugger/profiler.

I’m not really interested in what editor/IDE you use, that’s a very personal choice. I’m interested in things that are absolutely top of their class couldn’t live without them essentials.

Any new developer would do well to glean something from this information.

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