Passing database connection by reference in PHP

Question

The question is if a database connection should be passed in by reference or by value?

For me I'm specifically questioning a PHP to MySQL connection, but I think it applies to all databases.

I have heard that in PHP when you pass a variable to a function or object, that it is copied in memory and therefore uses twice as much memory immediately. I have also heard that it's only copied once changes have been made to the value (such as a key being added/removed from an array).

In a database connection, I would think it's being changed within the function as the query could change things like the last insert id or num rows. (I guess this is another question: are things like num rows and insert id stored within the connection or an actual call is made back to the database?)

So, does it matter memory or speed wise if the connection is passed by reference or value? Does it make a difference PHP 4 vs 5?

// $connection is resource
function DoSomething1(&$connection) { ... }
function DoSomething2($connection) { ... }

Answer 1

Call-time pass-by-reference is being depreciated,so I wouldn't use the method first described. Also, generally speaking, resources are passed by reference in PHP 5 by default. So having any references should not be required, and you should never open up more than one database connection unless you really need it.

Personally, I use a singleton-factory class for my database connections, and whenever I need a database reference I just call Factory::database(), that way I don't have to worry about multiple connections or passing/receiving references.

<?php
Class Factory
{
  private static $local_db;

/**
* Open new local database connection
*
* @return MySql
*/
public static function localDatabase() {
 if (!is_a(self::$local_db, "MySql")) {
  self::$local_db = new MySql(false);
  self::$local_db->connect(DB_HOST, DB_USER, DB_PASS, DB_DATABASE);
  self::$local_db->debugging = DEBUG;
 }
 return self::$local_db;
}
}
?>

Answer 2

i don't really have a specific answer for php, but in general it would seem to me that you would want to pass this by reference if you are not explicitly sure that you encounter performance issues when passing by value.

Answer 3

A PHP resource is a special type that already is a reference in itself. Passing it by value or explicitly by reference won't make a difference (ie, it's still a reference). You can check this for yourself under PHP4:

function get_connection() {
  $test = mysql_connect('localhost', 'user', 'password');
  mysql_select_db('db');
  return $test;
}

$conn1 = get_connection();
$conn2 = get_connection(); // "copied" resource under PHP4

$query = "INSERT INTO test_table (id, field) VALUES ('', 'test')";
mysql_query($query, $conn1);
print mysql_insert_id($conn1)."<br />"; // prints 1

mysql_query($query, $conn2);
print mysql_insert_id($conn2)."<br />"; // prints 2

print mysql_insert_id($conn1); // prints 2, would print 1 if this was not a reference

Answer 4

A database connection does not actually hold the underlying values, so you don't have to worry about losing assignments made inside a function. Metaphorically, you can think of a DB connection as, say, a runway number -- "OK, DB Connection 12 is cleared to be used for a query" -- The query and result set use the connection, and may need exclusive access for awhile, but the connection does not know anything about the underlying data.

Answer 5

I'm not sure what datatype $connection is.

If it is a PHP resource (e.g. what you get returned from mysql_connect()), then it should make no difference as it is just a reference to an external resource.

If it is an object (like an adodb connection), then you should pass it by reference in PHP4 otherwise the object could get copied. In PHP5 it will make no difference as far as I know as objects are held by reference anyway.

Answer 6

Generally speaking, references are not faster in PHP. It's a common misconception, because they are semantically similar to C pointers, so people familiar with C often assume they work the same way. Not so. In fact, references are a tiny bit slower than copies, unless you actually assign to the variable (Which in any case is bad style, unless the variable is an object).

PHP has a mechanism called copy-on-write, which means that a variable isn't actually copied before it needs to. You can pass a huge data structure to a function; As long as it just reads from it, it makes no difference. A reference however, needs an additional entry in the internal registers, so it would actually take some extra processing (Though barely noticeable).

Answer 7

A few people have said that you don't need to worry about this for PHP 5. This is incorrect, if you have a database OBJECT that you're using for all access. In that case, you do need to pass by reference, otherwise it instantiates a new DB object, which (often) creates a new connection to the database.

I discovered this using XDebug & WinCacheGrind, which kindly shows all the destructors that get called - in my case, a halfdozen or more database objects.

To clarify: The reason I point this out is that this is a common way of using Database connections, instead of the raw connection resource.

Answer 8

It isn't the speed you should be concerned with, but the memory.

In PHP 4, things like database connections and resultsets should be explicitly passed by reference. In PHP 5, this is done automatically, so you don't have to make it explicit.

BTW, singleton methods for creating database handles are a good idea: you can do $db = & Database::Connection(); and always get the correct handle. This saves you from using a global and the static method can do extra magic (like opening it automatically) for you. Just be careful of when your application scales enough that it needs multiple databases: then your magic function will have to know how to hand you back the correct one. IME this is not hugely difficult; the basic way to solve that is for the code layer that needs the DB handle to know how to ask for the correct one.