MySQL connection: globally or in object?

Question

I have two PHP classes. One is for connecting to the database, building queries, executing them, and disconnecting from the database. The other class is for users: adding them, updating them, logging them in, etc.

I'm debating whether I should connect to the database on the page globally and use that connection (passing the database object into the method of the user object), or whether I should connect and disconnect from the database from within the a user method itself.

The advantage I see for connecting globally, is that once connected I have that connection available for executing multiple methods. The disadvantage is that I 7need to worry about passing the database object around.

The advantage of connecting within the method is that it's completely transparent, however, there might be 4 or 5 connections established and disconnected, which may lead to an overhead.

Is there a best practice for using either, or does it really depend on the amount of users and the server's specs e.g. memory, cpu, etc. The system needs to support up to about 1,000 users, so it's fairly small scale.

Any feedback would be much appreciated.

-Ryan

Answer 1

In PHP, the best practice is to take the global approach. This is mainly due to the fact that repeated connects/disconnects to the MySQL server can cause a significant decrease in performance.

In fact, though this may seem counter-intuitive, most PHP experts (including myself) recommend that you avoid using mysql_close() altogether, unless there's a pressing reason not to. This is because that is handled automatically in PHP's cleanup anyway, so adding mysql_close() just creates a further deterioration in performance.

Answer 2

Create a function that creates the connection on demand and provides access to the connection object:

function getDb()
{
    static $db;
    if (!$db) $db = ...;
    return $db;
}

Wrapped into class this approach is called Singleton