Some More PDO Weirdness

I’ve said before that I’m a great fan of PDO and use it wherever possible. That said there are some annoying quirks in it, one of which I encountered today. I’ll outline what I was trying to do, what I expected to happen and what actually happened. I’m also curious as to what anyone else thinks of this problem and whether it’s something worth reporting as a bug or if it’s a ‘feature’.

What I Was Trying to Do…

I’m currently adding a clearance item section to a website. This will aggregate items from over 35 stores across Canada and allow a user to search for items in a number of ways. As part of this I’ve created a business object to represent a clearance item. This mainly consists of protected properties with accessors and mutators for setting and getting the properties. Since the site is in English and French the accessors allow the properties to be formatted accordingly before being returned. I use this clearance item class when importing information into the database (items are imported from Excel worksheets, but that’s a topic for another post) and when returning results from searches. I use data mappers to handle the CRUD operations in the database. What I wanted to do was to find a quick and easy way to transform a row from a resultset into an instance of a clearance item object without using a full ORM solution. I thought I had found this with PDO.

The ‘Solution’

PDO allows you to set the fetch mode to fetch an object of a user defined class for each row returned from the database. You can either use PDOStatement::fetchObject() or PDOStatement::setFetchMode() to define this behaviour. It will take the names of the columns and assume that these are properties of the object, setting the appropriate values for these properties. In theory this sounds great but unfortunately it’s not as easy as it sounds.

The Problem

The problem in my case is that all properties of my object are declared as protected and I use mutators to set them. I anticipated this and provided a __set() method to provide the needed property overloading. I thought that what would happen is that PDO would attempt to set an undefined public property, triggering the __set() method where there is logic to call the appropriate mutator. Unfortunately it doesn’t work like that at all. According to comments in the PHP manual it seems that PDO uses something called reflection injection to set the properties inside the object regardless of their visibility. This meant that I was getting back an instance of my clearance item class with the protected properties set directly by PDO. This may not seem like too much of a problem, but my mutators include logic to transform the properties being set to the correct data type. For instance, columns containing integers or floating point numbers come back as strings and the mutators make sure they are set as numeric data types. Worst of all for me was the information on stores. I’m using MySQL’s GROUP_CONCAT() function to ‘implode’ information about stores and the inventory in each into a string, meaning that I can fetch all of the information I need in a single query. The logic in the mutator then splits that string apart into an array of information on stores and the inventory. PDO was bypassing this completely and setting the protected stores property as the string that came back from GROUP_CONCAT().

A Partial Solution

Once again the PHP manual was my partial saviour. There is a way to fetch a row from a resultset into an existing instance of a class. In this case the visibility of properties is honoured and the __set() function is called, in turn triggering the correct mutators. The code I came up with looks like this:


//The statement is prepared before this.

$item = new ClearanceItem($this->lang);
 $st->setFetchMode(PDO::FETCH_INTO, $item);
 $st->bindParam(':ItemId', $id, PDO::PARAM_INT);
 $st->execute();
 foreach ($st as $result) {
//The query in this case only returns a single row.

return $result;
 }

While this works it’s a bit of a hack to say the least. It seems that PDOStatement::fetch() doesn’t work here to fetch the info into the object, necessitating the foreach loop and the immediate return. This is fine in this case but in other areas of the code I need to iterate over a resultset, getting a new instance of ClearanceItem each time. This wouldn’t work as the information is populated into the existing object bound to the statement. The best I could do is to clone the object each time through the loop and store the cloned copy.

There is another way out of this: return each row as an associative array and create a factory method to return instances of ClearanceItem from this. I actually coded this solution already when I intitially found the strangeness that PDO was doing in return instances of ClearanceItem. That solution works perfectly well but it seems strange that it’s not possible to loop over a record set, getting a new instance of a class each time.

Conclusion

It seems that it’s quite possible to return instances of a class from a PDOStatement providing that all of the properties are public or if you don’t mind PDO directly setting your private and protected properties. If you want to use mutators to control how your data is set and to make sure it’s of the correct type it seems that the options are far more limited and you may be out of luck. Of course there are other options, such as writing a factory method to create an object from an array or using a full blown ORM like Doctrine. In this instance, as I haven’t used Doctrine before and as time is of the essence I was hoping that PDO would give me what I need.

It seems very strange that PDO is allowed to directly set private and protected properties when all other code accessing instances of ClearanceItem have to use the accessors and mutators. It seems to me to drive a coach and horses through the concept of property visibility. Why should PDO (at least I’m assuming it’s just PDO) have the ability to bypass the visibility protections that OO code provides while everyone else has to work with it? Is there some good reason for doing this that I’m missing? I’d be really interested to hear what the possible advantages of this are.

Leave a Reply