52
\$\begingroup\$

There are many PHP PDO classes out there, agreed. However I find they do not allow for flexibility. So I created one that helps reduce development time as little as it may be but it does the job (maybe apart from the disconnect part, but it allows to trace whether database is connected via $database->isConnected). Can you please point out any flaws and any possible improvements?

<?php 
    class db
    {
        public $isConnected;
        protected $datab;
        public function __construct($username, $password, $host, $dbname, $options=array()){
            $this->isConnected = true;
            try { 
                $this->datab = new PDO("mysql:host={$host};dbname={$dbname};charset=utf8", $username, $password, $options); 
                $this->datab->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); 
                $this->datab->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);
            } 
            catch(PDOException $e) { 
                $this->isConnected = false;
                throw new Exception($e->getMessage());
            }
        }
        public function Disconnect(){
            $this->datab = null;
            $this->isConnected = false;
        }
        public function getRow($query, $params=array()){
            try{ 
                $stmt = $this->datab->prepare($query); 
                $stmt->execute($params);
                return $stmt->fetch();  
                }catch(PDOException $e){
                throw new Exception($e->getMessage());
            }
        }
        public function getRows($query, $params=array()){
            try{ 
                $stmt = $this->datab->prepare($query); 
                $stmt->execute($params);
                return $stmt->fetchAll();       
                }catch(PDOException $e){
                throw new Exception($e->getMessage());
            }       
        }
        public function insertRow($query, $params){
            try{ 
                $stmt = $this->datab->prepare($query); 
                $stmt->execute($params);
                }catch(PDOException $e){
                throw new Exception($e->getMessage());
            }           
        }
        public function updateRow($query, $params){
            return $this->insertRow($query, $params);
        }
        public function deleteRow($query, $params){
            return $this->insertRow($query, $params);
        }
    }
    //USAGE 
    /*      
        Connecting to DataBase
        $database = new db("root", "", "localhost", "database", array(PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8'));

        Getting row
        $getrow = $database->getRow("SELECT email, username FROM users WHERE username =?", array("yusaf"));

        Getting multiple rows
        $getrows = $database->getRows("SELECT id, username FROM users");

        inserting a row
        $insertrow = $database ->insertRow("INSERT INTO users (username, email) VALUES (?, ?)", array("yusaf", "yusaf@email.com"));

        updating existing row           
        $updaterow = $database->updateRow("UPDATE users SET username = ?, email = ? WHERE id = ?", array("yusafk", "yusafk@email.com", "1"));

        delete a row
        $deleterow = $database->deleteRow("DELETE FROM users WHERE id = ?", array("1"));
        disconnecting from database
        $database->Disconnect();

        checking if database is connected
        if($database->isConnected){
        echo "you are connected to the database";
        }else{
        echo "you are not connected to the database";
        }

    */
\$\endgroup\$
  • 6
    \$\begingroup\$ You are actually making it worse! At this moment you throow away one of the most powerfull features of PDO: prepared statements. You can execute prepared statements multiple times with different variables. But your function doesnt allow this. Extending/wrapping a class should add functionality, not remove it \$\endgroup\$ – Pinoniq Aug 5 '13 at 8:00
  • \$\begingroup\$ @Pinoniq: every query this class performs uses a prepared statement, AFAIKT \$\endgroup\$ – Elias Van Ootegem Aug 5 '13 at 11:40
  • 2
    \$\begingroup\$ Wrapping PDO into something "better" is impossible. PDO is as good as it gets. What you did is made it worse. Why would anyone learn your class methods for inserting/deleting/updating if they already know SQL? I'd never use $obj->insertRow() or any of similar. I'd use a class that implements ActiveRecord pattern which completely obliviates the existence of insert/delete/update commands. Don't take it the wrong way, but do check ActiveRecord and Object Relational Mapper. \$\endgroup\$ – N.B. Aug 5 '13 at 13:18
  • 1
    \$\begingroup\$ @EliasVanOotegem true about the db providing those stmt's but what use is your code if it doesnt allow us to access them. I was simply pointing out that saying (he uses prepared statemends because of the ->prepare() function is for me the same as 'he is writing OO because of the keyword class'. Apologies if I offended you... I'm not the morning type ;) \$\endgroup\$ – Pinoniq Aug 6 '13 at 8:26
  • 1
    \$\begingroup\$ I'm reading this question now and crying with laughter. \$\endgroup\$ – Yusaf Khaliq Aug 21 '16 at 21:39
68
\$\begingroup\$

Personally, I must say that close to all PDO derived/based classes I've seen so far suffer from the same problem: They are, essentially, completely pointless.
Let me be clear: PDO offers a clear, easy to maintain and neat interface on its own, wrapping it in a custom class to better suit the needs of a particular project is essentially taking a decent OO tool, and altering it so that you can't reuse it as easily in other projects. If you were to write a wrapper around MySQLi, I could understand the reasoning, but PDO? No, I'm really struggeling to understand the logic behind that unless:

You were to write a table mapper class to go with it, that establishes a connection between the tables you query, and the data models into which you store the data. Not unlike how Zend\Db works.
MySQL is, as you well know, not as flexible as PHP is in terms of data-types. If you are going to write a DB abstraction layer, common sense dictates that layer reflects that: it should use casts, constants, filters as well as prepared statements to reflect that.
Most mature code out there also offerst an API that doesn't require you to write your own queries:

$query = $db->select('table')
            ->fields(array('user', 'role', 'last_active')
            ->where('hash = ?', $pwdHash);

These abstraction layers often (if not always) offer another benefit, they build the queries for you, based on what DB you're connection to. If you're using mysql, they'll build a MySQL query, if you happen to switch to PostgreSQL, they'll churn out pg queries, without the need to rewrite thousands of queries. If you want to persist and write your own abstraction layer, make sure you offer something similar, too. If you don't, you're back to square one: embarking on a labour intensive, pointless adventure that won't be worth it.

An alternative approach is to extend the PDO class. Again, this has been done before and is, in theory, perfectly OK. Although, again this might be personal, it does violate one principle which is upheld by many devs I know: Don't extend, or attempt to change an object you don't own. PDO is a core object,so it's pretty clear you don't own it.
Suppose you were to write something like:

class MyDO extends PDO
{
    public function createProcedure(array $arguments, $body)
    {
        //create SP on MySQL server, and return
    }
}

And lets assume that, after some serious debugging, and testing, you actually got this working. Great! But then what if, some time in the future, PDO got its own createProcedure method? it'll probably outperform yours, and might be more powerful. That, in itself isn't a problem, but suppose it's signature were different, too:

public function createProcedure (stdClass $arguments)
{
}

That would mean you either have to ditch your method, and refactor your entire code-base to sing to the tune of PDO's latest and greatest hit, or you'd have to alter your method to:

public function createProcedure(array $arguments, $body)
{
    $arguments = (object) $arguments;//create stdClass
    //parse $body and:
    $arguments->declare = $body[0];
    $arguments->loops = (object) array('loop1' => $body[1], 'loop2' => $body[2]);
    $arguments->body = (object) array(
        'main' => $body[3],
        'loop1_body' => $body[4],
        'loop2_body' => $body[5]
    );
    return parent::createProcedure($arguments);
}

That would mean that, for all code you wrote, you're actually having to call 2 methods, turning your once so clever createProcedure method into dead weight. So what, you might say? Well, don't think you're out of the woods just yet, because This alternative method above is illegal, it can't be written, it can't work, it shouldn't work, it's just all shades of wrong, here's why:

The Liskov principle states that a child (the class extending PDO) may not alter the signature of inherited methods if those alterations constitute a breach of contract, meaning the expected types (type-hints) may not be stricter than or different to the types defined in the parent (ie: array vs stdClass is not allowed). Additional arguments are allowed, provided they're optional.
If the PDO method itself takes but a single argument of the type stdClass, then your child class may only add optional arguments, and should either drop the type-hint, or uphold it (ie: hint at stdClass, which would break all existing code), or don't hint at all (which is as error-prone as it gets).

What's more, after a couple of months, people might use third party code (frameworks), that rely on the createProcedure method, and pass it an instance of stdClass. You'll have to change your method again, to the vague, and error prone signature:

public function createProcedure($arrOrObject, $body = null)
{
    if ($arrOrObject instanceof stdClass)
    {
        return parent::createProcedure($arrOrObject);
    }
    if ($body === null)
    {
        //What now??
    }
    //parse body
}

If $body is null, and $arrOrObject is an array, the user might have structured the $arrOrObject array in the same way as PDO would like to see the object structured, in which case json_decode(json_encode($arrOrObject)); would do the trick (not casting, because a cast doesn't cast recursive), but it's just as likely that the code calling your method contains a bug. What to do? convert to an object, and try-catch, with the extra overhead that might cause?

This leads me to the last, and for now biggest omission:
When using a wrapper object, it's generally a good idea to implement a (slow) magic __call method, that checks if a method call was meant for the wrapper object, or if it was meant for the wrapped object.
Using your object, I might want to set another attribute on the PDO extension, but since you failed to implement the setAttribute method, I can't change the charset, nor can I change how PDO deals with NULL values. Which can only be considered to be a glaring omission. Especially since you expect the user to pass bare PDO constants to the constructor. Basically, the least you should do is add:

public function __call($method, array $arguments )
{
    return call_user_func_array($this->datab, $arguments);
}

This way, you semi-expose the actual wrapped object to the user, in the sense that, methods you haven't implemented can still be used. New methods that might be implemented in the future will automatically be accessible, too.
What's more, you'll be able to validate/check the arguments and log which methods are used by the users, so that you could fine-tune your class to better reflect the way it is being used.

Recap:

  • Building an abstraction class on a user-friendly raw-API like PDO is, IMHO, like a broken pencil: Pointless
  • Extending PDO means you don't have to create pass-through methods that call the wrapped API (like creating a prepare method), but it does mean there is a chance you'll have to refactor your code whenever PDO changes
  • If you still feel like using a wrapper object, either consider wrapping it around something less well known, but in some respects better (mysqli_* is what I'm hinting at), but implement the magic __call and, if required __callStatic methods.
  • Again, if you're going to procede: work on an entire abstraction layer, that allows for users to map tables to data models, and take it from there. Ensure that those data-models can be used easily to build HTML forms, for example in order for those forms to be linked to the DB in the blink of an eye, bot don't forget about sanitizing your data, of course.

On the code itself:

There is one thing you have to fix about your code, above all else, and that's your constructor:
I might choose to construct an object like so:

$database = new db("user", "pwd", "host", "mydb",
             array(PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_OBJ));

Setting the default fetch-mode to fetch objects, and the new instance of PDO will be passed the correct attribute array. Sadly, right after constructing that PDO object, you're deciding that the default fetch-mode should've been PDO::FETCH_ASSOC, because 2 lines further down:

$this->datab->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);

I'd hate to use that code. Also, this just doesn't add up:

try
{ 
    $this->datab = new PDO("mysql:host={$host};dbname={$dbname};charset=utf8", $username, $password, $options); 
    $this->datab->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); 
    $this->datab->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);
} 
catch(PDOException $e)
{ 
    $this->isConnected = false;
    throw new Exception($e->getMessage());
}

Try-catch-throw? Why? The connection failed, the PDOException tells me why, that's what I want to know, why catch that exception, and throw a new, more general one? What if I passed PDO::ATTR_ERRMODE => PDO::ERRMODE_SILENT to the constructor, and the connection failed? You're probably better of replacing it with this:

$this->datab = new PDO($connString, array(
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8'//careful with this one, though
));
foreach($options as $attr => $value)
{
    $this->datab->setAttribute($attr, $value);
}

And let the exception go. If the connection to the DB fails, the script can't do what it's supposed to do anyway. The PDOExceptions are usefull in a scenario where you're using transactions. If 999 queries succeeded, but the 1000th query failed, rather than inserting partial data, catching the exception, and rolling back the transaction is what you do, but catching an exception to rethrow it again is just silly.

But, again, I'm not going to stop you from doing what you want, perhaps you can prove me wrong and actually make something great. But in order to do that, you must know what's out there already:

\$\endgroup\$
  • 7
    \$\begingroup\$ @YusafKhaliq: I understand your thinking behind catching the error if a malformed query-string were passed as an argument, but you shouldn't do that. If you catch that exception and attempt to fix the error, that malformed query won't get fixed, and every time that piece of code gets executed, you'll slow the system down. If a query is malformed, it's a bug: don't work around it, fix it. \$\endgroup\$ – Elias Van Ootegem Aug 6 '13 at 6:49
7
\$\begingroup\$

Kind of an old post, but I had to add something where I saw this line

$this->datab->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); 
$this->datab->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);

Which seems to me like you had hard-coding the settings, right after this line:

$this->datab = new PDO("mysql:host={$host};dbname={$dbname};charset=utf8", $username, $password, $options); 

Which means, whatever (relating to ATTR_MODE, ATTR_DEFAULT_MODE) the user sets as $options in the constructor will be overriding by the setAttribute method. Which in my opinion seems pointless. I would suggest changing the constructor to something like:

...

private $defaultPdoAttr = [
    \PDO::ATTR_EMULATE_PREPARES => FALSE,
    \PDO::ATTR_ERRMODE => \PDO::ERRMODE_EXCEPTION,
    \PDO::ATTR_DEFAULT_FETCH_MODE => \PDO::FETCH_ASSOC
];

public function __construct($dsn, $username, $password, array $driverOptions = [])
{
    if (!$driverOptions) {
        $driverOptions = $this->defaultPdoAttr;
    }  

    $this->datab = new PDO("mysql:host={$host};dbname={$dbname};charset=utf8", $username, $password, $driverOptions );

...
\$\endgroup\$
  • \$\begingroup\$ I'm not saying you did it on purpose, but you've basically repeated what I've said in my answer (first thing I discuss under "On the code itself"). PS: I wouldn't merely reassign $driverOptions if it was empty, I'd iterate over my defaults, and add the keys that aren't set in $driverOptions (merging the arrays), similar to what I suggest in the last code snippet in my answer ;) \$\endgroup\$ – Elias Van Ootegem Sep 10 '15 at 15:56

protected by Community Nov 17 '14 at 15:21

Thank you for your interest in this question. Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site (the association bonus does not count).

Would you like to answer one of these unanswered questions instead?

Not the answer you're looking for? Browse other questions tagged or ask your own question.