PDO database class review

Question

I've taken this class and made it my own but I'm curious to know what other think of it? Are there things I'm missing? Anything you can recommend?

class Database {
    private $host = '';
    private $user = '';
    private $pass = '';
    private $dbname = '';

    private $dbh;
    private $error;
    private $stmt;

    public function __construct() {
        $dsn = 'mysql:dbname=' . $this -> dbname . ';host=' . $this -> host . '';
        // Set options
        $options = array(PDO::ATTR_PERSISTENT => true, PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION);
        // Create a new PDO instanace
        try {
            $this -> dbh = new PDO($dsn, $this -> user, $this -> pass, $options);
        } catch(PDOException $e) {
            $this->error = $e -> getMessage();
        }
    }

    public function query($query) {
        $this -> stmt = $this -> dbh -> prepare($query);
    }

    public function bind($param, $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($param, $value, $type);
    }

    public function execute() {
        return $this -> stmt -> execute();
    }

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

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

    public function rowCount() {
        return $this -> stmt -> rowCount();
    }

    public function countAll($arr) {
        return count($arr);
    }

    public function lastInsertId() {
        return $this -> dbh -> lastInsertId();
    }
}

Answer 1

I have set up something very similar with a couple changes. In __contruct() I assign the credentials to variables, then call an instance to start the PDO connection. I also use try-catch for each PDO statement to make sure that I am not getting any errors.

private $_pdo = null;
private $_host, $_dbname, $_uname, $_pass;
private $_sql, $_result;
private $_preparedStmt;

public function __construct($host, $dbname, $username, $password) {
    // You could run checks here to make sure that...
    // no values or specific values are not equal to ''
    $this->_host = $host;
    $this->_dbname = $dbname;
    $this->_uname = $username;
    $this->_pass = $password;
    $this->startPDO();
}

// ... Later on in code, under Private functions comment tag
private function startPDO() {
    try {
        $this->_pdo = new PDO('mysql:host='.$this->_host.';dbname='.$this->_dbname, $this->_uname, $this->_pass);
        $this->_pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    } catch (PDOException $e) {
        //$this->handleFatalErr($e->getMessage(), 'connection'); // 'die' error handling
        //$this->handleErr($e->getMessage(), 'connection'); // Non-'die' error handling
    }
}

In each function I check the connection, I would recommend something similar. You could either kill the script if there is no connection or you could re-connect using the current variables $this->_host, etc:

public function query($sql) {
    $this->checkConnection();
    $this->_sql = $sql; // This is used for logging and for a getter function
    try {
        $this->_result = $this->_pdo->query($this->_sql);
    } catch (PDOException $e) {
        //$this->handleFatalErr($e->getMessage(), 'connection');
    }
}

private function checkConnection() {
    ($this->getPDO() === null) ? die('There is no connection to the database, please re-establish connection.') : NULL;
        //$this->handleFatalErr(self::ERR_CONNECTION_LOST, 'CONNECTION_LOST');
}

private function getPDO() {
    return $this->_pdo;
}

public function close() {
    (isset($this->_pdo) && $this->_pdo != null) ? $this->_pdo = null : NULL;
}

Answer 2

    private $host = '';
    private $user = '';
    private $pass = '';
    private $dbname = '';

    // ....

    public function __construct() {
        $dsn = 'mysql:dbname=' . $this -> dbname . ';host=' . $this -> host . '';

        // ...
    }

Two things here:

• How can I set the connection credentials? The properties are private and I see no setters or parameters to the constructor?

• Why use PDO if you only use mysql?

And an other thing:

public function bind($param, $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($param, $value, $type);
}

This can be dangerous. Let's say you have code like this:

$db->bind('param', $_POST['id']);

Your method would detect it's a string, but it should be an integer!

After all your class doesn't bring much value, it mostly forwards calls to PDO.