Restoring PDO functionality

A few years ago, while PHP 5 was still in a state of flux, a change was made to the way that PDO handles parameters bound to prepared statements. Somewhere between versions 5.2.0 and 5.2.1 a change was made that gave rise to much annoyance and debate in bug 40417. Long story short, it used to be acceptable to reuse a placeholder in statement several times and bind a single variable to all of the instances thusly:

[sourcecode language=’php’]< ?php

// Connect to the database with defined constants
$dbh = safePDO_Factory::getInstance(PDO_DSN, PDO_USER, PDO_PASSWORD);
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

try {

// Construct SQL query
$query = ‘
SELECT
*
FROM
tblnews
WHERE
title ILIKE :search
OR content ILIKE :search
‘;

// Prepare the statement
$stmt = $dbh->prepare($query);

// Bind the search string variable to the statement
$smt->bindParam(‘:search’, $search, PDO::PARAM_STR);

// Execute the query
$stmt->execute();

// Check to see if we have any results
if ($stmt->rowCount() > 0) {
// Process the results here . . .
} else {
echo ‘No search results were returned.';
}
} catch (Exception $e) {
echo $e->getMessage();
)

// Destroy the database connection
$dbh = null;

?>[/sourcecode]

But all of a sudden the above rudimentary news searching code would cease to work if you upgraded to PHP 5.2.1 and there was no notice in the PHP change log at the time, which obviously led to much confusion. The issue was that it was no longer acceptable to bind a single variable to multiple placeholders – each placeholder required a unique name and explicit variable binding.

In much the same way that I subclassed the PDO connection class, the individual PDO Statement class can be extended to restore this multiple placeholder / single variable behaviour.

Continue reading Restoring PDO functionality

PHP Development with Snow Leopard

I spend most of my working day developing with PHP and Apache on a Postgres database back-end. Mac OS X coming complete with two out of these three things is great but traditionally the standard installs have been a little out-dated leaving most developers to rely on people like Marc Liyanage to take care of building the packages for us. Ironically, it was a remnant of a previous entropy install that was causing me grief with Apache earlier in the week1.

With Snow Leopard I was very happy to see PHP 5.3 installed but Postgres was still notable by it’s absence. In order to replicate my day-to-day environment, I’d have to roll up my sleeves and get a little dirty with the Terminal – but not too dirty!

I also upgraded PEAR to install PHPUnit, PhpDocumentor and Phing. I had been meaning to play around with some more advanced debugging and profiling tools for a while and found the instructions for installing Xdebug very helpful.

Finally, I installed some pretty GUIs makes my life easier and shinier:

1 In case you’re curious, an extra and obsolete configuration file in /private/etc/apache2/users/ cascaded down to httpd-userdir.conf and httpd.conf which meant that Apache would not start.

[ad#individual-post]

Snow Leopard

After waiting a decent length of time to make sure that there were no serious issues with Snow Leopard1 and with the release of OS X 10.6.1 I’ve finally got around to installing the thing. Here’s how things went for me. Hope my experiences help you if – and – when you install.

Continue reading Snow Leopard

Keep smiling

Today is World Mental Health day. The big screen in Edinburgh’s Festival Square on Lothian Road has been broadcasting related local short films for a couple of days now and will be the centre point of a day of activities to support the ‘What Makes You Smile’ initiative. Accordingly, here’s a collection of random smiles that I’ve noticed – or constructed – over the past few months.

smiles

Take some time today to notice the smiles around you. Here’s one to get you off to a good start: take a look at number two in the Buzzfeed list of the coolest flags ever, the flag of the North Caucasian Emirate.

I am the one and only

After previously explaining how to harden PDO I’m going to expand on the basic class I developed with the help of some design patterns.

A large part of using design patterns lies in recognising the situations in which each one should be used. The temptation is to implement the safePDO database class as a singleton so that only one instance of the object is used and extra connections are not made. An issue arises immediately as the constructor must be defined as public – the same as the parent PDO class – and therefore it will be able to be instantiated from anywhere. Besides, occasionally, you may also need to connect to another database which renders the singleton pattern useless.

A better choice for this situation, in my opinion, is the flyweight pattern. This is ordinarily used where a large number of similar items are to be used but can easily be applied in this situation so that database connection requests that have the same credentials can be reused.

In the following code a singleton factory pattern is used to handle and distribute requests for database connections, using the dsn and username as an array key for a pool of safePDO instances. You can rest assured that the database connection is wrapped in a try/catch block. As an added bonus, as all safePDO construction should be made from the factory class, we can make a check for this using a debug backtrace and therefore prevent a safePDO object from being instantiated at random.

If PHP code isn’t your thing – and assuming you’ve not already stopped reading by now – then you may want to give the code after the jump a miss. If your curiosity is piqued, read on . . .

Continue reading I am the one and only