[RESOLVED] Database Connections and Recordsets

_sh

I'm not sure whether I'm currently doing this correctly - within the site that I've built, my configuration file is included into the top of every page, within that file I have a string to open my DB as follows...

		if (!$conn = mysql_connect($TheDBHostName, $TheDBUsername, $TheDBPassword)) { 
			header("Location: offline.php?!=GeneralDBConnectionFailure");
			exit;
		} else {
			$db = mysql_select_db($TheDBDatabaseName,$conn);
		}

Then, whenever in a page I require a Recordset, I'm doing something like this...

$sql = "SELECT MemberFirstName, MemberLastName FROM tbl_members_main WHERE MemberID = " . $TheMemberIDToAdd;
$RS_GrabNameOfNewFriend_Query = mysql_query($sql, $conn) or header("Location: offline.php?!=DatabaseTableUnavailable");
$RS_GrabNameOfNewFriend = mysql_fetch_assoc($RS_GrabNameOfNewFriend_Query);

My question is should I close recordsets, OR perhaps even the DB connection? Or am I doing this right by having the opening of the DB connection on every page? This is the way I've always worked in PHP (being a relative newbie), I'm really seeking clarification that I'm right, or that there's a better way?

Also, should I be closing recordsets after using them, at the base of each page? Or are they cleansed automatically?

My host is very stringent on PHP memory usage and currently I'm always going over quota (although its more likely my SMF forum that I have running on the webspace).

_sh

Anyone give some advice?

laserlight

ashleyhall wrote:
My question is should I close recordsets, OR perhaps even the DB connection? Or am I doing this right by having the opening of the DB connection on every page?

If you can avoid opening a database connection on a page, then do not open it. If you can close a database connection early, and will not open a connection to the same database later on in the script, then go ahead and close it. If you can free a result set, then go ahead and free it.

ashleyhall wrote:
Also, should I be closing recordsets after using them, at the base of each page? Or are they cleansed automatically?

Except due to bugs beyond your control, all resources in use will automatically be reclaimed when the script terminates.

_sh

laserlight;10904694 wrote:
If you can avoid opening a database connection on a page, then do not open it. If you can close a database connection early, and will not open a connection to the same database later on in the script, then go ahead and close it. If you can free a result set, then go ahead and free it.

Thanks for your reply Laserlight. So just so that I understand, does this mean that I open the Database connection once, when the user first enters the site, and never again? Am I opening a new database connection on each page load at the moment?

laserlight

ashleyhall wrote:
So just so that I understand, does this mean that I open the Database connection once, when the user first enters the site, and never again? Am I opening a new database connection on each page load at the moment?

Opening a database connection once per page load is fine.

_sh

laserlight;10904699 wrote:
Opening a database connection once per page load is fine.

Thanks for your reply, but now I'm confused - are you basically saying that what I'm doing at the moment fine then?

laserlight

Yes.

_sh

Thanks!