mysql_connect vs mysql_pconnect

[IamStang]

I have developed a script that needs to make several calls to a database. I originally wrote it using mysql_connect. After looking everything over, I realized that one page load might have to open a database connection 5 or more times. Thus I went in search of a "better" method.

After reading up on mysql_pconnect, I backed up my files and rewrote the script with one single mysql_pconnect at the beginning and tested it. I know that this opens a "persistant" connection and that there are some issues when using persistant connections. So far, I haven't had any issues arise from the change, but the script is not being used by more than a few people at a time during testing.

Now, I just had a look at my php.ini file and noticed that the "mysql.allow_persistent" is set to off. Yet, the script still works fine.

Thus, my question. If mysql_pconnect works, even if mysql.allow_persistent is set to off, why write any other way? One "open database call" as apposed to several throughout one script seems more efficient to me?

Thoughts and suggestions are greatly apprecieated.

IamStang

[FalseDawn]

A persistent connection will not automatically be closed when the script ends, but a regular concection will.

My guess is that the mysql_pconnect in your case is acting like a regular connect, as you have the "allow persistent connections" set false.

A regular connection is persistent for a script's duration and would work fine in your situation anyway, there was no need to use pconnect.

There's really very few situations where you'd want to use pconnect.

[IamStang]

FalseDawn wrote:

"A regular connection is persistent for a script's duration and would work fine in your situation anyway, there was no need to use pconnect."

Thank you very much FalseDawn, but this brings me to another question.

If a regular connection is persistent for the duration of a script, why is it I get errors when making a query inside a function if I do not open the connection inside the function itself?

for example and shortened greatly:

EXAMPLE 1
<?PHP
include db_config.php;
include opendb.php;
$sql = "SELECT * FROM users WHERE name='$name'";
$tmp = @mysql_query($sql, $conn);
$dbaddy = mysql_result($tmp, 0, "addy");
echo $dbaddy;
whatever($name);
include close_db.php;

function whatever($name){
$query = "SELECT * FROM data WHERE name='$name'";
$dat = @mysql_query($query, $conn);
$dbinfo = mysql_result($query, 0, "info");
echo $dbinfo;
}

EXAMPLE 2
<?PHP
include db_config.php;
include opendb.php;
$sql = "SELECT * FROM users WHERE name='$name'";
$tmp = @mysql_query($sql, $conn);
$dbaddy = mysql_result($tmp, 0, "addy");
echo $dbaddy;
whatever($name);
include close_db.php;

function whatever($name){
include db_config.php;
include opendb.php;
$query = "SELECT * FROM data WHERE name='$name'";
$dat = @mysql_query($query, $conn);
$dbinfo = mysql_result($query, 0, "info");
echo $dbinfo;
}

I get errors when using something similar to example 1. However, if I add the includes into the function itself, as in example 2, it works fine. As I have several functions throughout the script, this causes me to open and close the connections several times each page load. Thus the reason for my original question.

Thanks again!
IamStang

[jatar_k]

sounds more like a variable scope problem

a connection opened at the top of a script should be globally available even to functions. Your problem is something aside from the connect type. You could also pass the connection to your function as opposed to including the same files twice.

[FalseDawn]

a connection opened at the top of a script should be globally available even to functions

I don't know if this is true or not - in fact, I think it's likely to be false, unless the variable has global scope.

As an alternative to passing $conn to your function, you could declare it as a global, viz:
global $conn;

both in your include file, and any functions where you'd need to access it.

Edit: But yes, there's no need to be using your DB include file so many times - especially not in functions like that.

[coopster]

Yes, you will need to make an existing connection available inside your function(s) as they will be out of scope otherwise. I'll often check the connection and if it is there, use it otherwise make it. Using a ternary operation:

function myfunction() 
{ 
 global $my_db_conn; 
 $my_db_conn = (isset($my_db_conn) && $my_db_conn) ? $my_db_conn : my_connection_function(); 
 // etc ... 
}

[IamStang]

Thanks everyone. Will take this info and run with it.

Looks like I will revert back to connect instead of pconnect. Seems like they could come up with a better way of handling this "problem". Who knows? Maybe in a future release?

Could I not just pass the connection to the function? Something like, using the example above:

<?PHP
include db_config.php;
include opendb.php;
$sql = "SELECT * FROM users WHERE name='$name'";
$tmp = @mysql_query($sql, $conn);
$dbaddy = mysql_result($tmp, 0, "addy");
echo $dbaddy;
whatever($conn,$name);
include close_db.php;

function whatever($conn,$name){
$query = "SELECT * FROM data WHERE name='$name'";
$dat = @mysql_query($query, $conn);
$dbinfo = mysql_result($query, 0, "info");
echo $dbinfo;
}

Or would I still need to open the db connection inside the function? Guess I will test it with this method. If it works, great. If not, I will open it inside the function.

Again, thank you fr the help/comments!
IamStang

[coopster]

Could I not just pass the connection to the function?

Yes, the way you did it is exactly what jatar_k and FalseDawn were referring to when they said you could pass the connection variable to your function. An alternative is the *global* variable use solution mentioned and demonstrated. Either way, the variable needs to have scope inside the function.

[IamStang]

Thanks again all!

All help is greatly appreciated.

IamStang