Use PHPLIB to access multiple databases

Use PHPLIB to access multiple databases. read about using PHPLIB to access multiple databases. PHPLIB is an extension library of PHP. using it, we can easily perform various operations on the database. However, if you want to use multiple databases, it will not be enough. This article introduces how to extend PHPLIB to give you both fish and bear's paw, while using PHPLIB, "> <LINKhref =" http: // w

PHPLIB is an extension library of PHP. we can use it to perform various operations on the database. However, if you want to use multiple databases, it will not work, this article describes how to extend PHPLIB so that you can have both fish and bear's paw. you can use multiple databases while using PHPLIB, in addition, you can also learn about object-oriented programming and how to expand the library, which is worth reading.

Database management

You can put any table in a large database. However, after a long time, the database will become larger and larger, and the server may not be able to keep up with the I/O work, or will not have enough memory to cope with all the accesses? It is very difficult to separate existing data. It is wise to use separate databases at the beginning and perform effective database management. If you have a website for selling books, you may have a list of authors, a list of book prices, and a list of current inventory and orders. As your business continues to grow, orders will continue to grow, and processing each order requires a lot of disk access. It is very likely that you will put all the orders in an accounting system one day.

Now, place the order in an independent database. Because the inventory is updated through the order, the inventory is also placed in the same database.

The author's list and book list are static information that must be read frequently but rarely updated. In fact, updating an author's record may only take 5 years, only when the author writes a new book (or dies. The configuration of the server where the data is stored can be completely different from that of the server where the order database is placed.

Including PHPLIB

PHPLIB accesses the SQL database through a class called DB_ SQL. Include different inc files in your code based on the database type you need. In this example, I use the MySQL version.

To use DB_ SQL in your code, install the PHPLIB files in their own directories. 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 and change the "export de_path =" line to the phplib directory.

Include_path is the directory found when PHP uses include () or require (). in my NT workstation, the include path is:

Include_path = ".; I:/project52/includes; I:/project52/phplib ";

On a Linux system

Include_path = ".;/home/httpd/shortdes;/home/httpd/phplib ";

Add at the top of each PHP page
<? Php
Require (common. php );
?>
Common. php3 is placed in the "des" directory, which contains all the data and functions required for 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 usefulness of each inc file, read the PHPLIB documentation on the http://phplib.netuse.de. Db_mysql.inc contains the definitions of all DB_ SQL classes. If you want to use PostGreSQL instead of MySQL, you only need to use db_pgsql.inc instead of db_mysql.inc. There are 10 other. inc files, which can be ms SQL, Oracle, Sybase, or other databases.

In this example, require () and include () are identical. However, if it is stored in the code or used in the if statement, the use of Require () and include is completely different and has different running results.

Extend 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 in common. php, which will be added after the line containing db_mysql.inc.

DB_ SQL contains many functions used for query. what we want to modify is:

<? Php
/* Public: connection management */
Function connect ($ Database = "", $ Host = "", $ User = "", $ Password = ""){
/* Process the default connection */
If ("" = $ Database)
$ Database = $ this-> Database;
If ("" = $ Host)
$ Host = $ this-> Host;
If ("" = $ User)
$ User = $ this-> User;
If ("" = $ Password)
$ Password = $ this-> Password;

/* Create a connection and select a 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 (Link_ID ">! @ Mysql_select_db ($ Database, $ this-> Link_ID )){
$ This-> halt ("cannot use database". $ this-> Database );
Return 0;
}
}

Return $ this-> Link_ID;
}
?>

In your db_mysql.inc (or related to other databases. in the inc file), find the connect () function and copy it to common. php, put it behind the code containing db_mysql.inc, and then encapsulate it as a class definition.

I found the code somewhat unreadable. Therefore, we should first make the copied code more readable:

<? Php
/* Public: connection management */
Function connect ($ Database = "", $ Host = "", $ User = "", $ Password = ""){
/* Process the default connection */
If ("" = $ Database ){
$ Database = $ this-> Database;
}
If ("" = $ Host ){
$ Host = $ this-> Host;
}
If ("" = $ User ){
$ User = $ this-> User;
}
If ("" = $ Password ){
$ Password = $ this-> Password;
}
/* Create a connection and select a 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 (Link_ID ">! @ Mysql_select_db ($ Database, $ this-> Link_ID )){
$ This-> halt ("cannot use database". $ this-> Database );
Return 0;
}
}
Return $ this-> Link_ID;
}
?>

I adjusted the brackets and added a braces before and after a single row. In the PHP if statement, you do not need to use parentheses if there is only one line of code. However, if you add more lines of code, an error will occur immediately. Therefore, we recommend that you add a bracket to avoid errors when you add code later.

Before changing the connect code, you must first understand how connect () works. it checks whether a connection exists. If no connection exists, it creates a connection. Run the connect () function before each database query. Unfortunately, it only selects the database when connecting for the first time. if your PHP page uses more than one database, connect () does not select another database.

There are several different methods to change the code. We need to select 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 PHPLIB. Add the following content to common. php:

<? Php
$ Db_connection = 0; // Database Connection id
$ Db_database = ""; // Current database status
?>

Next, we will modify PHPLIB to store the connection id and database name in these variables. In other code, you can set and use the same variable name. When you analyze the problem, if you need to know which database you are using, just insert the following code in 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 */
?>

With this code, new variables can be accessed by connect ().

After $ db_database is defined, 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 );
}
?>

Just define these public functions once, you can use these public variables in different places without adding global declarations. The following are the common functions that use the preceding db functions:

<? Php
Function connect ($ Database = "", $ Host = "", $ User = "", $ Password = ""){
/* Process the default connection */
If ("" = $ Database ){
$ Database = $ this-> Database;
}
If ("" = $ Host ){
$ Host = $ this-> Host;
}
If ("" = $ User ){
$ User = $ this-> User;
}
If ("" = $ Password ){
$ Password = $ this-> Password;
}
/* Create a connection and select a 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 database test is separated from the connection test, so that even if connect () has a current connection, you can also check whether you want to replace it with another database. This means that db_connect () and 0 are compared twice as many as before, but this additional processing is necessary.
We place the database connection and database selection outside PHPLIB, so that you can use the same database selection function anywhere in the PHP code.
However, the current processing has a limit. 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 to do it? You only need to define the following variables:

<? Php
$ Db_host = "";
$ Db_user = "";
$ Db_pass = "";
?>

By extending the db_database () function, you can compare the current user and the host with a user and the host. You can also add:

<? Php
$ Db_type = "";
?>

This variable is used to store database types, such as mysql or Oracle. In this way, you can access multiple databases.

However, it is quite complicated to change the code to process multiple different types of databases. You must also change the query function, and connect and select the function. You may use php odbc to connect and then use the ODBC option of PHPLIB for processing. ODBC processes multiple databases in a common way, so it will be slower. ODBC allows you to use the same code to process multiple different types of databases. However, there will be problems when different processing formats are used, and there will be some strange differences between databases. ODBC 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 the definition of a class:
<? Php
Class DB_ SQL {
}
?>

When we copy this function to common. php, we must redefine the DB_ SQL class. we can encapsulate connect () as follows ():
<? Php
Class db_DB_ SQL extends DB_ SQL {
}
?>

To learn more about "extends", let's take a look at the object and class section in the PHP document. Simply put, any definition of the extended part replaces and overwrites all previous definitions.

You can use db_DB_ SQL. When you configure PHPLIB, you need to make the following statement:

<? Php
$ X = new DB_ SQL;
?> Change it to: <? Php
$ X = new db_DB_ SQL;
?>

In this way, you can use the modified class instead of the previous class.

When an error occurs during database connection, you can output the current connection status in an external function. If an error occurs in an SQL statement, you can copy the query () function in DB_ SQL to db_DB_ SQL in common. PHP, and insert an output statement to see what the current SQL statement is.

You can also write errors or diagnostic information to a disk file. By defining

$ Db_log_file = "t:/diag.txt ";

Or a similar text file. If you use Windows, make sure that 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, "");
Fwrite ($ db_log_f, date ("Y m d H: I: s"). "". $ db_log_message. "rn ");
Fclose ($ db_log_f );
}
?>

Add the following code to record information:

<? Php
Db_log ("current database:". db_database ());
?>

In fact, you can use built-in or system log files. However, you need to find a small piece of information in a lot of files. Therefore, this independent record file can be used for testing. We recommend that you write the following code before and after the record:

<? 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 instead of the database defined in PHPLIB. You can create an encapsulated function for the database or change the function you are using. 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 ());
}
?>

To replace

<? Php
Db_database ("bookcatalogue ");
$ Result = mysql_query ("select * from? ", Db_connect ());
?>

Now you can do

. Use PHPLIB (or similar software) to access multiple databases
. Extension class/object
. Insert diagnosis check
. Create a log file