Simple PHP PDO Wrapper : light, static, easy

A few days ago, I was looking for a PDO wrapper to work with a small PHP application. I’ve searched around and found fractalizer’s PDO wrapper nearest to my purpose. Then I just wrote my wrapper on the shadow of his one.

How to use

To use this class, first you have to set the connection information using Db::setConnectionInfo. Here you have to pass the schema name, username, password, database type and hostname respectively. Database is mysql and hostname is localhost by default.

Please remember that it will NOT create any connection with the database, yet. Connection will be made on the first time a first query is executed and will be used from them onwards. However, the class will take care of this and you need not bother about it.

You are now ready to run query. For the queries which don’t return a result set, you can use Db::execute, this function returns the number of effected rows. The first argument is SQL query (PDO format) and 2nd is optional array of input parameters. Here is an example:

For retrieving result sets, you can use a number of functions: Db::getValue, Db::getRow and Db::getResult. Db::getValue returns value of a single field. Db::getRow and Db::getResult returns array of a single row (as single dimension) and multiple rows (as 2 dimension ) respectively. Parameters are same as Dd::execute function.

See the DbExamples.php file (in downloaded archive) for more examples. There are some other functions available in the class for using transaction, getting insert id etc. See function references for list of all public functions.

Function references

Here is the list of public (and static as well) functions of this class.

setFetchMode($fetchMode) – set the PDO fetch mode. Default is PDO::FETCH_ASSOC

getPDOObject() – Returns a reference of connected PDO object.

BeginTransaction()

commitTransaction()

rollbackTransaction()

setDriverOptions(array $options) – Set PDO driver options to use while preparing statements.

One might wonder why a wrapper for PDO is needed? Well, I can say that it saves a lot of your time if you need to do a lot of database related task in your application. With not much overload, it just makes my life a bit easier. Give it a try and see if does the same to you.

What is the meaning of hide objects behind a series of static methods? Returning to functional programming? A collection of static methods is simply functional programming, PDO is designed to be object oriented.

And because it’s all static / pseudo singleton there is no way to create 2 instances to talk with more than one database ….
i don’t find any real additional benefit for this wrapper against using pdo directly.

Well, may be I should explain the purpose of this class a bit more.
Here, I am doing nothing new then PDO does. I am just wrapping a wrapper to reuse the PDO connection and some common statements that I need for most of the database operations.

Now for accessing database from my models using PDO, I can –
* Take an instance of PDO connection in each model and use it in that model.
* Create a PDO connection in controllers and pass it to models.
— this two are seems clearly wrong approach to me. It’s nothing but a redundant work, I’ve to do same thing in multiple place.

* Create a PDO connection in bootstrap and store in a globally reachable place, like Registry. Then take it’s reference in model or wherever I need and use it.
— My wrapper is here taking this responsibility. After setting connection info, it just connect when required first time and store the connection reference for next executions. And it’s globally reachable.

Ok. Now I’ve the connection available to use in model or anywhere I need.
Then when querying to database using this PDO, there are some common statements in almost all queries. And needs some other statements for getting the expected data from result set (eg, a single value). The functions of this class freeing from both of this hassles.
Let’s see at the point of movability. Suppose, in a PHP application, I’ve done all my database operations using Db::execute, Db::getRow etc functions. Now, if I want to use another abstraction layer, I just have to overwrite this functions in Db class. All other things will remain working though still unchanged.

So, I’ve used it, found useful (at least for small application) and liking to share.

The last thing I’ll like to mention, I think, Patterns are to help development not for bring elegance. We can tune up or change them to be fit for our purpose.

I have a problem with and insert and update query. I’m using CI and jqGrid. I have three sqlite tables that contain the data for three grids. The first 2 grids work perfectly but for some reason I get an exception when trying to insert/update data in the third grid. Exception is 25 bind or column index out of range. I have checked the columns passed against that specified in the database, the syntax is correct. Googeling for a solution produced no results.

Hello Anis
thanks for sharing this piece of coding, i just have a question, (i just begin to learn pdo)
you do not use the function bindParam or bindValue in your _query function in Db.class, after the prepare ?
i mean, in this manner, does the protection against SQL injection is active ?
did the use of bindValue or bindParam is adviced or you can forget it?
(sorry for my newb’s answer.. and.. im french also
🙂
thanks

Hi,Excellent blog post dude! i am Tired of using RSS feeds and do you use twitter?so i can follow you there:D.
PS:Have you considered putting video to the blog posts to keep the visitors more enjoyed?I think it works., Nedra Hurst