Apologies in advance for the long post that follows. I tried searching the forum first, but couldn't seem construct a search specific enough to get at my exact question, or maybe it hasn't been asked here before...
Anyway, here is my question, with examples to follow: What is the best way to manage a the connection to a single datasource when you need to use the connection for more than one query (insert, update, etc.) on the same page? Is it more efficient to declare a connection variable with global scope, then refer to that global variable inside each function that needs the connection, then do mysql_close() after everything else (example 1)? Or is it better to put all the mysql_connect() stuff in a function that returns the connection variable, and open and close the connection for each query (example 2)? The datasource is on the same box as the webserver, and there doesn't seem to be significant expense to making a connection.
Here are the examples:
//Example 1
//declare, open con; leave open; use global $con throughout
$con = mysql_connect('localhost','someuser','somepass');
mysql_select_db('my_db',$con);
//first database query
function query_1(){
global $con;
$ins_str = "INSERT INTO tbl_1..." //Some SQL INSERT
$result = mysql_query($ins_str,$con);
//con gets left open...
}
//second database query
function query_2(){
global $con;
$qry_str = "SELECT * FROM..." //Some SQL SELECT
$result = mysql_query($qry_str,$con);
//close con - we no longer need it
mysql_close($con);
}
//example 2
//use a function to return a database
//connection every time we need it
function get_con(){
$con = mysql_connect('localhost','someuser','somepass');
mysql_select_db('my_db',$con);
return $con;
}
//first database query
function query_1(){
$ins_str = "INSERT INTO tbl_1..." //Some SQL INSERT
$result = mysql_query($ins_str,get_con());
mysql_close(get_con()); //should we do this??
}
//second database query
function query_2(){
$qry_str = "SELECT * FROM..." //Some SQL SELECT
$result = mysql_query($qry_str,get_con());
mysql_close(get_con());
}
Both ways raise questions. I've gotten the impression that one shouldn't arbitrarily create global variables when they aren't needed, and also learned in school that one should strive for cohesion by properly grouping operations into functions, so there's the problem with example 1.
I also was taught in school that one should avoid a high level of coupling between functions, which seems to occur in example 2, but perhaps not to any degree that makes it "bad code". My bigger question is what happens when mysql_close(get_con()) occurs given that the function does mysql_connect() when the connection already exists? Is that really bad?
I can see several variations on these two approaches, but didn't want to make this longer than it is, so if anyone wants to chime in with additional examples, please do.
On a final note, after reviewing persistent connections in the PHP Manual, I don't think that's what I'm looking for. There is no need to use the connection after the page unloads, and there is no particular overhead to making connections (the db will always be on the same box).
Thanks in advance.
