picture of Peter Moulding
You have a web site with pages accessing a database. You want to use PHPLIB, but do not want to change your existing database to suit. Here is the answer to your dreams. PHPLIB and multiple databases.
This set up requires an extension to PHPLIB and explains how to create the extension. You may find this article helps you extend PHPLIB in other ways. Read this article to the end and then think about situations where PHPLIB performs 98% of what you want.
The PHPLIB extension in this article was submitted to the PHPLIB developers. Therefore, it may appear in a future PHPLIB release. The rest of the code goes in your pages and will help you organize your database management.

Database Management

You can put every table in one giant database. However, one day it will bite you. Database management will minimize future pain. What happens when your database grows too big for one server? What happens when a server cannot cope with the IO workload or does not have enough memory for all accesses? Splitting your existing database is hard. Starting with separate databases is easier and good database management helps.
If you run a book shop, you might have a list of authors, a list of books with prices, a list of current stock and a list of orders. As your business grows, the list of orders grows and each order creates a lot of disk activity. Chances are you will one day put the orders direct in to an accounting system.
Start off now with the orders in a separate database. As stock quantities are updated by the orders, put the stock quantities in the same database.
The list of authors and the list of books have static information that is read often, but rarely updated. In fact, the only update to an author record might be once each 5 years, when the author writes a new book (or dies). The data might suit a server with a completely different configuration to the orders database.

Including PHPLIB

PHPLIB accesses SQL databases through a class named DB_Sql. You include the version for your database in your code. I use the MySQL version in this example.
To get DB_Sql in to your code, install the PHPLIB files in their own directory. Then, find your cgi-bin directory and create the phplib directory next to the cgi-bin directory. Next, copy all the PHPLIB .inc files in to the phplib directory. Finally, put the phplib directory in to the php.inc file in the line starting with include_path =.
The include_path is where PHP grabs files named in include() or require(). On my NT workstation, the include path is...
 include_path = ".;i:/project52/includes;i:/project52/phplib";
On the Linux production machine, it is...
 include_path = ".;/home/httpd/includes;/home/httpd/phplib";
 
At the top of each PHP page is...

<?php

require(common.php3);

?>
common.php3 is in the includes directory and contains all the data and functions common to every page. In common.php3, are...

<?php

require(db_mysql.inc);
require(
ct_sql.inc);
require(
session.inc);
require(
auth.inc);
require(
perm.inc);
require(
user.inc);
require(
page.inc);

?>
Read the PHPLIB documentation at http://phplib.netuse.de and the fine articles at http://www.phpbuilder.com to find out which includes you need. Db_mysql.inc contains the DB_Sql class definition. If you want to use PostGreSQL instead of MySQL, include db_pgsql.inc instead of db_mysql.inc. There are 10 .incs covering MS SQL, Oracle, Sybase and others.
*Note that for this example, require() and include() are exactly the same. Require() and include() do work differently and have different results when used in the middle of your code or in if() statements.

Expanding PHPLIB

PHPLIB accesses databases through an object created from class DB_Sql. Db_mysql.inc includes the DB_Sql class as modified for MySQL. We will extend DB_Sql by adding code to common.php3, after the line that includes db_mysql.inc.
DB_Sql contains many functions to perform queries. The one we want to change is:

<?php

 
/* public: connection management */
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 ( == $this->Link_ID ) {
         
$this->Link_ID=mysql_pconnect($Host$User$Password);
        if (!
$this->Link_ID) {
            
$this->halt("pconnect($Host, $User, \$Password) failed.");
            return 
0;
        }
 
        if (!@
mysql_select_db($Database,$this->Link_ID)) {
            
$this->halt("cannot use database ".$this->Database);
            return 
0;
        }
    }
 
    return 
$this->Link_ID;
}

?>
Find the connect() function in your db_mysql.inc (or the .inc for your database), then copy it in to common.php3 somewhere after the include of db_mysql.inc. You will have to wrap it in a class definition as described at the end of this article.
I find the code hard to read. Therefore, the first thing to do is make the copied code readable:

<?php

/* public: connection management */

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 ( == $this->Link_ID ) {
        
$this->Link_ID=mysql_pconnect($Host$User$Password);
        if (!
$this->Link_ID) {
            
$this->halt("pconnect($Host, $User, \$Password) failed.");
            return 
0;
        }
        if (!@
mysql_select_db($Database,$this->Link_ID)) {
            
$this->halt("cannot use database ".$this->Database);
            return 
0;
        }
    }
    return 
$this->Link_ID;
}

?>
I indented the code so the levels let me match the brackets, with the enclosed code. This avoids errors caused by missing brackets. I added brackets to single lines. PHP lets you get away without brackets around single lines of code after if statements. As soon as you add extra code, the shortcut fails. I suggest always use the brackets to avoid errors when you add code later.
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_newdb_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 ( == db_connect()) {
        
$this->Link_ID db_connect($Host$User$Password);
        if (!
$this->Link_ID) {
            
$this->halt("pconnect($Host, $User, \$Password) failed.");
            return 
0;
        }
    }
    if (
!= 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.
Changing the code to handle multiple databases is more complex. You have to change the query functions, as well as the connect and select. You might want to read up on PHP's ODBC connection, then use the ODBC option in PHPLIB. ODBC handles many databases in a generic way that may be a little slower. ODBC lets you use the same code on multiple types of databases. If you do use multiple database types, you can have problems with dates requiring different formats and generally weird differences between the databases. ODBC simplifies the connection, but does not fix the way databases interpret data and SQL.
Now for a quick lesson on redefining object classes. The connect() function is wrapped in a class definition:

<?php

class DB_Sql {

}

?>
When we copy the function in to common.php3, we need to redefine the DB_Sql class. We do that by wrapping connect() in:

<?php

class db_DB_Sql extends DB_Sql {

}

?>
Have a look at the PHP documentation on objects and classes to see what "extends" does. The "25 words or less" summary is: Everything in the extended definition replaces and overrides everything in the original definition.
Now to use db_DB_Sql. When you set up PHPLIB, you will have a statement that says:

<?php

$x 
= new DB_Sql;

?>
Change it to:

<?php

$x 
= new db_DB_Sql;

?>
That will use the modified class, instead of the original.
You are now an expert on objects, classes, OOP and can demand $10,000 more per year.
We made an effective change with the minimum impact on the PHPLIB code. Keep track of the changes, so you can reapply them to a new release of PHPLIB. If you have errors with database access, you can place print statements in the external functions to see when connections are made. You can now do a lot more, without changing the PHPLIB code.
If the SQL seems to fail, you can copy the query() function from DB_Sql in db_mysql.inc to the db_DB_Sql in common.PHP3, then insert a print statement to see the SQL in use.

PHPLIB writes cookies. A print statement in the middle of PHPLIB may produce error messages about problems writing HTTP headers. Ignore the errors or write the diagnostic messages to a disk file. Start with:
 $db_log_file = "t:/diag.txt";
...or similar. Point to a text file somewhere on your disk. In Windows, make sure you use a directory that exists or you will get a weird error.
Now define:

<?php

function db_log($db_log_message) {
    
globals $db_log_file;
    
$db_log_f fopen($db_log_file"a");
    
fwrite($db_log_fdate("Y m d H:i:s")." ".$db_log_message."\r\n");
    
fclose($db_log_f);
}

?>
Anytime you need to see what is happening, add log messages like:

<?php

db_log
("current database: " db_database());

?>
There are some built in logging techniques and system logs you can use. You may not have access to the right directories and may have to search large files to find a tiny piece of information. This separate log gives you some control during testing. I recommend it before and after logs like:

<?php

db_log
("current database: " db_database());
db_database("bookcatalogue");
db_log("current database: " db_database());

?>
Remember to use the right database in your database accesses so you do not query the PHPLIB database. You might like to create a wrapper function for the database query function or change the function you use. If you use mysql_query(), you can use db_database() first. You can also replace:

<?php

db_database
("bookcatalogue");
$result mysql_query("select * from?"db_connect());

?>
with

<?php

$result 
mysql_db_query(db_database("bookcatalogue"), "select * from?",
db_connect());

?>
which suggests the function:

<?php

function db_query($db_query_database$db_query_sql) {
    return(
mysql_db_query(db_database($db_query_database), $db_query_sql,
    
db_connect());
}

?>
Now you can
Practice them in reverse order. Get the log file working, then the diagnostic check, then one class extension and then go wild.
Happy coding!
-- Peter