What's the better practice to database connect

Question

I would like to know what's the better way to make DB connection using PHPOO.

If I set the connection on __construct and close connection on __destruct, wouldn't I making too many useless connections?

For example.. My class is called "sqlClass" and it has some methods..

So.. let's instantiate this class...

$myObj = new sqlClass(); // mysql_connect and mysql_close executed here...

$myObj->insertData(); // mysql_connect and mysql_close executed here again...

Should I set the connection separated?

Do not use deprecated `mysql_*` functions! Use PDO / MySQLi instead. If you intend to use OO for DB connection, please make sure you're using same DB connection. Lots to consider if you want to wrap the DB connection in object-oriented approach. — Raptor, Dec 18 '14 at 02:37
There's no 1 answer that can fit all applications. You'll have to investigate the behaviour of your application with regards to its interaction with the database and decide on your solution based on that. — sn00k4h, Dec 18 '14 at 02:37

score 1 · Accepted Answer · answered Dec 18 '14 at 03:01

1 nailed connection

class sqlClass {
    private $_connection = null;
    public function __construct() {
        $this->_connection = new SomeConnection(array('param1', 'param2', 'param3'));
        }
    public function query($query) {
        return $this->_connection->query();
        }
    }

2 add more freedom

class sqlClass {
    private $_connection = null;
    public function __construct($params) {
        $this->_connection = new SomeConnection($params);
        }
    public function query($query) {
        return $this->_connection->query();
        }
    }

3.1 either add lazyness

class sqlClass {
    private $_connection = null;
    private $_params = null;
    public function __construct($params) {
        $this->_params = $params
        }
    public function query($query) {
        if ($this->_connection === null)
            $this->_connection = new SomeConnection($this->_params);
        return $this->_connection->query($query);
        }
    }

3.2 either add dependency injection

class sqlClass {
    private $_connection = null;
    public function __construct(SomeConnection $connection) {
        $this->_connection = $connection;
        }
    public function query($query) {
        return $this->_connection->query($query);
        }
    }

4 Move lazyness to connection class and use dependency injection

class LazyConnection extends SomeConnection{
    private $_params = null;
    private $_inited = false;
    public function __construct($params){
        $this->_params = $params;
        }

    public function query($query){
        if (!$this->_inited){
            parent::__construct($this->_params);
            $this->_inited = true;
            }
        parent::query($query);
        }
    }

With OOP you do not have to close connection manually: http://php.net/manual/en/features.gc.php

score 0 · Answer 2 · answered Dec 18 '14 at 02:37

0

you should open the connection as late as possible and close it as soon as possible. if your driver is using connection pooling it will re-use the connection. if your code fails for some reason, or never gets to the part where you are inserting data you will not be left with leaking connections.

As a rule of thumb, open them late, close them early and let the driver do the rest

answered Dec 18 '14 at 02:37

Born2Code

975
5
13

You don't necessarily always want to close early. Sometimes you may even want to keep a persistent connection and never close the connection at all. For example if you have few processes running queries frequently, making the establishing the connection become an overhead. – sn00k4h Dec 18 '14 at 02:47
i will wager that the driver will do a better job of handling the connection pool than he will :) here is an earlier discussion (6 years ago) regarding the same issue: http://stackoverflow.com/questions/312702/is-it-safe-to-keep-database-connections-open-for-long-time opinions vary, but having built many commercial applications that had to deal with scaling and stability issues, my opinion is you open late, close early and let the driver do its thing – Born2Code Dec 18 '14 at 03:01

What's the better practice to database connect

2 Answers2