Development Automagically sharing MySQL connections between classes

Here's a cool one that took me an hour or so to figure out.

100% of my php work these days is done using classes, most if not all of which at some point involve talking to a MySQL database - nothing fancy there. However, take a situation where i'm using 2 (or more) classes in the same script, each of which have a constructor which connects to my database so that the class & related functions can all work nicely. Great - but it's a bit daft if classes x, y and z (in the same script) create three separate MySQL connections.

Let's solve that with some fictional code:

Code:

<?php
	$a = &new class_a;
	
	$b = &new class_b($a->dbhandle);
	
	$c = &new class_c($a->dbhandle);
?>

All that i've done here is include a bit of logic in the constructor of each class to take a look to see if there's an argument passed into it - and if there is, and it's an object (ok, not so great error checking), to assume that it's a MySQL resource handle, and to use it throughout the class functions. So effectively right there you're connecting once in class_a, and using that connection throughout as many classes as you desire (assuming all classes need the same database you're connected to, etc).

Naturally I had to take this a little bit further... what if we could write some php that takes a look at all of the currently defined classes and looks for a mysql connection in each. If it finds one, it shares that through any other defined (and invoked) classes.

The following php uses strings in place of actual mysql connections and is really only a proof of concept. Anyway...

Here we have a class which looks at all of the defined classes (bar some which have been removed using array_splice() that we don't want to look in) and if it finds one, it sets $shared_dbhandle equal to the mysql connection. I hope that made sense:

Code:

	class clever
	{
		var $class_array = array();
		var $shared_dbhandle;
		
			function clever($foo)
			{
				//put defined classes into array
				$this->class_array = get_declared_classes();
				//clean up:
				$this->class_array = array_slice($this->class_array, 4);
								
				foreach($this->class_array as $class)
				{
					if($foo[$class]->dbhandle != '') {
						$this->shared_dbhandle = $foo[$class]->dbhandle;
					}				
				}	
				return(true);
			}
	}

Now all we need to do in any other defined classes that require a db connection is use that class to find us a db connection. Here are two imaginary classes that need db connections to do things: (they're both the same, just named differently)

Code:

	class doah
	{
		var $dbhandle;
		
		function doah($foo)
		{
			$shared = &new clever($foo);
			if($shared->shared_dbhandle != '') {
				//we've found a connection in another class
				$this->dbhandle = $shared->shared_dbhandle;
			} else {
				//we haven't found a connection in another class so
				// code here to return a mysql handle into $this->dbhandle:
				//$this->dbhandle = &new sql_db(blah, blah, blah, blah);
				$this->dbhandle = 'mysql connection created in doah()';
			}						
		}		
	}	
	
	class gomo
	{
		var $dbhandle;
		
		function gomo($foo)
		{
			$shared = &new clever($foo);
			if($shared->shared_dbhandle != '') {
				$this->dbhandle = $shared->shared_dbhandle;
			} else {
				$this->dbhandle = 'mysql connection created in gomo()';
			}		
		}
	}	

(Remember that I said at this point we're just using strings as the proof of concept here)

---

And now we come to the point of usage

Code:

	$bar['doah'] = &new doah($bar);

As you can see, I invoked a new doah into an array element of array $bar, and then passed $bar back into the class so it can check classes invoked in $bar for connections. In this case it will not find one, and doah() will create itself a new connection in the else{} section of the if statement.

With me so far?

So now we want to use class gomo...

Code:

	$bar['doah'] = &new doah($bar);
        $bar['gomo'] = &new gomo($bar);

Again, passing $bar into both classes. When we invoke gomo, after doah, gomo's instance of class clever will see that doah has a mysql connection (as he was invoked first), and pass that through to $foo['gomo']->dbhandle.

And as proof:

Code:

	$bar['doah'] = &new doah($bar);
        $bar['gomo'] = &new gomo($bar);
        echo $foo['gomo']->dbhandle;

will echo out:

Code:

mysql connection created in doah()

Drawbacks:

1) you must (as far as i can see) invoke all of your classes into an array, with elements named the same as the classes themselves
2) you must always have your db connections in class vars called $dbhandle.
3) probably more server intensive

Advantages:
1) just use your classes, as many as you wish and they'll sort out a single db connection for one and all to use - no manual passing of one to another
2) there is no #2?!

I hope this has made sense... 5:41AM + php explanations != good

 

I understood quite well .... seemed like a good idea when i first questioned you if it was possible... yet now it looks quite complicated at least it will force me into using some kind of conventional naming for my variables!

 

So, here's my newbie question, should I leave the MySQL connection open for the entire execution of the script?

I noticed that MS Access would weird out on me if I left the connection open between it and PHP, so I would always close the database each time I finished a function. This lead to disconnecting and reconnecting up to 10 or 15 times in a single script, but when I didn't do it my scripts would oftentimes time out.

Of course MySQL is faster, so speed really isn't the issue. The issue now is, is there any reason to not keep a connection to MySQL open for the entire script?

Also, I've noticed using PHP5's class destructor, the MySQL variable gets closed before the destructor is called. Does this mean I just leave my connection open and let PHP automagically close it? That sounds kind of sketchy...

Oh, and thanks for the code *nyoink* (the sound made as I steal the code)

 

I believe what you are trying to do by sharing a single database connection between your classes is satisfied by making your DB class a singleton. More information on this pattern can be found here:

http://www.phppatterns.com/index.php/article/articleview/6/1/1/ (PHP4)

http://www.zend.com/zend/trick/tricks-app-patt-php.php

Your classes can construct as many instances of your DB class as they require but it will only hand back the same instance if it has already been instantiated.

Hope this helps.