Accessing data with PHP Data Objects (PDO)

There are many PHP-scriptlers outside writing successful applications that makes use of the MySQL-extensions/MySQLi. That might work with small and medium applications, even with large enterprise applications. But using the Object-Oriented approach would make a lot much easier. With PHP 5.1 the PHP Data Object (PDO) came up to rule the young Object Oriented PHP 5.x. With this article I just want to step in a little bit as I’m looking for good ideas for the refactoring of some old PHP-applications.

Following example: You have a db with two tables: „dojos“ that hold a dojo-id and the name of a dojo, and „starters“ that hold a starter-id, the name of the starter and a dojo-id referencing to the „dojos“-table as foreign key. Quiet simple. And now, you just want show up an html-table with starter-id, starter-name and the joined value for dojo.

First make sure, you (or your webhoster) have the relevant pdo-extensions for you database-system installed/configured.
I like to use mysql-databases for this example, so in php.ini the line „extension=php_pdo_mysql.dll“ must be active.
You can test the activation by

<?php
print_r(PDO::getAvailableDrivers()); 
?>

After you made sure, you have the correct driver(s) in place, you need the database with tables, data, keys and all the other stuff:

DROP TABLE IF EXISTS `dojos`;
CREATE TABLE IF NOT EXISTS `dojos` (
  `id` int(2) NOT NULL auto_increment,
  `name` varchar(100) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1;

INSERT INTO `dojos` (`id`, `name`) VALUES
(1, 'Dojo A'),
(2, 'Dojo B'),
(3, 'Dojo C'),
(4, 'Dojo D');

DROP TABLE IF EXISTS `starters`;
CREATE TABLE IF NOT EXISTS `starters` (
  `id` int(3) NOT NULL auto_increment,
  `name` varchar(20) NOT NULL,
  `dojo` int(2) NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `FK_starters` (`dojo`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1;

INSERT INTO `starters` (`id`, `name`, `dojo`) VALUES
(1, 'Burmeister', 1),
(2, 'Klingenberger', 2),
(3, 'Lee', 2),
(4, 'Norris', 2),
(5, 'Miyagi', 3),
(6, 'Mishima', 4),
(7, 'Kang', 1),
(8, 'Tsung', 2),
(9, 'Dux', 1);

--
-- Constraints for table `starters`
--
ALTER TABLE `starters`
  ADD CONSTRAINT `FK_starters` FOREIGN KEY (`dojo`) REFERENCES `dojos` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

Next: the Db.class.php that simply constructs the PDO

<?php

class Db {

    public $dbh = null;

    public function __construct($db_host, $db_user, $db_pass, $db_name) {
        $mysql_DSN = 'mysql:host=' . $db_host . ';dbname=' . $db_name;

        try {
            $this->dbh = new PDO($mysql_DSN, $db_user, $db_pass);
            $this->dbh->setAttribute(PDO::ATTR_PERSISTENT, PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        } catch (PDOException $e) {
            // log this!
            echo $e->getMessage();            
        }

        return $this->dbh;
    }
}

?>

Next: the DojosDao.class.php

<?php

require_once 'Db.class.php';

class DojosDao extends Db {

    public $sth = array();

    public function __construct($db_host, $db_user, $db_pass, $db_name) {
        parent::__construct($db_host, $db_user, $db_pass, $db_name);
        $this->sth = $this->prepare();        
    }
    
    public function prepare() {
        $sth = null;

        $sth['getDojos'] = $this->dbh->prepare('select * from dojos');
        $sth['getDojoById'] = $this->dbh->prepare('select * from dojos WHERE id= ?');        

        return $sth;
    }

    public function getDojos() {
        $s = $this->sth['getDojos'];
        $s->execute();
        $s->setFetchMode(PDO::FETCH_OBJ);
        $result = $s->fetchAll();
        return $result;
    }
     
    public function getDojoById($id) {
        $s = $this->sth['getDojoById'];
        $s->execute(array($id));        
        $s->setFetchMode(PDO::FETCH_OBJ);
        $result = $s->fetch();
        return $result;
    }   

}

?>

And the StartersDao.class.php:

<?php

require_once 'Db.class.php';

class StartersDao extends Db {

    public $sth = array();

    public function __construct($db_host, $db_user, $db_pass, $db_name) {
        parent::__construct($db_host, $db_user, $db_pass, $db_name);
        $this->sth = $this->prepare();        
    }
    
    public function prepare() {
        $sth = null;

        $sth['getStarters'] = $this->dbh->prepare('select * from starters');
        $sth['getStarterById'] = $this->dbh->prepare('select * from starters WHERE id= ?');
        $sth['getStartersByDojo'] = $this->dbh->prepare('select * from starters WHERE dojo= ?');

        return $sth;
    }

    public function getStarters() {
        $s = $this->sth['getStarters'];
        $s->execute();
        $s->setFetchMode(PDO::FETCH_OBJ);
        $result = $s->fetchAll();
        return $result;
    }
     
    public function getStarterById($id) {
        $s = $this->sth['getStarterById'];
        $s->execute(array($id));
        $s->setFetchMode(PDO::FETCH_OBJ);
        $result = $s->fetch();
        return $result;
    }
    
    public function getStartersByDojo($dojo) {
        $s = $this->sth['getStartersByDojo'];
        $s->execute(array($dojo));
        $s->setFetchMode(PDO::FETCH_OBJ);
        $result = $s->fetchAll();
        return $result;
    }

}

?>

and the index.php that displays the output of the queries:

<?php

require_once 'StartersDao.class.php';
require_once 'DojosDao.class.php';

$db_host = 'localhost';
$db_user = 'christoph';
$db_pass = 'topfsecret';
$db_name = 'test_db';

$startersDao = new StartersDao($db_host, $db_user, $db_pass, $db_name);
$dojosDao = new DojosDao($db_host, $db_user, $db_pass, $db_name);

echo 'getStarters:<br>';
echo '------------<br>';
echo '<table border="1">';
echo '  <tr>';
echo '      <th>id</th><th>name</th><th>dojo-id</th><th>dojo-name (referenced from dojos)</th>';
echo '  </tr>';
$starters = $startersDao->getStarters();
foreach($starters as $starter){
    $dojo = $dojosDao->getDojoById($starter->dojo);
    echo '<tr>';
    echo '  <td>'.$starter->id.'</td><td>'.$starter->name.'</td><td>'.$starter->dojo.'</td><td>'.$dojo->name.'</td>';
    echo '</tr>';
}
echo '</table>';
echo '<br><br>';

echo 'getStarterById(3):<br>';
echo '---------------<br>';
echo '<table border="1">';
echo '  <tr>';
echo '      <th>id</th><th>name</th><th>dojo</th>';
echo '  </tr>';
$starter = $startersDao->getStarterById(3);
$dojo = $dojosDao->getDojoById($starter->dojo);
echo '  <tr>';
echo '      <td>'.$starter->id.'</td><td>'.$starter->name.'</td><td>'.$dojo->name.'</td>';
echo '  </tr>';
echo '</table>';
echo '<br><br>';

echo 'getStartersByDojo(2):<br>';
echo '---------------------<br>';
echo '<table border="1">';
echo '  <tr>';
echo '      <th>id</th><th>name</th><th>dojo</th>';
echo '  </tr>';
$starters = $startersDao->getStartersByDojo(2);
foreach($starters as $starter){
    $dojo = $dojosDao->getDojoById($starter->dojo);
    echo '<tr>';
    echo '  <td>'.$starter->id.'</td><td>'.$starter->name.'</td><td>'.$dojo->name.'</td>';
    echo '</tr>';
}
echo '</table>';

?>

references: