Multiple connections to mysql database with php

Question

Let's say I call multiple functions on a given page.

function getProfile {
 <mysql code to connect>
 ...
 ...
}
function getMatchingUsers {
 <mysql code to connect>
 ...
 ...
}

If I call the two functions above on a given page, wouldn't that open multiple connections to the DB? I assume php would automatically close the DB connection after the first function runs, but then it would automatically open another connection for the second function. My question is, what if my page makes a bunch of calls to the DB. Is this good for memory, io, etc...?

Answer 1

Why not use some form of connection pooling. That way the pool maintains a set of connections, and your app is merely taking connections from the pool and returning them. The pool looks after opening and closing as required (and maintains a set of open connections).

This SO answer covers more options wrt. PHP and connection pooling.

Answer 2

The simplest way is to make the connection at the beginning of your script. The mysql functions will use the last open connection to do their work, so there is no need to connect inside each function.

//Start of your file
mysql_connect()
function a(){
// no connect here, just use mysql
mysql_query($sql)
}
function b(){
// no connect here, just use mysql
mysql_query($sql)
}

• sdot257: that was obvious but i couldnt think of it! thanks ... any gotchas w/ this approach?
• Daniel Von Fange: No downsides. It does only work when you are working with just one database - but that's what you do 98% of the time.

Answer 3

Try to use 1 connection if you are conneciting to the same mysql server.

$host = "localhost";
$user = "user";
$pass = "password";
$database = "dbname";
$linkID = mysql_connect($host, $user, $pass) or die("Could not connect to host.");
mysql_select_db($database, $linkID) or die("Could not find database.");

Note that you have a $linkID and that's what you use to reference it. Make a connection specific query like this:

$resultID = mysql_query($query, $linkID) or die("Data not found.");

When you are finished, close that specific connection

mysql_close($linkID)

If you have more than 1 mysql sever addresses, then use a separate reference like $linkID2