mysql/php/tools

Simple PHP PDO Wrapper : light, static, easy

Posted on by Anis Ahmad / 26 Comments

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.

Let’s see what’s inside and how it works.

Features

  1. Easy to use.
  2. Works with MySQL and Sqlite.
  3. All pubic functions works statically.
  4. Connection string created internally.

Get the files

From github, you can clone/fork/ download the files.
Github repository https://github.com/ajaxray/static-pdo

The download archive/repository includes –

  • Db.php – the PDO Wrapper class.
  • DbTest.php – Unit testing class for Db.
  • DbExample.php – Example of some common uses.
  • Some other unit test related files.

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.

// Connecting to mysql. Using default hostname
Db::setConnectionInfo('schemaname','root', '123456');

// Connecting to mysql. With different host
Db::setConnectionInfo('basecamp','dbuser', 'password', 'mysql',  'http://mysql.abcd.com');

// Connecting to sqlite. Here, the 1st param is sqlite file path.
Db::setConnectionInfo('path/to/filename.db3', null, null,  'sqlite',);

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:

// Inserting a user
$user = array('name' => 'someone', 'pass' => '123456');
Db::execute('INSERT INTO users(username, password) VALUES(:name, :pass)', $user);

// An update query. You can pass the param directly for single parameter
$updated = Db::execute("UPDATE users SET status = 'active' WHERE id = ?", 4);

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.

$totlalUsers = Db::getValue('SELECT COUNT(*) FROM users');
$aUserName  =  Db::getValue("SELECT name from users WHERE id = ?", 4);
$aUser  =  Db::getRow("SELECT name, status from users WHERE id = 1");
$activeUsers = Db::getResult("SELECT * from users WHERE status = 'active'");

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.

  • setConnectionInfo($schema, $username = null, $password = null, $database = ‘mysql’, $hostname = ‘localhost’)
  • execute($sql, $params = array()) – Execute a statement and returns number of effected rows
  • getValue($sql, $params = array()) – Execute a statement and returns a single value
  • getRow($sql, $params = array()) – Execute a statement and returns the first row as array
  • getResult($sql, $params = array()) – Execute a statement and returns row(s) as 2D array
  • getLastInsertId($sequenceName = “”) – Returns the last inserted id
  • 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.

Anis Ahmad

26 Comments

  4. 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.

    Reply

  5. 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.

    Reply

  6. @Giorgio Sironi, @ferdhie, @Luca Bernardi, @ robo47
    Thanks a lot for commenting. Your opinion is most welcome.

    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.

    The application that I’ve mentioned in first line was using a little MVC framework. Where I was intended not to make models as “just database handler” and not to force them to inherit a specific class. I think, they should be independent on framework and capable to work outside of the box.
    See http://blog.astrumfutura.com/archives/373-The-M-in-MVC-Why-Models-are-Misunderstood-and-Unappreciated.html

    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.

    Reply
  8. Pingback: ??????? » [Web] ????

  9. Hi,

    Nice blog you have, have been reading few articles.

    I personally don’t see the problem of hiding objects behind static methods. I have only recently started to use PDO and find this class a lo0t easier to use than PDO directly.

    Thanks for sharing.

    Reply

  10. Is it slower to use the same connection every time?

    If 5000 users are connected to the application, they will use the same DB connection since the class is static and no connection pool exist. Is this an issue?

    I’m not sure about this one.

    Reply

  12. It would be nice to have detailed examples for these:

    •setConnectionInfo($schema, $username = null, $password = null, $database = ‘mysql’, $hostname = ‘localhost’)
    •execute($sql, $params = array()) – Execute a statement and returns number of effected rows
    •getValue($sql, $params = array()) – Execute a statement and returns a single value
    •getRow($sql, $params = array()) – Execute a statement and returns the first row as array
    •getResult($sql, $params = array()) – Execute a statement and returns row(s) as 2D array
    •getLastInsertId($sequenceName = “”) – Returns the last inserted id
    •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.

    Also with the array() in detail.

    Good work !

    Reply

  13. Hi

    Tanks for this.

    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.

    Do you have any suggestions?

    Reply

  14. bro you need more functions.

    for example insert(tablename,array of data)

    is a lot better than what you have right now which is:

    foreach($data as $user){
    db::execute(‘INSERT INTO users(username, password) VALUES(:name, :pass)’, $user);
    }

    Reply

  16. 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

    Reply

  17. 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

    Reply

  19. How are you taking care of SQL injection without BindParam? Was actually looking for a warapper that uses bindParam. Thanks though

    Reply
  20. Pingback: PDO??????????????? | 1000g
  22. Pingback: pdo page wise fetching - Question Lounge

Leave a Comment

Your email address will not be published. Required fields are marked *