PDO wrapper class

Question

<?php

class Database
{
    private $_dbh;
    private $_stmt;
    private $_queryCounter = 0;

    public function __construct($user, $pass, $dbname)
    {
        $dsn = 'mysql:host=localhost;dbname=' . $dbname;
        //$dsn = 'sqlite:myDatabase.sq3';
        //$dsn = 'sqlite::memory:';
        $options = array(
                    PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8',
                    PDO::ATTR_PERSISTENT => true
                    );
        try {
            $this->_dbh = new PDO($dsn, $user, $pass, $options);
        }
        catch (PDOException $e) {
            echo $e->getMessage();
            exit();
        }
    }

    public function query($query)
    {
        $this->_stmt = $this->_dbh->prepare($query);
    }

    public function bind($pos, $value, $type = null)
    {
        if (is_null($type)) {
            switch (true) {
                case is_int($value):
                    $type = PDO::PARAM_INT;
                    break;
                case is_bool($value):
                    $type = PDO::PARAM_BOOL;
                    break;
                case is_null($value):
                    $type = PDO::PARAM_NULL;
                    break;
                default:
                    $type = PDO::PARAM_STR;
            }
        }
        $this->_stmt->bindValue($pos, $value, $type);
    }

    public function execute()
    {
        $this->_queryCounter++;
        return $this->_stmt->execute();
    }

    public function resultset()
    {
        $this->execute();
        return $this->_stmt->fetchAll(PDO::FETCH_ASSOC);
    }

    public function single()
    {
        $this->execute();
        return $this->_stmt->fetch(PDO::FETCH_ASSOC);
    }

    // returns last insert ID
    //!!!! if called inside a transaction, must call it before closing the transaction!!!!!!
    public function lastInsertId()
    {
        return $this->_dbh->lastInsertId();
    }

    // begin transaction // must be innoDatabase table
    public function beginTransaction()
    {
        return $this->_dbh->beginTransaction();
    }

    // end transaction
    public function endTransaction()
    {
        return $this->_dbh->commit();
    }

    // cancel transaction
    public function cancelTransaction()
    {
        return $this->_dbh->rollBack();
    }

    // returns number of rows updated, deleted, or inserted
    public function rowCount()
    {
        return $this->_stmt->rowCount();
    }

    // returns number of queries executed
    public function queryCounter()
    {
        return $this->_queryCounter;
    }

    public function debugDumpParams()
    {
        return $this->_stmt->debugDumpParams();
    }

}

Testing the class....

/**
 * Establish a DB connection.
 */
$database = new Database('root', '', 'testing');


/**
 * Insert 1 new record
 */
$database->query('INSERT INTO testing (col1,col2,col3) VALUES (:name,:tag,:date)');
$database->bind(':name', 'john doe');
$database->bind(':tag', 'hi');
$database->bind(':date', 'may 1');
$database->execute();
//returns last auto increment ID
echo $database->lastInsertId();


/**
 * Insert 2 new records using Transactions
 * Also Bind new results without running $database->query() a second time
 */
$database->beginTransaction();
$database->query('INSERT INTO testing (col1,col2,col3) VALUES (:name,:tag,:date)');
$database->bind(':name', 'user 1');
$database->bind(':tag', 'hello');
$database->bind(':date', 'may 2');
$database->execute();

// Add second item to DB without runing $database->query() again
$database->bind(':name', 'user 2');
$database->bind(':tag', 'hello 2');
$database->bind(':date', 'may 22');
$database->execute();
//returns last auto increment ID
echo $database->lastInsertId();
//$database->cancelTransaction();
$database->endTransaction();


/**
 * Select a row of items from DB
 */
$database->query('SELECT col1, col2, col3 FROM testing WHERE id > ? LIMIT ?');
$database->bind(1, 1);
$database->bind(2, 5);
$row = $database->single();
echo "<pre>";
print_r($row);
echo "</pre>";


/**
 * Create a new query, bind values and return a resultset.
 */
$database->query('SELECT col1, col2, col3 FROM testing WHERE col2 = :col2 LIMIT :limit');
$database->bind(':col2', 'hello 2');
$database->bind(':limit', 10);
$rs = $database->resultset();

foreach ($rs as $row) {
    echo $row['col1'] . '<br>';
    echo $row['col2'] . '<br>';
    echo $row['col3'] . '<br>';
}


/**
 * Show number of queries executed on DB
 */
echo $database->queryCounter();
?>

That is what I have so far. I am wanting to modify it though so I can run it more like this below instead of running the separate bind() methods on every item...

$query = 'INSERT INTO user(user_id, user_username, user_password)
        VALUES( :user_id, :user_username, :user_password))';
$args = array(
    ':user_id' => $data['user_id'],
    ':user_username' => $data['user_username'],
    ':user_password' => $data['user_password']
    );
$db->query($query, $args);


$query = "SELECT user_id FROM user WHERE user_username = :username LIMIT 1";
$results = $db->query($query, array(':username' => $username));

Can someone help modify this code to allow this format?

I am also concerned about the new method. My current code allows for me to prepare a statement and bind some params and insert them, then bind some more and insert them without preparing the statement again. Would the new way I am wanting to change it to would kill that functionality?

Answer 1

I am making some minor comments on this now old question.

The way you have made your wrapper hard-codes your choices unnecessarily.

Observe your current constructor:

public function __construct($user, $pass, $dbname)
{
    $dsn = 'mysql:host=localhost;dbname=' . $dbname;
    //$dsn = 'sqlite:myDatabase.sq3';
    //$dsn = 'sqlite::memory:';
    $options = array(
                PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8',
                PDO::ATTR_PERSISTENT => true
                );
    try {
        $this->_dbh = new PDO($dsn, $user, $pass, $options);
    }
    catch (PDOException $e) {
        echo $e->getMessage();
        exit();
    }
}

It is clear from your comments that you are already considering a different dsn. You are needlessly locking PDO down to mysql localhost connections. All of these settings make your class less reusable.

Catching and calling exit further locks your class down to self-destructing. It gives no chance for someone to use your class, see that the database didn't connect catch the exception themselves and deal with it the way they want.

What does your class need?

It needs a PDO object (that is all). The rest of the class does not depend on all of the settings that you made in your constructor.

So, I would construct your PDO object outside of this class. Your constructor then becomes:

public function __constructor(PDO $pdo)
{
    $this->_dbh = $pdo;
}

Flexibility and Reusability ensues.

Answer 2

I think adding a new method to your existing class and calling existing query,bind and execute methods should be enough:

public function query2($query,$params)    {        
    $this->query($query);    
    foreach($params as $k=>$v){
        $this->bind($k, $v);
    }
    return $this->execute();
}