phpied.com

Let's go ahead and optimize our phpBB installation for front-end performance. I'll follow Yahoo's 14 optimization rules, but only implement the ones that apply for phpBB. During this short workshop there will be no changes to the phpBB code, we'll create a new template instead, so that in case something bad happens, your board will continue to work normally. As an example, I'll use the bgcanada.com/phpBB2, the board I volunteer to administrate.

The plan

1. creating two subdomains to host assets - images and css, maximizing parallel downloads (also following rule #9)
2. creating a new template (theme) based on the default subSilver template
3. moving CSS to an external file (rule #8), merging the two css files (rule #1)
4. copying images and css to the new subdomains
5. making sure css is served gzipped (rule #4) and also making sure php pages are served gzipped)
6. turning ETags off (rule #13)
7. setting the Expires header (rule #3)
There will be no explanations on the reasoning why these rules exists, but only how to implement the applicable ones in phpBB. It's a good idea though to go through the links above and read the detailed description of the rules. Maybe you'll find something that can be done in addition to the plan above. The suggested implementations were done and tested on a normal $60/year shared host, working around the limitations of such hosting. I'll speculate that at least 90% of the phpBB installations out there use shared host so I hope my implementation will be relevant to your phpBB install as well.

New subdomains

Typically, shared hosts allow subdomains and have their admin interface for doing so. In case your host is an exception, alternatively you can buy one or two extra domains to use for the same purpose - storing page assets.

So in my case, for the domain bgcanada.com I create two sub-domains - i1.bgcanada.com and i2.bgcanada.com (i as in image). Now the question would be how to divide assets between the two domains. I decided that more or less half of the images are multilingual (they don't contain text) so these go to i2. i1 will have all the rest - translatable images found in subSilver/images/lang_english, smilies and the stylesheet.

creating a new template

Let's go ahead and create a new template (theme), based on the default subSilver template. I'll call mine "sso" as in "subSilver optimized" (or the longer mirror-like version "ssoss" as in "stoyan stefanov, oh, so smart" )

For this purpose, just take the contents of path/to/phpbb/templates/subSilver and copy it under the name "sso", so you'll have /templates/sso, an exact subSilver copy.

Now go ahead and modify templates/sso/subSilver.cfg, the theme configuration. First rename it to sso.cfg. Then at the top of the document, replace the line:

$current_template_images = $current_template_path . "/images";

with

//$current_template_images = $current_template_path . "/images";
$i1 = "http://i1.bgcanada.com/sso"; // language and smilies
$i2 = "http://i2.bgcanada.com/sso"; // the rest

use your domain, of course.

Then do a search/replace. All occurrences of:

$current_template_images/{LANG}/

should become

$i1/{LANG}/

And all occurrences of

$current_template_images/

become

$i2/

At the end of the file, just before ?> add a new line:

$board_config['smilies_path'] = 'http://i1.bgcanada.com/smilies';

This will overwrite the path to the smilies you set in the admin interface. This is optional, you can achieve the same by using the admin panel, but we said we wanted to have the board unaffected by the changes.

Now open templates/sso/theme_info.cfg and replace all occurrences of "subSilver" with "sso".

Now the bad news is that some of the template files (.tpl) still contain relative paths to images, we need to make these absolute and pointing to the subdomians. Since these paths are hardcoded in the templates we're sure that they are language independent, so they'll all go to the i2 subdomain. If you have a good text editor that can search/replace in multiple files, go ahead. Alternatively, use the php script I came up with. Download it, copy it to your /templates/sso folder, rename to replace.php and use it like:
C:\path\to\phpbb\templates\sso> php replace.php
It will report what it replaced.
It only searches for templates/subSilver/images and replaces with http://i2.bgcanada.com/sso

Yep, almost done with the template, one last step - the css.

Moving CSS to an external file, merging

In the default subSilver there are style definitions in overall_header.tpl. Remove them, the whole thing between the <style> tags and replace with:

<link rel="stylesheet" href="http://i1.bgcanada.com/sso.css" type="text/css">

Now rename subSilver.css to sso.css. Copy the contents of formIE.css and append it to the end of sso.css. Optionally, you can walk through the new file and strip all comments and white space. Or use my stripped version.

Note that as a side effect, the font of the board will look somewhat different, because the definitions in subSilver.css are not exactly the same as those in the overall_header.tpl. It's not a big difference, but if it's important to you, just ignore the original subSilver.css and create sso.css copying the styles from overall_header.tpl and formIE.css.

We're done with the files, the rest is sysadmin stuff.

copying files to the new subdomains

That's easy, just take everything from templates/sso/images (leave the lang_english or any other language folders) and copy to http://i2.bgcanada.com/sso

Than take all lang_* folders and copy to http://i1.bgcanada.com/sso, so you'll have http://i1.bgcanada.com/sso/lang_english/

Now copy all smilies from phpbbroot/images/smilies to http://i1.bgcanada.com/smilies/

Now take the sso.css you created and copy to http://i1.bgcanada.com/sso.css

Last, take the whole sso directory and copy to the main domain - http://www.bgcanada.com/phpBB2/templates/ so that it's in the same folder next to subSilver.

Login to the admin panel and activate the new theme as usual. Login to your account and change your theme preference to sso. You still want to test before making it a default theme for everyone.

At this point you should be able to browse your forum with the new theme and everything should look like as if you were using subSilver.

Serving gzipped content

The rule is that all served files should be gzipped, with exception for images, because gifs, jpgs, pngs are already compressed.

To serve the normal html (php) pages gzipped, just use the built-in phpBB feature, log on to the admin panel and enable gzip compression.

To serve sso.css gzipped, ideally you should only create an .htaccess file and have Apache do it for you (more info). Unfortunately my host won't allow it, so I took the alternative path - have PHP gzip and serve the css file. To do so I created an .htaccess file http://i1.bgcanada.com/.htaccess and put in it:

AddHandler application/x-httpd-php .css

This affects all CSS files (but we have only one) and makes them php scripts. If your host allows you to use php_value in .htaccess, you can do the rest of the job by only using .htaccess. Otherwise create a http://i1.bgcanada.com/php.ini file and put in it:

[PHP]
default_mimetype = "text/css"
zlib.output_compression = On
zlib.output_compression_level = 6
expose_php = Off
auto_prepend_file = "pre.php"

The first line makes php send text/css header instead of the default text/html. Some browsers won't like CSS files served with text/html header. The second line enables compression and the next line sets the compression level (could be up to 9). 4th line is absolutely optional, just removes an extra header that PHP sends. The last line sets that all served php files should include the file pre.php as if you used include "pre.php" inside every PHP script. This is actually used later for the Expires header.

turning ETags off

That's super easy. Just add .htaccess rules in both i1 and i2 to say:
FileETag none

Expires header

To set the expires header, add these lines to .htaccess on both i1 and i2

ExpiresActive On
ExpiresDefault "access plus 2 years"

This makes all files - images and CSS - on i1 and i2 expire in 2 years, if that's too much/not enough, feel free to change.

If it doesn't work for you (it didn't for me, because of the host), you can go the php auto_prepend route described above and add in the pre.php file
header('Expires: ' .gmdate("D, d M Y H:i:s",time() + (60 * 60)) . ' GMT');
Feel free to set 60 * 60 to whatever you think makes sense for you. Note that this will only affect the CSS file, images will be served "normally". You can also have PHP serve all the images, like we did for CSS, but I think it's probably too much.

Sysadmin summary

There was a lot of if-then above so let me summarize what worked for me, but try your other options first to see how "liberal" your host is in order to do a better job than me.

http://i1.bgcanada.com/.htaccess

AddHandler application/x-httpd-php .css
FileETag none

http://i1.bgcanada.com/php.ini

[PHP]
default_mimetype = "text/css"
zlib.output_compression = On
zlib.output_compression_level = 6
expose_php = Off
auto_prepend_file = "pre.php"

http://i1.bgcanada.com/pre.php

<?php
header('Expires: ' .gmdate("D, d M Y H:i:s",time() + (60 * 60)) . ' GMT');
?>

Done!

We're done! Did it take more than an hour? Hope not, although with these computers and stuff, everything takes more time than expected.

Do you see anything missing? Or something that can be improved? Or something didn't work for you? Please post a comment. The whole posting is a bit fast-paced and written in a rush (I really need to go to bed), so if there's something unclear please ask.

So I launched this little tool csssprites.com that allows you to upload images and create one CSS sprite image, plus it gives the background-position CSS definitions to use in order to show parts of the sprite. People have been trying it out, but unfortunately sometimes uploading 20 megs of images to create a sprite, which is not the point of the css sprites technique. Anyway as a result I exceeded the disk quota my host gives me and since the site is hosted on the same server as this blog, the blog stopped working. Hence the publishing diet.

Initially I blamed WordPress because it started acting strangely, asking me to update my database, saying that I don't have admin privileges, then not loading the CSS files and finally just stopped working even on the front end. I said oh well, I need to upgrade it anyway, so let's do it now. Just trying to FTP a single file got me the message that I can't copy so I finally figured out the real case - the exceeded disk space.

It's all good now, I just deleted all CSS sprite images that were generated, I was planning to do a cron job do delete the ones older than a day or two anyway, but never got around doing it. I should just check and warn the cssspritres.com users not to upload huge images, because this is not how CSS sprites were designed to work anyway.

Long story, short message. I'm off the publishing diet now.

Meanwhile I wrote an article for the International PHP Magazine, it's an intro to unit tetsing with PHPT, called "PHPT - Unit testing for the rest of us". Nice, eh? Just got an email today that the new IPM issue is out the door, you can check the TOC here. I wanted to further experiment with PHPT and was thinking of writing this test generation tool. Say you have a bunch of classes, you run the tool and it generates PHPT test stubs, based on the classes and methods in finds. Then you tweak the generated stubs here and there to implement the actual tests. PHPUnit has this feature, so why not PHPT as well. We'll see if I'll find the time.

On a different technology, I was playing around implementing the decorator pattern in Javascript, will post about it later (sneak peek).

On a different subject, just added a few very simple tools to my favorite Textpad, I found them helpful for PHP development, will post about them later.

On a yet another subject a few days ago I finished a draft outline for my new book-to-be and we started discussing with the editor.

On a totally unrelated subject, I did a new phpBB theme (copy of the default subSilver) following as many of the Yahoo front end performance rules as I found applicable. Naturally, I'll post about it later.

Otherwise life has been good. I moved with my family to LA to start working for this company called Yahoo!. Work is great, LA was bit of a surprise and not very welcoming, but hey, it's the experience. We had some initial rental issues (we lost quite a bunch of money double and some point triple renting), then there was the stress of the whole move, having to start everything over again, driving licenses, shocking 20% APR rates from Toyota, credit cards refusals and stuff (the only thing that I still use here from Canada is the Costco card!). Yahoo did help a lot during the moving process, can't imagine what would have been without all the little and not so little perks I got during the relocation. So anyway, after all that initial shock, the family is starting to settle. The kids just loooove Disneyland, we ended up getting anual passes for California residents, so we'll be seeing it a lot. Also the beach is quite nice, not the cleanest mind you, but it's probably because we're new don't know where to go, we just hit the closest to us, in Venice. By the way, Venice is amazingly similar to some little Bulgarian towns on the Black Sea. In general LA is quite expensive, especially the Santa Monica area, where the office is, but I wanted to be close to the family in those times of change, so we ended up renting a place only 5 miles from the office (still getting used to those miles and pounds). I proudly bike to work now, doing my share in saving the environment. Half an hour in each direction, it's a nice excersise. Talking about biking, here's some biking-to-work wisdom for you:

Tree branches are hanging lower than they appear.

also

Just when you thought you learned how to bike without using your hands and to light a cigarette meanwhile... you didn't.

Here's my last weekend's project - a web-based tool to generate images for CSS sprites: http://www.csssprites.com. Cool domain name, eh? I couldn't believe it was not taken.

CSS Spr...what?

This is a simple technique used for page load performance purposes. Since HTTP requests are the most expensive thing you can do in regards to front-end performance, you strive for making as little requests as possible. So instead of having the browser download 4 rounded corner images for example, you create one image that has all four. Then you use CSS' background-position to only show the part of the image you want. More on the subject in this ALA article

How does the tool work

You upload as many images as you want and the tool generates a mosaic of all images, gives you the result as PNG and gives you the coordinates you need to use in the background-position declaration. The tool also gives you an html page as an example, so you can save both the PNG and the html page for reference.

Image size

If you properly optimize the big image, you might actually have smaller size than all the individual images combined. In my tool, the PNG image generated is not optimized at all, I leave this to you to use PNGOUT or any other tool you know. Also you can convert the PNG into GIF if that's better for your purposes.

Implementation - PHP

The PHP code is fairly simple. The actual spriting (is that a word?) class takes a list of images and calls getimagesize() on each one to get the dimensions. The image with the biggest height is used as distance between images. The rest is just composing the imagemagick command that will to the work. Here's the important method:

<?php
function combine() {
    if ($this->distance === false) {
        $distance = $this->_biggest;
    } else {
        $distance = (int)$this->distance;
    }
 
    if ($this->output_dir === false) {
        $output_dir = $this->_dir;
    } else {
        $output_dir = $this->output_dir;
    }
 
    $half = ceil($distance / 2);
 
    $coord = array();
    $y = 0;
 
    foreach ($this->images as $i=>$data) {
        $this->images[$i]['x'] = $half;
        $this->images[$i]['y'] = $half + $y;
        $coord[] = '-page +0+' . $y . ' ' . $i;
        $y += $data[1] + $distance;
    }
 
    $cmd = 'convert ' . implode(' ', $coord)
         . ' -background none -mosaic -bordercolor none -border '
         . $half . 'x' . $half
         . ' ' . $output_dir . '/result.' . $this->output_format;
    system($cmd, $ret);
 
    return $ret === 0;
 
}
?>

Implementation - JS

In the spirit of web2 I couldn't afford a complete page reload although it would've been much simpler. I just had to get fancy. YUI to the rescue. On page load I set up the form for async request, using YAHOO.util.Connection. In case of file uploads YUI generates an iframe behind the scenes and uploads to the iframe. Then it takes whatever is in the body on the iframe and gives it to you instead of the XMLHttpRequest's responseText property.

So the files are uploaded to upload.php which calls the class that has the method mentioned above then loops through the $images property of the said class and writes the example html file as well as prints out a JSON string with the same image information.

YUI's Connection calls my callback function and I get the invaluable responseText. Parsing the JSON with the json.js, I get a JS object. Looping through it and DOM-generating a table of results is the semi-last step. The last is (we're fancy, remember?) to yellow-fade the background color of the result, using YAHOO.util.Animation.

BTW, I got fancy once again and combined and minified json.js with my JS file, so that there is one less request and a side effect impossible to read. The unminified version of the JS that does all the work is here for reference.

Comments

I hope this tool cane be useful for quickly generating those sprites, if only for prototyping purposes. Any comments, requests, bug reports are all very welcome.

And how do you like the version of the tool? Anyone n00b can do "beta", it takes a true h@x0r (or something) to do a better job

Ah, yeah, and the page badly needs a stylesheet, do you want to help?

I've been selected as a judge in this year's Open Source CMS Award, organized by Packt Publishing. I'll be voting for three out of 5 PHP CMS projects where those 5 are selected by votes from the site visitors. Join me in a discussion I started at opensourcecommunity.org "What do you think makes a good CMS?"

Nice title, eh?

OK, so all of a sudden theweathernetwork.com buttons I was using to show the weather in Sofia, Bulgaria and selected Canadian cities on the community site bgcanada.com, stopped working. Reason: unknown. So it was time for a change, since those buttons were generated by some javascripts, hosted by theweathernetwork, which is kind of a web 1.0 way of sharing content. In the days of APIs and stuff, this is a shame.

The first substitute that came to mind to check were the Yahoo APIs and feeds and, no surprise, they do offer weather feeds. All you need is the code of the city you're interested in (or zip code for the States) and you're good to go. I also wanted the temperatures to be displayed in Celsius, and there was indeed an option to set this. The feed URL for Montreal for example looks like:

http://weather.yahooapis.com/forecastrss?p=CAXX0301&u=c

With a little searching on the real Yahoo weather site, I was able to figure out the codes of the cities I'm interested in. From there it was a breeze.

Here's the script that takes an array of cities and produces a single HTML page with the weather information. It's using simplexml_load_file(), which makes things so simple, it's scary.

<?php
// array of cities we're interested in
// the codes are taken from weather.yahoo.com URLs
// by searching for the city
$cities = array(
    'Sofia'     => 'BUXX0005',
    'Montreal'  => 'CAXX0301',
    'Toronto'   => 'CAXX0504',
    'Vancouver' => 'CAXX0518',
    'Ottawa'    => 'CAXX0343',
);
 
// general purpose feed URL with placeholder for city code
$feed = 'http://weather.yahooapis.com/forecastrss?p=%s&u=c';
 
// loops cities
foreach ($cities AS $name => $code) {
 
    // load XML from the real feed URL
    $url = sprintf($feed, $code);
    $res = @simplexml_load_file($url);
 
    // on success, spit out city name and weather data
    if ($res) {
        $data = $res->channel->item->description;
        echo '<h2>', $name, '</h2>', $data;
    }
}
?>

If you use PEAR::MDB2, you can set a custom debug handler and collect all the queries you execute for debugging and performance tunning purposes, as shown before. But what if you're using PEAR::DB? Well, since PEAR::DB doesn't allow you such a functionality out of the box, you can hack it a bit to get similar results.

Simple app

Let's say you have a simple app:

<?php 
require_once 'DB.php';
 
$dsn = 'mysql://root@localhost/test';
$db =& DB::connect($dsn);
$db->setFetchMode(DB_FETCHMODE_ASSOC);
 
$sql = 'SELECT * FROM zipcodes';
$result = $db->getAll($sql);
$result = $db->getOne($sql);
$result = $db->getCol($sql);
$result = $db->getAll($sql);
$sql = 'SELECT zipcode FROM zipcodes';
$result = $db->getAll($sql);
$result = $db->getAll($sql);
$sql = 'SELECT CONCAT(zipcode, " - ", city) FROM zipcodes';
$result = $db->getAll($sql);
?>

Of course, this is an oversimplified example, usually you have more included files, class libraries and such, and it's not difficult to lose track of the database work as your app grows in complexity and size.

Now let's debug this app to see what type of database work it does.

Hacking PEAR::DB

In my case, I'm using MySQL, so I need to find the DB/mysql.php file in my PEAR directory. I open that file and find the simpleQuery() method. That's where all queries and up, sooner or later. I find this piece of code:

<?php
if (!$this->options['result_buffering']) {
    $result = @mysql_unbuffered_query($query, $this->connection);
} else {
    $result = @mysql_query($query, $this->connection);
}
?>

Then I hack this piece of code, adidng some lines before and after it. The result:

<?php
// start
$start_time = array_sum(explode(' ',microtime()));
// end
 
if (!$this->options['result_buffering']) {
    $result = @mysql_unbuffered_query($query, $this->connection);
} else {
    $result = @mysql_query($query, $this->connection);
}
 
// start
$query_took = array_sum(explode(' ',microtime())) - $start_time;
@$GLOBALS['global_query_counter']++;
@$GLOBALS['all_the_queries'][$GLOBALS['global_query_counter'] . ' - ' . $query] = $query_took;
//end
?>

Now as my app's pages are executed, I'll collect invaluable DB information.

Reporting

Let's see what we've collected.

You can add different types of reports in the footer of your application, or better yet, you can register a shutdown function to do the same. Here are some reporting ideas:

<?php
// report 1.
echo "<pre>All the queries, by the order they are executed:\\n";
print_r($GLOBALS['all_the_queries']);
echo '</pre>';
 
// report 2.
echo "<pre>All the queries, ordered by the time they took, descending:\\n";
arsort($GLOBALS['all_the_queries']);
print_r($GLOBALS['all_the_queries']);
echo '</pre>';
 
// report 3.
$sum = 0;
foreach ($GLOBALS['all_the_queries'] AS $t) {
    $sum += $t;
}
echo '<pre>';
echo 'Total number of queries:   ' . $GLOBALS['global_query_counter'] . "\\n";
echo 'Total time spend querying: ' . $sum;
echo '</pre>';
 
 
// report 4.
$distinct = array();
foreach ($GLOBALS['all_the_queries'] AS $q=>$t) {
    $parts = explode(' - ', $q);
    unset($parts[0]);
    $query = implode(' - ', $parts);
    @$distinct[$query]++;
}
echo "<pre>How many duplications:\\n";
arsort($distinct);
print_r($distinct);
echo '</pre>';
?>

Report results

Let's see what these reports gives us.

All the queries, by the order they are executed:
Array
(
    [1 - SELECT * FROM zipcodes] => 0.00626707077026
    [2 - SELECT * FROM zipcodes] => 0.00730204582214
    [3 - SELECT * FROM zipcodes] => 0.00796985626221
    [4 - SELECT * FROM zipcodes] => 0.00654602050781
    [5 - SELECT zipcode FROM zipcodes] => 0.0058650970459
    [6 - SELECT zipcode FROM zipcodes] => 0.0239379405975
    [7 - SELECT CONCAT(zipcode, " - ", city) FROM zipcodes] => 0.00581502914429
)

All the queries, ordered by the time they took, descending:
Array
(
    [6 - SELECT zipcode FROM zipcodes] => 0.0239379405975
    [3 - SELECT * FROM zipcodes] => 0.00796985626221
    [2 - SELECT * FROM zipcodes] => 0.00730204582214
    [4 - SELECT * FROM zipcodes] => 0.00654602050781
    [1 - SELECT * FROM zipcodes] => 0.00626707077026
    [5 - SELECT zipcode FROM zipcodes] => 0.0058650970459
    [7 - SELECT CONCAT(zipcode, " - ", city) FROM zipcodes] => 0.00581502914429
)

Total number of queries:   7
Total time spend querying: 0.0637030601501

How many duplications:
Array
(
    [SELECT * FROM zipcodes] => 4
    [SELECT zipcode FROM zipcodes] => 2
    [SELECT CONCAT(zipcode, " - ", city) FROM zipcodes] => 1
)

Thanks for reading!

Any comments or suggestions are very welcome!

What could possibly be better for a writer's ego other than being read and being praised? Hmm...

So I wanted to have a page that shows the books I've written, together with their Amazon sales rank and the average customer rating and number of reviews. It's really easy. I took one example out of the PEAR book and slightly modified it.

The result is here.

Implementation at a glance

• Get an Amazon Web Services (AWS) subscription id
• Get a copy of PEAR::Services_Amazon package
• Make a request
• Display the response

When making a request, you need to say what type of request it is and what type of response you want. It's documented here. For the type of request, look under "API reference -> Operations" and for the response type, look under "API reference -> Response Groups"

The code

<?php
 
// include the PEAR package
require_once 'Services/AmazonECS4.php';
 
// Your AWS subscription id
$subscriptionId = '1WQDAES5PQ**********';
 
// create a new client by supplying
// subscription id
$amazon  = new Services_AmazonECS4($subscriptionId);
$amazon->setLocale('US');
 
// output options
// what do you need returned?
$options = array();
$options['ResponseGroup'] = 'SalesRank,ItemAttributes,Reviews';
 
// for which books
// comma-delimited list of ISBNs
$items = '1904811795,1904811914,1904811132';
 
// do the request
$result = $amazon->ItemLookup($items, $options);
 
// check for errors
if (PEAR::isError($result)) {
    print "An error occured<br/>";
    print $result->getMessage() . "<br/>";
    exit();
}
 
 
// some spaghetti to display HTML response
echo '<ul>';
foreach ($result['Item'] as $book) { // loop the books
    // URL
    echo '<li><a href="'. $book['DetailPageURL'] .'">';
    // book title
    echo $book['ItemAttributes']['Title'], '</a><br />';
    // authors, comma-delimited
    echo implode(', ',$book['ItemAttributes']['Author']);
    // sales rank
    echo '<br />Sales rank: ', $book['SalesRank'];
    // average rating
    if (!empty($book['CustomerReviews'])) {
        echo '<br />Rating: ';
        echo $book['CustomerReviews']['AverageRating'];
        echo ', based on ';
        echo $book['CustomerReviews']['TotalReviews'], ' reviews';
 
    }
    echo '</li>';
}
echo '</ul>';
?>
 
<a
  href="#"
  onclick="javascript:document.getElementById('response').style.display='block';"
  >Show complete response</a>
<pre id="response" style="display: none">
<?php print_r($result); ?>
</pre>

Today I saw a post at digg.com on image resizing with PHP and there was quite a discussion. Let me share the laziest way (that I know of) how to do it - PEAR::Image_Transform is all it takes. Here goes:

<?php
require_once 'Image/Transform.php';
$i =& Image_Transform::factory('');
 
$i->load('test.jpg');
$i->fit(100,100);
$i->save('resized.png', 'png');
?>

In addition, the Image_Transform library offers diffferent ways (to skin the old cat) to resize an image - by given pixel value, only on the X axis, on Y, scalling in percentage and so on. And, of course, the library can do much more than resizing, as you can see in the API docs.

It supports all kinds of image manipulation extensions - GD, GD1, ImageMagick, NetPBM, Imlib... If you want to use a specific one, you set as a parameter to the factory() method. In the example above I passed an empty string, so it will try to figure out what's available in my PHP setup and use it, trying Imagick2 first, then GD, then Imlib.

You have setOption() and setOptions() methods if you want to play around with the image quality and those sort of things.

This is a follow-up to Lars' comment about the PEAR book. In the MDB2 chapter I showed an example how you can create custom debug handlers in MDB2 and then gave a suggestion about a useful application of this functionality for performance tuning. Basically the idea is that your custom debug handler collects all queries that are executed during the life of a given script. Then, once the script finishes execution, the debug handler reports the stats that it has collected. In the book, the example is how you count the number of times each distinct query is executed, this way you can spot problems caused by the OO abstraction. For example, say you have a come class Users that has a method loadUser(), which abstracts the database work. While debugging with the custom error handler, you might figure out that without noticing, you're calling this method in a few places and it makes the same repeating query(queries) over and over again. So you can now optimize/cache results and so on.

The suggestion I made in the book is that in addition to counting, you might want to try executing all SELECTs again, just to see how much time they take and you can execute them once again, prepending them with EXPLAIN to get some details on possible room for improvement.

Now here's one solution to this suggestion. What you can see in this script is:

• Setting up MDB2
• Declaring a custom debug handler class
• "Attaching" it to the MDB2 instance
• Registering it for execution at the end of each script
• Testing it (creating a DB, table, some queries)

I hope you like it and try it out.

Here's the result of executing this script, you can see what you get back.

Room for improvement

Obviously, the method dumpInfo() can be improved. First, it can print out a nice table, instead of lazy print_r(). Then, it can include some logic, my idea is for it to "understand" the EXPLAIN results and to give you a hint by using colors, for exampe green background for queries that are OK, yellow for warnings and red for queries that definitelly need some work. Could be nice, no?

Test script

Kinda longish, but I hope I added enough comments. I also hope I didn't introduce any syntax errors while formatting it for posting here, chopping long lines, etc.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290

<?php
 
// PEAR error handling setup
require_once 'PEAR.php';
function pearError ($e)
{
  echo '<pre>';
  echo $e->getMessage().': '.$e->getUserinfo();
  echo '</pre>';
}
PEAR::setErrorHandling(
  PEAR_ERROR_CALLBACK,
  'pearError'
);
 
// creating MDB2 instance
require_once 'MDB2.php';
$dsn = 'mysql://root:test@localhost';
$mdb2 =& MDB2::factory($dsn);
$mdb2->setFetchMode(MDB2_FETCHMODE_ASSOC);
 
// The custom error handler
//
// It will collect all the queries being executed
// in the script, the collection is done by the
// collectInfo() method.
// Once the script finishes executing, we'll call
// the method executeAndExplain() which will
// execute all unique SELECTs once again
// in order to give us an info of how much time
// each query takes.
// Then executeAndExplain() will execute again
// all SELECTs, this time prepending an EXPLAIN
// so that we can get valuable
// optimization-related information
// Not only that but instead of simple EXPLAIN,
// we can use EXPLAIN EXTENDED and after that
// we can call SHOW WARNINGS -
// this will give us even more optimization hints
//
// http://dev.mysql.com/doc/refman/5.1/en/explain.html
// http://dev.mysql.com/doc/refman/5.1/en/show-warnings.html
//
class Explain_Queries
{
  // how many queries were executed
  var $query_count = 0;
  // which queries and their count
  var $queries = array();
  // results of EXPLAIN-ed SELECTs
  var $explains = array();
  // the MDB2 instance
  var $db = false;
 
  // constructor that accepts MDB2 reference
  function Explain_Queries(&$db) {
    $this->db = $db;
  }
 
  // this method is called on every query
  function collectInfo(
    &$db,
    $scope,
    $message,
    $is_manip = null)
  {
    // increment the total number of queries
    $this->query_count++;
    // the SQL is a key in the queries array
    // the value will be the count of how
    // many times each query was executed
    @$this->queries[$message]++;
  }
 
  // print the debug information
  function dumpInfo()
  {
    echo '<h3>Queries on this page</h3>';
    echo '<pre>';
    print_r($this->queries);
    echo '</pre>';
    echo '<h3>EXPLAIN-ed SELECTs</h3>';
    echo '<pre>';
    print_r($this->explains);
    echo '</pre>';
  }
 
  // the method that will execute all SELECTs
  // with and without an EXPLAIN and will
  // create $this->explains array of debug
  // information
  // SHOW WARNINGS will be called after each
  // EXPLAIN for more information
  function executeAndExplain() {
 
    // at this point, stop debugging
    $this->db->setOption('debug', 0);
    $this->db->loadModule('Extended');
 
    // take the SQL for all the unique queries
    $queries = array_keys($this->queries);
    foreach ($queries AS $sql) {
 
      // for all SELECTs…
      $sql = trim($sql);
      if (stristr($sql,"SELECT") !== false){
        // note the start time
        $start_time = array_sum(
            explode(" ", microtime())
        );
        // execute query
        $this->db->query($sql);
        // note the end time
        $end_time = array_sum(
            explode(" ", microtime())
        );
        // the time the query took
        $total_time = $end_time - $start_time;
 
        // now execute the same query with
        // EXPLAIN EXTENDED prepended
        $explain = $this->db->getAll(
          'EXPLAIN EXTENDED ' . $sql
        );
 
        $this->explains[$sql] = array();
        // update the debug array with the
        // new data from
        // EXPLAIN and SHOW WARNINGS
        if (!PEAR::isError($explain)) {
          $this->explains[$sql]['explain'] = $explain;
          $this->explains[$sql]['warnings'] =
               $this->db->getAll('SHOW WARNINGS');
        }
 
        // update the debug array with the
        // count and time
        $this->explains[$sql]['time'] = $total_time;
      }
    }
  }
}
 
// instance of the custom debug handler
$my_debug_handler = new Explain_Queries($mdb2);
// set debug option
$mdb2->setOption('debug', 1);
// set debug handler to the method that
// collects all queries
$mdb2->setOption(
  'debug_handler',
  array($my_debug_handler, 'collectInfo')
);
// register functions to be executed on shut down
// after the script has finished execution.
// Now that the show's over, it's the time to
// report what happened in this script db-access-wise
// First shutdown function executes the
// SELECTs again, the other one prints the results
register_shutdown_function(
  array($my_debug_handler, 'executeAndExplain')
);
register_shutdown_function(
  array($my_debug_handler, 'dumpInfo')
);
 
 
//
//
// At this point all MDB2 setup is done,
// time for the actual script to do something
//
//
 
// load the DB manager module
$mdb2->loadModule('Manager');
 
// drop database if it exists
// temporarily change the PEAR error handling
PEAR::pushErrorHandling(PEAR_ERROR_RETURN);
$mdb2->dropDatabase('test_db_explain');
PEAR::popErrorHandling();
 
// create and set a new database
$mdb2->createDatabase('test_db_explain');
$mdb2->setDatabase('test_db_explain');
 
// create table "events" from a definition array
// the table has event ID, name and date/time
$definition = array (
  'id' => array (
    'type' => 'integer',
    'unsigned' => 1,
    'notnull' => 1,
    'default' => 0,
  ),
  'name' => array (
    'type' => 'text',
    'length' => 255
  ),
  'datetime' => array (
    'type' => 'timestamp'
  )
);
 
$mdb2->createTable('events', $definition);
 
// create a primary key - the ID field
$definition = array (
  'primary' => true,
  'fields' => array (
    'id' => array()
  )
);
$mdb2->createConstraint(
  'events',
  'myprimekey',
  $definition
);
 
// load the class that has some static helper
// functions to work with MDB2's cross-RDBMS
// date format
MDB2::loadFile('Date');
 
// INSERT
// some data to insert into the events table
$data = array(
  // using MDB2-managed sequences
  'id'     => $mdb2->nextId('events'),
  'name'     => "Breakfast a Tiffany's",
  'datetime'   => MDB2_Date::unix2Mdbstamp(
    strtotime('Jan 15, 2007')
  )
);
// The "datetime" value shows how you can use
// any date format you wish as long as you're
// able to get a unix timestamp out of it
// In this case I'm using strtotime()
// Then there is a call to MDB2's date helper
// to get the MDB2 timestamp
 
// auto insert
// for the autoExecute() method we need to
// load the Extended module
$mdb2->loadModule('Extended');
$result = $mdb2->autoExecute(
  'events',
  $data,
  MDB2_AUTOQUERY_INSERT
);
 
//
// Time to SELECT something
//
// Using date helpers again
$start_date = MDB2_Date::date2Mdbstamp(0,0,0,12,31,1980);
$end_date   = MDB2_Date::date2Mdbstamp(0,0,0,12,31,2020);
$sql = 'SELECT * FROM %s WHERE %s > %s AND %s < %s';
$sql = sprintf(
  $sql,
  $mdb2->quoteIdentifier('events'),   // quote table name
  $mdb2->quoteIdentifier('datetime'), // quote field name
  $mdb2->quote($start_date, 'date'),  // quote data as date
  $mdb2->quoteIdentifier('datetime'), // quote field name
  $mdb2->quote($end_date,   'date')   // quote data as date
);
$res = $mdb2->getAll($sql); // execute
 
//
// * Bad practice code follows *
// Just some more inserts and selects, bad
// practice because these queries are not
// necessarily portable accross various RDBMS,
// lacking proper quoting and preparation
for ($i = 2; $i < 31; $i++) {
  $mdb2->query(
    'INSERT INTO events VALUES('
    . $i
    . ', "test event", "2005-05-05 00:00:00")'
  );
}
$res = $mdb2->getCol(
  'SELECT DISTINCT datetime FROM events'
);
$res = $mdb2->getRow(
  'SELECT * FROM events WHERE id
  IN (SELECT id FROM events WHERE id > 1)'
);
?>