what is the php mysql db connection scope when used in a class?

Question

I have 2 classes, Base.class.php and MyClass.class.php. MyClass extends Base. Base has a var $link which is initialised as a mysql database connection when the constructor is called.
Base also has a function destroy() which closes the link.

Each page uses $myClass = new MyClass(); thereby initialising $link in Base. At the end of each page I call $myClass->destroy() to close the $link in the parent class. This works OK.

The problem is that when I instantiate AnotherClass.class.php which also extends Base, and is also used in the same pages, that when I call $anotherClass->destroy() before I call $myClass->destroy() it closes the $link in *both* $myClass *and* $anotherClass. This causes an error because $myClass should still be open for use.

What is $link in each Base class? Is it a reused connection? Is it an existing open connection? Is it a page scope connection? How should I get around this? Using true on the mysql open connection? Or never using destroy()?

Answer 1

Well, I found that mysql behaves a little differently than I expected it to when coding up my own PHP/MySQL website...

I don't need to dumb this down as you obviously know what you are talking about, my SQL connection is created in my config.php file which is called before anything else...

I use a function perform_SQL() to handle some of the database writes and that function does NOT need to be passed the connection, nor does it need something like $connection to be declared global... The mysql_query (etc...) commands which I use inside that function just seem to know that there is a connection available for use...

The same is true for all of my classes, I have about 30 different files all with different classes which interact with the database in different ways... None of these classes have any reference at all to the MySQL connection created in the config.php file.

I would suggest, but I don't know for sure, that opening up a MySQL database connection means that the scope of that connection covers your entire application... The MySQL connection seems to cover everything from your php page to a function inside a class with no reference to it.

if you just type :

$result=mysql_query('select * from [database].[whatever];')

in to a php page, function, class, whatever after your connection is established then it will return something if the data is there... You don't need to tell the mysql_query command which connection to use, it just knows...


At least that's how I see it when I'm coding my application, there's probably a much longer and more technically accurate reason though of course... Hope this helps anyway.

Answer 2

Connections are indeed reused. Moreover, if you try to open a connection to a server and another connection to that same server already exists, it will be used. Consequently, when a class closes a connection, it is no longer available.

As a practical matter, you should not open or close connections from within a class. Write a dedicated class managing DB connection(s) and write your other classes assuming a connection has already been opened.

You can force a new connection by passing an optional fourth argument to mysql_connect(), but this will hurt your application's performance.