SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Addict
    Join Date
    May 2008
    Location
    Missouri, USA
    Posts
    273
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    OO PHP - Managing Database Connections

    I've been working to refine my OO approach to PHP. Currently, I'm unable to come to a conclusion on the best approach to handling database connections.

    I have 3 main layers to my database interaction:
    1. The process layer which redirects requests and data to the appropriate objects.
    2. The objects that contain logic for handling the data
    3. A database object which handles connecting to the database and queries.


    For the objects in layer 2, each object initializes a database connection in its constructor and stores it as a member. This can then be accessed by all the objects methods non-static methods.

    The problem scenario:
    I have an import class which reads in an excel file (several hundred lines). Each line corresponds to an object. The result is I end up with an array of objects (all of the same type). In each of these objects I have an insert method which connects to the database and stores the data. Because the database connection is established in the constructor I end up with a large number of open connections (or at least I think I do based on my understanding of PDO/OO). When I try to run my inserts for each object in the array I end up exceeding my max-allowed-connections.

    Where and how do you handle your database connection so as not to run into this problem?

    Here are some code excerpts to understand my problem better:
    PHP Code:
    Main File:
    //User uploads excel document which I parse into an array
    $car = array();
    foreach(
    $array as $index => $data){
        
    $car[$index] = new Car(null,$data["make"],$data["model"]);
        
    $car[$index]->insert();
    }
    //return car array of objects 
    PHP Code:
    //Car Class
    class Car{

        protected 
    $pkey;
        protected 
    $make;
        protected 
    $model;
        protected 
    $db;

        public function 
    __construct($pkey,$make,$model){
            
    $this->pkey $pkey;
            if(isset(
    $make) && ($make != '')){
                
    $this->make $make;
            }else{
                throw new 
    Exception("Car must have make");
            }
            if(isset(
    $model) && ($model != '')){
                
    $this->model $model;
            }else{
                throw new 
    Exception("Car must have model");
            }
            
    $this->db = new Database();
        }

        public function 
    insert(){
            
    $sql "INSERT INTO TABLE (...) VALUES (..)";
            
    $data = array(
                
    ":make"=>$this->make,
                
    ":model"=>$this->model,
            );
            try{
                
    $this->pkey $this->db->insert($sql,$data);
                return 
    true;
            }catch(
    Exception $err){
                
    //catch errors
                
    return false;
            }
        }

    PHP Code:
    class Database {

        protected 
    $conn;
        protected 
    $dbstr;

        public function 
    __construct() {
            
    $this->conn null;
            
    $this->dbstr "jndi connection string";
            
    $this->connect();
        }

        public function 
    connect(){
            try{
                
    $this->conn = new PDO($this->dbstr); // Used with jndi string
            
    } catch (PDOException $e){
                
    //      print $e->getMessage();
            
    }
            return 
    "";
        }

        public function 
    insert($query$data){
            try{
                
    $this->conn->setAttribute(PDO::ATTR_ERRMODEPDO::ERRMODE_EXCEPTION);
                
    /* Execute a prepared statement by passing an array of values */
                
    $sth $this->conn->prepare($query, array(PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY));
                
    $count $sth->execute($data);
                return 
    $this->oracleLastInsertId($query);
            }catch(
    PDOException $e){
                throw new 
    Exception($e->getMessage());
            }
        }
        public function 
    oracleLastInsertId($sqlQuery){
            
    // Checks if query is an insert and gets table name
            
    if( preg_match("/^INSERT[\t\n ]+INTO[\t\n ]+([a-z0-9\_\-]+)/is"$sqlQuery$tablename) ){
                
    // Gets this table's last sequence value
                
    $query "select ".$tablename[1]."_SEQ.currval AS last_value from dual";
                try{
                    
    $temp_q_id $this->conn->prepare($query);
                    
    $temp_q_id->execute();
                    if(
    $temp_q_id){
                        
    $temp_result $temp_q_id->fetch(PDO::FETCH_ASSOC);
                        return ( 
    $temp_result ) ? $temp_result['LAST_VALUE'] : false;
                    }
                }catch(
    Exception $err){
                    throw new 
    Exception($err->getMessage());
                }
            }
            return 
    false;
        }

        public function 
    close(){
            
    $this->conn null;
        }

    Follow Me On Twitter: BryceRay

  2. #2
    Utopia, Inc. silver trophy
    ScallioXTX's Avatar
    Join Date
    Aug 2008
    Location
    The Netherlands
    Posts
    8,895
    Mentioned
    138 Post(s)
    Tagged
    2 Thread(s)
    You'll probably want to use the singleton pattern here to ensure there is only one and never more than one Database object.
    The main idea is to create a static function that returns the instance of the class if there already was on initialized, or creates one, stores it for later use, and then returns it.
    For this to work the constructor of the class must be made private (or protected) so it can only be called from within the class itself, ensuring no dynamic instances can ever be created.

    So basically, something like

    PHP Code:
    class Database 

        protected 
    $conn
        protected 
    $dbstr

        
    // keep the one and only instance of the Database object in this variable
        
    protected $instance;

        
    // visibility changed from public to private to disallow dynamic instances
        
    private function __construct() { 
            
    $this->conn null
            
    $this->dbstr "jndi connection string"
            
    $this->connect(); 
        } 

        
    // added this method
        
    public static function getInstance() {
          if (!isset(
    self::$instance)) {
            
    self::$instance = new Database();
          }
          return 
    self::$instance;
        }

        
    // everything below this comment is as it was; I made no changes here
        
    public function connect(){ 
            try{ 
                
    $this->conn = new PDO($this->dbstr); // Used with jndi string 
            
    } catch (PDOException $e){ 
                
    //      print $e->getMessage(); 
            

            return 
    ""
        } 

        public function 
    insert($query$data){ 
            try{ 
                
    $this->conn->setAttribute(PDO::ATTR_ERRMODEPDO::ERRMODE_EXCEPTION); 
                
    /* Execute a prepared statement by passing an array of values */ 
                
    $sth $this->conn->prepare($query, array(PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY)); 
                
    $count $sth->execute($data); 
                return 
    $this->oracleLastInsertId($query); 
            }catch(
    PDOException $e){ 
                throw new 
    Exception($e->getMessage()); 
            } 
        } 
        public function 
    oracleLastInsertId($sqlQuery){ 
            
    // Checks if query is an insert and gets table name 
            
    if( preg_match("/^INSERT[\t\n ]+INTO[\t\n ]+([a-z0-9\_\-]+)/is"$sqlQuery$tablename) ){ 
                
    // Gets this table's last sequence value 
                
    $query "select ".$tablename[1]."_SEQ.currval AS last_value from dual"
                try{ 
                    
    $temp_q_id $this->conn->prepare($query); 
                    
    $temp_q_id->execute(); 
                    if(
    $temp_q_id){ 
                        
    $temp_result $temp_q_id->fetch(PDO::FETCH_ASSOC); 
                        return ( 
    $temp_result ) ? $temp_result['LAST_VALUE'] : false
                    } 
                }catch(
    Exception $err){ 
                    throw new 
    Exception($err->getMessage()); 
                } 
            } 
            return 
    false
        } 

        public function 
    close(){ 
            
    $this->conn null
        } 

    Then in the Car class, instead of setting $this->db = new Database(), use $this->db = Database::getInstance();

    Does that make sense?
    Rémon - Hosting Advisor

    Minimal Bookmarks Tree
    My Google Chrome extension: browsing bookmarks made easy

  3. #3
    SitePoint Addict
    Join Date
    May 2008
    Location
    Missouri, USA
    Posts
    273
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    That works perfectly. There is a small typo though for anyone else who might want to use the example. The $instance variable should be declared static.
    Follow Me On Twitter: BryceRay

  4. #4
    Utopia, Inc. silver trophy
    ScallioXTX's Avatar
    Join Date
    Aug 2008
    Location
    The Netherlands
    Posts
    8,895
    Mentioned
    138 Post(s)
    Tagged
    2 Thread(s)
    oops. yes, you're right. sorry about that.

    glad you like it though
    Rémon - Hosting Advisor

    Minimal Bookmarks Tree
    My Google Chrome extension: browsing bookmarks made easy

  5. #5
    SitePoint Addict
    Join Date
    May 2008
    Location
    Missouri, USA
    Posts
    273
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    This class has been working great, but my project requirements have expanded and I'm now a little confused as to how I can modify this approach to my new requirements. I now have to handle connections to multiple database.

    I have 1 database setup that this class was designed for. This database continues to exists and be used. However, I also now have a second type of database to connect to. I will not know the connection_string or credentials for this database until run time (when the user selects 1 of the 4 available databases).

    At most, the app will have to handle 2 separate database connections. As I understand it, this begins to move away from the singleton example. But if I wanted to keep the code as is and adjust for this new database (short-term fix) would it be as simple as adding a second class (under a different name) with almost identical code? Then I believe I would be able to maintain two simultaneous connections.

    In addition to the short-term approach outlined above, do you have any suggestions for better overall approaches?

    Thanks for the help and clarification.
    Follow Me On Twitter: BryceRay

  6. #6
    Utopia, Inc. silver trophy
    ScallioXTX's Avatar
    Join Date
    Aug 2008
    Location
    The Netherlands
    Posts
    8,895
    Mentioned
    138 Post(s)
    Tagged
    2 Thread(s)
    Yes, you could create a copy of that class, but it's not a very good idea as it totally goes against the DRY (don't repeat yourself) principle.
    Instead, I'd turn the Singleton into a Factory. Something like this:


    PHP Code:
    class Database 

        protected 
    $conn
        protected 
    $dbstr

        
    // keep the Database instances of the Database object in this variable
        
    protected $instances;

        
    // visibility changed from public to private to disallow dynamic instances
        
    private function __construct($dbstr) { 
            
    $this->conn null
            
    $this->dbstr $dbstr
            
    $this->connect(); 
        } 

        
    // added this method
        
    public static function getInstance($dbstr) {
          
    $id md5($dbstr);
          if (!isset(
    self::$instances[$id])) {
            
    self::$instances[$id] = new Database($dbstr);
          }
          return 
    self::$instances[$id];
        }

        
    // everything below this comment is as it was; I made no changes here
        
    public function connect(){ 
            try{ 
                
    $this->conn = new PDO($this->dbstr); // Used with jndi string 
            
    } catch (PDOException $e){ 
                
    //      print $e->getMessage(); 
            

            return 
    ""
        } 

        public function 
    insert($query$data){ 
            try{ 
                
    $this->conn->setAttribute(PDO::ATTR_ERRMODEPDO::ERRMODE_EXCEPTION); 
                
    /* Execute a prepared statement by passing an array of values */ 
                
    $sth $this->conn->prepare($query, array(PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY)); 
                
    $count $sth->execute($data); 
                return 
    $this->oracleLastInsertId($query); 
            }catch(
    PDOException $e){ 
                throw new 
    Exception($e->getMessage()); 
            } 
        } 
        public function 
    oracleLastInsertId($sqlQuery){ 
            
    // Checks if query is an insert and gets table name 
            
    if( preg_match("/^INSERT[\t\n ]+INTO[\t\n ]+([a-z0-9\_\-]+)/is"$sqlQuery$tablename) ){ 
                
    // Gets this table's last sequence value 
                
    $query "select ".$tablename[1]."_SEQ.currval AS last_value from dual"
                try{ 
                    
    $temp_q_id $this->conn->prepare($query); 
                    
    $temp_q_id->execute(); 
                    if(
    $temp_q_id){ 
                        
    $temp_result $temp_q_id->fetch(PDO::FETCH_ASSOC); 
                        return ( 
    $temp_result ) ? $temp_result['LAST_VALUE'] : false
                    } 
                }catch(
    Exception $err){ 
                    throw new 
    Exception($err->getMessage()); 
                } 
            } 
            return 
    false
        } 

        public function 
    close(){ 
            
    $this->conn null
        } 

    Then you can make as many connections as you like:

    PHP Code:
    $db1 Database::getInstance('jndi connection string 1');
    $db2 Database::getInstance('jndi connection string 2'); 
    Does that help?
    Rémon - Hosting Advisor

    Minimal Bookmarks Tree
    My Google Chrome extension: browsing bookmarks made easy

  7. #7
    SitePoint Addict
    Join Date
    May 2008
    Location
    Missouri, USA
    Posts
    273
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    That is exactly what I needed, and much simpler than what I was thinking. Thanks for your help.
    Follow Me On Twitter: BryceRay

  8. #8
    Utopia, Inc. silver trophy
    ScallioXTX's Avatar
    Join Date
    Aug 2008
    Location
    The Netherlands
    Posts
    8,895
    Mentioned
    138 Post(s)
    Tagged
    2 Thread(s)
    No problem, feel back to come back any time
    Rémon - Hosting Advisor

    Minimal Bookmarks Tree
    My Google Chrome extension: browsing bookmarks made easy


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •