PHP Database connection practice

Question

I have a script that connects to multiple databases (Oracle, MySQL and MSSQL), each database connection might not be used each time the script runs but all could be used in a single script execution. My question is, "Is it better to connect to all the databases once in the beginning of the script even though all the connections might not be used. Or is it better to connect to them as needed, the only catch is that I would need to have the connection call in a loop (so the database connection would be new for X amount of times in the loop).

Yeah Example Code #1:

// Connections at the beginning of the script
$dbh_oracle = connect2db();
$dbh_mysql  = connect2db();
$dbh_mssql  = connect2db();

for ($i=1; $i<=5; $i++) {
   // NOTE: might not use all the connections
   $rs = queryDb($query,$dbh_*); // $dbh can be any of the 3 connections
}

Yeah Example Code #2:

// Connections in the loop
for ($i=1; $i<=5; $i++) {
   // NOTE: Would use all the connections but connecting multiple times
   $dbh_oracle = connect2db();
   $dbh_mysql  = connect2db();
   $dbh_mssql  = connect2db();

   $rs_oracle = queryDb($query,$dbh_oracle);
   $rs_mysql  = queryDb($query,$dbh_mysql);
   $rs_mssql  = queryDb($query,$dbh_mssql);
}

now I know you could use a persistent connection but would that be one connection open for each database in the loop as well? Like mysql_pconnect(), mssql_pconnect() and adodb for Oracle persistent connection method. I know that persistent connection can also be resource hogs and as I'm looking for best performance/practice.

Here is a good post on why persistent connections could cause problems

score 15 · Accepted Answer · edited May 11 '14 at 17:58

15

Use a lazy connection wrapper class:

class Connection
{
    private $pdo;
    private $dsn;

    public __construct($dsn)
    {
        $this->dsn = $dsn;
    }

    public query($sql)
    {
        //the connection will get established here if it hasn't been already
        if (is_null($this->pdo))
            $this->pdo = new PDO($this->dsn);

        //use pdo to do a query here

    }
}

I hope you're already using PDO. If not, you should be. PDO is database independent. If you did this using procedural functions, you'd have to create a new class for each database type.

Anyways, this is just a skeleton (you'd want to add a $params option in query(), for example), but you should be able to get the idea. The connection is only attempted when you call query(). Constructing the object does not make a connection.

As an aside, consider using Doctrine. It has lazy connections and makes life easier in general.

edited May 11 '14 at 17:58

Rahil Wazir

10,007
11
42
64

answered Apr 01 '10 at 13:58

ryeguy

65,519
58
198
260

I like this approach, I have a similar class that I created to use a XML config and a generic connection and query class using the type (mysql,mssql,oracle) to define the connection/query functionality. – Phill Pafford Apr 01 '10 at 14:31
1

@Phill if you use PDO you don't even need to do that, it works no matter what database you connect to without changing your code. – ryeguy Apr 01 '10 at 14:39
will have to check PDO out, thnx again – Phill Pafford Apr 01 '10 at 14:46
@ryeguy How would I use this in a model class, for example? Do it with a setter (via DI)? myclass (){ public function setConn....} – johnny Sep 02 '14 at 21:15

score 2 · Answer 2 · answered Apr 01 '10 at 13:30

2

The best performance/practice rule is simple: do connect to one database only.

As for the connects - try to implement some database access class. Which can connect automatically on demand.

answered Apr 01 '10 at 13:30

Your Common Sense

156,878
40
214
345

1

+1 If you don't know, if a particular connection will be needed, use lazy connections that only become active if they are used. – selfawaresoup Apr 01 '10 at 13:32
lazy connections? I've never heard of this, Could you provide a good resource? Googling as well. Thanks – Phill Pafford Apr 01 '10 at 13:39

PHP Database connection practice

2 Answers2

Linked