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:
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":
Little Bobby Details has spoken.
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>'; } ?>