Design Patterns – Abstraction with PDO

posted on Jan 12 by admin in the News, Tutorials category
Featured Post Image

Today I want to write about some design patterns. There are really many out there and I want to summarize them a bit for you.
I will not write about design patterns of a specific language but for general. Many of them are really usefull!

Don’t you have sometimes the problem that you don’t know how to begin when you are programming a class or so?
You are thinking and thinking of how you could design the class or class hierachy (Polymorphism) that it is easy to understand, lightweight, not so much time consuming to implement it and so on…
Then after hours of thinking you have a solution but you are never sure if it’s really okay like that. You just implement the class and if something does not fit you have to change it.
But now think of it, is that really reasonable? You put so much time into designing a class or class hierachy but for what? That you can change everything in let’s say 200 files where you used that class? That shouldn’t be!

As an example we take a communication with a database. The standard in PHP is to use MySQL, that’s okay but what happens if you want to use PostgresSQL now? You maybe will have to change every query statement because it can be case that in this dialect something is written different than in the other. So it’s hard for use to change the database system just by replacing the connect call to a new one. We not only have the problem with the SQL statements, but you have to change the calls of the SQL statements too.

Here you can see a PHP example to know what I mean by that.

// Using PostgresSQL
$conString= "host=localhost port=5432 ".
                 "dbname=DATABASE ".
                 "user=USERNAME ".
                 "password=PASSWORD";
$connection = pg_connect($conString);

$sql = "SELECT field FROM table WHERE field = '$something' ";
$result = pg_query($connection, $sql);
$fetch = pg_fetch_row($result);

pg_close($connection);

// -------------------
// Using MySQL
$connection = mysql_connect("localhost", "USERNAME", "PASSWORD");
mysql_select_db ("DATABASE", $connection);

$sql = "SELECT field FROM table WHERE field = '$something' ";
$result = mysql_query($sql, $connection);
$fetch = mysql_fetch_row($result);

mysql_close($connection);

Here we don’t have a problem with the SQL statement, but what happens if you use the function mysql_query for about 500 times in 200 different files? Try to change that! Somebody will probably say now that there are tools which you can use to make such mass operations, but that’s not the point! What do you want to do now if we have a SQL statement that is not compatible to other SQL systems? You have to change nearly everything in your application to correct that. It’s really time consuming, so we should try to get to find a solution.

First of all think about something like a DAO class. DAO (Data Access Object) is really nice and saves you a lot of time. In a DAO class you make methods which give you back objects as result. Let’s say you have something like showing news on a website. Then make a method getNews() and just use that method everywhere you need the news. You can define parameters for that method and so on but you always get let’s say an array of news objects from that method. That’s really nice because you don’t have to think about what the name of the column was and so on, your IDE will suggest the methods and variables. This kind of system is very simmilar in some points to an ORM (Object Relation Mapping) system which don’t has any foreign keys but the objects which is related to the foreign keys. Of course you still have to implement data classes for each table but this will help you a lot.

So now we have a DAO object which handles the connection to the database and provides methods which give us arrays of objects but we still have the compability problem to other systems, for this I suggest to use PDO (PHP Data Object) in PHP. In Java we have JDBC, in .NET Linq and so on. With PDO you have only one class which uses drivers to communicate with the database systems, you only have methods like query() and fetch() so we don’t have to rename the calls any more.

// Using PDO
$connectionString = "mysql:host=localhost;dbname=DATABASE";
$user = "USERNAME";
$pass = "PASSWORD";
$pdo = new PDO($connectionString, $user, $pass);

$sql = "SELECT field FROM table WHERE field = '$something' ";
$stmt= $pdo->query($sql);
$fetch = $stmt->fetch(PDO::FETCH_NUM);

$stmt->closeCursor();

As you can see, it’s easy and the best is, if you change the database system, you just have to change the connection string!

I hope you enjoyed this article and you will go on reading my texts about design patterns!

One Comment

Bernd Artmüller, postet this comment on Jan 12th, 2010

great article mate…keep on writing such informative posts :D

Leave a Response