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

I'm not trying to understand why you use global static class methods here. — hakre
– hakre, Commented Mar 15, 2012 at 15:37

Steven · Accepted Answer · 2012-03-16 07:49:51Z

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);

Thanks very much for your reply Steven. I'm getting "Call to a member function new_obj() on a non-object in..." I've updated the sql query, and database info... would you be able to breifly comment what each part does? Sorry I'm new to OOP..!
I'm not using WP either.. but have defined those variables anyway. Also how to I add new queries? Thanks a lot :)
I've updated code to be as similar to my own code structure. Test and see if this work. It helps if you also understand the logic I use here.
When you pass one object to another, you do not have to use references. Not since PHP5. Also, since PHP5 the constructor is __construct() method. Both points making function PersonDAO (&$db) line to be really bad (it still will work, but it's an extremely bad practice).
Another note .. on prepared statements. If you do not set custom PDO::PARAM_ type when binding parameters, the value will be bound as strings.

Community · Accepted Answer · 2017-05-23 12:08:25Z

As i understood it, you want to have a "connection class" which implements lazy loading for the PDO instance. And then you want objects in your code to have access to that connection from every place in the code, effectively making a singleton.

Don't do it.

You are cleating a global state in your application and all your DB-enabled classes have tight coupling to the NAME of connection class.

I would recommend a bit different approach. As already @Steven hinted, you should use a factory to create objects, which require a DB connection.

Here is a simplified implementation.

class DataMapperFactory { protected $provider = null; protected $connection = null; public function __construct( Closure $provider ) { $this->provider = $provider; } public function create( $name) { if ( $this->connection === null ) { $this->connection = call_user_func( $this->provider ); } return new $name( $this->connection ); } }

You would use it kinda like this:

$provider = function() { $instance = new PDO('mysql:......'); $instance->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); $instance->setAttribute(PDO::ATTR_EMULATE_PREPARES, false); return $instance; }; $factory = new DataMapperFactory( $provider );

Now every time you execute $factory->create('SomeClass'), it will create a new instance of that class and provide it with proper DB connection in the constructor. And, when executed for the first time, it will open the connection to the DB.

Collectives™ on Stack Overflow

PDO connection class / code and class design

2 Answers 2

7 Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

Comments

Linked

Related