Hi,

I work on website which have large number of users in php.
I wonder which is the best way to handle database connection in this case.
Every database object have it's related class and on the start of every class method database connection opens (for example GetLatestNews(), GetNewsOfType($type) etc). But I have really lot of methods so I'm afraid there should be to much connections opening.

Is it best practice in this case to open only one connection, for example in $_SESSION variable and then check if(isset($_SESSION]) or maybe to open connection on the start of every page? I know that opened connection consume server memory etc...

Can you please give me some advice from your experience?

Thanks in advance,

Amer

If your user base is large and you have a lot of pageviews, then instead of worrying about your mysql connections, worry about caching.

If you're opening a mysql connection for every method call you are definitely doing it inefficiently.

The best way to handle the connection would be to setup one connection per request, since php's scope is limited pretty much to each request, and pass it into all of your different objects where it is needed.

e.g.

<?php

try {
    $dbh = new PDO($dsn, $user, $password);
} catch (PDOException $e) {
    echo 'Connection failed: ' . $e->getMessage();
}

//Inject the database handler via constructor
$someClass = new SomeClassThatNeedsDatabaseAccess( $dbh );
// OR use a setter
//$someClass = new Some ClassThatNeedsDatabaseAccess();
//$someClass->setDbh( $dbh );

$someClass->useTheDbConnection();

//.....

class SomeClassThatNeedsDatabaseAccess
{
	protected $dbh;
	
	public function __construct( $dbh = null )
	{
		if( !is_null( $dbh ) ){
			$this->setDbh( $dbh );
		}
	}
	
	public function setDbh( PDO $dbh )
	{
		$this->dbh = $dbh;
	}
	
	public function getDbh()
	{
		return $this->dbh;
	}
	
	public function useTheDbConnection()
	{
		$result = $this->getDbh()->query('...');
	}
	
}

Thanks for replyes. But I'm not using PDO for working with database.
So, the best way is to make connection for every request?

Make sure per request you have single connection. When user base increases then start caching pages and if that overruns then think of more servers and sync them!

You don't have to use PDO for this and you don't have to use the type hinting like I did. The concept is still applicable for any database connection.

Whether it is Mysql (hopefully not), Mysqli, or PDO, create the connection into a variable and then pass the connection into the classes that need a database connection.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.