Best way to hand a $db connection to a function

Scope question

[trillianjedi]

Hi all,

My C background reminds me that variables handed to a function are copied locally for use (unless using pointers within the function of course).

I'm wondering if PHP is similar? My particular brain ache relates to database connections. EG:-

<?php

$db = &ADONewConnection('mysql');
$db->Connect($db_hostname, $db_username, $db_password, $db_database);

$sql = 'SELECT something';

$result = $db->GetRow($sql);
$bitIwant = $result['thisbit'];

?>

If I want to turn this into a function, should I hand a pointer to the function like this:-

<?php

// Keep this database in scope as we need it again later
$db = &ADONewConnection('mysql');
$db->Connect($db_hostname, $db_username, $db_password, $db_database);

$mybit = getMyBit(&$db);

function getMyBit(&$db) {
$sql = 'SELECT something';
$result = $db->GetRow($sql);
$bitIwant = $result['thisbit'];
}

?>

Or, within each function that requires a database connection should I recreate it (seems a bit wasteful)?

I'm just curious as to what is considered best practice for this.

Thanks :)

[phranque]

i'm not a php programmer but i would assume the function syntax allows the interpreter to handle the required referencing and dereferencing.
you should probably leave out the '&'.

and yes you should minimize the number of connections made for efficiency.

as a matter of fact, in perl you can use the Apache::DBI module to initiate a persistent database connection [search.cpan.org].
not sure if there is a php equivalent...

[mack]

Again im not a php programer but do quite a bit of work with php. What I do is simply create one database connection within all script files that will require to use or modify the database. I have seen scripts where they re-connect every time a databse action is required. This sort of application simply wont scale well. Its like phranque mentioned. The fewer connections you need the more efficient your application will be.

Mack.

[trillianjedi]

Thanks for the help chaps.

I'll double-check that I don't need to reference the $db connection when using it with a function.

[eelixduppy]

PHP actually has a built-in way to prevent from opening multiple connections to a database server. mysql_connect [php.net] has three return possibilities:

1. If a call to mysql_connect() contains the same arguments as a previous call to mysql_connect() and the link is still open, it returns the resource link that is already open.
   
2. If no connection already exists, it creates one and returns the resource link to it.
   
3. If there is a failure FALSE is returned.
   

So even if you were to "create multiple connections" they aren't really happening, unless, of course, you specify the "new link" parameter as part of your mysql_connect function call, which I wouldn't normally advise.

>> I'll double-check that I don't need to reference the $db connection when using it with a function.

You shouldn't need to but I always (or try to) do it for clarity and good practice. :)

[coopster]

I just wanted to add ...

... don't confuse the perl DBI "persistent" database connection module with persistent database connections as defined in any given RDBMS with a persistent database connection feature. The HTTP protocol in and of itself is stateless so the "persistence" part of it just doesn't exist across connections/requests. I just wanted to clarify to eliminate any confusion.

You can indeed get your db connection to "persist" in a single http request transaction though and the best method for accomplishing the task in PHP (IMHO) is a singleton design pattern. It is easy to set something like this up and running quickly, the hard part is wrapping your head around it all :) Here is an example class and at the end I'll throw down a couple of helpful links. Note, the example assumes you have a separate Database class that actually performs the connection and perhaps has other properties and methods ...

/** 
 * Obtain the Database class as it is going to be our object instance 
 */ 
require_once 'Database.php'; 
class DatabaseInstance 
{ 
  /** 
   * The instance of this class 
   * @var object 
   * @access private 
   */ 
  private static $_i; 
  /** 
   * Database class object 
   * @var object 
   * @access private 
   */ 
  private $_db; 
  /** 
   * Establish a connection to the database 
   * Private constructor prevents direct creation of object 
   * 
   * @access private 
   */ 
  private function __construct() 
  { 
    $user      = 'username'; 
    $password  = 'password'; 
    $name      = 'database'; 
    $server    = 'localhost'; 
    $this->_db = new Database($user, $password, $name, $server); 
  } 
  /** 
   * Creates a single instance of the database connection 
   * 
   * @return object singleton instance of the database class 
   * @access public 
   */ 
  public static function create() 
  { 
    self::$_i = !is_null(self::$_i) ? self::$_i : new self; 
    return self::$_i->_db; 
  } 
  /** 
   * Prevents cloning 
   * 
   * @return void 
   * @access public 
   */ 
  public function __clone() 
  { 
    trigger_error('Clone is not allowed.', E_USER_ERROR); 
  } 
}

So, put that in it's own class and you can include it whenever you need it:

require_once 'DatabaseInstance.php'; 
$db = DatabaseInstance::create(); 
// Example of getting the link identifier 
// (assuming your DB class has a public method that will return it!) 
$dbLinkId = $db->getDbLinkId(); 

PHP singleton pattern [php.net]
IBM examples [ibm.com]

[whoisgregg]

If you only have one database connection, or if you know for a particular function that it should only ever use a particular database connection, you can call it using global scope [php.net]:

$db = &ADONewConnection('mysql'); 
function doSomeDbThing(){
global $db;
$sql = 'SELECT something';
$result = $db->GetRow($sql); 
}