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 ( 0 == $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 ( 0 == $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_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.
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_f, date("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
- use PHPLIB (and any similar software) with multiple databases
- extend classes/objects
- insert diagnostic checks
- write a log of anything to a file
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
-- Peter