Page 1 of 1

Maintaining A Database Connection

Posted: Tue Jan 04, 2005 4:15 am
by crouse
I have created a database abstractionI have created a database abstraction class. This class currently has three methods. The three methods are Init, setDatabase, and Execute. The Init method accepts the host, username and password for authenticating to a MySQL database. The method setDatabase accepts the name of a database and then sets the active database for the connection established by the Init method. The Execute method accepts a string that is sent to the MySQL database established by the setDatabase method. This class has two private members $_link, and $_database. $_link stores the resource that is created by the mysql_connect function. $_database stores the name of the database; however this member is currently not being used. Each method throws appropriate exceptions in order to ensure proper execution.

When I instantiate this class and call the methods one right after another everything works as expected. I am able to log into the database server, set the proper database and execute a query that returns information. My problem occurs when I place the object into a $_SESSION variable. I first call a function that initializes the object and places the object in the $_SESSION array. Later in the script I attempt to execute a simple query and the connection is gone. I even attempt to set the database again and I am unable to do so because I no longer have a connection to the database server.

Is there anyway that I can connect to a MySQL server, or any RDMS for that matter, and maintain that connection through out the session of a user. I am looking for a way to maintain the connection between pages. The basic idea is that the user logs into my systems and a connection that belongs to only that user is created and used until that user logs out or times out. What I am attempting to avoid is the open connection, execute, close connection method of database queries in my objects. I have been able to implement a similar method of connection handling in other languages however I am unable to find a way for PHP to facilitate such a need. I also don’t want to use mysql_pconnect unless someone can provide a way to close the connection once the connection is no longer needed.

I can provide any clarification that is needed to further answer this request.

Any help is greatly appricated,
Chris

Posted: Tue Jan 04, 2005 8:44 am
by feyd
each page request requires opening the connection to mysql again. you may want to look at persistant connections. [php_man]mysql_pconnect()[/php_man]

It worked Thank you

Posted: Wed Jan 05, 2005 3:11 am
by crouse
After reading some more about mysql_pconnect and some of the users comments I did a little more research on the mysql site. I was able to come to a happy medium regarding a persistant connection not requiring me to reauthenticate to the server everytime a statement is ran and being able to close the connection when not needed any longer. By setting the inactivity timers to a given time I let MySQL handle the management of connections. Currently, if a connection is not used for over a minute then the server closes the connection. However, the connection will be open as long as there is activity against the server under that connection at least once a minute. My time range might change as my needs are different, however at this point in my project it is just proof of concept.

Thank you,
Chris