PHPLib and Multiple Databases Page 4

Now, for the connect code change. Notice how the connect() code checks the
existence of a connection and creates the connection if the connection does
not exist. This connect() function is run before every database query.
Unfortunately, it only selects the database once when it makes the
connection. If your PHP pages use more than one database, the connect()
code will not pick up the change of database.

There are several ways to change the code. We are looking for the change
that has the least impact on PHPLIB and lets us display the status of the
database activity, if we need to diagnose a problem. The two things we need
outside PHPLIB are the connection id and the database name. Therefore, make both
external to PHPLIB. In common.php3:

<?php
$db_connection = 0; // Common database connection id.
$db_database = ""; // Name of current database.
?>

Next, we change PHPLIB to store the connection id and database name in these
fields. The rest of your code can set and use the same fields. If you need
to find which database is in use when diagnosing a problem, insert this in
your page:

<?php
Print("<p>db_database: " . $db_database . "</p>");
?>

(There are shorter ways to write the print line. This way highlights the
variable name in editors that have colour coding. It also works reliable
with arrays and other compound variable names.)

How do we get connect() to use our new variables? We could add an extra
line near the top so you have:

<?php
{
    globals $db_connect, $db_database;
    /* Handle defaults */
?>

The extra line makes our extra variables available within connect().

Here is a more sophisticated way. Straight after the definition of
$db_database, add:

<?php
function db_connect($db_connect_host="", $db_connect_user="",$db_connect_pass="") {
    globals $db_connect;
    if(!empty($db_connect_host)) {
        $db_connect = mysql_pconnect($db_connect_host, 
            $db_connect_user, $db_connect_pass);
    }
    return($db_connect);
}
function db_database($db_database_new="") {
    globals $db_database;
    if(!empty($db_database_new)) {
        $db_database = @mysql_select_db($db_database_new, db_connect());
    }
    return($db_database);
}
?>

By defining these common functions once, you can get the common variables
in all sorts of places, without having to add the globals line all over
the place. Here is the common() function using our db functions:

<?php
function connect($Database = "", $Host = "", $User = "", $Password = "") {
    /* Handle defaults */
    if ("" == $Database) {
        $Database = $this->Database;
    }
    if ("" == $Host) {
        $Host     = $this->Host;
    }
    if ("" == $User) {
        $User     = $this->User;
    }
    if ("" == $Password) {
        $Password = $this->Password;
    }
 /* establish connection, select database */
    if ( 0 == db_connect()) {
        $this->Link_ID = db_connect($Host, $User, $Password);
        if (!$this->Link_ID) {
            $this->halt("pconnect($Host, $User, $Password) failed.");
            return 0;
        }
    }
    if (0 != db_connect()) {
        if($Database != db_database()) {
            $this->Database = db_database($Database))
            if(empty($this->Database)) {
                $this->halt("cannot use database " . $this->Database);
                return 0;
            }
        }
    }
    return $this->Link_ID;
}
?>

Note the slight changes.

The test for the database is taken outside the test for the connect so that
connect() can check for a new database, even when there is a current
connection. It means we compare db_connect() to 0 twice as often. The result is worth the
slight extra processing.

We place the database connection and the database selection outside of
PHPLIB, so we can use the same functions for database selection everywhere
else in your PHP code.

There is only one disadvantage at this stage: We assume the same host, user
and password for all database accesses. If you have a user logging on, then
accessing a special database with special privileges, you will have to set
up a special connection for that access. How? Define variables:

<?php
$db_host = "";
$db_user = "";
$db_pass = "";
?>

Expand the db_database() function to compare the current user and host to
the special user and host. You could also add:

<?php
$db_type = "";
?>

…and store in it the type of database, mysql, Oracle, etc. so you can access
multiple databases.