PDO connection class / code and class design

Question

I'm trying to understand how to use PDO with a "connection" class.

class db { 

    private static $dbh; 

    private function __construct(){}
    private function __clone(){} 

    public static function connect() { 
        if(!self::$dbh){ 
            self::$dbh = new PDO("mysql:host=localhost;dbname=database", "user", "password");
            self::$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); 
        } 
        return self::$dbh; 
    } 

    final public static function __callStatic( $chrMethod, $arrArguments ) {   
        $dbh = self::connect(); 
        return call_user_func_array(array($dbh, $chrMethod), $arrArguments);  
    }
} 

I've taken the above from http://php.net/manual/en/book.pdo.php, and modified the variables slightly but I'm wondering how I then connect to the PDO connection object within this db class?

$dbh = new db; //intiate connection???

$stmt = $dbh->prepare("SELECT * FROM questions WHERE id = :id"); // or should I do db::prepare.. ???
$stmt->bindParam(':id', $_GET['testid'], PDO::PARAM_INT);

if ($stmt->execute()) {
    while ($row = $stmt->fetch()){
        print_r($row);
    }
}

Any ideas please? thanks

Answer 1

This is more or less how I do it. I'm not sure if this is the best way of doing it, but it works for me.

My factory class is the CORE of my code. From here I generate all classes I work with. My factory class is saved in a separate file factory.class.php.

By having a factory class, I only need to include class files only once. If I did not have this, I would have to include my class files for each file having to use it. If I need to update a class file name later, I only need to make the update in factory class file.

Another reason for creating a factory object, was to reduce the number of DB connections.

I save each class as a separate file

Factory class

include_once('person.class.php');
include_once('tracking.class.php');
include_once('costAnalyzis.class.php');
include_once('activity.class.php');

class Factory {
  function new_person_obj($id = NULL) { return new Person(Conn::get_conn(), $id); }  
  function new_tracking_obj($id = NULL) { return new Tracking(Conn::get_conn(), $id); }
  function new_costAnalyzis_obj() { return new CostAnalyzis(Conn::get_conn()); }
  function new_activity_obj() { return new Activity(Conn::get_conn()); }
}    

Connection class

// I have this class in the same file as Factory class
// This creates DB connection and returns any error messages
class Conn {
  private static $conn = NULL;

  private function __construct() {}

  private static function init() {
      $conf = self::config();
      try { 
        self::$conn = new PDO($conf['dsn'], $conf['user'], $conf['pass'], array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"));
      } 
      catch (PDOException $e) {
        // We remove the username if we get [1045] Access denied
        if (preg_match("/\b1045\b/i", $e->getMessage())) 
          echo "SQLSTATE[28000] [1045] Access denied for user 'name removed' @ 'localhost' (using password: YES)";
        else
          echo $e->getMessage();  
      }
  }

  public static function get_conn() {
    if (!self::$conn) { self::init(); }
    return self::$conn;
  }

  // I used to get login info from config file. Now I use Wordpress constants
  private static function config() {
    $conf = array();

    $conf['user']    = DB_USER; //$config['db_user'];
    $conf['pass']    = DB_PASSWORD; //$config['db_password'];
    $conf['dsn']     = 'mysql:dbname='.DB_NAME.';host='.DB_HOST;

    return $conf;
  }  
}

Different class objects

These are your classes. This is where you work with your data In my own code I'm using tri-tier architecture, separating presentation, from business layer and data object layer.

class Person extends PersonDAO {

  function getPersonData($id) {
    $result = parent::getPersonData($id);

    // Here you can work with your data. If you do not need to handle data, just return result
    return $result;
  }
}


// I only have SQL queries in this class and I only return RAW results.
class PersonDAO {

  // This variable is also available from you mother class Person 
  private $db;

    // Constructor. It is automatically fired when calling the function.
    // It must have the same name as the class - unless you define 
    // the constructor in your mother class.
    // The &$db variable is the connection passed from the Factory class.
    function PersonDAO (&$db) {
      $this->db = &$db;
    }


  public function get_data($id) {
     $sql ="SELECT a, b, c
          FROM my_table
          WHERE id = :id";

     $stmt = $this->db->prepare($sql);
     $stmt->execute(array(':id'=> $id));
     $result = $stmt->fetchAll(PDO::FETCH_ASSOC);

     return $result;
  }

  public function get_some_other_data() {
    $sql ="SELECT a, b, c
          FROM my_table_b";

    $result = $stmt->fetchAll(PDO::FETCH_ASSOC);

    return $result;      
  }
}

Do the same for your other classes.

Putting it all together

Notice that we only include one file, the factory files. All other class files are included in Factory class file.

// Include factory file
include_once('factory.class.php');

//Create your factory object
$person = Factory::new_person_obj();

//Get person data
$data = $person->getPersonData('12');

// output data
print_r($data);