PDO in my own PHP Class

Question

I'm newbie on PDO. I watch several videos. And i want to use my own database class as previous times. I want to use PDO instead of mysql* functions. I know that mysql* functions are deprecated in PHP 5.5

I am not working on my old database class. Because it'll be more harder for a newbie. Now i only create a connection. And i want to select my products.

Here is my code :

class Database extends PDO {
    private $_server         = "localhost";
    private $_user           = "root";
    private $_password       = "";
    private $_db             = "demo";

    private $_engine = 'mysql';
    private $link = NULL;


    public function __construct() {
        $dsn = $this->_engine.':dbname='.$this->_db.';host='.$this->_server.';charset=utf8';

        try {
            $this->link = new PDO($dsn, $this->_user, $this->_password);
            $this->link->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        } catch (PDOException $e) {
            echo 'Connection failed: ' . $e->getMessage();
        }
    }
}

header('Content-Type: text/html; charset=utf-8');
$DB = new Database();

$sql = "SELECT * FROM product";
$q  = $DB->query($sql);
while($r = $q->fetch(Database::FETCH_ASSOC)){
    $products[] = $r;
}

echo "<pre>";
print_r($products);
echo "</pre>";

I couldn't fetch my products. I'm recieving errors.

Warning: PDO::query(): SQLSTATE[00000]: No error: PDO constructor was not called in /var/www/dt/includes/class/class.database.php on line 52

Fatal error: Call to a member function fetch() on a non-object in /var/www/dt/includes/class/class.database.php on line 53

What is my mistake ? And what should i do ?

Answer 1

Some random thoughts about your code.

Misuse of class inheritance

class Database extends PDO {
    private $link = NULL;

    public function __construct() {
            $this->link = new PDO($dsn, $this->_user, $this->_password);

The Database instance itself will be a PDO object so it doesn't make any sense to have a PDO object inside another PDO object (the outer of which will not even work properly since it hasn't been initialized). All you need is this:

class Database extends PDO {
    public function __construct() {
        parent::__construct($dsn, $this->_user, $this->_password);

Uninitialised attributes

public function __construct() {
    $dsn = $this->_engine.':dbname='.$this->_db.';host='.$this->_server.';charset=utf8';

Where do all those $this-> variables come from? You probably want to pass them as constructor parameters.

Break error handling

try {
    $this->link = new PDO($dsn, $this->_user, $this->_password);
    $this->link->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch (PDOException $e) {
    echo 'Connection failed: ' . $e->getMessage();
}

Your class degrades connection errors into plain strings on standard output. Now, there's no way to log or detect connection errors.

Edit: You ask how to detect or log connection errors from your class. There're many things you could do but the simplest way is to do... nothing. Seriously. If you just remove the try/block from the constructor, PDO exceptions will bubble up automatically and you'll be able handle exceptions wherever you need them. E.g.:

try{
    $DB = new Database();
}catch(PDOException $e){
    // I want to handle connection errors in a special way
    // I'll log them here and I'll redirect the user to a static error page
    // That's something I couldn't do from the class constructor because I'd be missing context
}

... or even:

try{
    $DB = new Database();
    $sql = "SELECT * FROM product";
    $q  = $DB->query($sql);
    while($r = $q->fetch(Database::FETCH_ASSOC)){
        $products[] = $r;
    }
}catch(Exception $e){
    // I prefer to handle all exceptions the same way, no matter the source
}

... but I'd go for the first approach: connection errors are the only PDOException instances you can expect to find in production and there's often nothing you can do about it so it makes sense to give them a special treatment.

Answer 2

First get your error reporting sorted. You need to be able to see the errors if you want to debug!

To do so you need to edit the following settings in php.ini

error_reporting display_errors

Make sure this is done in the php.ini file (rather than using ini_set() in code) and then restart the server.

With regards to your database abstraction, I cannot see any real benefit of extending the PDO class. Generally, I would only extend the class if there was some functionality within the parent that I wanted to overload.

What might be more useful is to encapsulate all calls to the PDO instance by wrapping it in your own custom class. The main benefit here is that there is no client code that deals directly with the PDO instance and you can change or swap out the implementation of the class without effecting the rest of your code.

One contrived example:

class Database
{
  protected $config = array();

  protected $pdo;

  public function __construct(array $config)
  {
    $this->config = $config;
  }

  protected function getPdo()
  {
    if (null == $this->pdo) {
      try {
        $config = $this->config;

        $dsn = $config['engine'].':dbname='.$config['database'].';host='.$config['host'].';charset=utf8';

        $this->pdo = new PDO($dsn, $config['user'], $config['password']);
        $this->pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

      } catch (PDOException $e) {
        echo 'Connection failed: ' . $e->getMessage();
      }
    }
    return $this->pdo;
  }

  public function query($sql)
  {
    return $this->getPdo()->query($sql);
  }

}

$db = new Database(array(
  'engine' => 'mysql',
  'database' => 'databasename',
  'host' => 'localhost',
  'user' => 'fred',
  'password' => 'mypassword'
));
$results = $db->query('SELECT * FROM sometable');