PHPLIB is some extension libraries of PHP. We can use it to easily perform various operations on the database. However, if you want to use multiple databases, it will be beyond your capabilities. This article introduces how to extend PHPLIB to allow you to use it. You have the best of both worlds. You can use multiple databases while using PHPLIB, and you can also learn about object-oriented programming and how to extend the library. It is worth reading.
Database Management
You can put any table in a large database. However, over time, the database will become larger and larger. The server may not be able to keep up with the IO work, or may not have enough memory to cope with all accesses? It is very difficult to separate existing data. It is wise to start with separate databases and implement efficient database management. If you have a website that sells books, you probably have a list of authors, a list of book prices, and a list of current inventory and orders. As your business grows, orders will continue to grow, and processing each order requires a lot of disk access. Most likely you will put all your orders into an accounting system at some point.
Now put the orders into a separate database. Since inventory is also updated via orders, inventory quantities are also placed in the same database.
The list of authors and the list of books are static information that must be read frequently but rarely updated. In practice, updating an author's record may only need to be done once every 5 years, only when the author writes a new book (or dies). The server hosting this data can be configured completely differently than the server hosting the order database.
Contains PHPLIB
PHPLIB accesses SQL databases through a class called DB_Sql. Depending on the type of database you need to use, include different inc files in your code. In this example I'm using the MySQL version.
In order to use DB_Sql in 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 PHPLIB .inc files to the phplib directory. Finally, modify the php.inc file, just change the "include_path=" line to the phplib directory.
include_path is the directory that PHP searches for when using include() or require(). In my NT workstation, the include path is:
include_path = ".;i:/project52/includes;i:/project52/phplib";
in Linux On your system,
addinclude_path = ".;/home/httpd/includes;/home/httpd/phplib";
at the top of each PHP page
<? php
require(common.php);
? >
common.php3 is placed in the includes directory and contains all data and functions used by each page. In this example common.php is:
<? php
require(db_mysql.inc);
require(ct_sql.inc);
require(session.inc);
require(auth.inc);
require(perm.inc);
require(user.inc);
require(page.inc);
? >
If you want to know the purpose of each inc file, you can read the PHPLIB documentation at http://phplib.netuse.de . Db_mysql.inc contains the definitions of all DB_SQL classes. If you want to use PostGreSQL instead of MySQL, just use db_pgsql.inc instead of db_mysql.inc. There are 10 other .inc files for use with MS SQL, Oracle, Sybase or other databases.
Note that in this example, require() and include() are exactly the same. However, if placed in code or used in an if statement, the use of Require() and include are completely different and have different running results.
Extending PHPLIB
PHPLIB accesses the database through an object generated by the DB_Sql class. Db_mysql.inc contains the DB_Sql class modified for MySQL. We will extend DB_sql by adding code to common.php after the line containing db_mysql.inc.
DB_Sql contains many functions for querying. What we need to modify is:
<? php
/* public: connection management*/
function connect($Database = "", $Host = "", $User = "", $Password = "") {
/* Handle default connection */
if ("" == $Database)
$Database = $this->Database;
if ("" == $Host)
$Host = $this->Host;
if ("" == $User)
$User = $this->User;
if ("" == $Password)
$Password = $this->Password;
/* Establish connection and 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 other database-related .inc files), then copy it to common.php and put it behind the code that contains db_mysql.inc. At the end, you must also add it Encapsulated as a class definition.
I found the code a bit hard to read, so I first made the copied code more readable:
<? php
/* public: connection management*/
function connect($Database = "", $Host = "", $User = "", $Password = "") {
/* Handle default connection */
if ("" == $Database) {
$Database = $this->Database;
}
if ("" == $Host) {
$Host = $this->Host;
}
if ("" == $User) {
$User = $this->User;
}
if ("" == $Password) {
$Password = $this->Password;
}
/* Establish connection and 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 adjusted the position of the brackets and added a brace before and after the single line. In PHP's if statement, you don't need parentheses if there is only one line of code, but if you add one more line of code, an error will occur immediately. Therefore I suggest you add a bracket to avoid errors when adding code later.
Before changing the connect code, you must first understand how connect() works. It checks whether a connection currently exists. If there is no connection, it creates a connection. Before each database query, first run this connect() function. Unfortunately, it only selects the database when connecting for the first time. If your PHP page uses more than one database, connect() will not select another database.
There are a few different ways to change the code. We need to choose a method that has the least impact on PHPLIB and allows us to display the database connection status when we need to analyze the problem. We need to save the connection id and database name outside of PHPLIB. Just add in common.php:
<? php
$db_connection = 0; // ID of database connection
$db_database = ""; // Current database status? >
Next, we will modify PHPLIB to store the connection id and database name in these variables. You can set and use the same variable name in other code. When analyzing the problem, if you need to know which database is being used, just insert the following code into the page:
<? php
Print(" db_database: " . $db_database . "");
? >
How can we make connect() use these new variables? We can add a line at the top:
<? php
{
globals $db_connect, $db_database;
/* Handle defaults */
? >
Through these codes, the new variables can be accessed by connect().
After defining $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);
}
? >
As long as you define these public functions once, you can use these public variables in different places without adding global declarations. The following are the public functions using the above db function:
<? php
function connect($Database = "", $Host = "", $User = "", $Password = "") {
/* Handle default connection */
if ("" == $Database) {
$Database = $this->Database;
}
if ("" == $Host) {
$Host = $this->Host;
}
if ("" == $User) {
$User = $this->User;
}
if ("" == $Password) {
$Password = $this->Password;
}
/* Establish connection and 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;
}
? >
Pay attention to the following changes:
The test of the database is separated from the test of the connection, so that even if connect() has a current connection, it can still check whether to change to another database. This means that db_connect() compares to 0 twice as often as before, but this extra processing is necessary.
We keep the database connection and database selection outside of PHPLIB so you can use the same database selection function anywhere in your PHP code.
However, there is a limitation of the current processing. Here we assume that the same host, user and password are used for all databases. If your database has different permissions for different users, you must establish a special connection to access it. How? Just define the following variables:
<? php
$db_host = "";
$db_user = "";
$db_pass = "";
? >
By extending the db_database() function, compare the current user and host with a certain user and host. You can also add:
<? php
$db_type = "";
? >
This variable is used to store the type of database, mysql or Oracle, etc. This way you can access multiple databases.
But changing the code to handle multiple different types of databases is quite complicated. You must also change the query function, as well as the join and selection functions. You might be able to connect via PHP's ODBC and then use PHPLIB's ODBC options to handle it. ODBC handles multiple databases in a common way, so it will be slower. ODBC allows you to use the same code to handle multiple different types of databases. But there will be problems when dates in different processing formats need to be used, and there will also be some strange differences between databases. ODBC only simplifies the connection, but does not modify the way the database interprets data and SQL.
Now let's learn how to redefine an object class. The connect() function is encapsulated into a class definition:
<? php
class DB_Sql {
}
? >
When we copy this function to common.php, we must redefine the DB_Sql class. We can encapsulate connect() like this:
<? php
class db_DB_Sql extends DB_Sql {
}
? >
To learn more about the working of "extends", we can take a look at the section on objects and classes in the PHP documentation. Simply put: any definition in the extension replaces and overrides all previous definitions.
db_DB_Sql can now be used. When you configure PHPLIB, you make the following statement:
<? php
$x = new DB_Sql;
? > Change it to: <? php
$x = new db_DB_Sql;
? >
This way you can use the modified class instead of the previous one.
When an error occurs when connecting to the database, you can output the current connection status in an external function. If an error occurs in the SQL statement, you can also copy the query() function in DB_Sql to db_DB_Sql in common.PHP, and then insert an output statement to see what the current SQL statement is.
You can also write error or diagnostic information to a disk file. By defining
$db_log_file = "t:/diag.txt";
or a similar text file. If using Windows, you need to make sure the directory exists, otherwise you will get an error message.
Then define a function:
<? 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 md H:i:s")." ".$db_log_message."rn");
fclose($db_log_f);
}
? >
Where you need to record information, add the following code:
<? php
db_log("current database: " . db_database());
? >
Actually you can use built-in or system log files. But then you have to find a small piece of information in a lot of files. So this separate log file helps you with testing. I suggest writing the following code before and after recording:
<? php
db_log("current database: " . db_database());
db_database("bookcatalogue");
db_log("current database: " . db_database());
? >
When accessing data, remember to use the correct database, not the database defined in PHPLIB. You can create a wrapper function for the database, or change the function you use. If you use mysql_query(), you can use db_database() first, you can use
<? 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());
}
? >
instead of
<? php
db_database("bookcatalogue");
$result = mysql_query("select * from?", db_connect());
? Now
you can do it
. Use PHPLIB (or similar software) to access multiple databases
.extend class/object
.Insert diagnostic tests
.Create log files